Mysql causing high load

[First-Dragon]

I have a problem on my VPS, last few days i am getting high load of cpu probably caused by mysql, here is a result of top command:

http://gyazo.com/a5473090fb03b5894839609bf2727634

I have WordPress site on this server, VPS have good specs,2gb ram and 4 cpu's . I have 60k posts on site and most people online at same time is around 100. Site is on nginx server.

Any thoughts of how to reduce the load. Thanks.

 

[Loonycgb2]

If you like i can help optimize your vps to keep it from such high usage.

Add me on skype los33cartel

 

[REAK]

You need to optimize your my.cnf file,

Here's a good link : http://www.botskool.com/geeks/how-reduce-server-load-mysql-caching-and-optimization

You can PM / IM me if you need further help.

 

[5gbps]

use SSD for your mysql you will see a significant increase in performance

 

[PacificHost]

We'll need to know a few things:

cat /etc/my.cnf /etc/mysql/my.cnf
grep -c processor /proc/cpuinfo
free -mt
du -shc /var/lib/mysql/*/*.* | tail -n1
mysqladmin proc stat

 

[First-Dragon]

Hope this help:

cat /etc/my.cnf /etc/mysql/my.cnf

grep -c processor /proc/cpuinfo

8

free -mt

du -shc /var/lib/mysql/*/*.* | tail -n1

1.1G total


mysqladmin proc stat

 

[cvrle77]

backup everything from my.cnf to notepad, delete content of my.cnf , then try this:

[mysqld]
skip-innodb
skip-bdb

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

hit service mysqld restart

sometimes nothing there works the best

 

[Akhlis]

^ I do have to agree with you.

 

[First-Dragon]

Is this normal for database of wordpress site:

This table is 550mb and wp_posts is 272mb:

Table: wp_cbnetpo_ping_optimizer

Records: 4,291,085

Data Us: 558.9 MiB

Index Usage: 41.9 MiB

 

[Loonycgb2]

Each persons my.cnf will not be the same due to each having 1 or more cpu's and having more ram then the other.

With a empty my.cnf you will take on more cpu load since nothing is set to be used in memory. When a mysql is optimized it will balance all the table queries to memory which creates a faster and more responsive mysql.

This is why most hosts/sites get slow due to bad configs. EMPTY CONFIGS ARE VERY BAD!!

@First Dragon: ping optimizer table may just be caching ip activity and pages to keep from users having to load it them selfs. Just do a wordpress database clean up and it should lower that number

 

[Sp32]

http://www.wjunction.com/13-tutorials-guides/116141-optimize-mysql-mysqltuner.html