Hi, I'm facing a serious problem since 2 days ago regarding mysql database my site is on a shared host and is not busy at all(max 4000 visits a day) but as of thursday I got suspended due to heavy resource usage, of course I managed to get my site running again, but the problem persists, Now this is my problem: my server load which must be 1 or 2 is staying within 5 to 10 when I type 'top' in shell I see that lots of php,httpd and mysqld processes are running, mysql user agent specifically sits at top and occasionally hits 200% cpu load :O I've disabled almost all of my plugins (it's a wordpress site) and adjusted my php files as mush as possible, nevertheless, this damn server load stays high! I know there are some experts here, please help me, how can I find out what part of the SQL is causing this issue??????
A link to the site would be helpful. Before doing so, make sure these function calls are in your footer please: http://lastblogger.com/archives/339 That's the problem right there. Wordpress is very heavy pulling resources out of MySQL. One site I saw, it was doing well over 200 requests for each and every page load. Please take a look at the wp-super-cache plugin as a solution: http://wordpress.org/extend/plugins/wp-super-cache/ You may want to try google'ing for 'wordpress very heavy server load' as this is a common problem. It's one of the reasons I moved to MT. Sure it takes a few minutes to complete rebuild a site or under a minute to publish a post but then it's no load what so ever until I do something again to the site. Very light on the site. Hope this helps, -drmike
Thanks mike for your help, I actually installed supercache though I'm not sure it won't cause trouble to my search targeted advertisements!! Anyway, I've also shaved quit a lot of weight off my database by deleting unnecessary(at least by my opinion!) tables, It's a bit better now, but still seems like a script or something is keeping the server busy, and I can't find it. I think the best solution would be to move to a VPS, specially as the traffic is growing so I eventually will have to, whatdaya think? will VPS be trouble free in this sense??
@theapparatus: Thanks for the link ... i have added those lines in every of my wordpress site now @Dr-AMG: Even i faced the same problem .. see even if u disable the plugins things like "Latest Posts" ," Recent Comments","Popular Posts" etc will cause queries too .. See whether u can disable a few .. and also see whether u can replace common tags like <?php bloginfo('url'); ?> etc with the actual ones ... And set the expiry time in Super Cache to 7200 sec i.e, 2 hrs
Instead of deleting all these nice features, I think upgrading to a VPS is a better option. You're probably going to need it eventually, so you may as well do it before making unnecessary changes to the site.
I do refer you transfer to VPS, even if you user super cache still it will eat up a bit and depends on visitors pageviews or you just did call all the crawlers of SE
Hey guys, thank ya'll for advises weirdly supercache plugin just caused more server load !!!! it even hit 16 (8cpu) last night :O so is diabled it, this problem is really strange, i have made all the other adjustment including replacing theme tags, but still mysqld process keep using 700% + cpu! I.m now removing current database and installing my recent backup to see if it helps BUT, I'm really thinking about moving to a good VPS, I dont wanna ruin my site by disabling features I'm considering EuroVPS since they are cheap and look reliable, please share your advises regarding this case... Thanks
EuroVPS as name suggests are located in europe and are a reliable company.any reason for european location? Also, do your full research before choosing a provider
@gary4gar No actually my only reason for considering EuroVPS is their cheap price (14 euros/month) for the basic plan which seems to cover my needs for now most of my traffic is from US, will it affect the traffic if I move server to Europe??
If most of your traffic is from US then its not smartest thing to move to Europe. because latency would increase. further you can find providers catering to your price point in US also. So I recommend a provider with US based location as most of your visitor base is from US, the site would be loading faster
You make sense Gary, I will search for US based providers, with this level of server load, I'm gonna get banned on my shared host again sooner or later! so I gotta be quick, I checked the one in your signature, price is good, but since I'm a noob! I didn't get it if that plan is sufficient for a heavy wordpress site with visitor around 5k a day (I guess it'll get to 10k in a couple month with daily 30k pageviews)?????? any recommendation is much appreciated
If this is a shared host, are you sure that you are the one causing the high server loads? The processes you see through the top command are for the entire server, not just your account. It may be that another account is causing the loads to spike. The server admin should also be looking at optimizing mysql on the system itself.
Really , I didn't know that! apart form the top, the number I see in front of server load in my cpanel, is that also for the whole server or that is just me?? BTW, today it hit 42.5
A good solution is a VPS. Then you can install the memcached daemon and the PHP Memcache client. Then there is a WP cache script you can simply download and you're done. Using that solution my page view queries dropped from over 80 to just 6. If shared hosts could/would offer a memcached service for their clients they'd save everyone the frustration of heavy loads and non-responsive WP installations.
If you have direct access to MySQL on the server, just watch it with the "show full processlist" command and see what specific queries are wrecking it...
Problem is I have minimum access possible! I cannot even view my.cnf, Now that I realized that server load is for the whole thing and not just my account it's a bit of relief, after all I reduced the size of mysql from 172 to 42 MB and made some other adjustments that you guys suggested and I'm very thankful for, (only super cache plugin didn't work properly as it increased the load immediately once activated) so if the host picked on me again I will fire back with complaints about their server(and then they will shut me down!! hehe) I believe VPS is the ultimate solution here mainly for the freedom it gives adjusting things, I'm looking for a good company, one that last forever and I wouldn't have to change it in a year or so If are happy with your VPS provider (price,reliability,support, etc..) please tell me about it, (or PM me if it's not right to post here) Many many thanks
For starting, decide. you need a managed or unmanaged VPS. Managed is when hosts helps you with software issues. Unmanaged - you are on your own
Well make a PHP script or something that just does the query "show full processlist" and then spits out the output for you... that's one way of seeing what processes are going on.