MySQL: Innodb bottlenecks

Recently we've discovered couple of Innodb performance issues I was not aware of. Well... I knew these phenomena exists but I did not think
the impact is that dramatic.

1) Innodb auto_increment lock.
We've been seeing a lot serious performance degradations with SysBench tests with Innodb tables with large amount of connections. At 1024 connections
performance could be 10 times lower than with 10 connections. Well, it turns out the lock taken for auto_increment column for insertion is responsible for large portion of it. The sad thing is this lock is taken even if you explicitely specify value you're inserting - as soon as column has auto_increment column. We're still to investigate why regression is so serious but in the meanwhile the advise could be to avoid using auto_increment
if you do not really use it. The good part however is - it is not that typical case. Many connections I'm speaking about is not really number of connections but of concurrently running query - if you have 1000 connections but they are idle most of the time you would not see this effect.

2) The second surprise was "deleted index entries" - the fact is Index in Innodb contains pointers to all rows, even thouse which are deleted,
or which changed column value so they no more match the current key. This is done to allow using indexes for reading old versions, however this means
queries may slow down significantly if there is long transaction running which does not allow purge to happen. Just to clarify - it is _any_ long transaction - even the one which does not touch table in question - Innodb needs to maintain old version for old tables as there is information which table
going transaction will need to access next. So if you have some script which hung for hours with trasaction open you might be in trouble.

So when this hurts a lot ? When there are a lot of rows in the index which are no more where. The typical case when I've seen is for example various queues implementations. For example you have flag "needs_review" in the table which is set to 1 when object needs to be reviewed. You may
have simple query to pull up next one for review - "select * from tbl where needs_review=1 limit 1" - Logically this query will require single row read by index, but in practice it may be pretty long index scan to find first non-deleted row.

This tricky thing versioning adds are often forgotten about.

Faking the sync

Someone could find it an old and boring story but it is still popping up and people are still loosing data, so it is
worth to go other it once again.

MySQL/Innodb as any other transactional database relies on OS guaranties to provide its own. Transaction will be durable
and database intact on the crash only if database will perform synchronous IO as synchronous - reporting it is done when
data is physically on the disk.

Read more...Collapse )

MySQL: Upgrading MySQL 4.0 to 4.1

Upgrading to next MySQL version was usually quite safe. I remember quite painlessly uprading from 3.22 to 3.23, Upgrade 3.23 to 4.0 cause a bit of head ache as behavior of substraction was changed, this however was easily fixed by --sql-mode=NO_UNSIGNED_SUBTRACTION option.

With upgrade of 4.0 to 4.1 there are much more surprises, which I still see users and customer running into. We have extensive list of upgrade notes
but the practice shows not everyone has read it before upgrading:

So few highlights, ranging from minor to serve.

- There are various minor but annoying syntax changes. For example you can't use "create table x(i int)" any more as I became function,
you also now need () in your UNION queries when you previously could have skipped them.

- SHOW CREATE TABLE and SHOW TABLE STATUS was changed to use "Engine" instead of "Type" which broke some of my scripts which detect storage engine of the table to decide how to work.

- ENUM values are now case insensitive by default which can give you some surprises,

- TIMESTAMP fomat output has changed significantly so if you parse it in your scripts it is very likely they are broken.

- "Illegal mix of collations" can drive you nuts, for example previously you could compare string to binary string while now you can't

- Sorting order was changed if you have characters with codes less than 32 in the end, for example "\t" and "\n" This can corrupt your tables, and even reported to hang some applications, so it is better to run CHECK TABLE after upgrade or check if you have such data in tables before. There are
few more cases when data dump/reload may be needed for upgrade.

- Authentication changes may break your applications linked to old client library, use --old-passwords option at server or relink

- Client library name (version part) changed so dynamically linked applications may no more work after upgrade. MySQL-Compat RPM package can be used
to cure it. Especially this is frequently reported with PHP

- Limit does not accept -1 as second parameter, use some large number instead (yes find all your queries and fix them)

- I also heard some changes were made in a way meta-data returned (column types etc) - this can affect PHP or JDBC applications getting extensive
use of it.

- Database,Table names and meta data are treated as unicode now, so if you used non-latin1 (ie Russian) table name you better to rename them for
upgrade otherwise you will get garbage where.

I'm sure there are more surprises, these are just I have seem most typically to happen.

Also be careful upgrading/downgrading if you're using UTF8. If you will configure MySQL 4.1 to use utf8 by default and just start it with MySQL 4.0 latin1 tables you're likely to trash your data, as there is no charset information in old tables and MySQL 4.1 will assume the data is in unicode.
So do not change character set straight ahead but run 4.1 with same charset as 4.0 before and use ALTER TABLE to convert tables to 4.1 format - this
has character set information so you should be safe.

And yes... Downgrade from 4.1 to 4.0 is no more safe with this all these changes. Worst of all it is not clearly sure when it is safe and when it is not
(On my dev machine I bounce between 4.1 and 4.0 pretty frequently and it works fine, but I do not do anything advanced wher), so take backup before upgrade and test it well before going to production (which is always good idea anyway).

Preparing for Users Conference

Time to work on Presentations for User Conference 2005. I'll have Tutorial and Presentation both on MySQL Performance.
Tutorial will be about general MySQL performance tuning starting from pretty simple and going to rather advanced stuff. Presentation
will be pretty advanced. If someone heard my presentation last year - This time I'm going to go even deeper.

You can find more information about conference at
Pre-registration is now open, so if you're planning to attend it is good to register now.

MySQL: Loading large tables with Unique Keys

Normally MySQL is rather fast loading data in MyISAM table, but there is exception, which is when it can't rebuild indexes by sort but builds them
row by row instead. It can be happening due to wrong configuration (ie too small myisam_max_sort_file_size or myisam_max_extra_sort_file_size) or
it could be just lack of optimization, if you're having large (does not fit in memory) PRIMARY or UNIQUE indexes.

Below is the internal letter I've sent out on this subject which I guessed would be good to share

Read more...Collapse )

MySQL: Making caching more efficient

In many applications the data is frequently accessed not uniform way. Some rows are accessed a lot while others barely are. So lets say you have 100.000.000 rows in the table while only 1.000.000 of them are regularly accessed. How much memory would you need to make all of your accesses served from memory ? If you would answer enough to hold 1.000.000 rows with indexes and overhead you might be miscounting quite a lot.

Read more...Collapse )

MySQL: Make sure fsync() works

I've seen people many times people loosing latest transaction(s) because of their broken OS fsync() implementation,
Problem probably was not that bad as no database corruption was happening due to the fact the problem normally was flushing
drive cache which seems to only affected sequential writes, so pages going via Innodb doublewrite buffer made it to the disk.

It looks however as it is not always the case. We got customer loosing a lot of data on MacOS X which known to have fake fsync(). I do not know if MacOS X fsync() was broken more than usual or some hardware could loose data for longer time
frame, still recommendation is the same - if you're storing sensitive data in MySQL make sure you platform is as safe as you need.

Poweroff boxes few times during some disk intensive multi use benchmark running and see if you get database corruption -
run CHECK TABLE to see if they are fine. Also try simple sequential insert application to make sure last insert record is
always where after poweroff (if you need such guaranties). If you're using external SCSI you can also try disconnecting
SCSI cables and other torches to emulate hardware failures you might have. Spend the time or you will spend much more time recovering your data if problem happens.

By the way this problem is not specific to MySQL at all, more or less it should affect all on disk databases.