Slow server (MySQL / IOPs)

[PatrickVeenstra]

I have a VPS with 6x3,2GHz and 16GB RAM.

It was responding slow, so I decided to do a reboot. The reboot took a lot of time, but finally it worked.

Right now it's responding extremely slow. Server load is 25; the primary consumer is probably MySQL, top:
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1877 mysql 20 0 8755m 1.1g 5732 S 2.7 6.8 2:23.33 mysqld

although I guess that heavy IOPs is the main cause:

Trans./Sec 35.15
Blocks Read/sec 1289.24
Blocks Written/Sec 393.83
Total Blocks Read 2005588
Total Blocks Written 612648

executing tuning-primer.sh freezes (or seems to freeze, it's been there for quite some minutes) at INNODB status.

I haven't changed the my.cnf file in months, but here it is:

[myisamchk]
read_buffer=32M
write_buffer=32M
key_buffer=128M
sort_buffer=128M

[mysqld]
table_definition_cache=1600
query_cache_type=1
read_buffer_size=16M
join_buffer_size=3M
max_user_connections=300
low_priority_updates=1
thread_cache_size=512
tmp_table_size=256M
query_cache_size=64M
sort_buffer_size=8M
max_heap_table_size=256M
innodb_log_buffer_size=4M
innodb_file_per_table=1
open_files_limit=12010
key_buffer=2048M
table_cache=3072
query_cache_limit=3M
concurrent_insert=2
myisam_sort_buffer_size=512M
max_allowed_packet=268435456
max_connections=300
innodb_buffer_pool_size=3G

[isamchk]
read_buffer=32M
write_buffer=32M
key_buffer=128M
sort_buffer=128M

Anyone can help please?

 

[storminternet]

I would suggest you to run tuning-primer.sh again as it will give you clear idea about the actual usage by mysql.
Secondly if you need to check with host if there is any other vps, consuming resources badly and overloading hardware node on which your vps is hosted.

 

[PatrickVeenstra]

Thank you for your reply. I already tried to run tuning-primer.sh again, but it hangs again on INNODB status. I'm going to contact my provider now.

 

[cPanelMichael]

Hello

The following command may also help you identify any particular databases responsible for the usage:

mysqladmin processlist

Thank you.

 

[PatrickVeenstra]

Right now my server load is 16, being the top user mysql (command: mysqld). mysqladmin processlist says:
+---------+--------------+-----------+--------------+----------------+------+--------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+---------+--------------+-----------+--------------+----------------+------+--------------------+------------------+
| 121 | DELAYED | localhost | eximstats | Delayed insert | 1 | Waiting for INSERT | |
| 419267 | eximstats | localhost | eximstats | Sleep | 1 | | |
| 1144633 | leechprotect | localhost | leechprotect | Sleep | 4694 | | |
| 1205799 | DELAYED | localhost | eximstats | Delayed insert | 215 | Waiting for INSERT | |
| 1209751 | root | localhost | | Query | 0 | | show processlist |
+---------+--------------+-----------+--------------+----------------+------+--------------------+------------------+

- - - Updated - - -

Right now, Load Averages: 10.83 12.12 14.69

top:
1877 mysql 20 0 8990m 2.4g 6360 S 10.6 15.3 156:55.97 mysqld
.. and a three PHP processes that use much less CPU
.. and a lot of stuff that uses 0.0

 

[PatrickVeenstra]

The issue has been solved by moving the VPS to another server. Load at peak is < 0.5 so everything is great again. The server is not identical (6x3,4GHz instead of 6x3,2GHz), but I suppose that it was caused by hard disk overloading.

 

[cPanelMichael]

I am happy to see you were able to resolve the issue. Thank you for updating us with the outcome.

 

[codelabkenya]

hello

i have same issue my server load has been running at an average of 0.5 but for the last 5 days it has been running at an average of 2.0 there is these specific mysql processes that is consuming 25% of cpu usage and 18% of RAM

/usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/codelab.codelabkenya.net.err --open-files-limit=50000 --pid-file=/var/lib/mysql/codelab.codelabkenya.net.pid

i will appreciate your assistance
Robert Mwaura

 

[cPanelMichael]

I'm not sure it is the same issue as you suggest, but you are welcome to run the command I suggested earlier in this thread to get an idea of which databases are utilizing the most resources.

Thank you.