Ever since MySQL 5.5 beta has been announced by Edward Screven, Oracle’s chief corporate architect; there is lot of positive buzz (here, here, …) about the performance and scalability improvements added in this release. We should all be thankful to Michael Ronstrom (as most of the key developers are already working on different forks), who did a great job in the improvements especially scalability related that allows to scale beyond 16 cores by improving the performance by 2-5X in most common workloads. Not to forget about numerous improvements to replication by replication team.
Even though 5.5 has lot of new improvements officially from Sun/Oracle; but some of the changes are actually driven by community (yet another thanks to Google, Mark Callaghan and his team, Percona and his team, Facebook etc) and most of the ideas or patches were already floating for a while and they were used in the production as well (5.0 or 5.1). This is actually a good sign that community can look forward for 5.5 GA instead of worrying about what patches and builds to use.
This is a clear indication that 5.5 performance and scalability improvements were actually driven by community.
Key improvements in 5.5:
- InnoDB changes in 1.1
- Multiple buffer pools (controlled by innodb_buffer_pool_instances)
- Multiple rollback segments
- Splitting of purge operation from main background thread (controlled by innodb_purge_threads)
- New log_buf mutex now controls the mini transaction writes in buffer pool instead of shared log_sys, reduces the contention on buffer pool
- Separate mutex for flush list handling, reduces the contention on buffer pool
- Improved recovery time
- Rest of the changes as part of InnoDB plugin 1.0.x
- Numerous replication related changes
Even though they announced InnoDB as the default storage engine in 5.5; but the latest build still has MyISAM as the default
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
Server version: 5.5.4-m3 MySQL Community Server (GPL) Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec) |
There is more to come here, Mikael’s new thread pool
http://mikaelronstrom.blogspot.com/2009/12/new-threadpool-design.html
shows great results as well and will make the non released 6.0 thread pool
taken by MariaDB
http://askmonty.org/wiki/Manual:Pool_of_Threads
obsolete.
New blog post: http://tinyurl.com/y7kf67n – MySQL 5.5 – A Community Winner
MySQL 5.5 – A Community Winner http://goo.gl/fb/l26Ge #database #mysql #performance #scalability #innodb16cores
I gave Calvin and Mikael a hard time about this before knowing what would be in the release. This is way better than I expected. I assume that Ken Jacobs also played a part in making this a great release. It is too bad that the talented developers at InnoDB are so quiet — they did a lot for this release along with Mikael.
I also look forward to the pool-of-threads features when they get published. They solved the problems that could have lead to deadlock under load.
Yes, all the changes that are part of InnoDB 1.0.X belongs to Ken Jacobs and his team. Thread pool is something that we all can benefit down the line; and myself I did patched this way back..
https://venublog.com/2008/05/21/mysql-51-thread-pool-support/
MySQL 5.5 – A Community Winner http://bit.ly/apEj5W
Mark,
Glad to know that we have finally made you happy! The talented InnoDB developers are no longer quiet. You can read their blogs at http://blogs.innodb.com/. Inaam, Sunny, and Jimmy provided more details in their articles. As for the conference, the main player, Inaam, supposed to present along with Mikael. But he had to stay away from traveling due to personal issue.