Issues with Server Load, Suggestions...

CivilWarTalk

CivilWarTalk

Active member
I'm having some issues with my server load, and my server tech sent me this message:

I have checked the server. I could see that the memory usage by Mysql is a bit high.

---
[root@host ~]# nice top -c
top - 09:59:12 up 32 days, 19:55, 1 user, load average: 14.35, 9.28, 7.01
Tasks: 67 total, 1 running, 65 sleeping, 0 stopped, 1 zombie
Cpu(s): 0.1%us, 0.1%sy, 0.0%ni, 74.3%id, 25.6%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 983040k total, 833688k used, 149352k free, 0k buffers
Swap: 0k total, 0k used, 0k free, 0k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3771 apache 17 0 64124 19m 9988 S 0.7 2.1 0:00.48 /usr/sbin/httpd -k start -DSSL
3782 apache 16 0 64068 17m 7172 S 0.7 1.8 0:00.02 /usr/sbin/httpd -k start -DSSL
13984 mysql 15 0 780m 625m 5324 S 0.3 65.2 10:36.53 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/my
1 root 18 0 2868 1360 1188 S 0.0 0.1 0:02.78 init
1161 root 14 -4 2464 548 320 S 0.0 0.1 0:00.00 /sbin/udevd -d

[root@host ~]# free -m
total used free shared buffers cached
Mem: 960 751 208 0 0 0
-/+ buffers/cache: 751 208
Swap: 0 0 0
[root@host ~]#
---

I have changed the following values in the Mysql configuration.

---
max_connections=300
thread_cache_size=6
interactive_timeout=120;
wait_timeout=120
---

The load in the server has been reduced a bit.

---
[root@host ~]# nice top -c
top - 10:38:07 up 32 days, 20:34, 2 users, load average: 1.11, 3.52, 4.32
Tasks: 47 total, 1 running, 45 sleeping, 0 stopped, 1 zombie
Cpu(s): 0.3%us, 0.1%sy, 0.0%ni, 97.3%id, 2.2%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 983040k total, 247880k used, 735160k free, 0k buffers
Swap: 0k total, 0k used, 0k free, 0k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
8036 apache 18 0 62416 18m 9.8m S 4.7 1.9 0:00.49 /usr/sbin/httpd -k start -DSSL
8038 apache 15 0 64652 19m 8556 S 1.0 2.0 0:00.07 /usr/sbin/httpd -k start -DSSL
7607 mysql 15 0 734m 121m 4908 S 0.3 12.7 0:02.91 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/my
8046 apache 15 0 0 0 0 Z 0.3 0.0 0:00.20 [httpd]
---

The available free memory has been increased.

---
[root@host ~]# free -m
total used free shared buffers cached
Mem: 960 231 728 0 0 0
-/+ buffers/cache: 231 728
Swap: 0 0 0
[root@host ~]#
---

However, the Mysql memory usage is still a bit high.

I have monitored the Mysql queries running in the server. I could see queries like the following continuously in the process list.

