Galloping Europe: MySQL 5.4 Changes
It so happened that I hadn’t been working with MySQL for quite a while, since Rambler mainly uses PostgreSQL. Now I finally have free time, and I decided to catch up with the lost time. As it turned out, quite a lot has changed in the MySQL world over the past year and a half.
MySQL AB was purchased by Sun, which was soon bought and absorbed by Oracle itself. Many developers managed to leave the project, and some managed to start their own forks.
In addition to old friends - MyISAM and InnoDB (as well as Merge, Heap, Archive, and others), new storage engines appeared: Falcon and Maria . Apparently, Falcon is not yet suitable for production-use, since the 6.0 branch is in the early alpha stage, but, with time, it will probably surpass InnoDB in features and usability. But the need for the existence of Maria, personally for me, is not yet obvious.
')
Apparently, the current MySQL developers have decided that they will not pull the simultaneous development of two branches - information on 6.0 on dev.mysql.org is quite difficult to find. The latest community version available on the site is 5.4.2 and has beta status, although version 5.4.5 is mentioned in the documentation.
At one time, I had to work a bit with 5.1, so I decided to start with the list of changes 5.1-> 5.4, which was not so long. You can look at it completely on the MySQL site , and here I will give a few points that seemed meaningful to me.
- MySQL began to scale better on multi-core CPUs - improved locking and memory management ( dev.mysql.com/doc/refman/5.4/en/smp-improvements.html ).
- InnoDB more effectively uses the capabilities of the I / O subsystem ( dev.mysql.com/doc/refman/5.4/en/innodb-io-changes.html ).
- Added new features for diagnostics and search for “bottlenecks”, most of the functionality is the same “patches from Google” ( dev.mysql.com/doc/refman/5.4/en/monitoring-improvements.html ), which, finally, embedded in the main branch.
- MySQL, finally, comes with more or less responsible configuration files, instead of the old examples, designed for almost 386 computers. :)
- Improvements in the query optimizer, which should lead to faster execution of subqueries and JOINs in InnoDB and MyISAM.
- Support for "semi-synchronous" replication. This means that the master must wait until at least one of the slaves receives a confirmation of the receipt of the transaction before it is considered completed. By default, MySQL uses asynchronous replication, that is, the wizard does not care what state the slaves are in ( dev.mysql.com/doc/refman/5.4/en/replication-semisync.html ). However, now it is possible to turn on the alternative mode - it will give greater safety of data during replication, at the cost of some loss in performance.
By the way, another important change is that the InnoDB-plugin has now moved to the place of the old “default” InnoDB, that is, it is no longer necessary to connect it separately.
To make sure that the promised increase in performance really takes place, I tried to test two versions of MySQL: 5.1.37 (build from Fedora) and MySQL 5.4.2 (build from MySQL AB) using MySQL-benchmark.
The test machine is a dual-core Core2 with 4 GB of RAM, running under Fedora 11, x86_64. The tested versions were also compiled under x86_64. Hard disk - SATA, hdparm -t / dev / sda gave a linear read speed of about 78 MB / sec
The following config was used in the test (some insignificant fragments are omitted):
Apparently, MySQL 5.4.2 compiled MySQL (from dev.mysql.com) does not support the use of atomic CPU operations, so the effect of this factor on the increase in performance is not yet clear. Nevertheless, the increase in performance when working with the database really is.
In order to reduce the impact of autoextend, each test was run twice. The results of the restarts did not differ much. Run string for test:
./run-all-tests --server = MySQL --log --cmp = mysql --random --verbose
Unfortunately, the benchmark from MySQL with the option - server = MySQL, considers that the server does not support transactions (despite default-storage-engine = InnoDB). I do not want to give full test results here, since there are quite a few of them. But, in brief, the results are as follows.
5.1.37 - total execution time: 981 seconds.
5.4.2 - total execution time: 955 seconds.
5.4.2 wins in the following tests (time in seconds is indicated):
Unfortunately, for the time being there are several tests in which 5.4 merges the older version.
Finally, I apologize to those who saw the unfinished version of this text. The fact is that for some reason a very handy daw "to show an article to friends only" has disappeared from the editor. To show the article to friends only, I simply published the text on my blog, hoping that no one would see it, except for those whom I ask to see. As it turned out, it did not work. :)
UPD: At the same time, I estimated the effect of google tcmalloc on the work of MySQL. There is a fairly noticeable gain, which is achieved by simply installing google-perftools and setting the variable LD_PRELOAD.
MySQL AB was purchased by Sun, which was soon bought and absorbed by Oracle itself. Many developers managed to leave the project, and some managed to start their own forks.
In addition to old friends - MyISAM and InnoDB (as well as Merge, Heap, Archive, and others), new storage engines appeared: Falcon and Maria . Apparently, Falcon is not yet suitable for production-use, since the 6.0 branch is in the early alpha stage, but, with time, it will probably surpass InnoDB in features and usability. But the need for the existence of Maria, personally for me, is not yet obvious.
')
Apparently, the current MySQL developers have decided that they will not pull the simultaneous development of two branches - information on 6.0 on dev.mysql.org is quite difficult to find. The latest community version available on the site is 5.4.2 and has beta status, although version 5.4.5 is mentioned in the documentation.
At one time, I had to work a bit with 5.1, so I decided to start with the list of changes 5.1-> 5.4, which was not so long. You can look at it completely on the MySQL site , and here I will give a few points that seemed meaningful to me.
- MySQL began to scale better on multi-core CPUs - improved locking and memory management ( dev.mysql.com/doc/refman/5.4/en/smp-improvements.html ).
- InnoDB more effectively uses the capabilities of the I / O subsystem ( dev.mysql.com/doc/refman/5.4/en/innodb-io-changes.html ).
- Added new features for diagnostics and search for “bottlenecks”, most of the functionality is the same “patches from Google” ( dev.mysql.com/doc/refman/5.4/en/monitoring-improvements.html ), which, finally, embedded in the main branch.
- MySQL, finally, comes with more or less responsible configuration files, instead of the old examples, designed for almost 386 computers. :)
- Improvements in the query optimizer, which should lead to faster execution of subqueries and JOINs in InnoDB and MyISAM.
- Support for "semi-synchronous" replication. This means that the master must wait until at least one of the slaves receives a confirmation of the receipt of the transaction before it is considered completed. By default, MySQL uses asynchronous replication, that is, the wizard does not care what state the slaves are in ( dev.mysql.com/doc/refman/5.4/en/replication-semisync.html ). However, now it is possible to turn on the alternative mode - it will give greater safety of data during replication, at the cost of some loss in performance.
By the way, another important change is that the InnoDB-plugin has now moved to the place of the old “default” InnoDB, that is, it is no longer necessary to connect it separately.
To make sure that the promised increase in performance really takes place, I tried to test two versions of MySQL: 5.1.37 (build from Fedora) and MySQL 5.4.2 (build from MySQL AB) using MySQL-benchmark.
The test machine is a dual-core Core2 with 4 GB of RAM, running under Fedora 11, x86_64. The tested versions were also compiled under x86_64. Hard disk - SATA, hdparm -t / dev / sda gave a linear read speed of about 78 MB / sec
The following config was used in the test (some insignificant fragments are omitted):
[mysqld]
skip-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
thread_concurrency = 4
default-storage-engine=InnoDB
skip-networking
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_buffer_pool_size = 1024M
innodb_additional_mem_pool_size = 256M
innodb_log_file_size = 256M
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
Apparently, MySQL 5.4.2 compiled MySQL (from dev.mysql.com) does not support the use of atomic CPU operations, so the effect of this factor on the increase in performance is not yet clear. Nevertheless, the increase in performance when working with the database really is.
In order to reduce the impact of autoextend, each test was run twice. The results of the restarts did not differ much. Run string for test:
./run-all-tests --server = MySQL --log --cmp = mysql --random --verbose
Unfortunately, the benchmark from MySQL with the option - server = MySQL, considers that the server does not support transactions (despite default-storage-engine = InnoDB). I do not want to give full test results here, since there are quite a few of them. But, in brief, the results are as follows.
5.1.37 - total execution time: 981 seconds.
5.4.2 - total execution time: 955 seconds.
5.4.2 wins in the following tests (time in seconds is indicated):
| Test name | 5.1 | 5.4 |
|---|---|---|
| update_of_key_big (501) | 25.00 | 11.00 |
| create_key + drop (10,000) | 77.00 | 70.00 |
| delete_all_many_keys (1) | 16.00 | 14.00 |
| delete_big_many_keys (128) | 16.00 | 14.00 |
| drop_table_when_MANY_tables (10000) | 20.00 | 16.00 |
| insert (350768) | 39.00 | 35.00 |
| select_key2_return_key (200000) | 41.00 | 38.00 |
| select_key2_return_prim (200000) | 42.00 | 40.00 |
| update_big (10) | 24.00 | 20.00 |
| update_of_key (50000) | 8.00 | 7.00 |
| update_of_key_big (501) | 25.00 | 11.00 |
Unfortunately, for the time being there are several tests in which 5.4 merges the older version.
| Test name | 5.1 | 5.4 |
|---|---|---|
| create + drop (10,000) | 39.00 | 40.00 |
| create_MANY_tables (10000) | 22.00 | 30.00 |
| insert_key (100000) | 32.00 | 42.00 |
| select_cache2 (10000) | 41.00 | 44.00 |
| update_of_primary_key_many_keys (256) | 43.00 | 46.00 |
| update_with_key_prefix (100000) | 13.00 | 14.00 |
| wisc_benchmark (114) | 1.00 | 2.00 |
Finally, I apologize to those who saw the unfinished version of this text. The fact is that for some reason a very handy daw "to show an article to friends only" has disappeared from the editor. To show the article to friends only, I simply published the text on my blog, hoping that no one would see it, except for those whom I ask to see. As it turned out, it did not work. :)
UPD: At the same time, I estimated the effect of google tcmalloc on the work of MySQL. There is a fairly noticeable gain, which is achieved by simply installing google-perftools and setting the variable LD_PRELOAD.
Source: https://habr.com/ru/post/71594/
All Articles