PlanetMysql.ru – информация о СУБД MySQL

TRUNCATE is usually a fast operation (much faster than DELETE FROM). But sometimes it just hangs; I’ve has several such uncheerful events with InnoDB (Plugin) tables which were extensively written to. The TRUNCATE hanged; nothing else would work; minutes pass.

TRUNCATE on tables with no FOREIGN KEYs should act fast: it translate to dropping the table and creating a new one (and it all depends on the MySQL version, see the manual).

What’s faster than TRUNCATE, then? If you don’t have triggers nor FOREIGN KEYs, a RENAME TABLE can come to the rescue. Instead of:

TRUNCATE log_table

Do:

CREATE TABLE log_table_new LIKE log_table;
RENAME TABLE log_table TO log_table_old, log_table_new TO log_table;
DROP TABLE log_table_old;

I found this to work well for me. Do note that AUTO_INCREMENT values can be tricky here: the “new” table is created with an AUTO_INCREMENT value which is immediately taken in the “working” table. If you care about not using same AUTO_INCREMENT values, you can:

ALTER TABLE log_table_new AUTO_INCREMENT=some high enough value;

Just before renaming.

I do not have a good explanation as for why the RENAME TABLE succeeds to respond faster than TRUNCATE.

I’ve been noticing an undeniable trend in my consulting engagements in the last year or so, and when I vocalized this today, heads nodded all around me. Everyone sees a growth in the number of cases where otherwise well-optimized systems are artificially limited by InnoDB contention problems.

A year ago, I simply wasn’t seeing the need for analysis of GDB backtraces en masse. These days, I’m writing custom tools to gather and analyze backtraces. A year ago, I simply looked at the SEMAPHORE section of SHOW INNODB STATUS. These days I’m writing custom tools to aggregate and reformat that data so I can interpret it more easily. And I’m actually seeing cases of this type of problem multiple times every week. I remember the first time I ran into a server that was literally optimized to the limit, but struggling under the load. It was something new for me, not that long ago. Oh, I’d seen it before, plenty, but was always able to point out where something could be improved without changing InnoDB itself. Now it’s commonplace: schemas are fine — check. Queries are all well-indexed — check. Everything else — check. InnoDB is bottlenecked and absolutely nothing can be improved — check.

Part of the difference is the rapidly improving hardware. It’s getting hard to buy a server with fewer than 8 or even 16 cores, and 16GB of RAM feels like something I’d install in a wristwatch. But I also suspect that if I’d been characterizing the workload of servers over time in a way that was easy to compare, I’d see a clear trend towards bigger data and more queries per second. We’re just pushing MySQL + InnoDB harder today than we ever have before.

What can be done? Well, InnoDB needs to be improved, that’s all. Oracle, Percona, Google, Facebook and others are working on it, and in many cases these efforts have yielded dramatic results. But there is still much room for improvement.

Related posts:

1. Hindsight on a scalable replacement for InnoDB A while ag
2. What do you know about Oracle’s InnoDB+ storage engine? That
3. Xtrabackup is for InnoDB tables too, not just XtraDB Just thoug

Related posts brought to you by Yet Another Related Posts Plugin.

I’ve heard about the innodb_plugin but not had time to put it to the test.

Recently though due to some problems I’ve been having with the MySQL Enterprise Monitor (Merlin) I’ve had to try a few changes and had the opportunity to try out the innodb plugin.

I have been using Merlin for some time and like it a lot. It is not perfect but does a good job for me.  However, since upgrading to version 2.1 I have been having some database load problems. I long ago split the merlin server into a front- and back-end server with the backend running a standard MySQL 5.1 Advanced package. That has been working fine.

I have been monitoring more and more mysqld servers and recently the database backend could not cope. Basically the writes of data collected from the agents and the deletes of old date (purging) caused too much I/O and that is on a box with 6 disks in RAID-10 with a battery backed write-cache.

So I upgraded the db server to a new box with lots of memory and a 300 GB Fusion IO card. I expected all problems to go away. Well not quite. In spite of the solid state drive which was not I/O bound, and the CPU which was not CPU bound, mysqld could not keep up with the load. This was running the MySQL 5.1.44 Advanced rpm. Looking more deeply it seems that mysqld itself was the bottleneck and there was too much contention on the PK by the different INSERTing and DELETing threads.

The Merlin team suggested trying the innodb_plugin (1.0.6) and all of a sudden the bottleneck seems to have gone away.

