Server load and MySql problem

Hi!

Unfortunately, it is very difficult to give you valuable advice with so little server information. How are PHP and MySQL configured, what web server and operating system do you use? How large are the databases and what table engine is in use?

To check whether the problem lies in WordPress, themes or plugins, you can use Debug Objects and/or P3 (Plugin Performance Profiler) for debugging. Further, make sure you’re not using multiple caching plugins (like WP-Super-Cache and W3 Total Cache), and try to use an object cache like APC or Redis.

Newer MySQL versions require far more RAM than the 4 GB you mention, especially with InnoDB, you might want to look into that as well.

If Linux is your operating system, you can use top to see CPU and memory usage, free -m for the amount of RAM that is used and available, strace -p <PID> to see what handles a process uses.

Right, what Jan said. I would monitor the CPU and memory usage using TOP within your linux os, specially during the spike. Test it with more ram and you might be looking into optimizing your DB configuration, however your DB dies because your Web server takes the big chunk if not all of it away from the DB. Maybe also limit your Web server to how much resources it can absorb, 50/50 … or another option would be to use one VPS for Web server and one VPS for DB. ??

So I tried finding the cause with “top” command more than once, but the spike never occurs when I’m in front of it…

So the server runs Linux with WHM, Cpanel, mysql 5.6.28 and PHP 5.6.17

I’ve ran P3 on both my biggest sites and they are an average of 86 and 93 MySQL Queries. I don’t know if this is high or not?

The thing that’s weird, is that normally when I have “heavy” traffic on the websites(I use live view of analytics) the server load doesn’t goes up. The load goes up randomly…

I have also enabled “debug” on the 2 biggest sites, but with my knowledge, I don’t know what to look for…

Here are some lines I have in the debug on both sites.

PHP Warning: mysqli_real_connect(): (HY000/1040): Too many connections in /public_html/wp-includes/wp-db.php on line 1488

PHP Deprecated: mysql_connect(): The mysql extension is deprecated and will be removed in the future: use mysqli or PDO instead in /public_html/wp-includes/wp-db.php on line 1518

PHP Notice: wpdb was called incorrectly. wpdb must set a database connection for use with escaping. Please see Debugging in WordPress for more information. (This message was added in version 1024.) in /public_html/wp-includes/functions.php on line 3792

PHP Warning: mysqli_real_connect(): (HY000/2002): No such file or directory in /public_html/wp-includes/wp-db.php on line 1488

This is one line I have on only one site, since I’ve installed debug objects

Backtrace from warning ‘Array to string conversion’ at /public_html/wp-content/plugins/debug-objects/inc/class-cron.php 247: /public_html/wp-content/plugins/debug-objects/inc/class-cron.php 159 calling display_events() | < unknown file > < unknown line > calling render_data() | /public_html/wp-content/plugins/debug-objects/inc/class-wrap.php 209 calling call_user_func() | < unknown file > < unknown line > calling get_content() | /public_html/wp-includes/plugin.php 525 calling call_user_func_array() | /public_html/wp-admin/admin-footer.php 67 calling do_action() | /public_html/wp-admin/admin.php 267 calling include() | /public_html/wp-admin/tools.php 10 calling require_once()

I don’t know if that can help?

Hi,

Just a thought, what version of MySQL are you running?

What WordPress version are you using? If >= 3.9.2 your database connection should automatically use mysqli in stead of ext/mysql. Or are you running some plugin that uses its own database functions and not $wpdb?

86 and 93 can be a lot queries, but can be normal behavior as well. If the number of queries are always (almost) the same after page refreshes, you might want to look into your WordPress caching plugin. Do you have MySQL query cache configured?
MySQL 5.6 default is to use 8 “innodb_buffer_pool_instances”, which means you need to have 8 times more RAM [1] than your configured “innodb_buffer_pool_size”. However, that would consume all memory and not, as you explain, spike CPU usage. Have you configured PHP’s OPcache feature?

Suggestions:
* update WordPress, theme’s and plugins
* configure PHP’s OPcache, test with APC, Memcache, Redis
* use a caching plugin like WP-Super-Cache
* move MySQL to a second VPS if possible
* configure MySQL query cache
* check your web server and PHP configuration for odd settings (too many, or too few server processes to start, threads kept spare and so forth)

[1] https://www.saotn.org/mysql-55-innodb-performance-improvement/

WP version is 4.1.1
MySQL version 5.6.28
MySQL query cache and OPcache isn’t configured.
I already have WP Super Cache installed

Regarding the spike, what I noticed yesterday in MyPhpAdmin monitor, is that the ram usage was slowly rising, until it was all taken, then the cpu spiked.
You can see this here: https://i.imgur.com/xDHJ8qF.png

Hi, hmseb.

Your crash interval seems quite regular.

Did you look into any external processes on your server? Like automatic backups, CRON scripts that might be running every 12 hours?

The rise in spike you describe could be a sign that some process isn’t able to get past a certain point during script execution.

If you have a huge database (containing many old post-revisions or holding corrupt entries) a backup-script (or any other script that’s working with the database on a regular basis) could be responsible for the crash.

I have 4 CRON backups running every night from 1AM to 2AM, they do not run at the same time. One other thing, is that the crash isn’t timed with the backups.

ok, I understand.

Do you have access to any of your server logs?

Usually they should tell you which script ran exactly before the time of the crash.

I don’t, all I have for now is the debug.log from WP.

These are the logs for the last 24h, if that can help.

https://dl.dropboxusercontent.com/u/5725048/Pinuts%20Logs.txt

Have you been in contact with your host (to access the error logs)?
The support of the hosting provider should be able to see unusual spikes.

If you don’t have any special agreement concerning error-assistance with them, they should at least be able to help to pinpoint the problem.

Another guess: Did you scan for malware? Just to be sure that there is no script (on your server) being executed or being accessed from external sources every 12 hours.
https://www.ads-software.com/plugins/sucuri-scanner/

Yeah well support of the hosting company are not really helping out, from time to time they say “install a cache plugin” “it might be sql related” “you don’t have enough ram, upgrade your package”… There’s never a clear answer to what is the cause of this.

I’ll scan the site to make sure there’s no malware

There’s never a clear answer…

Oh, boy. That’s rough. Sorry to hear.