MySQL Performance Blog

As of MySQL 5.1 get MySQL slow query log logged in mysql.slow_log table instead of the file as you had in previous versions.
We rarely would use this feature as it is incompatible with our slow query analyses patch and tools
Fixing this is not trivial while staying 100% compatible to standard format as TIME type which is used to store query execution time and lock time does not store fractions of the second.

Today I’ve got some time to play with table based slow query log in production while tuning one of the systems. It is pretty nice to be able to work with data in SQL as it easy to filter all queries which happened within certain time interval (ie after I’ve done some changes) or filter out queries which you already looked at using LIKE statement.

As default table format for slow_log is CSV with no indexes typical operations like finding how many queries were accumulated in the logs or finding 10 last queries is not fast. The good thing however you’re allowed to change table to other storage engine and add extra indexes as you require. Doing so however may affect your performance - updating table with a lot of indexes may be significant overhead, so you can instead create analyze_low_log table of similar structure with needed index and populate it with insert… select statement as you need it.

One little gotcha which confused me first is the order query come in the log file. If you used to do tail -100 log-slow.log and now change it to SELECT * FROM slow_log ORDER BY start_time DESC LIMIT 100 you would find new queries coming to the top of the list rather than in the end. If you want to get new queries in the end you can do
SELECT * FROM (SELECT * FROM slow_log ORDER BY start_time DESC LIMIT 100) l ORDER BY start_time which shows last queries in the end similar to tail command.

If you’re lazy typing it over and over again you can create stored procedure, something like LAST_SLOW_QUERIES and use it instead, just remember unless you add extra indexes this table this can be rather time and resource consuming.

This is actually where being able to sort records by physical position quickly without need to have indexes would be quite handy

I also should tell log table implementation in MySQL is well though of in regards to avoiding trouble. For example you can’t ALTER log table while query is running, neither you can delete rows from it (or do any write queries) only read access is possible, with exception of TRUNCATE query. Which just recreates table which should be fast. Interesting enough however what would happen if one would use storage engine for log table which does not optimize TRUNCATE ? I guess it should be blocked as normal DELETE statements are blocked.

More than that. I found even if I convert table to MyISAM, disable concurrent inserts and run long reporting query on the log file query execution is still proceeds. I have not checked if concurrent insert is always forced for log table if it is allowed or if there is a buffer of slow log queries which is used if log table is locked.

One thing which would be rather handy is UDF or stored procedure to “normalize” query by removing comments and replacing constants with some placeholders as this would allow to aggregate log entirely in SQL finding most frequent slow query types or queries which load server the most.

The other handly feature would be the function which returns as as SET list of tables which query is using. This would allow for example to find all queries touching given table quickly which is now hard to do reliably.

I guess over time we would have more tools provided with these and similar features if not by MySQL than by community.

Reading this slashdot article today and two CIO magazine articles linked from it.

Such discussions started at right place at right time always attract a lot of flamers and can be fun to read.

What hit me this time is quality of the articles in CIO magazine. If this is what managers suppose to use to make their “informed” decisions about products, not a big surprise huge portion of IT budgets are wasted. It looks like someone who has not got a clue is writing for someone who does not even pretend. I see zero “meat” - mostly using of marketing materials. This applies both to pro-MySQL and against-MySQL articles.

The funniest argument for me was the age of the product. Where does this “older is better” comes from ? Oh yes I know, it comes from the point of view market old timers try to show the value of their technology to their customers and shareholders.

In reality however most of 30 year code would ether have to be scrapped and rewritten or it would become passive being hard to maintain and extend. Over time systems tend to get entropy it is as true as people aging as they are getting older. You can postpone things by healthy coding techniques as you can do by healthy living but you can’t stop the process completely.

Take a look at MaxDB for example, which has extremely old code base (with a lot written in pascal).

Indeed you can apply age argument to the code which is few months/couple of years old - indeed this may be not battle tested enough or just too dynamic in development (nothing stops you from starting to actively break old code though). Indeed there are some problems which may happen only once or twice a year in production but 10 years is enough by far.

