rationalwiki.org is getting hammered again. It looks like MySQL is the busiest portion - seriously just doing a lot of work.
Our current arrangement is: one box for MySQL, Apache, Lucene (the latter reindexing weekly); two Squids; a load balancer. These are all virtual machines on Linode (who we like). Apache and Squid boxes are Ubuntu 12.04 servers.
The *usual* thing when we get hammered is that Reddit discovers an amusing tumbleweed article. The squids take care of this, of course. But then something like the Bill Nye/Ken Ham debate happens, we score pretty highly in Google for skeptical material and a wide variety of articles gets hit and MySQL has to work for a living.
So, what's a good approach to scaling up MySQL on a VM? Add more memory? Add more cores? (How's MySQL 5.5-ubuntu do for multicore?) We can trivially add more Squids, and we haven't doubled up on Apache but shirley that won't be entirely unfeasible.
- d.
I am sorry if this seems obvious, I am new to these lists but it looks like you are not leveraging cache.
On Tue, Feb 18, 2014 at 5:11 PM, David Gerard dgerard@gmail.com wrote:
rationalwiki.org is getting hammered again. It looks like MySQL is the busiest portion - seriously just doing a lot of work.
Our current arrangement is: one box for MySQL, Apache, Lucene (the latter reindexing weekly); two Squids; a load balancer. These are all virtual machines on Linode (who we like). Apache and Squid boxes are Ubuntu 12.04 servers.
The *usual* thing when we get hammered is that Reddit discovers an amusing tumbleweed article. The squids take care of this, of course. But then something like the Bill Nye/Ken Ham debate happens, we score pretty highly in Google for skeptical material and a wide variety of articles gets hit and MySQL has to work for a living.
So, what's a good approach to scaling up MySQL on a VM? Add more memory? Add more cores? (How's MySQL 5.5-ubuntu do for multicore?) We can trivially add more Squids, and we haven't doubled up on Apache but shirley that won't be entirely unfeasible.
- d.
MediaWiki-l mailing list MediaWiki-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
A few quick questions you can ask yourself and basic things to look into:
1. Are you using a PHP opcode cache like APC? Is it configured properly (enabled, enough memory, etc...)? 2. Are you using Memcached (or something similar) for $wgMainCacheType? http://www.mediawiki.org/wiki/Manual:$wgMainCacheType 3. Is your Squid caching everything it can? Look at your average Squid cache hit rates. Depending on your anonymous/logged in traffic it should be around the 80-90% hit rate. 4. Do you have the MySQL query cache enabled and configured properly? We're around the 60-70% hit rate on the query cache. https://dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html 5. Are you giving MySQL enough memory? On a busy site MySQL can and will use pretty much as much memory as you give it. On a server shared with other services I'd be concerned about having enough memory for it especially considering you mention that MySQL appears to be the bottleneck.
Regarding point 3: Looking at your site and the X-Cache/X-Cache-Lookup header responses you are not caching the actual page content for anonymous users which is the most expensive part of a page load. I remember having the same issue on our site and it required mucking about in the MediaWiki code (I don't remember exactly but something to do with setting the cache content for pages) as well as Squid to implement the X-Cache-Vary header (might not be required). I know after doing this our Squid cache hit rate went from 50% to 80-90%. Actually, looking at my site just now we're at only a 50% Squid cache hit rate...it appears we somehow reverted our custom changes so I'll have to look at what changed recently. If I find anything I can post it here but it may not be for a few weeks depending when I have time and how long it takes.
On 18 February 2014 17:22, David Chamberlain david@alaskawiki.org wrote:
I am sorry if this seems obvious, I am new to these lists but it looks like you are not leveraging cache.
On Tue, Feb 18, 2014 at 5:11 PM, David Gerard dgerard@gmail.com wrote:
rationalwiki.org is getting hammered again. It looks like MySQL is the busiest portion - seriously just doing a lot of work.
Our current arrangement is: one box for MySQL, Apache, Lucene (the latter reindexing weekly); two Squids; a load balancer. These are all virtual machines on Linode (who we like). Apache and Squid boxes are Ubuntu 12.04 servers.
The *usual* thing when we get hammered is that Reddit discovers an amusing tumbleweed article. The squids take care of this, of course. But then something like the Bill Nye/Ken Ham debate happens, we score pretty highly in Google for skeptical material and a wide variety of articles gets hit and MySQL has to work for a living.
So, what's a good approach to scaling up MySQL on a VM? Add more memory? Add more cores? (How's MySQL 5.5-ubuntu do for multicore?) We can trivially add more Squids, and we haven't doubled up on Apache but shirley that won't be entirely unfeasible.
- d.
MediaWiki-l mailing list MediaWiki-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
-- David Chamberlain http://alaskawiki.org/ http://alaskawiki.org/index.php?title=Alaska http://about.me/david.chamberlain Mission: To be the largest and most accurate source of information about Alaska. _______________________________________________ MediaWiki-l mailing list MediaWiki-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
My "knowledgeable beginner" input would be: - Check your Squids, see if you can look at their cache hit/miss and cache usage stats. -- If their caches are maxed see if you can expand the cache space they have available (more ram?) -- If they're not maxed but you have a high miss ratio check to see if you have something like a cookie that's busting the cache. - More ram "might" help MySQL but if you've got a lot of read load after checking out your Squids consider scaling that outwards by setting up some read-only MySQL slaves to load balance that read load and take it off of the master database server.
~Daniel Friesen (Dantman, Nadir-Seen-Fire) [http://danielfriesen.name/]
On 2014-02-18 2:11 PM, David Gerard wrote:
rationalwiki.org is getting hammered again. It looks like MySQL is the busiest portion - seriously just doing a lot of work.
Our current arrangement is: one box for MySQL, Apache, Lucene (the latter reindexing weekly); two Squids; a load balancer. These are all virtual machines on Linode (who we like). Apache and Squid boxes are Ubuntu 12.04 servers.
The *usual* thing when we get hammered is that Reddit discovers an amusing tumbleweed article. The squids take care of this, of course. But then something like the Bill Nye/Ken Ham debate happens, we score pretty highly in Google for skeptical material and a wide variety of articles gets hit and MySQL has to work for a living.
So, what's a good approach to scaling up MySQL on a VM? Add more memory? Add more cores? (How's MySQL 5.5-ubuntu do for multicore?) We can trivially add more Squids, and we haven't doubled up on Apache but shirley that won't be entirely unfeasible.
- d.
MediaWiki-l mailing list MediaWiki-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
A few thoughts:
* If you're not already using memcached with a big RAM allocation as an in-memory cache, I strongly recommend it. This will reduce churn on the 'objectcache' table in MySQL for things like localization caches and the parser cache (which may be churning a lot if you're seeing a large spike in both visits and edits).
* Consider separating MySQL out to its own VM for easier management or to split up memory usage. Watch for high i/o usage and make sure indexes are in memory... Beware that MediaWiki expects MySQL to be on the local network and makes lots of roundtrips -- make sure if you use a separate VM to locate it in the same DC at least.
* Consider setting up a read-only slave database in another VM; depending on your workload this may also help keep expensive lookups on the secondary. It's also possible to route certain kinds of queries to a particular database replica, which we use a lot on high-load Wikipedia sites. This is a little tricky but not impossible.
-- brion
On Tue, Feb 18, 2014 at 2:11 PM, David Gerard dgerard@gmail.com wrote:
rationalwiki.org is getting hammered again. It looks like MySQL is the busiest portion - seriously just doing a lot of work.
Our current arrangement is: one box for MySQL, Apache, Lucene (the latter reindexing weekly); two Squids; a load balancer. These are all virtual machines on Linode (who we like). Apache and Squid boxes are Ubuntu 12.04 servers.
The *usual* thing when we get hammered is that Reddit discovers an amusing tumbleweed article. The squids take care of this, of course. But then something like the Bill Nye/Ken Ham debate happens, we score pretty highly in Google for skeptical material and a wide variety of articles gets hit and MySQL has to work for a living.
So, what's a good approach to scaling up MySQL on a VM? Add more memory? Add more cores? (How's MySQL 5.5-ubuntu do for multicore?) We can trivially add more Squids, and we haven't doubled up on Apache but shirley that won't be entirely unfeasible.
- d.
MediaWiki-l mailing list MediaWiki-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
And oh yeah, add the HELL out of memory. Memory is fast, disk is sllloooow.
-- brion
On Tue, Feb 18, 2014 at 2:40 PM, Brion Vibber bvibber@wikimedia.org wrote:
A few thoughts:
- If you're not already using memcached with a big RAM allocation as an
in-memory cache, I strongly recommend it. This will reduce churn on the 'objectcache' table in MySQL for things like localization caches and the parser cache (which may be churning a lot if you're seeing a large spike in both visits and edits).
- Consider separating MySQL out to its own VM for easier management or to
split up memory usage. Watch for high i/o usage and make sure indexes are in memory... Beware that MediaWiki expects MySQL to be on the local network and makes lots of roundtrips -- make sure if you use a separate VM to locate it in the same DC at least.
- Consider setting up a read-only slave database in another VM; depending
on your workload this may also help keep expensive lookups on the secondary. It's also possible to route certain kinds of queries to a particular database replica, which we use a lot on high-load Wikipedia sites. This is a little tricky but not impossible.
-- brion
On Tue, Feb 18, 2014 at 2:11 PM, David Gerard dgerard@gmail.com wrote:
rationalwiki.org is getting hammered again. It looks like MySQL is the busiest portion - seriously just doing a lot of work.
Our current arrangement is: one box for MySQL, Apache, Lucene (the latter reindexing weekly); two Squids; a load balancer. These are all virtual machines on Linode (who we like). Apache and Squid boxes are Ubuntu 12.04 servers.
The *usual* thing when we get hammered is that Reddit discovers an amusing tumbleweed article. The squids take care of this, of course. But then something like the Bill Nye/Ken Ham debate happens, we score pretty highly in Google for skeptical material and a wide variety of articles gets hit and MySQL has to work for a living.
So, what's a good approach to scaling up MySQL on a VM? Add more memory? Add more cores? (How's MySQL 5.5-ubuntu do for multicore?) We can trivially add more Squids, and we haven't doubled up on Apache but shirley that won't be entirely unfeasible.
- d.
MediaWiki-l mailing list MediaWiki-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
On 19/02/14 09:11, David Gerard wrote:
So, what's a good approach to scaling up MySQL on a VM? Add more memory? Add more cores? (How's MySQL 5.5-ubuntu do for multicore?)
You would add more cores only if the CPU is maxed out at peak times. More memory (with almost all allocated to innodb_buffer_pool) would be advisable if there is a significant amount of disk read activity. The buffer pool size should be sufficient to hold all the indexes in memory. Use SHOW TABLE STATUS to estimate the size of them. Ideally, there should be enough memory to hold all the data as well, except for the text table.
-- Tim Starling
On 18 February 2014 22:11, David Gerard dgerard@gmail.com wrote:
rationalwiki.org is getting hammered again. It looks like MySQL is the busiest portion - seriously just doing a lot of work.
Update: so we're finally taking action on this. We're just putting up a new 4GB VM for Apache, we'll serve the main site from that which should free up a ton o' RAM for MySQL on the increasingly loaded 8GB main box. (We'll leave minor sites over there today, move them at our leisure.) We have a bit of cash on hand (we have no paid staff) so can put up a few extra boxes as needed. Thankfully, horizontal scaling is PHP's strength. I have reminded Trent of the high quality recommendations for other things to do to be found in this thread ...
(We just got sued by an aggrieved creationist. Thankfully WMF has already set precedent for sec 230 applying, so we're not at all worried about that. What we *are* worried about is well-wishers knocking the server over again ... INCOMING!!!)
- d.
I dont know enough about servers/linux as many of the other people here do and sorry if I repeat any of the earlier advice because I havent read it. Here's what has worked for our wiki although it has a smaller traffic ranking than rationalwiki (which is about 22K right now or close). Instead of Apache, use nGinx. Its good at serving files from what I've read before. Use the simple file cache: http://www.mediawiki.org/wiki/Manual:File_cache Also run APC. PHP type/mode is fpm-fcgi. Try it out and see what happens. Some google searches for "apache vs nginx": http://arstechnica.com/business/2011/11/a-faster-web-server-ripping-out-apac...
Another: http://www.theorganicagency.com/apache-vs-nginx-performance-comparison/ This has the same NGinx + php-FPM configuration I mentioned. it says:
*-As you can see, nginx offers a higher transfer rate compared to Apache, but also has less of a wait time between receiving the request and passing a response back. -I also noticed that nginx can handle more requests per second, and is able to ramp up as the load increases, however apache remained pretty static on this front.* In the 3rd and last comparison, you can see there are failed requests for Apache but none for Nginx in that scenario. That graph is: https://lh6.googleusercontent.com/TGZJ8_IWGWsn-0M6Txi9rrt7m5shn_Y5wwi475r8ew...
Could I ask how many page views a day (total for the site) you get when this kind of crash happens? Or per hour. Dan
On Fri, Feb 28, 2014 at 10:52 AM, David Gerard dgerard@gmail.com wrote:
On 18 February 2014 22:11, David Gerard dgerard@gmail.com wrote:
rationalwiki.org is getting hammered again. It looks like MySQL is the busiest portion - seriously just doing a lot of work.
Update: so we're finally taking action on this. We're just putting up a new 4GB VM for Apache, we'll serve the main site from that which should free up a ton o' RAM for MySQL on the increasingly loaded 8GB main box. (We'll leave minor sites over there today, move them at our leisure.) We have a bit of cash on hand (we have no paid staff) so can put up a few extra boxes as needed. Thankfully, horizontal scaling is PHP's strength. I have reminded Trent of the high quality recommendations for other things to do to be found in this thread ...
(We just got sued by an aggrieved creationist. Thankfully WMF has already set precedent for sec 230 applying, so we're not at all worried about that. What we *are* worried about is well-wishers knocking the server over again ... INCOMING!!!)
- d.
MediaWiki-l mailing list MediaWiki-l@lists.wikimedia.org https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
mediawiki-l@lists.wikimedia.org