This is the iostat output taken before switching to the innodb plugin:

Device:    rrqm/s wrqm/s   r/s     w/s  rsec/s  wsec/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
fioa         0.00   0.00  4.21 4049.50   33.67 32337.07    16.83 16168.54     7.99   192.42    6.31   0.25 100.22
fioa         0.00   0.00  9.78 4379.44  153.29 34942.12    76.65 17471.06     8.00     0.00    6.17   0.00   0.00
fioa         0.00   0.00 20.80 4455.40  408.00 35552.60   204.00 17776.30     8.03     0.00    5.96   0.00   0.00
fioa         0.00   0.00 21.80 4685.00  267.20 37391.20   133.60 18695.60     8.00    24.00    5.87   0.21 100.02
fioa         0.00   0.00 23.60 5200.40  320.00 41490.40   160.00 20745.20     8.00     0.00    6.04   0.00   0.00
fioa         0.00   0.00 15.17 5020.36  202.79 40055.09   101.40 20027.54     7.99     0.00    5.65   0.00   0.00

This is the iostat output taken after switching to the innodb plugin:

Device:    rrqm/s wrqm/s   r/s     w/s  rsec/s   wsec/s   avgrq-sz avgqu-sz   await  svctm  %util
fioa         0.00   0.00  4.40 2344.80  160.00 201520.00     85.85     0.00    0.41   0.00   0.00
fioa         0.00   0.00  2.00 2188.20   64.00 193500.80     88.38     0.00    0.41   0.00   0.00
fioa         0.00   0.00  1.80 2113.60   57.60 200889.20     94.99     0.00    0.44   0.00   0.00
fioa         0.00   0.00  0.40 1961.60   12.80 194291.20     99.03     0.00    0.43   0.00   0.00
fioa         0.00   0.00  0.00 2118.00    0.00 202496.40     95.61     0.00    0.47   0.00   0.00
fioa         0.00   0.00  0.00 2030.00    0.00 191482.40     94.33     0.00    0.46   0.00   0.00
fioa         0.00   0.00  0.00 2152.60    0.00 208485.20     96.85     0.00    0.44   0.00   0.00
fioa         0.00   0.00  0.00 1936.20    0.00 178732.40     92.31     0.00    0.42   0.00   0.00
fioa         0.00   0.00  4.79 1249.70  153.29 115475.45     92.17     0.00    0.40   0.00   0.00

Note: the first set of figures was taken using CentOS 4, and the second using CentOS 5. The IO statistics aren’t exactly identical and on CentOS 5 for some reason the driver appears not to be providing all the stats. However the wsec/s value clearly shows a significant performance improvement and the original problem mysqld was having of not being able to purge as fast as it was inserting data seems to have been solved. At least initial signs seem to indicate this. The only configuration change made to the server was the following:

ignore_builtin_innodb
plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so

innodb_adaptive_flushing = 1
innodb_io_capacity = 1000

Conclusion, if you are having performance problems on your MySQL server and perhaps the hardware is not the bottleneck then try using the plugin. It may make a big difference.

Also a big thanks to the Merlin team for helping me out with this problem and getting things up and running.

Shortly after I posted my last summary of MySQL releases, our son Mats was born and I went on a 2.5-week vacation. Our developers did not rest in the meanwhile and I'd like to give you a quick update of what's new since then:

MySQL Connector/Net 6.3.0

• Visual Studio 2010 RC support
• Nested transaction scope support

MySQL Workbench 5.2.16 Beta 6

• Fixed 67 bugs
• Saving your profile/connection passwords in OSX keychain, gnome-keyring or in an encrypted password-vault-file.
• New rapid development features for generating complete SQL Select/DML statements or names for selected objects in Query Editor to either the query area or clipboard.
• The ability to set a preference for the placement  (left or right side) of the sidebar in the Query Editor (Currently on Windows only, coming to Mac and Linux soon).
• Further optimization and stabilization of the administrator components

MySQL Server 5.1.44

• This release now includes InnoDB Plugin version 1.0.6, wich is considered to be of Release Candidate (RC) quality.
• Lots of bug fixes - see the ChangeLog for details.

MySQL Server 5.5.2 Milestone 2

• Also includes the updated InnoDB Plugin 1.0.6 and several fixes - see the ChangeLog for details. For an overview of new features in the 5.5 code base, check out the "What's new in 5.5" page in the reference manual.

