Control Web Panel

WebPanel => MySQL => Topic started by: filz51 on February 20, 2020, 12:49:09 PM

Title: Recommendations from MySQLTuner
Post by: filz51 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?

Title: Re: Recommendations from MySQLTuner
Post by: Sandeep on February 20, 2020, 02:58:53 PM

check the log for MySQL for exact error.
http://wiki.centos-webpanel.com/service-log-paths

Title: Re: Recommendations from MySQLTuner
Post by: ejsolutions 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 =16MCopied verbatim, rather than following the recommendation:

Code: [Select]

    performance_schema = ON
    innodb_log_file_size =16MThough 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..

Title: Re: Recommendations from MySQLTuner
Post by: filz51 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.

Title: Re: Recommendations from MySQLTuner
Post by: ejsolutions on February 27, 2020, 10:18:53 AM

Quote from: filz51 on February 27, 2020, 08:56:32 AM

Thanks, ejsolutions.

You're welcome.