2

I'm migrating my database (MySQL 5.1.73) to a new server, and I am trying to replace it with mariadb (5.5.5-10). Globally mariadb seems much faster, but there is a couple of queries that it refuses to cache where mysql does. This is a big problem because those queries are used LOTS (there are sort of on the home page so will be executed pretty much every time the user interact).

I thought mariadb was a drop in replacement for mysql, so I'm surprised of this behavior ? As anyone any idea why such a different behavior or how can I force the cache to be used (I tried SELECT SQL_CACHE but that doesn't change anything.

Here is the mysql configuration

    +------------------------------+------------+
    | Variable_name                | Value      |
    +------------------------------+------------+
    | binlog_cache_size            | 32768      |
    | have_query_cache             | YES        |
    | key_cache_age_threshold      | 300        |
    | key_cache_block_size         | 1024       |
    | key_cache_division_limit     | 100        |
    | max_binlog_cache_size        | 4294963200 |
    | query_cache_limit            | 1048576    |
    | query_cache_min_res_unit     | 4096       |
    | query_cache_size             | 16777216   |
    | query_cache_type             | ON         |
    | query_cache_wlock_invalidate | OFF        |
    | table_definition_cache       | 256        |
    | table_open_cache             | 64         |
    | thread_cache_size            | 8          |
    +------------------------------+------------+

And the mariadb one

+--------------------------------+----------------------+
| Variable_name                  | Value                |
+--------------------------------+----------------------+
| aria_pagecache_age_threshold   | 300                  |
| aria_pagecache_buffer_size     | 134217728            |
| aria_pagecache_division_limit  | 100                  |
| aria_pagecache_file_hash_size  | 512                  |
| binlog_cache_size              | 32768                |
| binlog_stmt_cache_size         | 32768                |
| have_query_cache               | YES                  |
| host_cache_size                | 228                  |
| innodb_disable_sort_file_cache | OFF                  |
| innodb_ft_cache_size           | 8000000              |
| innodb_ft_result_cache_limit   | 2000000000           |
| innodb_ft_total_cache_size     | 640000000            |
| join_cache_level               | 2                    |
| key_cache_age_threshold        | 300                  |
| key_cache_block_size           | 1024                 |
| key_cache_division_limit       | 100                  |
| key_cache_file_hash_size       | 512                  |
| key_cache_segments             | 0                    |
| max_binlog_cache_size          | 18446744073709547520 |
| max_binlog_stmt_cache_size     | 18446744073709547520 |
| metadata_locks_cache_size      | 1024                 |
| query_cache_limit              | 1048576              |
| query_cache_min_res_unit       | 4096                 |
| query_cache_size               | 16777216             |
| query_cache_strip_comments     | OFF                  |
| query_cache_type               | ON                   |
| query_cache_wlock_invalidate   | OFF                  |
| stored_program_cache           | 256                  |
| table_definition_cache         | 400                  |
| table_open_cache               | 400                  |
| thread_cache_size              | 100                  |
+--------------------------------+----------------------+
  • 1
    If you want to know what's in query cache, MariaDB has a plugin for that mariadb.com/kb/en/mariadb/query_cache_info-plugin . 5.5.5-10 is not a real version, it is a trick to workaround the bug in MySQL replication (long story) . I think the version is 10.0.something. 'SELECT @@version' gives you real one. – Vladislav Vaintroub Oct 27 '16 at 13:14
1

There are many different "caches"; not all of them have 'cache' in their name. The most important is innodb_buffer_pool_size. An that one is important only if you are using InnoDB (which you should be) instead of MyISAM. Furthermore, was the Engine of individual tables changed during the upgrade?

Check the table Engines and buffer_pool_size, then report back. BTW, the buffer_pool should be about 70% of available ram. And show us the non-cached queries, together with SHOW CREATE TABLE for them.

I see that the Query cache is set the same way. However, that is usually bad for an active production server. But that is another topic.

"Drop in replacement" -- mostly it is. But, as you found out, it is "faster" in some ways, "slower" in others. Even if you moved from MySQL 5.1 to MySQL 5.5, you might see similar better/worse scenarios.

  • Nothing has been changed during the upgrade. I'm using docker to switch between mysql and mariadb and start with the same dump. However I tried mariadb 10 and I don't have this problem anymore, or not for some queries. – mb14 Oct 26 '16 at 20:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.