Posted by Baron Schwartz |
I have run into a number of cases recently that all had a similar look and feel. In most of these cases, the symptoms were very complicated, but they boiled down to just a few problems that can be prevented in very simple ways.
If you are not doing any of the following three simple things, you probably should. These are essential practices for building successful applications, which everyone should be doing. And the best part is how easy it is to do them — read on for the details.
[read more...]
Posted by
Baron Schwartz @ 7:26 pm ::
tips ::
Posted by peter |
Couple of days ago I was looking for a way to improve update performance for the application and I was replacing single value UPDATE with multiple value REPLACE (though I also saw the same problem with INSERT ON DUPLICATE KEY UPDATE)
As I went from 1 value to 3 or 10 in the batch performance improved, especially for network tests, however going to batches of 100 and 1000 values performance started to degrade badly – the process which was taking 45 seconds with single value statements was taking over 12 minutes with 1000 values in a batch. This was a big surprise for me as I’ve used batching with great performance gains a lot of times.
[read more...]
Posted by
peter @ 12:43 pm ::
Innodb ::
Posted by peter |
Recently I had a chance to take a look at Redis project, which is semi-persistent in memory database with idea somethat similar to memcache but richer feature set.
Redis has simple single process event driven design, which means it does not have to deal with any locks which is performance killer for a lot of applications. This however limits it scalability to single core. Still with 100K+ operations a second this single core performance will be good enough for many applications. Also nothing stops you from running many Redis instance on single server to get advantage of multiple cores.
I call Redis semi-persistent because it does not store the data on disk immediately but rather dumps its all database every so often – you have a choice of configuring time and number of updates between database dumps. Because dump is basically serial write Redis does not an expensive IO subsystem. Also because this dump is background it does not affect read/write performance to the database which is in memory. In the tests I’ve done I’ve seen Redis doing writes some 4MB/sec for probably 50% of test duration where Innodb had to write 50MB/sec for about third of throughput and doing a lot of random IO as it was doing it. This is among other things because Innodb has to flush full 16K pages while doing flush.
[read more...]
Posted by
peter @ 10:48 am ::
lamp ::
Posted by Morgan Tocker |
I wanted to write a few administrative updates in one so I didn’t spam everyone’s feed readers too much. Here we go:
Posted by
Morgan Tocker @ 6:55 pm ::
announce ::
Posted by Aleksandr Kuzminsky |
Dear Community,
The release 0.9 of the opensource backup tool for InnoDB and XtraDB is available for download.
Changelog:
- tar4ibd could not treat over 8GB file, now max 64GB
- prepare-speed-hack is added
Fixed bugs:
The binary packages for RHEL4,5, Debian, FreeBSD, MacOS as well as source code of the XtraBackup is available on http://www.percona.com/mysql/xtrabackup/0.9/.
The project lives on Launchpad : https://launchpad.net/percona-xtrabackup and you can report bug to Launchpad bug system:
https://launchpad.net/percona-xtrabackup/+filebug. The documentation is available on our Wiki.
For general questions use our Pecona-discussions group, and for development question Percona-dev group.
For support, commercial and sponsorship inquiries contact Percona.
Posted by Aleksandr Kuzminsky |
Dear Community,
Today we are announcing XtraDB release 7.
This is the last release based on InnoDB plugin version 1.0.3, as you might know Innobase has released 1.0.4.
The release includes following new features:
- MySQL 5.1.37 as a base release
- speed hack for buf_flush_insert_sorted_into_flush_list controlled by the new variable innodb_fast_recovery
Fixed bugs:
The builds for RedHat4,5 and Debian are located on http://www.percona.com/mysql/xtradb/5.1.37-7/
The latest source code of XtraDB, including development branch you can find on LaunchPAD.
Please report any bugs found on Bugs in Percona XtraDB Storage Engine for MySQL.
For general questions use our Pecona-discussions group, and for development question Percona-dev group.
For support, commercial and sponsorship inquiries contact Percona
Posted by Baron Schwartz |
Ever wondered how fast stored routines are in MySQL? I just ran a quick micro-benchmark to compare the speed of a stored function against a “roughly equivalent” subquery. The idea — and there may be shortcomings that are poisoning the results here, your comments welcome — is to see how fast the SQL procedure code is at doing basically the same thing the subquery code does natively (so to speak).
[read more...]
Posted by Morgan Tocker |
Note: This post is part 2 of 4 on building our training workshop.
Last week I talked about why you don’t want to shard. This week I’m following up with the top 10 enhancements that XtraDB has over the built-in InnoDB included in MySQL 5.0 and 5.1. Building this list was not really a scientific process – It’s always difficult to say which feature is better than another, because a lot of it depends on the individual workload. My ranking method was to pick the features that have the highest impact and are most applicable to all workloads first:
- CPU scalability fixes – XtraDB improves performance on systems with multi-cpus (see docs 1, 2).
- Import/Export Tables – XtraDB allows you to import an arbitrary table from one server to another, by backing up the .ibd file with Xtrabackup (see docs).
- IO scalability fixes – A lot of the internal algorithms of InnoDB are based on the non-configurable assumption that the server has only a single disk installed (100 iops). One problem in particular that this causes, is that InnoDB the algorithm which chooses if InnoDB is too busy to flush dirty pages can consider it’s self busy very easily. Keeping a large percentage of pages dirty increases recovery time, and will lead to more work when checkpoints are eventually forced at the end of a log file. XtraDB improves this with innodb_io_capacity, as well as configuration items for innodb_read_threads, innodb_write_threads (see docs).
- Better Diagnostics – The SHOW ENGINE INNODB STATUS command in XtraDB shows a lot more information than the standard InnoDB status (see docs). The built-in InnoDB status also has some problems with the placement of items (a long transaction list will prevent the rest of the information shown). In addition to this, XtraDB diagnostics include the ability to see the contents of the buffer pool (see docs), and InnoDB row statistics are inserted into the slow query log (see docs).
- Fast Crash Recovery – In the built-in InnoDB, the crash recovery process is sometimes best measured in hours and days – this restricts users to using very small transaction log files (innodb_log_file_size), which is worse for performance. In a simple test, XtraDB recovered ten times faster (see docs).
- InnoDB Plugin Features – XtraDB is derived from the InnoDB plugin, which has fast index creation (as opposed to recreating the whole table!) and page compression.
- Adaptive Checkpointing – The built-in InnoDB can have erratic dips in performance as it approaches the end of a log file and needs to checkpoint – which can cause a denial of service to your application (this can be seen in any benchmark – such as this one). In XtraDB, adaptive checkpointing can smooth out the load, and checkpoint data more aggressively as you approach the end of a log file (see docs).
- Insert Buffer control – The insert buffer is a great feature of InnoDB that is not often discussed. It allows you to delay the writing of non-unique secondary index pages, which can often lead to a lot of merged requests and reduced IO. The problem with the insert buffer in the built-in InnoDB, is that there are no options to tweak it. It can grow to 1/2 the size of your buffer pool, and when it does, it doesn’t try to aggressively free entries (a full buffer provides no use) or reduce its size (see docs).
- Data dictionary control – Once an InnoDB table is opened it is never freed from the in-memory data dictionary (which is unlimited in size). XtraDB introduces a patch to be able to control this, which is useful in cases where users have a large number of tables. (see docs).
- Additional undo slots – In the built-in InnoDB, the number of open transactions is limited to 1023 (see bug report). XtraDB allows this to be expanded to 4072 (Warning: Incompatible change!). (see docs).
All of these 10 items will be covered in our Training workshops for InnoDB and XtraDB. In Santa Clara / San Francisco between 14-16 September? Come along!
My next post in this series will be on XtraDB: The Top 10 Configuration Parameters.
Posted by peter |
As you might have seen Innodb Plugin 1.0.4 was released today. I am very excited to see this release which is released exactly 5 months after release of Innodb Plugin 1.0.3 (I honestly expected to see Innodb Plugin 1.0.4 to be released by MySQL Conference and Expo in April). This also is still “early adopter” version of a plugin which is a bit of disappointment as we can’t wait for Innodb plugin to become stable/GA but considering number of improvements this is probably good thing.
We’re also pleased to see some of Percona contributions made in this release (in modified form) while others were evaluated and given ideas for different implementations.
Among other changes I’m especially pleased with fixed Group Commit Bug which I reported about 4 years ago and which was known for about 5 years, since early MySQL 5.0 beta releases. We had semi-fix for this problem earlier this year but it is great to see solution with even less mutex code.
Now the fun part starts – we need to evaluate/benchmark/stress test Innodb Plugin 1.0.4 to integrate it with next XtraDB release.
Posted by Aleksandr Kuzminsky |
Dear Community,
The 18-th build of MySQL server with Percona patches is available now.
Comparing to the previous release it has following new features:
Fixed bugs in the build:
You can download binaries and sources with the patches here
http://www.percona.com/mysql/5.0.84-b18/
The Percona patches live on Launchpad : https://launchpad.net/percona-patches and you can report bug to Launchpad bug system:
https://launchpad.net/percona-patches/+filebug. The documentation is available on our Wiki
For general questions use our Pecona-discussions group, and for development question Percona-dev group.
For support, commercial and sponsorship inquiries contact Percona.
del.icio.us
digg
) which has been a nightmare for all DBA's.