lorax

It's going to depend on your system and where the bottlenecks are, how much memory you have, how much you're writing versus reading. I'm not an expert by any means. For a production system, you should get the tuning done by someone who really knows how to scale InnoDB for you.

I should probably just leave it there and tell the OP to go do some research. I will elaborate, but I will also say that take what follows below as a starting point for your research and please come back and correct any errors I make below - I'm just sort of shooting from the hip here.


In my case, I just do basic playing with values to get my local dev station so that it can run Drupal reasonably well with InnoDB tables (and I assume WP would have the same issues with InnoDB).

The basic principle is this - InnoDB defers writing to disk when it can, but constraints on the memory it's allowed to use, the requirement to be ACID compliant and so forth can force it to write to disk more often than you need.

If you are monitoring disk activity and you run a query (say load a WP page), you'll see disk activity spike to very high levels and stay there long after the page is loaded as InnoDB "catches up", that is takes care of the deferred work it needs to do. With the wrong values, InnoDB can almost max out your disk all day long with hardly any queries actually getting run.

So if you're having I/O bottlenecks, your goal is to get it to buffer to memory more and write to disk less. But if you're having memory bottlenecks...

And if you're having both problems, you need to get more memory and an SSD probably.

Anyway, looking over my config file (my.cnf except on Windows, my.ini) and trying to remember which values I've customized... (again, check my work!)

innodb_buffer_pool_size = 256M

I believe the default here is 5M, which everyone says is inadequate. I've heard it said (I don't know as this is official or speculation) that the value is so low by default so that you *have* to set it to get it to work decently.

I'm running with a fairly low 256M. The docs say you can go as high as 80% of memory, but I would say that would probably only be okay on a dedicated DB server. Like PHP memory, you don't want to go so high that you exhaust memory for other processes. I should probably bump this up to 512MB though - this is an 8GB machine.

innodb_log_file_size = 64M

Docs suggest 25% buffer pool size

innodb_flush_log_at_trx_commit = 1

This determines how and when the buffer is written to disk. 1 is required for ACID compliance, but if you can afford some data loss in event of a server crash, then you can set this to 0 and reduce disk usage.

Some of these are not straightforward to change. To change some log file settings you need to shut down the server, move the files to a new location, change your settings, reboot the server so it will generate new files, then once you've verified that it all works, delete the original files. You can otherwise end up with a server that won't start.

Here's an example for a serious InnoDB setup that can afford 4GB of memory for InnoDB
[gist.github.com...]

Hi ergo,
I split this off so we could all explore this more. For neophytes like me, this is a very useful topic that I fear would hijack the original thread.

So, I'm looking into this more now. My situation is that I have multiple WP installs on a dedicated server with 4GB. Now I'm thinking I want to bump that memory up to at least 8GB but I don't know enough about servers to know if it would even be able to use it. SO, my next step is to speak with the host about the max I can get on the box along with prices. This will set the ceiling for me. Then I can back into the rest of the setup based on what I have to work with.

I don't really have a lot to add honestly.

Again, I don't want to present myself as a server ninja. I'm not.

That said, you can always make good use of more memory :-)

I would say that you need to find out where your bottlenecks are though, principally

- I/O
- CPU
- RAM

If your system is bogging, you might want to start by profiling Wordpress, but you may not have control over this. If people absolutely have to have a given feature, telling them it burns a lot of horsepower may not change anything.

Quick example - I had a site that was bogging down after some changes. One change I made was using PHP to grab the image size so I could inject proper height and width attributes into the HTML for the IMG tag. When I profiled the site it turned out that about 80% of the time was spent grabbing these dimensions on a gallery page. Simple fix and now, in the age of responsive sites, it's better to avoid coding dimensions into your image tags anyway.

But assuming that you can't do anything at the software end, you can always shift things around.

If you're CPU/DB constrained, for example, you can put a reverse proxy (Varnish, Squid, etc) on the front end and do a lot more caching.

If you're I/O constrained, you can load things into memory with Memcache so you get a lot fewer disk hits. I'm not sure how big your DB is, but with an extra 4GB, you might be able to just load your whole MySQL database in memory with the understanding that a system crash between writes to disk would cause data loss (a reasonable tradeoff for a pure content site, but not acceptable in other contexts).

And then, finally, if you're not already on an SSD, you might look into spending your money there rather than on memory. But you would first need to benchmark with a high load and find your failure point.

Thanks ergo. I hadn't thought of using a SSD - I inhereted the setup I have now. I actually just negotiated a new 2 year contract with the vendor. I could probably get modifications without and issue.

Digging in now. I'll share what I learn here.

First up. Developing a cron job to remove revisions of posts on a regular basis.

Second learn more about how to measure I/O on the database. Any thoughts on using performance_schema ?

No thoughts on performance_schema. Looks good

You can get some idea of things with your basic Unix tools
- top
- iostat

But like I said, I've just done what I need to get simple setups running okay.

List of tools here:

[mysqlperformanceblog.com...]

Many good articles there about tuning MySQL.

have you looked at MySQL's reference page on performance tuning?

MySQL Performance Tuning and Optimization Resources:
http://www.mysql.com/why-mysql/performance/index.html [mysql.com]


