Status
Not open for further replies.

cvrle77

Active Member
5,787
2009
4,353
0
So I have (had) this blog with lots of posts, db was about 140MB, and lately, db is making huge load on VPS to that point, that it crash, and shuts down. It wouldn't upset me at all, if I don't have other sites there, and everything goes byebye. Right now, I had to put it in maintenance mode, so visitors don't pull data from db.

List of things I've tried:

  • I had W3 cache plugin, nothing, doesn't help when db starts going wild
  • I've tried turning off all plugins, didn't helped
  • Tried turning off all RSS feeds, nothing
  • Also tried to export all posts with wordpress export tool, and reinstall fresh wordpress with fresh db, then import back all posts, by dividing original file into smaller chunks, didn't even imported all posts, same..
  • Ok, just tested it, even with maintenance plugin, I am getting load of 18

7Gliu.png


Btw, there is few other sites on same VPS, receiving same amount of visitors like the troubled one, but load doesn't go over 1. Also wordpress sites. So, I guess MySQL settings would impact them too, if something is optimized bad.


I'm basically clueless what it can be.
Any ideas??

The one who finds me a working solution will be credited with rapidpoints equal to 2 years RS account, in case that is ok with the WJ rules, and I can actually offer that here. That is not a PAYMENT, it's a reward for your effort and time.
To mods: in case this type of reward is not allowed, please, only remove RS line, don't close topic.
Since it's not a trade case, and I don't give the actual premium account ( I give points from my own account, transferring to someone elses )
So, I can ask for help for free, but instead, I will give away something.

EDIT: Huge load occurs ONLY when I post a new post on blog. Not when lots of users are online, or do a search or anything, in regular circumstances, load is not over 1.
 
Last edited:
43 comments
Try disabling all your plug ins and monitor the load. If the load no longer shoots up high, you know it's a plug in fault and the you can try enabling them one by one to find out which is the causing the high load. If you don't have plug ins installed then I'm not too sure :|
 
If you have spare hosting elsewhere, could you try to move the site and do a simple test? It could just be the vps/hosting you are on o_O
 
Yes, I've already moved because of that. Funny, is that other wordpress sites are having same theme, same plugins, same settings, none of them is making problems.
 
The issue is not with wordpress. Its with your VPS.

I had the same issue few months ago. The problem was with the VPS high IO wait. This problem was due to the VPS upstream provider and eventually the host (BuyVPS.co) fixed it.

But before that, I tried optimizing the mysqld. I also converted my DB from InnoDB to MyISAM engine.

Login to SSH
Type iostat or iotop and show us the output here. Also type top and free -m and show us the output.
 
problem is, that one db is causing issue, while few other on same VPS, with same amount of posts, have no problems at all. So, it's DB for sure. But, what with it exactly.. :|
Sometimes I get Error connection to database, and after refresh, it's all ok, not sure if it's even related. But, it looks there's a problem with how wordpress is calling db, handling queries... And I simply have no idea what to look for, tried few google searches, everything that can be optimized, is...

---------- Post added at 10:07 PM ---------- Previous post was at 05:38 PM ----------

Ok, so new moments. I've manage to isolate when this is happening, and it's exactly when I make new post, and post it. That is when load goes wild.
 
Last edited:
Yes, they are :|

Not sure if this is everything I need to do:

ALTER TABLE wp_commentmeta ENGINE=INNODB;
ALTER TABLE wp_comments ENGINE=INNODB;
ALTER TABLE wp_links ENGINE=INNODB;
ALTER TABLE wp_options ENGINE=INNODB;
ALTER TABLE wp_postmeta ENGINE=INNODB;
ALTER TABLE wp_posts ENGINE=INNODB;
ALTER TABLE wp_terms ENGINE=INNODB;
ALTER TABLE wp_term_relationships ENGINE=INNODB;
ALTER TABLE wp_term_taxonomy ENGINE=INNODB;
ALTER TABLE wp_usermeta ENGINE=INNODB;
ALTER TABLE wp_users ENGINE=INNODB;
 
Last edited:
It sounds very strange, especially when you're using W3 Cache. I tend to get a load of around 2.00 on my database server, but this is mostly caused by the volume of search queries.

I would test whether the cache is actually working. The easiest way is to stop mysql for a minute and reload the site. If the site loads up then your cache is working, if it gives a database error, then I would check your rewrite rules.

It might also be worth turning on the MySQL slow queries log and keeping an eye on that. You can also turn on some of the MySQL caches as they shouldn't do any harm and usually take a bit of load off.
 
ok follow step get succes

install wp super cache
go in adavence select
Use mod_rewrite to serve cache files

Don’t cache pages for known users. untick this

now go on Preload tab

first make ur website in maintaince mode. then click on button Preload now . this will build cache of website all pages takes time but bare

let me known if this works. then send me Rapids :P
 
Actually, I do get DB error connection, when I stop myslq...
In W3, I have enabled Page Cache, Minify, Database Cache, Object Cache and Browser Cache
this is htaccess:


