MariaDB MariaDB 10.1 Slower After Upgrading From 5.5

Two days ago, I upgraded from MariaDB 5.5.59 to MariaDB 10.1 via the Centminmod upgrade script (I actually went from 5.5 -> 10.0 -> 10.1.31 using the menu options, of course). I expected slightly better performance, but I have not been impressed with 10.1 so far...

I immediately noticed that my site felt slower and sure enough, the metrics from AppOptics confirmed that. You can see the difference in this graph here that charts MySQL response times:

---

Basically, my average MySQL response time has gone from ~5ms to ~25ms, even after optimizations.

I should note that my original my.cnf file was apparently wiped during the upgrade, because I couldn't find any of my old settings afterwards, even in the automatic backup files. So, I created a new one based on recommendations from the Centminmod mysqlmymonlite mysqltuner script and some of my own research:

Code (Text):

[client-server]

!includedir /etc/my.cnf.d

[mariadb-10.0]
performance_schema=OFF
innodb_stats_on_metadata=OFF
innodb_sort_buffer_size=2M
innodb_online_alter_log_max_size=128M
query_cache_strip_comments=0
log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

[mariadb-10.1]
innodb_file_format = Barracuda
innodb_file_per_table = 1

innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1
innodb_buffer_pool_populate=0

innodb_defragment=1
innodb_defragment_n_pages=7
innodb_defragment_stats_accuracy=0
innodb_defragment_fill_factor_n_recs=20
innodb_defragment_fill_factor=0.9
innodb_defragment_frequency=40

query_cache_size=0
query_cache_type=0
join_buffer_size=256K
tmp_table_size=16M
max_heap_table_size=16M
thread_cache_size=4
innodb_buffer_pool_size=16G
innodb_log_file_size=2G
innodb_buffer_pool_instances=16

Is there anything else I can do to get my database performance back? I really don't want to downgrade but I'm not sure if I have any other choice at this point.

• Hardware: Dedicated server, Intel Xeon E3 1230 V3, 32GB RAM
• CentOS Version: centos-release-6-9.el6.12.3.x86_64
• Centmin Mod Version Installed: 123.09beta01
• Nginx Version Installed: 1.13.8
• PHP Version Installed: 7.2.3
• MariaDB MySQL Version Installed: 10.1.31
• When was last time updated Centmin Mod code base ? : Yesterday
• Persistent Config: None

 

Another thing I noticed from my AppOptics metrics: server load has doubled from an average of 0.55 to 1.1 since upgrading to MariaDB 10.1.

Thanks for this list, I had no idea which version was which. I didn't have a "original_before_mariadb55upgrade", just these:

Code (Text):

-rw-r--r--    1 root     root           251 Apr 29  2015 my.cnf-original
-rw-r--r--    1 root     root          2.9K Jan 24  2017 my.cnf.rpmsave
-rw-r--r--    1 root     root           715 Mar 15 03:16 my.cnf-original_before_mariadb101upgrade
-rw-r--r--    1 root     root           715 Mar 15 03:18 my.cnf-newold
drwxr-xr-x    2 root     root          4.0K Mar 15 17:48 my.cnf.d
-rw-r--r--    1 root     root          2.0K Mar 16 01:53 my.cnf

I think my original settings were in my.cnf.rpmsave, because that's the only one that has a innodb_buffer_pool_size parameter, which I remember setting. However, that file hasn't been touched in over a year, so I don't know what to make of that.

Code (Text):

[mysqld]
#character-set-server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#bind-address=127.0.0.1
# optimized my.cnf for MariaDB 5.5.x
# by eva2000
# vbtechsupport.com

tmpdir=/home/mysqltmp

#skip-innodb
#skip-federated
#skip-pbxt
#skip-pbxt_statistics
#skip-archive
#skip-name-resolve
#old_passwords
back_log = 75
max_connections = 300
key_buffer_size = 32M
myisam_sort_buffer_size = 32M
myisam_max_sort_file_size = 2048M
join_buffer_size = 64K
read_buffer_size = 64K
sort_buffer_size = 128K
table_definition_cache = 4096
table_open_cache = 2048
thread_cache_size = 64
wait_timeout = 120
connect_timeout = 10
tmp_table_size = 32M
max_heap_table_size = 32M
max_allowed_packet = 32M
max_seeks_for_key = 1000
group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 256K
bulk_insert_buffer_size = 8M
query_cache_limit = 512K
query_cache_size = 16M
query_cache_type = 1
query_cache_min_res_unit = 2K
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = InnoDB