---
| 136436 | admin_aaa | localhost | admin_xxx | Execute | 28 | update | INSERT INTO `xf_search` (`search_results`, `result_count`, `search_type`, `search_query`, `search_co |
| 136545 | admin_aaa | localhost | admin_xxx | Execute | 97 | Sending data | SELECT search_index.content_type, search_index.content_id
| FROM xf_search_index AS search_index
| 136559 | admin_aaa | localhost | admin_xxx | Execute | 76 | Sending data | SELECT search_index.content_type, search_index.content_id
| FROM xf_search_index AS search_index
| 136579 | admin_aaa | localhost | admin_xxx | Execute | 30 | update | INSERT INTO `xf_search` (`search_results`, `result_count`, `search_type`, `search_query`, `search_co |
| 136580 | admin_aaa | localhost | admin_xxx | Execute | 56 | query end | INSERT INTO `xf_search` (`search_results`, `result_count`, `search_type`, `search_query`, `search_co |
| 136581 | admin_aaa | localhost | admin_xxx | Execute | 55 | update | INSERT INTO `xf_session` (`session_id`, `session_data`, `expiry_date`) VALUES (?, ?, ?) |
| 136582 | admin_aaa | localhost | admin_xxx | Execute | 53 | Locked | INSERT INTO `xf_session` (`session_id`, `session_data`, `expiry_date`) VALUES (?, ?, ?) |
| 136583 | admin_aaa | localhost | admin_xxx | Execute | 51 | Creating tmp table | SELECT user.*,
user_profile.*,
user_option.*
FROM xf_user_follow AS user_follow
INNER |
| 136584 | admin_aaa | localhost | admin_xxx | Execute | 50 | Locked | SELECT session_data
FROM xf_session
WHERE session_id = '9c95b80eac8e87413563c372b4787463'
|
| 136585 | admin_aaa | localhost | admin_xxx | Execute | 47 | Locked | INSERT INTO `xf_session` (`session_id`, `session_data`, `expiry_date`) VALUES (?, ?, ?) |
| 136587 | admin_aaa | localhost | admin_xxx | Execute | 27 | update | INSERT INTO `xf_search` (`search_results`, `result_count`, `search_type`, `search_query`, `search_co |
| 136588 | admin_aaa | localhost | admin_xxx | Execute | 26 | Locked | INSERT INTO `xf_session` (`session_id`, `session_data`, `expiry_date`) VALUES (?, ?, ?) |
| 136589 | admin_aaa | localhost | admin_xxx | Execute | 25 | Locked | SELECT session_data
FROM xf_session
WHERE session_id = '9c95b80eac8e87413563c372b4787463'
|
| 136590 | admin_aaa | localhost | admin_xxx | Execute | 25 | Locked | SELECT session_data
FROM xf_session
WHERE session_id = 'db93133a77ec4e2138543775eb11e131'
|
| 136591 | admin_aaa | localhost | admin_xxx | Execute | 22 | Locked | SELECT session_data
FROM xf_session
WHERE session_id = '19c605191904626e4cf3f75003a3c931'
|
| 136592 | admin_aaa | localhost | admin_xxx | Execute | 16 | Locked | SELECT session_data
FROM xf_session
WHERE session_id = '9c95b80eac8e87413563c372b4787463'
|
| 136593 | admin_aaa | localhost | admin_xxx | Execute | 14 | Locked | INSERT INTO `xf_session` (`session_id`, `session_data`, `expiry_date`) VALUES (?, ?, ?) |
| 136597 | admin_aaa | localhost | admin_xxx | Execute | 5 | Sending data | SELECT search_index.content_type, search_index.content_id
| FROM xf_search_index AS search_index
| 136598 | admin_aaa | localhost | admin_xxx | Execute | 4 | Sending data | SELECT search_index.content_type, search_index.content_id
| FROM xf_search_index AS search_index
---

These Mysql queries are causing the issue. You need to contact a developer and optimize the database admin_xxx to reduce the load in the server.
Click to expand...

It almost seems like they are forgetting that I use Innodb.... Any suggestions?
 
Please post your my.cnf.

Parts of your high load are caused by IO (25% WA). Maybe there is no Innodb buffer in your my.cnf and Mysql struggles and has to read all from disk.
 
Code: 
[mysqld]
local-infile=0
#skip-innodb
set-variable = max_connections=300
safe-show-database
max_tmp_tables=1
query_cache_type=1
query_cache_limit=4M
query_cache_size=32M
thread_cache_size=6
table_cache=475
tmp_table_size=256M
max_heap_table_size=256M
innodb_buffer_pool_size=700M
interactive_timeout=120
wait_timeout=120
key_buffer = 256M
 
Your Innodb buffer pool is 700 MB but you only have 1000 MB RAM, that won't work :)

Reduce your Innodb buffer to a reasonable size, e.g. 150 MB. Same for key_buffer, 256 MB if you only have 1000 MB makes no sense.
 
If I increase my memory from 960 mb to 1216 mb, how much will that improve this issue? 256 mb doesn't sound like much, but if it makes a difference, maybe I should invest an extra $8 a month and upgrade...
 
Did some additional tweaking today, set:

innodb_buffer_pool_size=384M
key_buffer = 32M

Server seems to respond better under normal loads now. I'll report again after I cycle through our peak load tonight...
 
Maybe try halving it to 150, and that should lower the memory footprint of MySQL quite a bit.
 
MattW said:
Maybe try halving it to 150, and that should lower the memory footprint of MySQL quite a bit.
Click to expand...

IIRC, that wouldn't lower the foot print. It would just tell MySQL DB that 300 max connections is allowed and no more. If his forum uses more then 100 active connections then something isn't right to begin with.

@OP Download mysqltunner script, run it and post the results. First run the script, then change settings and restart mysql and then again run the script. Post both the outputs.

https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl

Also might want to change this:

interactive_timeout=120
wait_timeout=90
tmp_table_size=64M
max_heap_table_size=64M
max_connections = 75

max_tmp_tables is not used so you don't need that in config.
 
You must log in or register to reply here.
Top Bottom