Quite commonly I get a question similar to this - "My Innodb Buffer Pool is already 90% full, should I be thinking about upgrading memory already?"
This is a wrong way to put the question. Unless you have very small database (read as database which is less than innodb_buffer_pool_size) You will have all buffer pool busy sooner or later.
How to figure out if it is time for upgrade when ?

Look at number of misses per second Check number of innodb file reads and writes per second and see how high are these. Decent drive can do some 150-200 IOs/sec this is how you can guess about the load. If you just get couple of reads per second your working set fits to the memory very well if it is hundreds you're likely to be IO bound or becoming one.

Look at Iowait iostat -dx 10 will show disk utilization. Low (less than 50%) means there are rarely anyone waiting from disk …

Usually unused indexes are devil, they waste diskspace, cache, they make INSERT / DELETE / UPDATE operations slower and what makes them worse - it is hard to find them.

But now ( with userstatsV2.patch) you can find all unused indexes (since last restart of mysqld) by single query

PLAIN TEXT SQL:

1. SELECT DISTINCT s.TABLE_SCHEMA, s.TABLE_NAME, s.INDEX_NAME
2. FROM information_schema.statistics `s` LEFT JOIN information_schema.index_statistics IS
3. ON (s.TABLE_SCHEMA = IS.TABLE_SCHEMA AND
4. s.TABLE_NAME=IS.TABLE_NAME AND
5. s.INDEX_NAME=IS.INDEX_NAME)
6. WHERE IS.TABLE_SCHEMA IS NULL;

PLAIN TEXT SQL:

1. …

What bug makes you to recommend upgrading most frequently ? For me it is this bug which makes it quite painful to automate various replication tasks.

It is not the most critical bug by far but this makes it worse - critical bugs would usually cause upgrades already or were worked around while such stuff as causing things like "sometimes my slave clone script does not work" may hang on for years.

Entry posted by peter | One comment

Add to: | …

Over a year ago I wrote about pretty nasty Innodb Recovery Bug. I ran in the same situation again (different system, different customer) and went to see the status of the bug... and it is still open.

You may thing it is minor issue but in fact with large buffer pool this bug makes database virtually unrecoverable (if 10% of progress in 2hours qualifies as that). It is especially nasty as it is quite hard to predict. Both customers had MySQL crash recovery happening in reasonable time... most of the times until they run into this problem.

So what is the point ? Have modest expectations about when your favorite MySQL bugs are fixed (This is actually Innodb one, so Innobase/Oracle is responsible for fixing it not MySQL/Sun but there are …

I just need to get some basics off of my chest here, it’s by no means a full list but it’s the most basic list I can think of to start with, and it’s basic because I am surprised by some of the slop I’ve seen in production environments.

1. Highly available server clusters - this is different than load balancing cluster, if confused see here.

2. Disaster recovery

-> this means daily,weekly,monthly backups as well as off site backups, and tertiary backups as well as a plan to get those backups imported and running in production as fast as possible. Backups should have consistency checking when they are created.

3. Security

-> perimeter on the network, VLAN’d databases from the web/app servers, firewall, ACLs, etc

-> system level: strong passwords on OS and database accounts (no blank …

I probably don't spend quite enough time following progress around MySQL considering how critical the product is to us. I'd like to consider it part of the infrastructure in a way I treat Red Hat Enterprise Linux, ie something I can trust to make good progress and follow up on a quarterly basis. Naturally we have people who watch both much more closely, but my time simply should, and pretty much is, spent doing something else.

However, it seems MySQL really demands a bit more attention right now. Today I went and read Jeremy Cole's opinion about MySQL Community (a failure), and I have to say I agree on many of the points. MySQL simply has not yet found a model that works as well as that of Red Hat's Fedora vs Enterprise …