Skip to main content
cPanel Technical Support has been heavily impacted by hurricane Beryl and our ability to respond to tickets has been hindered as a result. We appreciate your understanding and patience as we address these delays.

Please Help Me Optimize MariaDB, Database is Really Slow

Comments

8 comments

  • 701Hosting
    This is what my.cnf contains [mysqld] log-error=/var/lib/mysql/xxx.xxx.com.err default-storage-engine=MyISAM innodb_file_per_table=1 performance-schema=0 max_allowed_packet=268435456 open_files_limit=40000
    0
  • cPanelAnthony
    Hello! If you require MySQL optimization, it may be worth reaching out to a systems administrator. A list of services can be found
    0
  • 701Hosting
    Thank you for the suggestion. I was hoping that someone might be able to check the output of mysqltuner and offer some suggestions. I'm guessing this might be the problem. *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** But I'm unsure on how to correct it. Honestly I think that cPanel should include better tools to improve MySQL functionality. Possibly allowing users to run mysqltuner from the GUI instead of SSH and offering suggestions. Then allowing those suggestions to be applied from WHM. That would have been awesome! Any help is appreciated.
    0
  • cPanelAnthony
    Thank you for the suggestion. I was hoping that someone might be able to check the output of mysqltuner and offer some suggestions. I'm guessing this might be the problem. *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** But I'm unsure on how to correct it. Honestly I think that cPanel should include better tools to improve MySQL functionality. Possibly allowing users to run mysqltuner from the GUI instead of SSH and offering suggestions. Then allowing those suggestions to be applied from WHM. That would have been awesome! Any help is appreciated.

    These are great suggestions! Would you be able to submit a feature request using the link in my signature?
    0
  • 701Hosting
    I'll gladly submit a feature request, but would like some help tuning my.cnf... Atleast an idea on where to start would be great.
    0
  • cPanelAnthony
    Thanks for the confirmation! I'm sure some users here will offer input. The following thread may contain useful information for you as well.
    0
  • HostNoc
    Hi Following parameters that need to be optimized to get the maximum out of your MariaDB as we did in our server and DB persormance is excellent innodb_file_per_table=1 innodb_buffer_pool_size = 1G sysctl -w vm.swappiness=0 Max connections = 300 Thread Cache Size : 100 "((Threads_Created / Connections) *100) query_cache_size = 64M tmp_table_size= 64M slow-query-log = 1 slow-query-log-file = /var/lib/mysql/mysql-slow.log long_query_time = 1 wait_timeout=60 Note: values should be set after proper calculation of traffic forecast and system specifications of database server. Regards HostNoc
    0
  • marcuszan
    Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** tmp_table_size (> 16M) max_heap_table_size (> 16M) table_definition_cache(400) > 2230 or -1 (autosizing if supported) performance_schema = ON enable PFS innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size. I'm having a hard time deciphering what to change. Any help would be greatly appreciated.

    I have been tuning myself and started from the suggestions I got from mysql tuner. So in your case, I would try to start with > locate your my.cnf file, make a backup before doing anything with this file and next add: set-variable = tmp_table_size=32M set-variable = max_heap_table_size=32M set-variable = table_definition_cache=2480 set-variable = performance-schema=ON set-variable = innodb_log_file_size=16M set-variable = innodb_buffer_pool_size=64M
    please note it suggests to do > innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size. so tube these values accordingly In my case I have this but that depends on server specs and use. innodb_buffer_pool_size=1024M innodb_log_file_size=256M
    save, restart your DB server and run mysqltuner again Give it a try and let us know if it works for you
    0

Please sign in to leave a comment.