mysql having loads

Question

My website and server both are huge and heavy, my server is having 8GB RAM and 6 cores of processor.

I got a mail from my hosting team, regarding the load of mysql database,

Now-a-days my site is running really slow, and the mail which I received contains a log file

root@server [~]# mysqladmin pr
+-------+------------------+-----------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------------------+-----------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 17969 | leechprotect | localhost | leechprotect | Sleep | 103 | | |
| 18706 | database_user | localhost | database_name | Sleep | 25 | | |
| 18717 | database_user | localhost | database_name | Query | 19 | Sending data | SELECT DISTINCT SQL_CALC_FOUND_ROWS
se_users.user_id,
se_users.user_username,
se_users.us |
| 18737 | database_user | localhost | database_name | Query | 19 | Sending data | SELECT DISTINCT SQL_CALC_FOUND_ROWS
se_users.user_id,
se_users.user_username,
se_users.us |
| 18745 | database_user | localhost | database_name | Query | 20 | Sending data | SELECT DISTINCT SQL_CALC_FOUND_ROWS
se_users.user_id,
se_users.user_username,
se_users.us |
| 18752 | database_user | localhost | database_name | Query | 19 | executing | SELECT DISTINCT SQL_CALC_FOUND_ROWS
se_users.user_id,
se_users.user_username,
se_users.us |
| 18777 | database_user | localhost | database_name | Query | 17 | executing | SELECT DISTINCT SQL_CALC_FOUND_ROWS
se_users.user_id,
se_users.user_username,
se_users.us |
| 18815 | database_user | localhost | database_name | Query | 15 | Sending data | SELECT DISTINCT SQL_CALC_FOUND_ROWS
se_users.user_id,
se_users.user_username,
se_users.us |
| 18926 | database_user | localhost | database_name | Query | 2 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18951 | database_user | localhost | database_name | Sleep | 1 | | |
| 18952 | database_user | localhost | database_name | Query | 1 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18953 | database_user | localhost | database_name | Sleep | 1 | | |
| 18954 | database_user | localhost | database_name | Sleep | 1 | | |
| 18955 | database_user | localhost | database_name | Query | 3 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18958 | database_user | localhost | database_name | Sleep | 1 | | |
| 18960 | database_user | localhost | database_name | Query | 2 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18961 | database_user | localhost | database_name | Query | 2 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18962 | database_user | localhost | database_name | Query | 3 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18963 | database_user | localhost | database_name | Query | 3 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18964 | database_user | localhost | database_name | Query | 2 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18965 | database_user | localhost | database_name | Query | 1 | executing | SELECT DISTINCT SQL_CALC_FOUND_ROWS
se_users.user_id,
se_users.user_username,
se_users.us |
| 18967 | database_user | localhost | database_name | Sleep | 0 | | |
| 18968 | database_user | localhost | database_name | Query | 2 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18974 | database_user | localhost | database_name | Query | 2 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18978 | database_user | localhost | database_name | Query | 1 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18979 | database_user | localhost | database_name | Query | 1 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18980 | database_user | localhost | database_name | Query | 1 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18982 | database_user | localhost | database_name | Query | 1 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18983 | database_user | localhost | database_name | Sleep | 0 | | |
| 18984 | database_user | localhost | database_name | Query | 1 | executing | SELECT DISTINCT SQL_CALC_FOUND_ROWS
se_users.user_id,
se_users.user_username,
se_users.us |
| 18985 | database_user | localhost | database_name | Query | 1 | Sending data | SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1' |
| 18986 | database_user | localhost | database_name | Sleep | 0 | | |
| 18987 | database_user | localhost | database_name | Sleep | 0 | | |
| 18988 | database_user | localhost | database_name | Sleep | 0 | | |
| 18989 | database_user | localhost | database_name | Sleep | 0 | | |
| 18990 | root | localhost | | Query | 0 | | show processlist 

I am not able to understand this log, can any one help me in this

