| Posted by arielkris, 03-24-2015, 07:44 AM | | I recently bought a new server, this is my server details:
Intel® Xeon® E5-1650 v2
2 x 240 GB 6 Gb/s SSD
128 GB RAM
Centos 7 minimal OS
I have more then 120,000 daily unique users (using cloudflare)
recently I saw that the server is getting load average of 50 in peak times and mysql server sometimes responding with connecting issues when there is high load average.
how should I config my server to prevent this?
Htop screenshot:
i.imgur.com/U7awVGj.png
Last edited by arielkris; 03-24-2015 at 07:57 AM.
|
| Posted by Srv24x7, 03-24-2015, 09:22 AM | | Hi,
I guess you are using Apache only. Having a caching in front of apache could be a better option. You can try installing varnish in front of the Apache that will act as HTTP accelerator serving the pages more quickly and reducing the overall I/O too.
|
| Posted by diman, 03-24-2015, 10:11 AM | | Hello,
I would start with following.
1. To make sure MySQL is not the reason. From htop it seems it's Apache which uses a lot of resources.
2. To use some HTTP proxy like Varnish or nginx.
3. Consider switching to nginx. We have been using it for a while and very happy with it. At this point I don't IO is the problem, it's just Apache's normal behavior. Clients send requests and Apache spawns new child processes for each of them. Some performance metrics could also help. munin can help with this, for instance.
Hope it helps.
|
| Posted by beri, 03-24-2015, 10:13 AM | | Yeah, having something that would cache requests like Varnish would do the trick. Unfortunately that would just be like sticking bandaid on it. Having a suspicion that your problem is MySQL... Load Average like in this case (not proc or memory spikes) usually happens when you have large IO Wait on MySQL caused by unindexed, badly done and/or not optimised queries... I usually tend to find the culprit then deal with it accordingly. However as Srv24x7 stated, Varnish bandaid might turn our to be work fine... Ideally you would put the Varnish first, then turn on mysql slow logs to check whats going on, then Introduce Redis as App cache, switch from Apache to Nginx etc...
|
| Posted by arielkris, 03-24-2015, 10:36 AM | | Ok doing that, some questions:
1. how much ram space should I give to varnish? (I have 128GB ram on the server)
2. is there anything else I might add?
3. I am using only Wordpress on all of my sites, I can't think of a reason that it's mysql problem, maybe too many connections?
edit: LOOKS LIKE VARNISH ISN'T THE SOLUTION
i.imgur.com/CgrVDkm.png
Another Edit (after 10min):
looking better - i.imgur.com/MgoKJLH.png
Last edited by arielkris; 03-24-2015 at 10:45 AM.
|
| Posted by beri, 03-24-2015, 10:53 AM | | 1. Give it as much as you are comfortable with (ie. 20 gigs)
2. Redis - google how to set up Wordpress cache with it (good for sessions too)
3. WordPress is a blog machine for general purpose that has indexed queries but by all means do not think of it as "the best DB layout in the world" as they have traded in some cases functionality for speed and vice versa... and if you add 10 3rd party modules in it... then it might not even be WP thats causing the prob. but some 3rd party plug which usually is the problem in 95% of the cases.
|
| Posted by arielkris, 03-24-2015, 11:18 AM | | Thank you beri,
I gave it 15G,
But How can I find which plugin in making the mess? (or which site)
I don't know too much but I installed mariadb, php and httpd and that's it, I didn't configure it (default), maybe I should configure something?
|
| Posted by beri, 03-24-2015, 11:39 AM | | Np arielkris,
Agreed it does look better, but this is still IMHO "bad".
Check MariaDB query cache settings (my.cnf) and try to increase those... What exact variables and which values to put idk you will have to google it... This would result in query not being run just returned from cache therefor lowering Load Average.
Next thing enable MySQL slow logs...
[mysqld]
long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log
and fiddle with long_query_time time while tailing the slow log till you get something useful ie. exec times of some queries over 3-4-5-6 secs... those are potential culprits... Keep in mind though... that if one of those crappy queries fire and cause Load all subsequent queries even if they are good will have long execution time...
When you figure out which query(ies) are causing the problem try to analyse it... and figure out which site it belongs to... thats gonna be a hard task... Usually we have separate MySQL sever on which sites connect to and you can see different users / computers executing queries so you know which one is causing it. In your case... dunno...
Last edited by beri; 03-24-2015 at 11:46 AM.
|
| Posted by arielkris, 03-24-2015, 12:06 PM | | MySQL test:
Time to perform: Query test (200 times) : 0.13 seconds (1538/sec)
Time to perform: select BENCHMARK(500000000, EXTRACT(YEAR FROM NOW())) : 8.94 seconds
Time to perform: select BENCHMARK(10000000,ENCODE('hello','goodbye')) : 1.18 seconds
Time to perform: select BENCHMARK(25000000,1+1*2); : 0.55 seconds
Total time (all MySQL tests) : 10.80 seconds
PHP test:
Time to perform: Math test : 0.35 seconds
Time to perform: StringManipulation test : 0.79 seconds
Time to perform: test Loop test : 0.33 seconds
Time to perform: test IfElse : 0.58 seconds
Total time (all PHP tests) : 2.05 seconds
|
| Posted by arielkris, 03-24-2015, 12:14 PM | | I think that this is the mysql... I did this in my.conf
innodb_buffer_pool_size = 384M
key_buffer = 256M
query_cache_size = 1M
query_cache_limit = 128M
thread_cache_size = 8
max_connections = 400
innodb_lock_wait_timeout = 100
and 10 sec after.. I got 120 load average
|
| Posted by beri, 03-24-2015, 12:28 PM | | This is something you will need to ask MySQL DB dude... we switched to MongoDB + Redis combo 2y ago and our lives got 100x easier... in your case this is a no go...
1. Back to Default with these and try to find some good DB Admin to tweak them
2. Your Math benchmarks above cannot be used for anything useful - you need to dig out bad queries from mysql slow log...
Also keep in mind that if you don't know how to analyse those and deduct where they originate and later how to fix WP to have 6 single table searches instead of 6 LEFT JOINS... you will not be able to fix this...
P.S. just got an idea... if you want to know which exact website is causing the massive load go in WP index.php and exit(); then leave it for 5 mins while TOP is on... this will give you pretty good idea which one of them is causing it, when load drops you found it...
Last edited by beri; 03-24-2015 at 12:32 PM.
|
| Posted by diman, 03-24-2015, 01:11 PM | | It would be much better if your data fit in InnoDB buffer which is set with innodb_buffer_pool_size. So if you have 512MB of data + indexes you should use at least the same amount of RAM for MySQL/MariDB. You could also use mysqltuner.pl to get hints on where to go from there.
|
| Posted by intime, 03-26-2015, 07:18 PM | | For Wordpress sites you could also try using caching. This can reduce the load on your DB by a fair amount and also speed up pages
|
| Posted by net, 03-26-2015, 07:38 PM | | Looks like your server is not properly optimized.
You need to look back and start from the beginning to see what you are missing.
|
| Posted by prashant1979, 03-28-2015, 11:46 AM | | If Apache is the culprit, then I would certainly replace it with Litespeed which would consume less resources and would serve your webpages faster. You can also buy a small caching module provided by Litespeed in case you opt for a single cpu Litespeed license. A Dual CPU Litespeed license will provide you caching option by default. Add another small SSD in the server. A 32 GB SSD would be fine. Use the new SSD for storing cached objects.
|
| Posted by Hosting4Real, 03-28-2015, 04:21 PM | | If you're using WordPress you should easily be able to serve 120k visitors a day. If your setup is just done correctly, and specially if WP is configured correctly.
|
| Posted by edigest, 03-29-2015, 01:46 PM | | Looks like most likely a MySQL tuning issue. Have you tried tuning-primer and mysqlreport?
A couple configurations that jump out:
innodb_buffer_pool_size = 384M --> 2GB (or more)
query_cache_size = 1M --> 128M
query_cache_limit = 128M --> 16M
thread_cache_size = 8 --> 50
+1 on LiteSpeed. I have a couple of 1-CPU LiteSpeed servers getting about 50% more traffic on E3-1270s and the load averages less than 0.5.
|
| Posted by nisamudeen97, 03-30-2015, 01:20 AM | | The main thing before taking decesion of configuration changes is to analyze server resource usage (cpu, memory and load), mail, apache and mysql access logs. You have to find out if some sites or certain links causing the issues. You also need to be aware about cron jobs of each user in the server. Need to verify if some certain cronjobs in peak time are causing the issues.
As per my openion the first thing that has to be done is analysis and find out the exact reason behiend the load spike. Once the exact reason is found we have to proceed accordingly. We may have to chose the best from the below set to reduce the server load
1. Webserver optimisation
2. Mysql optimisation
3. Enabling cache like varnish or xcache or apc
4. Reschduling of crons
5. Database optimisation
6. website optimisation
7. Reschedule backup if any
8. Migration of websites to VPS that needs high resouce
|
|
 Add to Favourites
 Print this Article
|
