Join the community today
Register Now

MariaDB MariaDB 10.1 Slower After Upgrading From 5.5

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by deltahf, Mar 17, 2018.

  1. deltahf

    deltahf Premium Member Premium Member

    270
    122
    43
    Jun 8, 2014
    Ratings:
    +194
    Local Time:
    1:23 PM
    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:

    Screen Shot 2018-03-16 at 10.45.54 PM.png

    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

     
  2. eva2000

    eva2000 Administrator Staff Member

    35,132
    7,753
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +11,956
    Local Time:
    3:23 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    You listed above my.cnf lost alot of optimised default settings that Centmin Mod auto applies on initial installs so probably why. But there's also a fact MariaDB 10.1/10.2/10.3 will have slower single threaded performance in favour of better multi-threaded performance than MariaDB 5.5. MariaDB 5.5 has much better single threaded performance than MariaDB 10.1/10.2/10.3. But given your traffic and mysql usage, probably not an issue.

    centmin mod 123.09beta01's centmin.sh menu option 11 submenu options shouldn't wipe your /etc/my.cnf just adjust and append or comment out settings

    you may have some backed up /etc/my.cnf when centmin mod initially installed your server - so I'd check those files to make sure if one doesn't have the rest of your my.cnf settings first though you maybe missing the changes auto applied between MariaDB 5.5 to 10.0 to 10.1 updates. Check particularly /etc/my.cnf-newold as it gets written to for each major upgrade via centmin.sh menu option 11

    Code (Text):
    ls -lahrt /etc/ | grep my.
    -rw-r--r--.  1 root  root  202 Feb  4 02:30 my.cnf.rpmnew
    -rw-r--r--.  1 root  root  570 Mar 11 16:11 my.cnf-original
    -rw-r--r--.  1 root  root 4.0K Mar 11 16:11 my.cnf-newold
    drwxr-xr-x.  2 root  root 4.0K Mar 11 16:13 my.cnf.d
    -rw-r--r--.  1 root  root 5.1K Mar 11 16:13 my.cnf-setiobackup
    -rw-r--r--.  1 root  root 5.1K Mar 12 16:24 my.cnf
    

    • my.cnf-original is one before centmin mod install
    • my.cnf-newold is one after mariadb initial version or upgraded version was installed before centmin mod applied settings
    • my.cnf-setiobackup is one before tools/setio.sh runs to optimise mysql disk related variable settings
    • my.cnf existing live /etc/my.cnf
    • as you updated from MariaDB 5.5 to 10.0 you may also have one called /etc/my.cnf-original_before_mariadb55upgrade - be careful using that one on MariaDB 10.x
    • you'll have one for pre MariaDB 10.1 updates at /etc/my.cnf-original_before_mariadb101upgrade
    Centmin Mod 123.09beta01 does have some base my.cnf templates which get applied after initial install located at centminmod/centminmod but these templates actually get auto tuned on the fly during Centmin Mod install and major version upgrades via centmin.sh menu option 11 to auto apply optimised settings based on your server specs and disk i/o performance and also get certain settings commented out or added based on MariaDB version used due to some settings or variables being deprecated etc i.e. if upgrading from MariaDB 5.5 to 10.0 to 10.1 to 10.2 etc.
     
    • Like Like x 1
  3. eva2000

    eva2000 Administrator Staff Member

    35,132
    7,753
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +11,956
    Local Time:
    3:23 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    one possibility of creating as close to as optimal /etc/my.cnf for mariadb 10.1 on centmin mod 123.09beta01 would be doing a test install of centmin mod 123.09beta01 on a test server. Closest and cheapest would be spinning up an hourly charged Vultr baremetal dedicated E3-1270v6 based server i.e. Vultr - Vultr 60% discount & Free $100 Credit Test Drive Vultr Bare Metal Instances! and grabbing the resulting /etc/my.cnf for use on your live server as it would be close in terms of number of cpus and memory and wouldn't take more than 1-2hrs in terms of cost

    My Affiliate link Vultr.com ;)

    after you apply the copied over /etc/my.cnf, re-run /usr/local/src/centminmod/tools/setio.sh to re-tune your mysql disk i/o related settings based on the server's measured disk i/o performance.

    While it doesn't help you now, I have updated 123.09beta01 centmin.sh menu option 11 routine to auto backup /etc/my.cnf on submenu selection as well as added safe guard for future usage Beta Branch - update centmin.sh menu option 11 mariadb major upgrades
     
    • Like Like x 1
  4. deltahf

    deltahf Premium Member Premium Member

    270
    122
    43
    Jun 8, 2014
    Ratings:
    +194
    Local Time:
    1:23 PM
    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. (n)

    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.
     
  5. deltahf

    deltahf Premium Member Premium Member

    270
    122
    43
    Jun 8, 2014
    Ratings:
    +194
    Local Time:
    1:23 PM
    Hooray for CDP backups! :D 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.
     
    Last edited: Mar 18, 2018
  6. eva2000

    eva2000 Administrator Staff Member

    35,132
    7,753
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +11,956
    Local Time:
    3:23 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    Just leave it in, centmin mod builds it's my.cnf based on previous existing my.cnf and appends or auto adjusts the settings based on major upgrade version's compatibility and known deprecated variables or changed variable names.

    that's the best way as you'd still be missing mariadb 10.0 and 10.1 specific changes that centmin mod auto adds on upgrade as it's not just variables set but their auto tuned/optimised values that are calculated on the fly during centmin mod mariadb upgrades.

     
    • Informative Informative x 1
  7. eva2000

    eva2000 Administrator Staff Member

    35,132
    7,753
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +11,956
    Local Time:
    3:23 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    actually you could try using that previous my.cnf and tack on the end of it the 1st post contents which seem to have maybe applied that auto tuned settings on mariadb 10.0 and 10.1 updates - maybe

    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
    
    [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
    
     
    • Like Like x 1
  8. deltahf

    deltahf Premium Member Premium Member

    270
    122
    43
    Jun 8, 2014
    Ratings:
    +194
    Local Time:
    1:23 PM
    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. :D

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

    Screen Shot 2018-03-18 at 1.23.43 PM.png

    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! :eek: 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.
     
    • Informative Informative x 1
  9. pamamolf

    pamamolf Well-Known Member

    3,111
    295
    83
    May 31, 2014
    Ratings:
    +530
    Local Time:
    8:23 PM
    Nginx-1.13.x
    MariaDB 10.1.x
  10. eva2000

    eva2000 Administrator Staff Member

    35,132
    7,753
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +11,956
    Local Time:
    3:23 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    @pamamolf ;)

    Yeah folks may underestimate how much difference Centmin Mod's auto tuned and optimised settings can be at initial install time - tuned for the detected server hardware and resources available :D APM seems to illustrate the differences nicely :)
     
    • Winner Winner x 1
  11. deltahf

    deltahf Premium Member Premium Member

    270
    122
    43
    Jun 8, 2014
    Ratings:
    +194
    Local Time:
    1:23 PM
    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.
     
    • Informative Informative x 3
  12. eva2000

    eva2000 Administrator Staff Member

    35,132
    7,753
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +11,956
    Local Time:
    3:23 AM
    Nginx 1.15.x
    MariaDB 5.5/10.x
    nice AppOptics will one day try..

    for some reason my free newrelic APM is still working heheh - think paid users get until May 2018 - but i am free tier heh
     
    • Like Like x 1
  13. BamaStangGuy

    BamaStangGuy Active Member

    526
    160
    43
    May 25, 2014
    Ratings:
    +212
    Local Time:
    12:23 PM
    I might take a stab at looking at the code and creating a stand alone mysql tuning script that will spit out recommended my.cnf based on what you have done with a fresh install.
     
    • Like Like x 3
..