As old time MySQL User I should say MySQL stability have not much improved (really going up and down) since MySQL 4.0. This even applies to Innodb tables which were already on par with general MySQL level of stability in MySQL 4.0

But now get back to the question of using MySQL.

I’m obviously biased and focused more on Web applications rather than Enterprise applications (though these are merging a lot these days). From my standpoint the question is when or how to use MySQL not just if it should be used or not.

There are many applications when MySQL works great, when there are some of applications when MySQL can be made to work and there are finally cases when MySQL limitations are not worth the trouble - would require too complex application changes or some other ugly workarounds.

In very many cases the problem of MySQL use is not problem of the product but problem of state of mind - you just develop applications for Oracle and MySQL differently and if you’re try to apply same patterns to MySQL as you used for Oracle or move existing applications without changes you may well run into trouble. PostgreSQL is probably closer to Oracle in this state of mind thing.

It is interesting one of mentioned reasons to use MySQL was MySQL simplicity and I’m afraid this is what is gradually leaving us.
MySQL 4.0 was indeed simple - you could not do anything complicated with it and you could get to know pretty much all features within rather short time. MySQL 5.0 with spice of enterprise features is far from being simple and MySQL 5.1+ with wide choice of storage engines are getting even more complicated.

It is true so far it is different degree of complexity than many other DBMS but it is increasing.

On the other hand many successful MySQL users would use few new technologies besides Stock MySQL 4.0 toolset. For example Google still has a lot of stuff on MySQL 4.0… looking at the patches they designed themselves such as Semi-synchronous replication, advanced Monitoring and scaling features some users need other features than MySQL adding in newer MySQL releases.

In other cases I can see people using for example SubQueries and Partitions and information schema for ease of management but rarely I would see large amount of big post MySQL 4 features used at the same time.

This is of course only technical side. There are obviously a lot of other factors but they are more obvious.

For Web shops and for most of enterprises you can use MySQL for free same as Linux. You do not have to pay for licenses and all
unless you want some extra services. You can use Community version and solve things yourself or use third party support, which indeed works as MySQL is OpenSource and nothing stops you from fixing bugs yourself or adding your own features. Again it is very similar as Linux - if you do not want to pay for RHEL you can use CentOS, Fedora or Debian,Ubuntu.

Most articles speaking about MySQL for Enterprises speaking about MySQL with full pack of services from MySQL AB. Obviously this is how MySQL AB wants its product to be seen so whomever uses MySQL buys services but it is important to understand this is not really required. Of course many enterprises may need that because of their internal policies and state of mind, but this may not have yet adapted to open source software world, at least when it comes to the classic enterprises.

Having said that I’m not advocating against buying services from MySQL. They indeed may be more efficient for you than having your own tool set and DBAs which can resolve all MySQL issues with help of Google rather than MySQL Support Team. You also may want to buy some services from MySQL simply so MySQL Development is continued. My point is simply you do not have to do if you do not have money for it or if you do not need them.

So this is fun articles and discussion to read and it brings up many thoughts but I better stop right here and go back to real work

I had this question asked many times during last week, and there is really no easy answer. There are just way too many variables to consider especially while loading large schemas with a lot of different table structures. So which variables affect the load speed:

Table Structure This one is tricky. The shorter rows you have the faster load will normally be, if you measure rows, but if you measure in amount of loaded data longer rows are faster.

Indexes The more indexes you have the longer load would take. Details depend on storage engines, for example MyISAM can build non-unique indexes by sort which is very fast and Innodb can use insert buffer for them.

Data Insert Order It is well known it is important to load data in primary key order into Innodb tables for optimal performance, but effect does not end here. If indexes are built by inserts in Btree order in which entries are inserted in the index can affect performance dramatically.

Table Size This is the worst trouble maker. Typically load starts fast but as it progresses it may slow down dramatically, typically if Index BTREE has to be built by insertion and data does not fit in memory any more. I’ve seen cases when load speed starts at 30.000 rows/sec and then goes down to less than 100 rows/sec which can kill your initial estimates and can cause much longer downtime than planned.

