title: How to Improve MariaDB 10.0.20 read/write performance than MySQL 5.1.43
body: <p>MariaDB 10.0.20 performace is not good as compare to MySQL 5.1.43, </p>
  
  <p><strong>What could be the affecting parameters like:</strong> </p>
  
  <ul>
  <li>Memory</li>
  <li>Disks I/O </li>
  <li>CPU </li>
  <li>Filesystem </li>
  <li>Buffering</li>
  </ul>
  
  <p><strong>How to identify Which parameter is impacting the performance?</strong> </p>
  
  <p>Here are some results of same queries which I run on both system: </p>
  
  <p><strong>MySQL 5.1.43:</strong></p>
  
  <p><strong>Total Memory:     16 GB</strong></p>
  
  <pre><code>[root@mysql]# time mysql -uroot -proot@12345 test_db &lt; /tmp/tmp.sql
  real    0m0.586s
  user    0m0.010s
  sys     0m0.045s
  </code></pre>
  
  <p><strong>MariaDB 10.0.20</strong>:</p>
  
  <p><strong>Total Memory:     35 GB</strong></p>
  
  <pre><code>[root@mysql]# time mysql -uroot -proot@12345 test_db &lt; /tmp/tmp.sql
  real    0m0.821s
  user    0m0.024s
  sys     0m0.022s
  </code></pre>
  
  <p>Here are innodb parameters from both MariaDB 10.0.20 and MySQL 5.1.43: </p>
  
  <p><strong>MySQL Version: 5.1.43-log: =====></strong> </p>
  
  <pre><code>mysql&gt; 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&gt; SHOW VARIABLES LIKE 'have_query_cache';
  +------------------+-------+
  | Variable_name    | Value |
  +------------------+-------+
  | have_query_cache | YES   |
  +------------------+-------+
  1 row in set (0.00 sec)
  
  mysql&gt; SHOW VARIABLES LIKE 'query_cache_size';
  +------------------+-------+
  | Variable_name    | Value |
  +------------------+-------+
  | query_cache_size | 0     |
  +------------------+-------+
  1 row in set (0.01 sec)
  
  mysql&gt; 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)
  </code></pre>
  
  <p><strong>MariaDB Version: 10.0.20-MariaDB-log: =====></strong></p>
  
  <pre><code>MariaDB [cma_db]&gt; 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]&gt; SHOW VARIABLES LIKE 'have_query_cache';
  +------------------+-------+
  | Variable_name    | Value |
  +------------------+-------+
  | have_query_cache | YES   |
  +------------------+-------+
  1 row in set (0.00 sec)
  
  MariaDB [cma_db]&gt; SHOW VARIABLES LIKE 'query_cache_size';
  +------------------+-------+
  | Variable_name    | Value |
  +------------------+-------+
  | query_cache_size | 0     |
  +------------------+-------+
  1 row in set (0.00 sec)
  
  MariaDB [cma_db]&gt;  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)
  </code></pre>
  
  <p><strong>here is the result of strace -c -f -p $( pidof mysqld )</strong></p>
  
  <pre><code>    % 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
  </code></pre>