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.

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 Mysql queries in mariadb server

We have others servers with percona mysql server 5.5. The graph below is one of themMysql queries in percona mysql server

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

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

Any help will be welcome.

Many thanks in advance

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 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 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.