Query cache size and my.cnf recommendations

[Acidshock]

I noticed vicibox sets query_cache_size to 0. I was wondering if this is intentional? Should this be changed? Also I ran the MySQL tuning script. It had some recommendations that I want to run by everyone and then see if anyone has any recommendations.

DB Server Specs: 128GB of Mem, 12 SSDs in raid 10, dual 8 core xeon procs.

Current mariadb settings:

Code: Select all

[mysqld]
port            = 3306
socket          = /var/run/mysql/mysql.sock
datadir = /var/lib/mysql
skip-external-locking
key_buffer_size = 24576M
max_allowed_packet = 2M
table_open_cache = 8192
sort_buffer_size = 4M
net_buffer_length = 8K
read_buffer_size = 4M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 128M
thread_cache_size = 50
query_cache_size = 0
thread_concurrency=16
skip-name-resolve
connect_timeout=60
long_query_time=3
log_slow_queries
max_connections=2000
open_files_limit=24576
max_heap_table_size=64M
expire_logs_days=3
default-storage-engine=MyISAM
table_definition_cache=8192
table_cache=16384
concurrent_insert=2
myisam_recover
myisam_repair_threads=1
tmpdir          = /tmp/


Large DB files:

Code: Select all

-rw-rw---- 1 mysql mysql  5.2G Jul  5 14:42 /var/lib/mysql/asterisk/call_log_archive.MYD
-rw-rw---- 1 mysql mysql  3.5G Jul  5 22:45 /var/lib/mysql/asterisk/call_log_archive.MYI
-rw-rw---- 1 mysql mysql  2.2G Jul  8 19:14 /var/lib/mysql/asterisk/recording_log.MYD
-rw-rw---- 1 mysql mysql  5.4G Jul  5 17:06 /var/lib/mysql/asterisk/vicidial_carrier_log_archive.MYD
-rw-rw---- 1 mysql mysql  1.8G Jul  5 22:52 /var/lib/mysql/asterisk/vicidial_carrier_log_archive.MYI
-rw-rw---- 1 mysql mysql  4.7G Jul  5 17:45 /var/lib/mysql/asterisk/vicidial_dial_log_archive.MYD
-rw-rw---- 1 mysql mysql  2.3G Jul  5 22:54 /var/lib/mysql/asterisk/vicidial_dial_log_archive.MYI
-rw-rw---- 1 mysql mysql  5.5G Jul  8 18:16 /var/lib/mysql/asterisk/vicidial_list.MYD
-rw-rw---- 1 mysql mysql  4.9G Jul  8 19:19 /var/lib/mysql/asterisk/vicidial_list.MYI
-rw-rw---- 1 mysql mysql  3.8G Jul  5 19:44 /var/lib/mysql/asterisk/vicidial_log_archive.MYD
-rw-rw---- 1 mysql mysql  1.9G Jul  5 22:58 /var/lib/mysql/asterisk/vicidial_log_archive.MYI
-rw-rw---- 1 mysql mysql  2.3G Jul  5 20:54 /var/lib/mysql/asterisk/vicidial_log_extended_archive.MYD
-rw-rw---- 1 mysql mysql  2.5G Jul  5 22:59 /var/lib/mysql/asterisk/vicidial_log_extended_archive.MYI


MySQL-tuning.sh recommendations.

Code: Select all

TABLE LOCKING
Current Lock Wait ratio = 1 : 106
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'


Code: Select all

MEMORY USAGE
Max Memory Ever Allocated : 35 G
Configured Max Per-thread Buffers : 47 G
Configured Max Global Buffers : 21 G
Configured Max Memory Limit : 69 G
Physical Memory : 126.13 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 23 G
Current key_buffer_size = 21 G
Key cache miss rate is 1 : 14
Key buffer fill ratio = 22.00 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is supported but not enabled
Perhaps you should set the query_cache_size

SORT OPERATIONS
Current sort_buffer_size = 4 M
Current read_rnd_buffer_size = 16 M
Sort buffer seems to be fine

JOINS
mysql-tuning.sh: line 286: export: `2097152': not a valid identifier
Current join_buffer_size = 132.00 K
You have had 4916 queries where a join could not use an index properly
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.



[williamconley]

I'd start by upgrading to the latest Vicibox (ie: OpenSuSE) installer with whatever the my.cnf big DB settings are from Kumba.

Then I'd also ask a question or two regarding whatever changes you propose to make. For instance: InnoDB has been tried and failed miserably on all attempts to date. I don't know if the InnoDB changes were only for specific tables, but I strongly suspect they were (and that InnoDB failed miserably as it always has with this level of DB throughput).

In all other cases, the only recommendation I can make is that you find yourself a nice "mysql watcher" application to see how well it's doing and make one or two changes while carefully watching your stats for improvement. This only counts, of course, if you're stressing the system and it's having problems.

If you are NOT stressing the system, and it's NOT having problems, then you may be "wandering around looking for things to break" and should probably stop now and go try to improve automation instead. (IMHO )

[Acidshock]

DB server is loaded with the latest Vicibox and is using the my-big.cnf file. I did modify it and gave 24GB to they key buffer in order to try to load all indexes into memory.

[williamconley]

https://mariadb.com/kb/en/mariadb/optim ... ffer_size/

Interesting article showing how you can test to see if your key buffer size is proper.

I can't say why the query_cache_size is 0 these days, all our servers have it set to 256M.