# BEGIN W3TC Browser Cache
<IfModule mod_deflate.c>
<IfModule mod_setenvif.c>
BrowserMatch ^Mozilla/4 gzip-only-text/html
BrowserMatch ^Mozilla/4\.0[678] no-gzip
BrowserMatch \bMSIE !no-gzip !gzip-only-text/html
BrowserMatch \bMSI[E] !no-gzip !gzip-only-text/html
</IfModule>
<IfModule mod_headers.c>
Header append Vary User-Agent env=!dont-vary
</IfModule>
<IfModule mod_filter.c>
AddOutputFilterByType DEFLATE text/css application/x-javascript text/x-component text/html text/richtext image/svg+xml text/plain text/xsd text/xsl text/xml image/x-icon
</IfModule>
</IfModule>
<FilesMatch "\.(css|js|htc|CSS|JS|HTC)$">
FileETag None
<IfModule mod_headers.c>
Header set X-Powered-By "W3 Total Cache/0.9.2.4"
</IfModule>
</FilesMatch>
<FilesMatch "\.(html|htm|rtf|rtx|svg|svgz|txt|xsd|xsl|xml|HTML|HTM|RTF|RTX|SVG|SVGZ|TXT|XSD|XSL|XML)$">
FileETag None
<IfModule mod_headers.c>
Header set X-Powered-By "W3 Total Cache/0.9.2.4"
</IfModule>
</FilesMatch>
<FilesMatch "\.(asf|asx|wax|wmv|wmx|avi|bmp|class|divx|doc|docx|eot|exe|gif|gz|gzip|ico|jpg|jpeg|jpe|mdb|mid|midi|mov|qt|mp3|m4a|mp4|m4v|mpeg|mpg|mpe|mpp|otf|odb|odc|odf|odg|odp|ods|odt|ogg|pdf|png|pot|pps|ppt|pptx|ra|ram|svg|svgz|swf|tar|tif|tiff|ttf|ttc|wav|wma|wri|xla|xls|xlsx|xlt|xlw|zip|ASF|ASX|WAX|WMV|WMX|AVI|BMP|CLASS|DIVX|DOC|DOCX|EOT|EXE|GIF|GZ|GZIP|ICO|JPG|JPEG|JPE|MDB|MID|MIDI|MOV|QT|MP3|M4A|MP4|M4V|MPEG|MPG|MPE|MPP|OTF|ODB|ODC|ODF|ODG|ODP|ODS|ODT|OGG|PDF|PNG|POT|PPS|PPT|PPTX|RA|RAM|SVG|SVGZ|SWF|TAR|TIF|TIFF|TTF|TTC|WAV|WMA|WRI|XLA|XLS|XLSX|XLT|XLW|ZIP)$">
FileETag None
<IfModule mod_headers.c>
Header set X-Powered-By "W3 Total Cache/0.9.2.4"
</IfModule>
</FilesMatch>
# END W3TC Browser Cache
# BEGIN W3TC Page Cache core
<IfModule mod_rewrite.c>
RewriteEngine On
RewriteBase /
RewriteRule ^(.*\/)?w3tc_rewrite_test$ $1?w3tc_rewrite_test=1 [L]
RewriteCond %{HTTP:Accept-Encoding} gzip
RewriteRule .* - [E=W3TC_ENC:_gzip]
RewriteCond %{REQUEST_METHOD} !=POST
RewriteCond %{QUERY_STRING} =""
RewriteCond %{HTTP_HOST} =wuper.net
RewriteCond %{REQUEST_URI} !(\/wp-admin\/|\/xmlrpc.php|\/wp-(app|cron|login|register|mail)\.php|\/feed\/|wp-.*\.php|index\.php) [NC,OR]
RewriteCond %{REQUEST_URI} (wp-comments-popup\.php|wp-links-opml\.php|wp-locations\.php) [NC]
RewriteCond %{HTTP_COOKIE} !(comment_author|wp-postpass|wordpress_\[a-f0-9\]\+|wordpress_logged_in) [NC]
RewriteCond %{HTTP_USER_AGENT} !(W3\ Total\ Cache/0\.9\.2\.4) [NC]
RewriteCond "%{DOCUMENT_ROOT}/wp-content/w3tc/pgcache/%{REQUEST_URI}/_index%{ENV:W3TC_UA}%{ENV:W3TC_REF}%{ENV:W3TC_SSL}.html%{ENV:W3TC_ENC}" -f
RewriteRule .* "/wp-content/w3tc/pgcache/%{REQUEST_URI}/_index%{ENV:W3TC_UA}%{ENV:W3TC_REF}%{ENV:W3TC_SSL}.html%{ENV:W3TC_ENC}" [L]
</IfModule>
# END W3TC Page Cache core
# BEGIN WordPress
<IfModule mod_rewrite.c>
RewriteEngine On
RewriteBase /
RewriteRule ^index\.php$ - [L]
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /index.php [L]
</IfModule>

# END WordPress

@ Allan, nah, I get overload only when I'm posting on blog, other than that, load doesn't go wild at all.
 
Last edited:
Status
Not open for further replies.
Back
Top