How to improve read / write performance of MariaDB 10.0.20 than MySQL 5.1.43
MariaDB 10.0.20 performace is not good compared to MySQL 5.1.43,
What can be the following parameters:
- Memory
- I / O drives
- CPU
- File system
- Buffering
How do you determine which parameter affects performance?
Below are some results from the same queries I run on both systems:
MySQL 5.1.43:
Total memory: 16 GB
[root@mysql]# time mysql -uroot -proot@12345 test_db < /tmp/tmp.sql
real 0m0.586s
user 0m0.010s
sys 0m0.045s
MariaDB 10.0.20 :
Total memory: 35 GB
[root@mysql]# time mysql -uroot -proot@12345 test_db < /tmp/tmp.sql
real 0m0.821s
user 0m0.024s
sys 0m0.022s
Here are the innodb options from both MariaDB 10.0.20 and MySQL 5.1.43:
MySQL version: 5.1.43-log: =====>
mysql> SHOW GLOBAL VARIABLES like '%innodb%';
+-----------------------------------------+------------------------+
| Variable_name | Value |
+-----------------------------------------+------------------------+
| have_innodb | YES |
| ignore_builtin_innodb | OFF |
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_size | 8388608 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 5242880 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_stats_on_metadata | ON |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
| innodb_use_legacy_cardinality_algorithm | ON |
+-----------------------------------------+------------------------+
37 rows in set (0.01 sec)
mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| query_cache_size | 0 |
+------------------+-------+
1 row in set (0.01 sec)
mysql> SHOW STATUS LIKE 'Qcache%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
+-------------------------+-------+
8 rows in set (0.00 sec)
MariaDB Version: 10.0.20-MariaDB-log: =====>
MariaDB [cma_db]> SHOW GLOBAL VARIABLES like '%innodb%';
+-------------------------------------------+------------------------+
| Variable_name | Value |
+-------------------------------------------+------------------------+
| ignore_builtin_innodb | OFF |
| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_lwm | 10.000000 |
| innodb_adaptive_hash_index | ON |
| innodb_adaptive_hash_index_partitions | 1 |
| innodb_adaptive_max_sleep_delay | 150000 |
| innodb_additional_mem_pool_size | 8388608 |
| innodb_api_bk_commit_interval | 5 |
| innodb_api_disable_rowlock | OFF |
| innodb_api_enable_binlog | OFF |
| innodb_api_enable_mdl | OFF |
| innodb_api_trx_level | 0 |
| innodb_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_dump_at_shutdown | OFF |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | OFF |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_populate | OFF |
| innodb_buffer_pool_size | 16106127360 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_checksum_algorithm | innodb |
| innodb_checksums | ON |
| innodb_cleaner_lsn_age_factor | high_checkpoint |
| innodb_cmp_per_index_enabled | OFF |
| innodb_commit_concurrency | 0 |
| innodb_compression_failure_threshold_pct | 5 |
| innodb_compression_level | 6 |
| innodb_compression_pad_pct_max | 50 |
| innodb_concurrency_tickets | 5000 |
| innodb_corrupt_table_action | assert |
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir | |
| innodb_disable_sort_file_cache | OFF |
| innodb_doublewrite | ON |
| innodb_empty_free_list_algorithm | backoff |
| innodb_fake_changes | OFF |
| innodb_fast_shutdown | 1 |
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | ON |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | O_DIRECT |
| innodb_flush_neighbors | 1 |
| innodb_flushing_avg_loops | 30 |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_foreground_preflush | exponential_backoff |
| innodb_ft_aux_table | |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_enable_diag_print | OFF |
| innodb_ft_enable_stopword | ON |
| innodb_ft_max_token_size | 84 |
| innodb_ft_min_token_size | 3 |
| innodb_ft_num_word_optimize | 2000 |
| innodb_ft_result_cache_limit | 2000000000 |
| innodb_ft_server_stopword_table | |
| innodb_ft_sort_pll_degree | 2 |
| innodb_ft_total_cache_size | 640000000 |
| innodb_ft_user_stopword_table | |
| innodb_io_capacity | 200 |
| innodb_io_capacity_max | 2000 |
| innodb_kill_idle_transaction | 0 |
| innodb_large_prefix | OFF |
| innodb_lock_wait_timeout | 50 |
| innodb_locking_fake_changes | ON |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | ./ |
| innodb_log_arch_expire_sec | 0 |
| innodb_log_archive | OFF |
| innodb_log_block_size | 512 |
| innodb_log_buffer_size | 8388608 |
| innodb_log_checksum_algorithm | innodb |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 268435456 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_lru_scan_depth | 1024 |
| innodb_max_bitmap_file_size | 104857600 |
| innodb_max_changed_pages | 1000000 |
| innodb_max_dirty_pages_pct | 75.000000 |
| innodb_max_dirty_pages_pct_lwm | 0.001000 |
| innodb_max_purge_lag | 0 |
| innodb_max_purge_lag_delay | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_monitor_disable | |
| innodb_monitor_enable | |
| innodb_monitor_reset | |
| innodb_monitor_reset_all | |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_open_files | 4096 |
| innodb_optimize_fulltext_only | OFF |
| innodb_page_size | 16384 |
| innodb_print_all_deadlocks | OFF |
| innodb_purge_batch_size | 300 |
| innodb_purge_threads | 1 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 8 |
| innodb_read_only | OFF |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_sched_priority_cleaner | 19 |
| innodb_show_locks_held | 10 |
| innodb_show_verbose_locks | 0 |
| innodb_simulate_comp_failures | 0 |
| innodb_sort_buffer_size | 1048576 |
| innodb_spin_wait_delay | 6 |
| innodb_stats_auto_recalc | ON |
| innodb_stats_method | nulls_equal |
| innodb_stats_modified_counter | 0 |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_sample_pages | 8 |
| innodb_stats_traditional | ON |
| innodb_stats_transient_sample_pages | 8 |
| innodb_status_output | OFF |
| innodb_status_output_locks | OFF |
| innodb_strict_mode | OFF |
| innodb_support_xa | ON |
| innodb_sync_array_size | 1 |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 0 |
| innodb_track_changed_pages | OFF |
| innodb_undo_directory | . |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 0 |
| innodb_use_atomic_writes | OFF |
| innodb_use_fallocate | OFF |
| innodb_use_global_flush_log_at_trx_commit | ON |
| innodb_use_native_aio | ON |
| innodb_use_stacktrace | OFF |
| innodb_use_sys_malloc | ON |
| innodb_version | 5.6.24-72.2 |
| innodb_write_io_threads | 8 |
+-------------------------------------------+------------------------+
147 rows in set (0.00 sec)
MariaDB [cma_db]> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
1 row in set (0.00 sec)
MariaDB [cma_db]> SHOW VARIABLES LIKE 'query_cache_size';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| query_cache_size | 0 |
+------------------+-------+
1 row in set (0.00 sec)
MariaDB [cma_db]> SHOW STATUS LIKE 'Qcache%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
+-------------------------+-------+
8 rows in set (0.00 sec)
here is the result of strace -c -f -p $ (pidof mysqld)
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
69.21 0.881874 2873 307 io_getevents
15.38 0.195972 3999 49 18 futex
11.53 0.146979 5444 27 select
3.84 0.048993 8166 6 5 restart_syscall
0.01 0.000167 0 1144 sendto
0.01 0.000120 0 2304 18 recvfrom
0.01 0.000114 0 628 time
0.00 0.000054 0 7741 clock_gettime
0.00 0.000014 1 21 stat
0.00 0.000000 0 21 read
0.00 0.000000 0 21 open
0.00 0.000000 0 22 close
0.00 0.000000 0 18 poll
0.00 0.000000 0 3 pwrite
0.00 0.000000 0 1 access
0.00 0.000000 0 1 sched_yield
0.00 0.000000 0 23 madvise
0.00 0.000000 0 1 accept
0.00 0.000000 0 1 shutdown
0.00 0.000000 0 2 1 setsockopt
0.00 0.000000 0 4 fcntl
0.00 0.000000 0 5 fsync
0.00 0.000000 0 122 gettimeofday
0.00 0.000000 0 2 io_submit
------ ----------- ----------- --------- --------- ----------------
100.00 1.274287 12474 42 total
source to share
This is a too long comment, so I try to catch it enough to be considered an answer that it cannot be.
TL; DR does not have a single size configuration that will optimize performance. You need to tweak the configuration on your system, OS, file system and actual operations .
In general, the various RDBMS operations are categorized into CPU and memory related, and I / O bindings with varying degrees of concurrency. Configuration options will always be a compromise between conflicting needs, and the weight assigned to those needs varies from request to request.
Given that you are interested in speeding up operations in general , the first step is to determine which requests are causing the longest delays .
Choosing a setting that speeds up 99% of your queries, making them twice as fast, and slows down the remaining 1% by just ten percent, may sound great ... but turns out to be counterproductive if that one percent of your queries were actually responsible for 95% acceleration time. You halve 5% to 2.5% and increase 95% to 104.5%; the net result is a 7% reduction in speed, in line with the Amdahl Law .
This is why the slow log runs in MySQL / XtraDB / Percona / MariaDB: because you almost always need to speed up the slowest queries in the first place (you really want to speed up those queries for which the highest production time is * times_executed. Usually these are not numerical dominant queries - there's another interesting observation , and you can get big rewards by optimizing relatively few, well-chosen queries).
Once you know which queries are that, you can check their indexing, see if the schema can be optimized for a given database, if they can benefit from caching, etc. The SQL structure being the same as not means that MySQL and MariaDB will be equally efficient when working with it. You may find that starting
SELECT * FROM yourtable PROCEDURE ANALYSE();
gives a different value Optimal_fieldtype
for the column. And the same goes for indexing.
Even a simple reordering of queries (if possible) can give you an edge on one database and lose another if they end up in filesystem caching differently.
Another approach would be to focus on those low-level features that all requests use (loading data from disk, saving data to disk, cleaning, etc.).
You want to load as little data as possible (hence spanning indexes ...) and store as little data as possible. Disabling implicit transactions and processing them explicitly can generate huge profits.
You also want to flush data as seldom as possible - but no more (there is data integrity to consider).
For example, in MariaDB, the value O_DIRECT
for is innodb_flush_method
not necessarily the fastest (see here ), depending on things like - filesystem (ext3, ext4, ...) - actual hardware (no RAID, WT RAID, WB RAID, WB w / cache ...) - support (SSD, RIO, HDD, SAN, ...) - actual data loading
Also, the various MySQL forks do not implement all of the same functionality, they cannot be directly comparable and do not scale. In fact, a small database can run faster in MySQL MyISAM than a full XtraDB InnoDB setup; while the opposite may be (and almost certainly) true for a huge database with massive concurrent accesses.
eg. with Percona fork,
- fdatasync: Use fsync () to flush data and log files.
- O_SYNC: use O_SYNC to open and clear log files; use fsync () to clean up data files.
- O_DIRECT: use O_DIRECT to open data files and fsync () system call to clear data and log files.
- O_DIRECT_NO_FSYNC: Use O_DIRECT to open data files, but do not use the fsync () system call to clear data and log files. This option is not valid for XFS file systems .
- ALL_O_DIRECT: Use O_DIRECT to open data and log files and use fsync () to clear data files, but not log files. This option is recommended when InnoDB log files are large (over 8 GB), otherwise there may even be performance degradation . When using this option on the ext4 filesystem variable innodb_log_block_size should be set to 4096 (ext4 default logblock size) to avoid inconsistent AIO / DIO warnings.
source to share