log_warnings=1
slow_query_log=0
long_query_time=1
slow_query_log_file=/var/lib/mysql/slowq.log
#log-error=/var/log/mysqld.log

# innodb settings
#innodb_extra_rsegments = 4
innodb_purge_threads=1
innodb_doublewrite = 1

innodb_file_per_table = 1
innodb_open_files = 1000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 16G
innodb_additional_mem_pool_size = 32M

innodb_log_files_in_group = 2
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 0
innodb_lock_wait_timeout=50
#innodb_flush_method = O_DIRECT
innodb_support_xa=1

# 200 * # DISKS
innodb_io_capacity = 100
innodb_read_io_threads = 2
innodb_write_io_threads = 2

# mariadb settings
[mariadb]
#thread-handling = pool-of-threads
#thread-pool-size= 20
#mysql --port=3307 --protocol=tcp
#extra-port=3307
#extra-max-connections=1

userstat = 0
key_cache_segments = 1
aria_group_commit = none
aria_group_commit_interval = 0
aria_log_file_size = 32M
aria_log_purge_type = immediate
aria_pagecache_buffer_size = 8M
aria_sort_buffer_size = 8M

[mariadb-5.5]
#ignore_db_dirs=
query_cache_strip_comments=0

innodb_read_ahead = linear
innodb_adaptive_flushing_method = estimate
innodb_flush_neighbor_pages = 1
innodb_stats_update_need_lock = 0
innodb_log_block_size = 512

log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

[mysqld_safe]
socket=/var/lib/mysql/mysql.sock
#log-error=/var/log/mysqld.log
#nice = -5
open-files-limit = 8192

[mysqldump]
quick
max_allowed_packet = 32M

[myisamchk]
key_buffer = 32M
sort_buffer = 16M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout

I guess I will try the Vultr VPS route, because I have no idea what will or won't transfer over from that year-old rpmsave file. I already have a small VPS with them, but I did use your affiliate link when I signed up, and will use it again just in case.

 

Hooray for CDP backups! I was able to find my previous my.cnf file!

I think it's pretty much exactly like the old my.cnf.rpmsave from above.

Now the question is, what do I need to take out for it to be compatible with 10.1? Just the [mariadb-5.5] section?

Code (Text):

[mysqld]
#character-set-server=utf8
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

#bind-address=127.0.0.1
# optimized my.cnf for MariaDB 5.5.x
# by eva2000
# vbtechsupport.com

tmpdir=/home/mysqltmp

#skip-innodb
#skip-federated
#skip-pbxt
#skip-pbxt_statistics
#skip-archive
#skip-name-resolve
#old_passwords
back_log = 75
max_connections = 300
key_buffer_size = 32M
myisam_sort_buffer_size = 32M
myisam_max_sort_file_size = 2048M
join_buffer_size = 64K
read_buffer_size = 64K
sort_buffer_size = 128K
table_definition_cache = 4096
table_open_cache = 2048
thread_cache_size = 64
wait_timeout = 120
connect_timeout = 10
tmp_table_size = 32M
max_heap_table_size = 32M
max_allowed_packet = 32M
max_seeks_for_key = 1000
group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 256K
bulk_insert_buffer_size = 8M
query_cache_limit = 512K
query_cache_size = 16M
query_cache_type = 1
query_cache_min_res_unit = 2K
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = InnoDB

log_warnings=1
slow_query_log=0
long_query_time=1
slow_query_log_file=/var/lib/mysql/slowq.log
#log-error=/var/log/mysqld.log

# innodb settings
#innodb_extra_rsegments = 4
innodb_purge_threads=1
innodb_doublewrite = 1