Please note that the MySQL downloads section has been split into two parts. As usual, you will find downloads of both GA and development versions of all MySQL products and releases on the MySQL DevZone. In addition to that, we've now added a pointer to the downloads of officially released (GA) versions to the main web site on http://www.mysql.com/downloads/.

I recently was asked what maximal amount transactions per second we can get using MySQL and XtraDB / InnoDB storage engine if we have high-end server. Good questions, though not easy to answer, as it depends on:

- durability setting ( innodb_flush_log_at_trx_commit = 0 or 1 ) ?
- do we use binary logs ( I used ROW based replication for 5.1)
- do we have sync_binlog options.

So why would not take these as variable parameters and run simple benchmark.
I took sysbench update_key scenario ( update indexed field on simple table)
and used Dell PowerEdge R900 with 16 cores, FusionIO as storage for table and RAID 10 with BBU as storage for innodb log files, innodb system table space and binary logs. And I used Percon-XtraDB-5.1.43-9.1 for benchmarks. All used partitions are formatted in XFS and mounted with nobarrier option.

I run update key for various threads and with next parameters

• trx_commit=0 : innodb_flush_log_at_trx_commit = 0 and no binary logs
• trx_commit=1 : innodb_flush_log_at_trx_commit = 1 and no binary logs
• trx_commit=0 & binlog : innodb_flush_log_at_trx_commit = 0 and binary logs
• trx_commit=1 & binlog : innodb_flush_log_at_trx_commit = 1 and binary logs
• trx_commit=1 & binlog & sync_bin : innodb_flush_log_at_trx_commit = 1 and binary logs and sync_binlog=1

There are results I get:


I found results being quite interesting.
with innodb_flush_log_at_trx_commit = 0 maximal tps is 36332.02 tps, which drops to 23115.04 tps as
we switch to innodb_flush_log_at_trx_commit = 1. As we use RAID10 with BBU, I did not expect the drops is going to be significant. In second case InnoDB spends

With enabling binary logs, the results drops to 17451.01 tps with innodb_flush_log_at_trx_commit = 0 and to 12097.39 tps with innodb_flush_log_at_trx_commit = 1. So with binary logs serialization is getting even worse.

Enabling sync_binlog makes things really bad, and maximal results I have is
3086.7 tps. So this is good decision if binary log protection is worth such drop.

---

Entry posted by Vadim | No comment

Add to: | | | |


PlanetMySQL Voting: Vote UP / Vote DOWN

I recently was asked what maximal amount transactions per second we can get using MySQL and XtraDB / InnoDB storage engine if we have high-end server. Good questions, though not easy to answer, as it depends on:

- durability setting ( innodb_flush_log_at_trx_commit = 0 or 1 ) ?
- do we use binary logs ( I used ROW based replication for 5.1)
- do we have sync_binlog options.

So why would not take these as variable parameters and run simple benchmark.
I took sysbench update_key scenario ( update indexed field on simple table)
and used Dell PowerEdge R900 with 16 cores, FusionIO as storage for table and RAID 10 with BBU as storage for innodb log files, innodb system table space and binary logs. And I used Percon-XtraDB-5.1.43-9.1 for benchmarks. All used partitions are formatted in XFS and mounted with nobarrier option.

I run update key for various threads and with next parameters

• trx_commit=0 : innodb_flush_log_at_trx_commit = 0 and no binary logs
• trx_commit=1 : innodb_flush_log_at_trx_commit = 1 and no binary logs
• trx_commit=0 & binlog : innodb_flush_log_at_trx_commit = 0 and binary logs
• trx_commit=1 & binlog : innodb_flush_log_at_trx_commit = 1 and binary logs
• trx_commit=1 & binlog & sync_bin : innodb_flush_log_at_trx_commit = 1 and binary logs and sync_binlog=1

There are results I get:


I found results being quite interesting.
with innodb_flush_log_at_trx_commit = 0 maximal tps is 36332.02 tps, which drops to 23115.04 tps as
we switch to innodb_flush_log_at_trx_commit = 1. As we use RAID10 with BBU, I did not expect the drops is going to be significant. In second case InnoDB spends

With enabling binary logs, the results drops to 17451.01 tps with innodb_flush_log_at_trx_commit = 0 and to 12097.39 tps with innodb_flush_log_at_trx_commit = 1. So with binary logs serialization is getting even worse.

Enabling sync_binlog makes things really bad, and maximal results I have is
3086.7 tps. So this is good decision if binary log protection is worth such drop.