My Top Command

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
11050 mysql     15   0 1510m 272m 4080 S 629.0  7.0 675:31.90 mysqld
26990 database_user  16   0  130m  24m 6812 R 25.4  0.6   0:00.84 php
26989 database_user  16   0  155m  39m 7652 R 21.8  1.0   0:00.76 php
26988 database_user  16   0  154m  38m 7648 R 21.5  1.0   0:00.74 php
27011 database_user  16   0     0    0    0 R 18.8  0.0   0:00.57 php
26835 database_user  16   0  156m  40m 7632 R 17.5  1.0   0:00.69 php
26909 database_user  16   0     0    0    0 Z 16.5  0.0   0:00.68 php <defunct>
26977 database_user  16   0     0    0    0 Z 14.2  0.0   0:00.51 php <defunct>
26947 database_user  15   0  154m  37m 7632 R 12.9  1.0   0:00.58 php
26844 database_user  16   0     0    0    0 Z 11.2  0.0   0:00.59 php <defunct>
26956 database_user  15   0  154m  38m 7632 R 10.9  1.0   0:00.42 php
27005 database_user  16   0  146m  30m 7596 R  8.3  0.8   0:00.25 php
27058 database_user  16   0  139m  22m 7328 S  7.9  0.6   0:00.24 php
26878 database_user  16   0     0    0    0 Z  7.6  0.0   0:00.44 php <defunct>
27052 database_user  17   0  140m  23m 7292 R  7.6  0.6   0:00.23 php
27037 database_user  15   0  143m  26m 7320 S  7.3  0.7   0:00.22 php
26964 database_user  16   0     0    0    0 Z  6.9  0.0   0:00.29 php <defunct>

:)

Answer 1

What your hosting team provided is not a log, but the current running Mysql process which was obtained from mysqladmin pr(ocesslist).

And your Mysql proc list shows you have multiple instances of mainly 2 queries:

SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1'

SELECT DISTINCT SQL_CALC_FOUND_ROWS se_users.user_id,se_users.user_username,se_users.us

I am not sure if they're complete, but then you should optimize your queries. eg:- you should use count(*) instead of count(friend_id)

And don't you have query cache enabled? If not, you should enable that as that will improve the performance immensly. If you do, you should check the query cache status to tweak it further so that your most frequently used queries stay in the cache.

As per system top output, Mysql is taking over 600% of CPU and it looks like your Mysql server badly need some tweaking. Btw, your top interpretation is incorrect, that's not the db user (mysql user), but the system user.

And since lots of PHP processes also consume server load, your server overall requires some tuning. Get it done sooner than later.

To optimize Mysql, you should try to tweak the following variables:

Following variables will set global buffers

max_connections

max_user_connections

table_cache

key_buffer_size

query_cache_size

vars below will set per thread buffers:

tmp_table_size max_heap_table_size

read_buffer_size sort_buffer_size

join_buffer_size

vars below have to be tuned If you're using InnoDB:

innodb_log_buffer_size

innodb_additional_mem_pool_size

innodb_buffer_pool_size

There are many other variables that need to be looked at as well. However, if you tune the above variables to suit your requirement, that would make a world of difference in terms of performance.

However, don't do it unless you really understand what should be the right value for those variables. So learn it before you go about tuning Mysql on a production server as it might backfire if you don't do properly.

If you don't have enough time to learn, bring someone who knows the job to get it done.

Answer 2

Lots of processes at 'Sending data' implies that either there is something very wrong with the handling of responses in the logic tier, or that a lot of the queries are returning very large result sets. You did not mention what the logic tier is implemented in.

Looking at the SQL, the same SQL crops up again and again:

SELECT count(friend_id) AS total_friends FROM se_friends WHERE friend_status='1'

I sincerely hope this is truncated - and that there is a more meaningful and specific bit of the command missing - otherwise this makes no sense at all. You should be able to see more from your slow query log.

This query only returns a single row. But its taking around 2 seconds for the SQL to execute and for the logic to retrieve the data (since there are none of these executing it suggests that the query is running very quickly, less than 0.1 seconds - the majority of the time is taken up by the logic tier). While this can arise due to a very slow network between the database and the logic tier, that is not the case here (connections are from localhost). It could also occur where there is a problem with context switching - the database server and client are constantly geting pre-empted by some other process. But with 6 cores, that would need to be a very aggressive process.

If you can get your logic tier to retrieve the data sooner this will make a huge difference to the load.

Please provide more details about the SQL being run and the logic tier and the output of 'top'

Answer 3

Looking at your time column above, it appears that the following statement is the slowest:

SELECT DISTINCT SQL_CALC_FOUND_ROWS se_users.user_id, se_users.user_username, se_users.us

It's taking the most time, and it looks like it's just a select all of the entire table, but I can't tell for sure because it looks like the rest of the statement got cut off...

In order to change this, you could include a WHERE statement that references indexes, use the LIMIT command, or use the EXPLAIN command to debug your statement.

You may want to also look into mysqltuner if you have the ability/access to tune the DB. If you are trying to debug the load of your MySQL database, the output of top is probably not relevant.