Hardware Configuration If you need to load data quick do it on decent hardware. Especially memory size is important. If you need database on some small box it may be faster to load it on the more powerful box and copy it back in a binary form.

MySQL Configuration Different storage engines have different settings which need to be set for optimal load speed. Depending on load type MyISAM may benefit from bulk_insert_tree_size increase myisam_sort_buffer_size or key_buffer_size increase. Innodb typically needs large innodb_buffer_pool_size and large innodb_log_file_size to perform load effectively

Load Options There two main ways to load data in MySQL you can use Multiple value insert (standard mysqldump output) or LOAD DATA INFILE (–tab mysqldump output). Generally LOAD DATA can be optimized better and a bit faster because of easier parsing. Mysqldump however takes care of special options to make multi value inserts as fast as possible as well, such as disabling indexes before the load and enabling them back - helpful to make sure MyISAM build indexes via sorting not by BTREE insertion. For Innodb SET UNIQUE_CHECKS=0 may be used to speed up load to tables with unique keys.

So as you can see there are many variables which affect load speed which makes it extremely hard to predict. Especially fact load can slow down dramatically as amount of data loaded in the table increased and the fact this depends not only on table structure but the data itself (it defines the insertion order for the indexes) cause the challenges.

If you have some particular history about loading similar data of similar size on similar hardware use that as estimate. If not use your best guess based on what other cases were “similar”

As the data load goes I usually tend to do something like “du -sh; sleep 3600; du -sh” in the database data directory to see how much data is loaded per hour (assuming your Innodb tablespace did not have free space or you’re using innodb_file_per_table=1). Do not expect the value to be constant though. During our recent data load speed we for example could see data load speed of 60GB during one hour and 10GB during another depending on the tables which were loaded.

During our recent data load we loaded almost 1TB of Innodb data within 24 hours. It was decent speed because we had tables partitioned and so no table was more than 15GB in size (allowing to fit all BTREE indexes in memory) plus we loaded data in parallel and our rows were relatively long. In most cases I would expect load speed to be a bit slower, sometimes much slower.
I’ve seen cases when loading 100-200GB of data in single table was taking a week due to amount of indexes and the fact they poorly fit in memory.

As I wrote we had been recovering corrupted Innodb Tablespace and it is finally done now.

As this was over than 1TB worth of data we really tried to avoid dumping the data and find some other way to recovery.
Examining Innodb page content and crash information we figured out it should be page of insert buffer itself, rather than page belonging to some of the pages touched by insert buffer.

If that would be just the table it should have been easy - dropping the table with corrupted page would remove all appropriate insert buffer records and be able just to load that table back. Unfortunately we were not that lucky.

If the page corrupted in insert buffer itself there seems to be no way to make Innodb avoid touching it. Even when we dropped all Innodb tables (for test purposes) Innodb still was crashing if we attempted to enable insert buffer.

I think it would be nice for Innodb to have some additional recovery options for example for skipping over insert buffer or undo buffer. True this would leave some tables corrupted but at least you could get by by dumping in reimporting couple of tables rather than full dump and restore.

The bug which we found during attempt to dump tables with innodb_force_recovery=4 was confirmed to be added in 5.0.33, meaning you could not really recover corrupted Innodb tables this way for about 6 months.

What does this mean ? I guess it could mean all sorts of different things:

Versions later than MySQL 5.0.33 are not frequently used True many distributions still have versions before this one which could have some impact.

Innodb tables rarely become corrupt Indeed even if you leave out crashes in my experience Innodb tables become corrupted more seldom than MyISAM. Thanks to checksums and crashing in case of corruptions and a lot of assertions most bugs were cleared out.

People finding the bug do not report it This is also probably true. Myself I probably report half of the bugs I find, the once I find most annoying or find easy to provide repeatable example for so they can be fixed.

Corruption usually happens in secondary index, so simple ALTER TABLE fixes it This also seems to be the case. In my experience probably 80-90% of Innodb corruptions are fixed this way, especially if they are not caused by faulty hardware.
Primary keys are typically simple integer keys in Innodb while secondary keys may have very complex structure.