probably a good idea to see what the "new" owners are referring to on the subject.

MySQL Tips & Tricks - MySQL - Oracle Wiki:
http://wikis.oracle.com/pages/viewpage.action?pageId=32015102 [wikis.oracle.com]

Thanks guys. I have looked into those resources phranque but to be frank phranque, I have a steep learning curve and it makes my head hurt. :)

The best way to optimize your database (and your application stack) is to ensure that you have rock solid caching at multiple levels.

Rendered pages do not need to be rendered again, unless something has changed - content edited, comments added etc. Use something like Varnish with content sensitive purging ( available in W4TC plugin).

This is far superior to application level caching as neither PHP nor MySQL are accessed unless your page has changed.

Also consider using a plugin like W3TC to cache queries and a bunch of other stuff in a memcached like setup. This will ensure further scalability.

Using these strategies we were able to get the query load down from 1000+ queries per second down to 20-50 queries per second on a WP based site that had around 2 million visitors over a weekend.

With a low query load like that you don't have to mess with changing your DB structure / indices etc.

No question that some sort of caching (or the combination of several sorts of caching) should usually be your first step if your server is bogging down.

This originally came up in the context originally of complaining (as I understood it) that a single page load was maxing him out and after a single page request he was having persistent high I/O and CPU activity and this was true even with plugins disabled.

That means that something is wrong on the server end, because Wordpress with very low traffic and no plugins should be fast without any caching at all.

So yes, in most cases people should look at the obvious. Especially for a content site with few logged in users, caching will give the biggest return for most people.

Good thread... it's worth mentioning that MyISAM relies on the OS cache but InnoDB will take care of its own cacheing, and that WP will use whichever storage engine is the default for your particular MySQL version (AFAIK).

InnoDB became the default in MySQL 5.5

If any WP tables do not have sequential inserts then they will become fragmented and will perform at less than optimal speed. [mysqlperformanceblog.com...]

shri! Goodness, haven't seen nor heard from you in years. Welcome back.

So caching at different levels is a good thing. Like the database, rendered pages, and what about DNS (using a CDN like cloudflare or amazon s3)? What are some of the gotchas in using caching at multiple levels. One I can think of might be that the results of tweaking anything in the delivery chain might not be apparent because one or more caches further down the delivery pipe haven't been updated or cleared.

BOL - how would one tell if they're using InnoDB or MyISAM? Is it easy using PHPMyAdmin or is this a command line type of check?

how would one tell if they're using InnoDB or MyISAM

For a whole database, something like
SELECT table_name,engine FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'databasename';

Or on a per table basis
SHOW CREATE TABLE tablename

Is it easy using PHPMyAdmin or is this a command line type of check

Whichever you prefer, both will show the same output. I prefer the command line as phpmyadmin will perform an extra query for pagination on the ui.

lorax - FYI - any site that was built on MySQl before 5.5 will still be running on MyISAM tables unless the site admin actively did something to convert to InnoDB.

As an aside, one should be careful about converting willy-nilly between MyISAM and InnoDB. As just one example, InnoDB by default has a lower max index length, being set to something like 765 bytes (as opposed to 1000+ for MyISAM).

If you have an index that's on a VARCHAR(255) field, that's all good... until you go fully international and switch to the utf8mb4 character set to support full Unicode... which means that your VARCHAR field now has 1020 bytes (255 chars * 4 bytes/char) and your site crashes.

>>PHPMyAdmin

If you want a nice GUI tool, check out SQLYog or HeidiSQL (the former being nicer in most respects, the latter having a great search feature though where it will search through all tables for text)

>> a single page load was maxing him out and after a single page request he was having persistent high I/O and CPU activity and this was true even with plugins disabled.

Enable the mysql slow query log.

log-slow-queries = [path to the log file]
and
long_query_time=5

That should reveal the slow query issues.

Most blogs will not have enough content to even slow down a half way decent server on a select * from wp_post type query.

The problem with innodb is that it does require a fair bit of tweaking - far more than MyISAM and is non-trivial, specially if you're not technical enough to drill down the code or the query that is causing the slow down.

InnoDB shines in high concurrency situations when tables are being hammered with updates. In MOST situations, myISAM works perfectly and is much more suitable for anyone who classifies themselves as a webmaster, as opposed to a database admin, IMHO.

If by chance you happen to be running PHP-FPM instead of mod_php in apache, take a look at the PHP Slow Query logs too.

request_slowlog_timeout - The timeout (in seconds) for serving of single request after which a php backtrace will be dumped to slow.log file. Default: "5s". Note: '0s' means 'off'
slowlog - The log file for slow requests.

>> shri! Goodness, haven't seen nor heard from you in years. Welcome back.

Lorax, I visit atleast once a day... but rarely have anything to say. :)

>> but rarely have anything to say

Well thanks for saying it when you do - some good stuff in your additions.

Yes, all valuable information. I'll report back as I work through my particular situation.

OT
@shri
Well I hope to see you in person someday soon. I believe I owe you some scotch and a few cigars - hmm... I think I owe the same to webwork too... coming to Vegas this year?

Jameson, straight, with a Guinness back, please.

(I won't be there, so somebody will have to drink it for me...)