Question MySQL 5.5 configuration

[Kapnos]

Hi,

I'm using Plesk Onyx with MySQL 5.5.53 for hosting a news site based on wordpress with WP Super Cache on a dedicated server with 32GB RAM and 2x250GB SSD RAID1. Can you give some advice for my.cnf for best performance? I have this settings for now:

join_buffer_size        = 2M
tmp_table_size          = 64M
max_heap_table_size     = 64M
innodb_buffer_pool_size = 8G
innodb_log_buffer_size  = 8M
key_buffer              = 32M
max_allowed_packet      = 32M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
max_connections        = 800
query_cache_limit       = 8M
query_cache_size        = 64M

and after running mysqltuner I get these results:

Variables to adjust:
    query_cache_type (=0)
    query_cache_size (> 64M)
    join_buffer_size (> 2.0M, or always use indexes with joins)
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)
    table_open_cache (> 400)
    innodb_file_per_table=ON
    innodb_log_file_size should be equals to 1/4 of buffer pool size (=2G) if possible.
    innodb_buffer_pool_instances(=8)

 

[Bitpalast]

Sounds more like a question for a MySQL performance tuning forum than for a Plesk forum.

Optimal MySQL performance settings are strongly determined by the actual usage and behavior of scripts. Noone can tell you if the above settings are good for your or not. The settings that a single execution of Mysqltuner determines are one out of many possible sets. As I understand Mysqltuner it is essential to continuously monitor the script and database actions by it to derive best performance settings from new data as they are logged. Running Mysqltuner next week for example might yield better values, because more data becomes available over time. For example, if a specific function that requires a large buffer is only execute once a week, your might not have caught it yet, so you won't know that your buffer size is too small.

 

[Linulex]

I just posted our my.cnf here https://talk.plesk.com/threads/bug-in-local-database.340742/

maybe you find it helpfull.

be carefull, this is a 5.6 my.cnf, you need to change

#5.5
#table_cache=32000
#5.6+
table_open_cache=32000

to use it on 5.5.

You might need to change the location of pid, logs, etc... to fit your needs.

regards
Jan