Re: MYSQL is getting slow
On Thu, 9 Dec 2004 13:13:10 -0600 Jeff Smelser [EMAIL PROTECTED] wrote: On Thursday 09 December 2004 01:06 pm, Patrick Marquetecken wrote: and for ansewring Jeff Smelser i have installed mysql 4.x on linux and then dit from the commandline create database and then run the create table commands from snort. No idea.. you can do a show create table db.table to find out the engine type.. I ask because those should be myisam.. If they are. I cant see how memory is getting so high. If they are innodb.. I would highly recommend looking into converting them into myisam.. myisam was created for this type of work, would be a easier on the mem. i checked it, and its indeed myisam. Patrick Just some avenues to look at. Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL is getting slow
On Thu, 9 Dec 2004 12:34:36 -0800 Dathan Pattishall [EMAIL PROTECTED] wrote: This is a very broad question, I sometimes do this myself. But to answer I know, but where must i begin ... the question to the best of my ability I have to ask a few. Are you using RAID? If so what RAID level? no raid What are you're my.cnf settings? standaard execpt for the bind address What type of Indexes are you using? On the larged table that has only 2 fields there are primary indexes on both fields. And each table has its ons primary index, and some other normal indexes. What does vmstat and iostat say? NIDS-console distfiles # free total used free sharedbuffers cached Mem:507508 497872 9636 0 2232 399024 -/+ buffers/cache: 96616 410892 Swap: 506036 62384 443652 procs ---memory-- ---swap-- -io --system-- cpu r b swpd free buff cache si sobibo incs us sy id wa 0 2 62384 5044 2380 40163611 251187 522 15 6 79 0 1 0 62384 4412 2392 40229600 451113 1272 1859 17 5 79 0 0 2 62384 4504 1840 40274400 455672 1325 1977 16 8 76 0 2 0 62412 4960 1808 40140800 3959 0 1170 1866 35 13 52 0 What Filesystem are you using? ext3 What are some typical queires for a given schema? I have no idee thats the whole problem, its a propretaire product. Typically for most orginizations mysql dedicated on the box below is wicked fast, even when not optimized because of system cache etc. But if your running out of diskspace then that's a problem in itself. I got a lot of disk space left. -Original Message- From: Patrick Marquetecken [mailto:[EMAIL PROTECTED] Sent: Thursday, December 09, 2004 7:49 AM To: [EMAIL PROTECTED] Subject: MYSQL is getting slow Hi, I have 3 snort sensors logging to a central mySQL database after two weeks the size of the database is about 3.3GB and the machine is getting slow, as i'm not used to be working with mySQL is my question how far can i go before the machine stop responding ? HP Pentium4 - 2.2mhz - 512 mb, 99% used and no swap, HD of 40GB. TIA Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL is getting slow
You are probably a little short of RAM for that size db. Lack of swap may bite you as well. How big are your keys? Pat... - Original Message - From: mos [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, December 10, 2004 10:48 AM Subject: Re: MYSQL is getting slow At 09:49 AM 12/9/2004, Patrick Marquetecken wrote: Hi, I have 3 snort sensors logging to a central mySQL database after two weeks the size of the database is about 3.3GB and the machine is getting slow, as i'm not used to be working with mySQL is my question how far can i go before the machine stop responding ? HP Pentium4 - 2.2mhz - 512 mb, 99% used and no swap, HD of 40GB. TIA Patrick Patrick, Try running Analyze Table to get MySql to rebalance the key distribution. See http://dev.mysql.com/doc/mysql/en/ANALYZE_TABLE.html Make sure you have plenty of disk space and time before running it. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MYSQL is getting slow
What are some typical queires for a given schema? I have no idee thats the whole problem, its a propretaire product. Nothing is proprietary when it comes to mysql, you can turn on the slow query log which is the first thing, do you have it turned on? What is the slow query set to? Are any showing up? If so, then it must be time to add some indexes until they go away. The next thing you can always do is turn on the general query log, and that will pretty much log everything, and then you can see everything that is being done. I've use a similar product that worked with snort in the past, it worked fine for us, but snort is boring. You need to pull the data right from the switch, using something like netflow and logging like 600mb/s of netflow logs to mysql is much more fun! Donny -Original Message- From: Patrick Marquetecken [mailto:[EMAIL PROTECTED] Sent: Friday, December 10, 2004 3:03 AM To: [EMAIL PROTECTED] Subject: Re: MYSQL is getting slow On Thu, 9 Dec 2004 12:34:36 -0800 Dathan Pattishall [EMAIL PROTECTED] wrote: This is a very broad question, I sometimes do this myself. But to answer I know, but where must i begin ... the question to the best of my ability I have to ask a few. Are you using RAID? If so what RAID level? no raid What are you're my.cnf settings? standaard execpt for the bind address What type of Indexes are you using? On the larged table that has only 2 fields there are primary indexes on both fields. And each table has its ons primary index, and some other normal indexes. What does vmstat and iostat say? NIDS-console distfiles # free total used free sharedbuffers cached Mem:507508 497872 9636 0 2232 399024 -/+ buffers/cache: 96616 410892 Swap: 506036 62384 443652 procs ---memory-- ---swap-- -io --system-- cpu r b swpd free buff cache si sobibo incs us sy id wa 0 2 62384 5044 2380 40163611 251187 522 15 6 79 0 1 0 62384 4412 2392 40229600 451113 1272 1859 17 5 79 0 0 2 62384 4504 1840 40274400 455672 1325 1977 16 8 76 0 2 0 62412 4960 1808 40140800 3959 0 1170 1866 35 13 52 0 What Filesystem are you using? ext3 What are some typical queires for a given schema? I have no idee thats the whole problem, its a propretaire product. Typically for most orginizations mysql dedicated on the box below is wicked fast, even when not optimized because of system cache etc. But if your running out of diskspace then that's a problem in itself. I got a lot of disk space left. -Original Message- From: Patrick Marquetecken [mailto:[EMAIL PROTECTED] Sent: Thursday, December 09, 2004 7:49 AM To: [EMAIL PROTECTED] Subject: MYSQL is getting slow Hi, I have 3 snort sensors logging to a central mySQL database after two weeks the size of the database is about 3.3GB and the machine is getting slow, as i'm not used to be working with mySQL is my question how far can i go before the machine stop responding ? HP Pentium4 - 2.2mhz - 512 mb, 99% used and no swap, HD of 40GB. TIA Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL is getting slow
At 09:49 AM 12/9/2004, Patrick Marquetecken wrote: Hi, I have 3 snort sensors logging to a central mySQL database after two weeks the size of the database is about 3.3GB and the machine is getting slow, as i'm not used to be working with mySQL is my question how far can i go before the machine stop responding ? HP Pentium4 - 2.2mhz - 512 mb, 99% used and no swap, HD of 40GB. TIA Patrick Patrick, Try running Analyze Table to get MySql to rebalance the key distribution. See http://dev.mysql.com/doc/mysql/en/ANALYZE_TABLE.html Make sure you have plenty of disk space and time before running it. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQL is getting slow
Hi, I have 3 snort sensors logging to a central mySQL database after two weeks the size of the database is about 3.3GB and the machine is getting slow, as i'm not used to be working with mySQL is my question how far can i go before the machine stop responding ? HP Pentium4 - 2.2mhz - 512 mb, 99% used and no swap, HD of 40GB. TIA Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL is getting slow
On Thursday 09 December 2004 09:49 am, Patrick Marquetecken wrote: I have 3 snort sensors logging to a central mySQL database after two weeks the size of the database is about 3.3GB and the machine is getting slow, as i'm not used to be working with mySQL is my question how far can i go before the machine stop responding ? HP Pentium4 - 2.2mhz - 512 mb, 99% used and no swap, HD of 40GB. What table type you using? Jeff pgpaVttIiLwf1.pgp Description: PGP signature
Re: MYSQL is getting slow
Patrick Marquetecken wrote: Hi, I have 3 snort sensors logging to a central mySQL database after two weeks the size of the database is about 3.3GB and the machine is getting slow, as i'm not used to be working with mySQL is my question how far can i go before the machine stop responding ? HP Pentium4 - 2.2mhz - 512 mb, 99% used and no swap, HD of 40GB. I f that is 99% disk used, you are already dead. That is why it is slow. You have no room to do anything. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL is getting slow
It can be a service that this running and you this saturating the yield of the CPU. top in a terminal like root uses the command to be able to see that service this consuming. greetings -- Ing.. Bismarck J. Rojas M. El Jueves, 9 de Diciembre de 2004 09:49, Patrick Marquetecken escribió: Hi, I have 3 snort sensors logging to a central mySQL database after two weeks the size of the database is about 3.3GB and the machine is getting slow, as i'm not used to be working with mySQL is my question how far can i go before the machine stop responding ? HP Pentium4 - 2.2mhz - 512 mb, 99% used and no swap, HD of 40GB. TIA Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL is getting slow
On Thu, 2004-12-09 at 10:08 -0600, gerald_clark wrote: Patrick Marquetecken wrote: Hi, I have 3 snort sensors logging to a central mySQL database after two weeks the size of the database is about 3.3GB and the machine is getting slow, as i'm not used to be working with mySQL is my question how far can i go before the machine stop responding ? HP Pentium4 - 2.2mhz - 512 mb, 99% used and no swap, HD of 40GB. I f that is 99% disk used, you are already dead. That is why it is slow. You have no room to do anything. To be fair Gerald, I think he means he's out of memory (correct me if I'm wrong here Pat), but you simply *must* allow the machine work space to work things out. Adding some swap will actually help. How active is the server? Darryl Waterhouse --- Software Development Manager Orbis Monitoring Services t: +44 151 357 7800 m: +44 7876 390212 e: [EMAIL PROTECTED] Suite 3, Sycamore House, Lloyd Drive, Ellesmere Port, Cheshire, CH65 9HQ
Re: MYSQL is getting slow
On Thu, 09 Dec 2004 16:17:17 + Darryl Waterhouse [EMAIL PROTECTED] wrote: On Thu, 2004-12-09 at 10:08 -0600, gerald_clark wrote: Patrick Marquetecken wrote: Hi, I have 3 snort sensors logging to a central mySQL database after two weeks the size of the database is about 3.3GB and the machine is getting slow, as i'm not used to be working with mySQL is my question how far can i go before the machine stop responding ? HP Pentium4 - 2.2mhz - 512 mb, 99% used and no swap, HD of 40GB. I f that is 99% disk used, you are already dead. That is why it is slow. You have no room to do anything. To be fair Gerald, I think he means he's out of memory (correct me if I'm wrong here Pat), but you simply *must* allow the machine work space to work things out. Adding some swap will actually help. How active is the server? Indeed its the memory, the computer has only mysql and apache on it installed, not even a graphical interface. There is a lot of data from the snort sensors that is getting inserted, and from a console machine there is a lot of queyring to the db. and for ansewring Jeff Smelser i have installed mysql 4.x on linux and then dit from the commandline create database and then run the create table commands from snort. Patrick Darryl Waterhouse --- Software Development Manager Orbis Monitoring Services t: +44 151 357 7800 m: +44 7876 390212 e: [EMAIL PROTECTED] Suite 3, Sycamore House, Lloyd Drive, Ellesmere Port, Cheshire, CH65 9HQ -- Uhura, signal our surrender -- Kirk Captain!!?? -- Uhura We surrender!! -- Kirk (Star Trek VI) Fingerprint = 2792 057F C445 9486 F932 3AEA D3A3 1B0C 1059 273B ICQ# 316932703 Registered Linux User #44550 http://counter.li.org pgpnyBHzWdvri.pgp Description: PGP signature
Re: MYSQL is getting slow
On Thursday 09 December 2004 01:06 pm, Patrick Marquetecken wrote: and for ansewring Jeff Smelser i have installed mysql 4.x on linux and then dit from the commandline create database and then run the create table commands from snort. No idea.. you can do a show create table db.table to find out the engine type.. I ask because those should be myisam.. If they are. I cant see how memory is getting so high. If they are innodb.. I would highly recommend looking into converting them into myisam.. myisam was created for this type of work, would be a easier on the mem. Just some avenues to look at. Jeff pgpXfDTgl3IZw.pgp Description: PGP signature
RE: MYSQL is getting slow
This is a very broad question, I sometimes do this myself. But to answer the question to the best of my ability I have to ask a few. Are you using RAID? If so what RAID level? What are you're my.cnf settings? What type of Indexes are you using? What does vmstat and iostat say? What Filesystem are you using? What are some typical queires for a given schema? Typically for most orginizations mysql dedicated on the box below is wicked fast, even when not optimized because of system cache etc. But if your running out of diskspace then that's a problem in itself. -Original Message- From: Patrick Marquetecken [mailto:[EMAIL PROTECTED] Sent: Thursday, December 09, 2004 7:49 AM To: [EMAIL PROTECTED] Subject: MYSQL is getting slow Hi, I have 3 snort sensors logging to a central mySQL database after two weeks the size of the database is about 3.3GB and the machine is getting slow, as i'm not used to be working with mySQL is my question how far can i go before the machine stop responding ? HP Pentium4 - 2.2mhz - 512 mb, 99% used and no swap, HD of 40GB. TIA Patrick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]