Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We change from Percona MySQL server 5.5 to MariaDB but performance is not as good as we think. This graph is from a day in MariaDB. You can see the low performance in terms of cache_hits: Mysql queries in MariaDBserver

We have others servers with Percona MySQL server 5.5. The graph below is one of them: Mysql queries in Percona MySQL server

We don't 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

mysqltuner:

[--] 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
share|improve this question
    
Is your data all InnoDB, all MyISAM, all Aria, or a mixture of all three? Are the buffers (InnoDB Buffer Pool and MyISAM Key Cache) the same on both Percona and MariaDB servers ? –  RolandoMySQLDBA Mar 17 '14 at 16:19
    
In both servers there is myisam as db engine.There are buffers too. You can see the conf attach in my post. –  David Martinez Mar 18 '14 at 15:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.