Author Topic: Recommendations from MySQLTuner  (Read 11658 times)

0 Members and 1 Guest are viewing this topic.

Offline
*
Recommendations from MySQLTuner
« on: February 20, 2020, 12:49:09 PM »
Hi, here is the output from MySQLTuner
General recommendations:
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce or eliminate unclosed connections and network issues
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Set thread_cache_size to 4 as a starting value
    Performance should be activated for better diagnostics
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
    Read this before changing innodb_log_file_size and/or innodb_log_files_in_group: http://bit.ly/2wgkDvS
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 1M, or use smaller result sets)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    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.
    innodb_buffer_pool_instances (=1)

How do I deal with that? I added the recommendations to adjust variables to /etc/my.cnf just below the line
!includedir /etc/my.cnf.d
Code: [Select]
[mysqld]
    query_cache_size = 0
    query_cache_type = 0
    query_cache_limit = 3M
    tmp_table_size = 32M
    max_heap_table_size = 32M
    thread_cache_size = 4
    performance_schema = ON enable PFS
    innodb_log_file_size should be =16M
    innodb_buffer_pool_instances =1

Restarting MySQL mariadb.service resulted in database connection errors. is a server reboot necessary or is the syntax not correct?

Offline
*****
Re: Recommendations from MySQLTuner
« Reply #1 on: February 20, 2020, 02:58:53 PM »
check the log for MySQL for exact error.
http://wiki.centos-webpanel.com/service-log-paths

Re: Recommendations from MySQLTuner
« Reply #2 on: February 20, 2020, 05:46:34 PM »
The answer is very obvious:
Code: [Select]
    performance_schema = ON enable PFS
    innodb_log_file_size should be =16M
Copied verbatim, rather than following the recommendation:
Code: [Select]
    performance_schema = ON
    innodb_log_file_size =16M
Though I never bother with the overhead of PFS - it's counter-productive except for short tests.
I sense other issues may arise.  ::)

Back to lurking..
« Last Edit: February 20, 2020, 05:49:06 PM by ejsolutions »

Offline
*
Re: Recommendations from MySQLTuner
« Reply #3 on: February 27, 2020, 08:56:32 AM »
Thanks, ejsolutions. It's working with the corrections you suggested. That was not so obvious for me.

Re: Recommendations from MySQLTuner
« Reply #4 on: February 27, 2020, 10:18:53 AM »
Thanks, ejsolutions.
You're welcome.