Compex corruptions are resolved by dump and restore from backup. I think this is the main reason. In fact dump and reload process can be so slow restoring even week old backup and running roll forward recovery using binary log can be a lot faster.

The consulting load keeps increasing so we’re looking for some help.

This job would be perfect for someone interested in high performance and scaling with decent knowledge of MySQL and eagerness to learn more.

We do encourage people from all countries to apply.

Dealing with dumping and recovery of large and partially corrupted database I’ve got couple of feature ideas for mysqldump or similar tool and appropriate import tool

Dump in parallel single thread dump is not efficient of course especially on systems with multiple CPUs and disks. It is lesser issue in recovery case because import takes most of the time anyway but will be handy for some backup needs.

Dump each table in its own file This is much more convenient compared to single say 100GB sql file allowing to prioritize data load and if load fails for any reason you can easily restart it. Also it allows to prioritize data load if you’ve got to load many tables which have different priority for your application.

Safe Dump Dumping corrupted Innodb tables you will have some of the them crashing Innodb which breaks mysqldump process. It would be great for load script to record in the logs such table was not dumped successfully, wait for MySQL to complete restart and continue going with other tables.

Parallel restore This is absolutely required if time is the issue as serious systems may perform much better in such case.

As usually when need arises there is no time to implement solid solution which will work for a lot of people so we get by with few quick and dirty shell scripts to do this.

I have not had a serious Innodb corruptions for a while, typically even if it happened it was some simple table related corruption which was easy to fix on table level. In couple of cases during last year when it was more than that we had backups and binary logs which means it was easier to recover from backup and replay binary logs.

This time I have a challenge to play it hard way because backup is in special form which will take a while to recover. It also should be nice exercise in patience because database is over 1TB in size.

One bug I already reported makes me worry. If it is global bug it should have been Innodb recovery show stopper while it goes back so many releases (5.0.33 surely still has it).

Lets see what else we run into.

One minor “practicality” I should mention is using –socket=/tmp/mysqlx.sock –port=3307 or something similar to make sure MySQL is isolated from all scripts which may bother it for the time of recovery. For complex systems it may be very hard to ensure no one touches MySQL using other ways.

We have certain type of the table corrupting with Innodb, as it is limited to only one particular index on one particular table type it is likely to be Innodb bug but Heikki currently could not find what could be causing it.

Happily as we have data stored in many tables of same format rather than one monster table these rare corruptions did not cause too much problems to us - as it was not clustered index we always could repair table by running ALTER TABLE with limited impact to production system (as only one table of about a hundred will be locked)

So everything kind of worked (of course we still hoped new MySQL release will have this Innodb bug fixed) until today we got MySQL to crash during recovery process right after 17% of log records were applied.

Such corruption is one of the worst onces, because you can’t really recover data on per table basics.

Also if MySQL crashes during recovery lower values of innodb_force_recovery normally would not work. Reading Documentation one may think you need to set it as far as 6 (SRV_FORCE_NO_LOG_REDO) if crash happens during redo stage of log recovery. As it is very nasty option I wanted to check if this is really required by trying lower recovery settings.

Happily I was able to restart MySQL with innodb_force_recovery=4 (SRV_FORCE_NO_IBUF_MERGE). So in my case probably Insert buffer merge was affecting corrupted page rather than log replay itself so I got lucky.

My initial plan was to now dump potentially corrupted tables (in this particular corruption instance Innodb did not print table name in question), drop tables, restart Innodb without innodb_force_recovery and load them back. But just yesterday one of the customers told me he had Innodb magically healing itself after he was able to complete recovery with innodb_force_recovery=4. So I decided to check if I’m to get lucky second time today.

And Indeed Restarting Innodb with innodb_force_recovery=0 allowed it to start normally and I could rebuild affected tables my
“normal way”.

So when dealing with Innodb recovery for bad corruptions you may want to:

- Start with lower innodb_force_recovery settings and see which one allows you to start.

- After recovery succeeds you can try restarting with lower recovery setting as it may allow you to recover more data or avoid full dump and restore.

Another trick which helped me in previous instances, but not this one is to do full system restart before attempting recovery. In few instances problems were caused by Kernel bug, inconsistence in OS cache or RAID cache or something else which made problem to disappear after system restart.

