Hi guys.
Just wondering if there are any "mySQL Gurus" here who can help me out. I ran the advisor in phpMyAdmin and it's recommended a few changes. However, not being a mySQL person, and having asked the SiteGround people, they have asked I ask the "developer" of my script. The Advisor is stating the following:
Issue
Less than 80% of the query cache is being utilized.
The query cache size is above 128 MiB. Big query caches may cause significant overhead that is required to maintain the cache.
{tmp_table_size} and {max_heap_table_size} are not the same.
MyISAM key buffer (index cache) % used is low.
The rate of opening tables is high.
The rate of opening files is high.
The InnoDB log file size is not an appropriate size, in relation to the InnoDB buffer pool.
My current mySQL config is:
datadir=/var/lib/mysql
socket=/tmp/mysql.sock
old_passwords=1
ft_min_word_len=3
#safe-show-database
skip-name-resolve
max_connections=500
max_connect_errors=25
connect_timeout=10
interactive_timeout=20
wait_timeout=50
delayed_insert_timeout=10
join_buffer_size=1M
max_allowed_packet=8M
myisam_sort_buffer_size=1M
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=2M
table_cache=2048
thread_cache_size=192
thread_concurrency = 4
query_cache_size=256M
query_cache_limit=32M
query_cache_type=1
tmp_table_size=128M
max_tmp_tables=10
slow_query_log=1
long_query_time=1
slow-query-log-file = /var/lib/mysql/mysql-slow.log
old-passwords = 1
log_err = /var/lib/mysql/mysql.err
innodb-file-per-table=1
character-set-server=utf8
collation-server=utf8_general_ci
innodb_buffer_pool_size = 256M
innodb_log_file_size = 5M
key_buffer_size = 128M
bulk_insert_buffer_size = 16M
innodb_thread_concurrency = 4
innodb_autoinc_lock_mode = 0
skip-external-locking
innodb_doublewrite = false
low_priority_updates = 1
innodb_support_xa = false
max_write_lock_count = 10
innodb_flush_log_at_trx_comm = 2
innodb_max_dirty_pages_pct = 40
innodb_io_capacity = 400
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_adaptive_flushing = 1
innodb_adaptive_flushing_method = estimate
innodb_flush_method = O_DIRECT
open_files_limit=5340
[mysqld_safe]
log-error = /var/lib/mysql/mysql.err
If anyone could help me out with the options I should be using, I'd appreciate it.
Thanks
Dave