Depressing bad MYSQL performance

03-17-2008, 06:48 AM

I've been dealing with a extremely high load, of around 20.00+ on my server at almost all times, if i restart my server it will be on 1.00-2.00 for a few hours, then 2.00-8.00 for a few more hours, then eventually it will go to a maximum of 20.00 for an indefinate amount of time until i do a full reset of my server.

I've activated slow mysql queries to try to find those and fix them, but the file reports nothing.

I'm kinda desperate here since all i have are 7-8 tables with 50,000 rows in them being accessed, and a DUAL XEON cloverfield server with 2gig memory and i've been told that i really shouldnt be having this problem.

OK, you seem to have 4GB RAM (according to your top) and the applications on your server is only using about 1GB right now.

But what is much more interesting is that you have a I/O-wait of 50%!!!!!

1.
What kind of disk setup do you have in your server?

2.
And how much data is your MailScanner's processing?

Because the CPU % value for mysql can be misleading due to rounding errors since mysql is running a _lot_ of threads within the same process.
But the fact that your mailscanners consume 15-30% cpu per each of the 5 processes is also a _very_ big figure.

3.
Attach a txt file with the output from SHOW STATUS; and SHOW VARIABLES; run in mysql and we can tell you if mysql has any obvious limitations in it's current setup.

Comment

OK, here are some comments:
1.
You have about 10% of the queries that performs a table scan on the table. But these queries are probably executed under 1 second and that is probably why you don't see them in the slow query log.
Read the manual about the slow query log settings. So that you can also see the queries that doesn't use index.

2.
Are you using InnoDB or MyISAM tables for your tables?
Because you seem to be using default settings for the cache so that value could be increased a bit but which variable to change depends on which storage engine you are using.

3.
Are the MailScanner processes always there consuming so much CPU?
Or was that just in that screen shot?
If they are always there consuming so much CPU, although you say that you only have about 150 small mails a day, I would check so that your sendmail isn't misconfigured and acts as an open mail relay which forwards all kinds of spam mail through it.

Comment

1.
I would suggest that you start by stopping the MTA(sendmail) and the mailscanner. Or is it primarily the mailscanner that uses the mysql database?
Something like:
/etc/init.d/MailScanner stop
/etc/init.d/sendmail stop
should work.
And then see what happens to the load on the server.
Because right now I think the mailscanner is imposing a lot more load on the server than mysql.

2.
For the MyISAM tables you should check the mysql data dir (usually /var/lib/mysql/[databasename] if it is a mysql that comes with the distribution).
And roughly calculate the sum of the .MYI files. That is the total index size of the DB. And if that size is less than 25% of the free RAM then you should set the value to approximately this.
Otherwise you set the key_buffer_size to 25% of the free RAM on your server.
Then you add something like:
key_buffer_size=128M
in your /etc/my.cnf file.

3.
For the slow query, try some of the alternatives for slow query log:
--log-long-format
or
--log-queries-not-using-indexes

Because you want to get the queries that doesn't use index but still takes less than one second to execute.

Comment

I turned off sendmail and mailscanner and now the load is around 0.50-1.00, althrough i did do abit of MYSQL optimiziation after i learnt a few things earlier today.

Turning them both on makes the load start to rise alot, any ideas what this could be? Like i said, i hardly use sendmail compared to anything else, especially enough to make this monster server raise by like 600% use.