First of all, many thanks in advance for your creating this nice site/place where people can find help to our "big" issues/problems. I usually use every day and you dont know how many times I fix my confs/servers/services.
We change from percona mysql server 5.5 to mariadb but performace is not as good as we think. This graph is from a day in mariadb. You can see the low performace in terms of cache_hits
We have others servers with percona mysql server 5.5. The graph below is one of them
We dont know how there is only 46% of query cache efficiency. With percona mysql server was near 90%.
This is my.conf from mariadb server
# # * Fine Tuning # bulk_insert_buffer_size = 16M concurrent_insert = 2 connect_timeout = 5 interactive_timeout = 14400 join_buffer_size = 1G key_buffer = 256M key_buffer_size = 256M max_allowed_packet = 16M max_connections = 200 myisam_recover = BACKUP myisam_sort_buffer_size = 500M open_files_limit = 20000 table_open_cache = 5000 table_definition_cache = 3000 query_cache_limit = 16M query_cache_size = 1G query_cache_min_res_unit = 4K read_buffer_size = 8M read_rnd_buffer_size = 4M sort_buffer_size = 512K aria-pagecache-buffer-size = 256M key-cache-segments = 8 thread_cache_size = 128 thread_concurrency = 8 thread_stack = 192K tmp_table_size = 2G max_heap_table_size = 2G wait_timeout = 14400
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM +SPHINX [--] Data in InnoDB tables: 8M (Tables: 10) [--] Data in MyISAM tables: 872M (Tables: 146) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 11 -------- Performance Metrics ------------------------------------------------- [--] Up for: 1d 20h 47m 59s (40M q [248.063 qps], 2M conn, TX: 87B, RX: 3B) [--] Reads / Writes: 97% / 3% [--] Total buffers: 2.9G global + 1.0G per thread (200 max threads) [!!] Maximum possible memory usage: 204.3G (651% of installed RAM) [OK] Slow queries: 1% (529K/40M) [OK] Highest usage of available connections: 11% (23/200) [OK] Key buffer size / total MyISAM indexes: 128.0M/92.7M [OK] Key buffer hit rate: 100.0% (61B cached / 44K reads) [OK] Query cache efficiency: 45.3% (28M cached / 62M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (111 temp sorts / 1M sorts) [!!] Temporary tables created on disk: 48% (1M on disk / 2M total) [OK] Thread cache hit rate: 99% (23 created / 2M connections) [OK] Table cache hit rate: 68% (370 open / 541 opened) [OK] Open file limit used: 2% (517/20K) [!!] Table locks acquired immediately: 93% [OK] InnoDB buffer pool / data size: 256.0M/8.4M [OK] InnoDB log waits: 0
Any help will be welcome.
Many thanks in advance