Reputation: 955
How to Improve MariaDB 10.0.20 read/write performance than MySQL 5.1.43
MariaDB 10.0.20 performace is not good as compare to MySQL 5.1.43,
What could be the affecting parameters like:
- Memory
- Disks I/O
- CPU
- Filesystem
- Buffering
How to identify Which parameter is impacting the performance?
Here are some results of same queries which I run on both system:
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 innodb parameters 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
Upvotes: 4
Views: 3706
Answers (1)
Reputation: 57418
This is too long for a comment, so I'm trying to lay down enough for it to be considered the answer it can't be.
TL;DR there is no one-size-fits-all configuration that will optimize performances. You need to tailor the configuration to your system, OS, file system, and actual operations.
In general the various RDBMS operations are divided into CPU- and memory- bound as well as I/O bound, with different degrees of concurrency. The config settings will always be a compromise between conflicting needs, and the weight assigned to those needs varies from query to query.
Given that you're interested in speeding up overall operations, the first step will be to determine what queries cause the longest delays.
Choosing a setting that accelerates 99% per cent of your queries making them twice as fast, and slows down the remaining 1% by as little as ten per cent, might seem great... yet turn out to be counterproductive if that one per cent of your queries was actually responsible for 95% of wallclock time. You halve 5% to 2.5%, and increase 95% to 104.5%; the net result is a speed decrease of 7%, in accordance with Amdahl's Law.
That is why there is a slow-log in MySQL/XtraDB/Percona/MariaDB: because you almost always need to speed up the slowest queries first (you actually want to speed up those queries for which the product time * times_executed is largest. Usually these are not numerically the dominating queries - there's another interesting observation in that - and you can garner large rewards by optimizing relatively few, well-chosen queries).
Once you know which queries are those, you can check their indexing, see whether the schema might be optimized for a given database, whether they could benefit by caching and so on. The SQL structure being the same does not mean that MySQL and MariaDB will be equally efficient in dealing with it. You might discover that running
SELECT * FROM yourtable PROCEDURE ANALYSE();
gives a different Optimal_fieldtype value for a column. And the same goes for indexing.
Even a simple reordering of the queries (if possible) might give you a benefit on one database, and a loss on another, if they hit differently the file system caches.
A different approach would be to focus on those low level functions shared by all queries (loading data from disk, saving data to disk, flushing, etc.).
You want to load as little data as possible (hence covering indexes...), and save as little data as possible. Disabling implicit transactions and handling them explicitly can yield huge returns.
You also want to flush data as seldom as possible -- but no more than that (there's data integrity to consider).
For example in MariaDB the value O_DIRECT for innodb_flush_method is not necessarily the fastest (see here), depending on things such as
- the filesystem (ext3, ext4, ...)
- the actual hardware (no RAID, WT RAID, WB RAID, WB w/cache...)
- the support (SSD, RIO, HDD, SAN, ...)
- the actual data load
Also, the various MySQL forks implementing not all the same features, they may not be directly comparable, nor scale as well. A really small database might run way faster on a MySQL MyISAM setup than on a full-fledged XtraDB InnoDB setup; while the opposite might be (and almost certainly is) true for a huge database with massive concurrent accesses.
E.g. with the Percona fork,
- fdatasync: use fsync() to flush both the data and log files.
- O_SYNC: use O_SYNC to open and flush the log files; use fsync() to flush the data files.
- O_DIRECT: use O_DIRECT to open the data files and fsync() system call to flush both the data and log files.
- O_DIRECT_NO_FSYNC: use O_DIRECT to open the data files but don’t use fsync() system call to flush both the data and log files. This option isn’t suitable for XFS file system.
- ALL_O_DIRECT: use O_DIRECT to open both data and log files, and use fsync() to flush the data files but not the log files. This option is recommended when InnoDB log files are big (more than 8GB), otherwise there might be even a performance degradation. Note: When using this option on ext4 filesystem variable innodb_log_block_size should be set to 4096 (default log-block-size in ext4) in order to avoid the unaligned AIO/DIO warnings.
Upvotes: 1
Related Questions
- MariaDB 10.4 random performance degradation
- Optimizing MariaDB for wordpress
- MariaDB 10.5.11 slow performance compared to MySQL 8.0.13
- MySQL/MariaDB write/insert take a very long time
- MySQL to older MariaDB - slower performance
- MariaDB 10.4.13 slow performance compared to MySQL 5.7.30
- Query in Mysql 5.5 is very slow compared to Mariadb 10.2
- Poor mariadb performance vs mysql
- Different performance mysql and mariadb
- Mariadb 5.5 slower than MySQL 5.1