---

Entry posted by Vadim | No comment

Add to: | | | |


PlanetMySQL Voting: Vote UP / Vote DOWN

Oracle recently announcent MySQL-5.5.2-m2 milestone, and I have a predition that MySQL-5.5 will be announced as GA on MySQL UC 2010 in April.

So let’s make quick on scalability characteristics we should expect from new release.

I made sysbench oltp benchmarks on 10 mln rows (worth 2.5GB of data), on our Dell R900 system ( 16 cores, 32GB of RAM, FusionIO + RAID10, but disk in this case does not matter).

There is results for various threads (results are in tps, more is better)

I think it is good results, and there couple conclusions we can come with

• InnoDB now can scale up to 16 threads pretty well
• InnoDB can keep load up to 128 threads, and for 256 threads the result is still acceptable, but drops by 25% comparing to peak in 16 threads

If you interested what is issue in scaling over 16 threads, that in current
benchmarks – this is concurrency on rollback segment (which is global shared structure in InnoDB)

sysbench command for reference:

./sysbench --test=oltp --oltp-table-size=10000000 --init-rng=1 --num-threads=$i --max-requests=0 --oltp-dist-type=uniform --max-time=180 run


Entry posted by Vadim | No comment

Add to: | | | |

Running many benchmarks on fast storage (FusionIO, SSDs) and multi-cores CPUs system I constantly face two contention problems.

So I suspect it’s going to be next biggest issues to make InnoDB scaling on high-end system.

This is also reason why in benchmarks I posted previously CPU usage is only about 50%, leaving other 50% in idle state.

First problem is index->lock mutex.
InnoDB uses single mutex per index, so when you run mixed read / write queries, InnoDB locks index for write operation and thus keeps all selects waiting when update/insert is done. This is implemented in this way because write operation may cause B-Tree page split, and InnoDB needs to move records between pages to finish operation. It is getting even worse when for write you need to perform some additional IO to bring page into buffer_pool.

What could be done there internally: there is B-Tree lock free or only page-level lock algorithms, so operation does not need to block whole B-Tree.
From end user point, to fight with this problem, you may need to partition (manually or using 5.1 partitions) table with big index into couple smaller table. It’s ugly, but it can help while main problem is not solved.

Second problem is adaptive_search index.
It appears when you have some scanning by secondary key select queries and write queries at the same time.
InnoDB again uses single global mutex for adaptive_search (single mutex for ALL table and ALL indexes), so write query blocks ALL select queries.
Usually first action is to disable adaptive_search (it is possible via global variable), but it rarely helps actually. With disabled adaptive index InnoDB needs to perform much more operations reading secondary keys.

How it can be solved internally: I think some hashing algorithms may be applied to not lock select queries. We may look how to implement it.

Until that InnoDB basically can’t utilize powerful hardware.
For example even in IO intensive load I am getting the same result
for single FusionIO card and for two FusionIO cards coupled in RAID0 (which theoretically doubles througput).

Entry posted by Vadim | No comment

Add to: | | | |

As I wrote about 2 years ago the feature of Innodb to store copy of master’s position in Slave’s Innodb tablespace got broken. There is a lot of discussions at the corresponding bug report while outcome of the fix remained uncertain for me (the bug is market duplicate while the bugs it seems to be duplicate for describe different issues).
Anyway. The customer came to me today having the following message in the error log after Slave crash while running MySQL 5.1.41 (extra virgin version without XtraDB or Percona Patches)

InnoDB: In a MySQL replication slave the last master binlog file
InnoDB: position 0 10000000, file name mysql-bin.005000
InnoDB: Last MySQL binlog file position 0 20000000, file name ./mysql-bin.003000

He has tried to restart replication from mysql-bin.005000 position 10000000 which failed with “Could not find first log file name in binary log index file” error message

Looking at the Master for this slave I could see its binary log files going only to about 2000, so the binary log file mentioned did not ever exist on this master. What is going on ?
The thing is Innodb does not update this information any more however if it is stored in the tablespace the code is still there to print it. This database was running older MySQL version a while back which was updated to MySQL 5.1 months ago, moved to the new hardware by physical copy and the log file numbers restarted back from 1 but tablespace still contained the ancient data.

I reported the bug on this which should be easy to fix. Otherwise it is easy mistake to make. We also have a patch which restores this information and uses it on slave crash recovery.

Entry posted by peter | No comment

Add to: | | | |