I generally find MySQL Sever sufficiently tested, meaning at least minor version upgrades rarely cause the problems. Of course it is not perfect and I remember number of big issues when some releases could not be used due to behavior changes in them and when something had to be rolled back in the next release.

But generally I think MySQL is doing much better than a lot of other projects in this respect.

Major version upgrades become more painful recently. In MySQL 3.22->3.23 upgrades or 3.23->4.0 upgrades you really could just swap binary and try out new version and if you need to get back you just swap binaries to the old version. I think it was great.
In MySQL 4.1 and later more caution is required because you could screw your data, for example by specifying utf8 as default character set together not to mention infamous timestamp format change which forced to change a lot of applications.
MySQL 5.0 added more issues to the pile with a lot of data type and sort order related changes, this is why long needed CHECK FOR UPGRADE was added in the newer versions and why number of people established slow by safe upgrade procedures - dump your data and reload.

Little tip to mention - upgrading to the new major version make sure you upgrade to latest current version at first. Ie upgrade to recent MySQL 5.0 before upgrading to MySQL 5.1 - this makes sure you will be at somewhat tested upgrade/downgrade path and your chances of successful downgrade if having problems with new major release will increase significantly. Making sure you can afford mysqldump and reload in worse case scenario is also good idea.

Anyway I was going to write not about MySQL Server upgrade process but rather about my experience with RPM scripts used during install and upgrade. These are obviously not part of automated mysql-test process and I’m wondering if they are tested at all.

For example if you use RHEL4 in default configuration (which is having SELinux enabled) upgrading RedHat RPM to MySQL “RedHat” RPM will not allow MySQL to start because SELinux will not let it. In some cases you will not even get server logs created because SELinux restricts console output as MySQL changes name to “mysql”.

Come On, as I remember MySQL And Redhat should be partners or is this partnership happening in area of marketing and taking money from the customers and have limited technical side ?

Today I spent another quite a while trying to Upgrade RedHat RPM binaries to MySQL Community RPM. Of course you may argue MySQL is not responsible for upgrade process from some “third party” binaries but I would argue they should as it is most common process - you get RedHat or Fedora which comes with MySQL binary and if you’re standard MySQL user you would just use that RPM and only upgrade it to something else if you need it for any reason. And this path does not work that great.

I logged some of my experiences as a bug but here are some more details about the problems

Upgrade Script fails to shut down MySQL Server. Obviously nothing can work without this one.

Upgrade Script Removes /var/lib/mysql symlink I’m not sure what is the problem about having this a a symlink - I like this approach a lot because it allows you to have database logically in same standard location while move it around as you seem suits. But even if there is the reason removing symlink on upgrade is just bad idea - it would be much better to tell me it is not supported any more so I go and change my environment instead of just finding it broken by upgrade. Not to mention it may be scary to find /var/lib/mysql gone.

If I’m not mistaken in one other instance I had new /var/lib/mysql created (probably when I retried second time) - this is even more scary to get running MySQL but find the database is gone now.

mysql user removed I’m not sure if this one is done by RedHat uninstall script or MySQL install script but simply I find no mysql user after upgrade.

MySQL Does not start after upgrade This is simply result of upgrade. Obviously with symlink removed

I can’t call myself a best expert in RPM out where but this was pure “rpm -Uvh MySQL*” in the directory with few downloaded MySQL binaries so it should be pretty straightforward.

As you probably know MySQL is Running AdSense Ads on PlanetMySQL.org for last few months. When the project was just started it was Ad free. I’m curious If MySQL Really ask syndicated Blog owners if they would allow that or if they just hope if someone does not like it he would speak up ?

I recently found an blog post on the same matter which make me to think on this once again.

So what is my take on this ? We currently run no Ads on MySQLPerformanceBlog and I would not like to see someone making money from something I have chosen to be free.

Would I like to be removed from PlanetMySQL aggregation ? No because many MySQL users are subscribed to PlanetMySQL rather than tens of individual MySQL blogs they would like to read, and I do not want to cut my RSS feed for the same reason.