innodb_file_per_table = 1
innodb_open_files = 1000
innodb_data_file_path= ibdata1:10M:autoextend
innodb_buffer_pool_size = 16G
innodb_additional_mem_pool_size = 32M

innodb_log_files_in_group = 2
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 0
innodb_lock_wait_timeout=50
#innodb_flush_method = O_DIRECT
innodb_support_xa=1

# 200 * # DISKS
innodb_io_capacity = 100
innodb_read_io_threads = 2
innodb_write_io_threads = 2

# mariadb settings
[mariadb]
#thread-handling = pool-of-threads
#thread-pool-size= 20
#mysql --port=3307 --protocol=tcp
#extra-port=3307
#extra-max-connections=1

userstat = 0
key_cache_segments = 1
aria_group_commit = none
aria_group_commit_interval = 0
aria_log_file_size = 32M
aria_log_purge_type = immediate
aria_pagecache_buffer_size = 8M
aria_sort_buffer_size = 8M

[mariadb-5.5]
#ignore_db_dirs=
query_cache_strip_comments=0

innodb_read_ahead = linear
innodb_adaptive_flushing_method = estimate
innodb_flush_neighbor_pages = 1
innodb_stats_update_need_lock = 0
innodb_log_block_size = 512

log_slow_filter =admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

[mysqld_safe]
socket=/var/lib/mysql/mysql.sock
#log-error=/var/log/mysqld.log
#nice = -5
open-files-limit = 8192

[mysqldump]
quick
max_allowed_packet = 32M

[myisamchk]
key_buffer = 32M
sort_buffer = 16M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout

=========
UPDATE
=========

OK, I found this page in the docs which outlines the configuration changes needed between 5.5 and 10.0/10.1.

Upgrading from MariaDB 10.0 to MariaDB 10.1

So, I made the following changes from my old 5.5 my.cnf:

Code (Text):

back_log = 75
# CHANGED TO...
back_log = 150

Code (Text):

innodb_flush_neighbor_pages = 1
# CHANGED TO...
innodb-flush-neighbors = 1

Deleted:

Code (Text):

innodb_adaptive_flushing_method = estimate
innodb_doublewrite = 1
innodb_read_ahead = linear
innodb_stats_update_need_lock = 0

I have to run so I won't be applying these changes until a bit later.

I'm still not sure what I should do about that [mariadb-5.5] "section"? OK to just leave it in? I don't understand what the bracket sections mean or how they are interpreted in the file.

 

Thanks for all the suggestions, eva!

Before going down the Vultr route, I decided to try the updated combined file to see what would happen... and it worked. I'm back to 5.5 speeds again.

You can see here in this AppOptics graph, when I applied the update right before 03:00 UTC:



I'm not sure if I could improve on those 5-10ms response times much with a Vultr test, do you? If I can find some time to spin up a new Vultr box I'll report back here, but I'm happy for now.

This whole experience has done well to remind me of two important things:

1.) You are a wizard, @eva2000, and Centminmod is an extraordinary project. I didn't realize all those parameters were inserted automatically by Centminmod, and I certainly didn't realize what a huge difference they made in database speed. I had always taken it for granted. Just imagine how much faster the web — dominated by PHP/MySQL sites like WordPress — would be if more people used Centminmod! This project is going to explode someday soon!

2.) This is why I like to use Application Performance Monitoring (APM). I don't think I would have been able to detect and diagnose this issue as easily without the graphs and metrics provided by the AppOptics APM. I could tell something wasn't quite right after the upgrade, but without clear before/after benchmarks, the problem would not have been so obvious.

 

I use Application Monitoring and Infrastructure Monitoring, both of which are included in the $23/month plan (or $20/month if you pay annually).

I had to move to AppOptics after getting kicked off NewRelic, which I had been using for free thanks to a small loophole from an old promotion they did with my previous hosting company (Voxel/Internap).

I did a lot of research for a new provider and found that most of the monitoring solutions out there actually just do the Infrastructure Monitoring, which is nice for giving you a good look at how the services on your server are performing, but do not give you any insight on how the applications themselves are actually running. There are also surprisingly few PHP application monitoring services which don't cost hundreds of dollars a month, so AppOptics was really the best solution for me.