Author Topic: how to fine tune mysql conf  (Read 714 times)

0 Members and 1 Guest are viewing this topic.

Offline
*
how to fine tune mysql conf
« on: May 11, 2020, 01:33:57 PM »
I am not oso familiar with mysql and just purchase the cwppro and running the server and websites. Generally running well but need to think about the increased load of DB in the future.

Here below is mysql tuner recommendation.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Dedicate this server to your database for highest performance.
    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)



I found not much info in my etc/my.cnf and /etc/my.cnf.d folder, how should i modify the items above? Shall I modify inside the cnf file? If so, how and which file should I edit?


my.cnf

[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d



my.cnf.d folder include the below files
client.cnf
enable_encryption.preset
mysql-clients.cnf
server.cnf
tokudb.cnf

Offline
*****
Re: how to fine tune mysql conf
« Reply #1 on: May 13, 2020, 12:09:11 PM »
you should edit /etc/my.cnf

Offline
*****
Re: how to fine tune mysql conf
« Reply #2 on: May 13, 2020, 12:27:14 PM »
you can edit /etc/my.cnf and options like below:
[mysqld]
 query_cache_size=0