-1

I change the MySQL 5.6 to MariaDB 10.1, the total number of record above 5 Million. Using PHP script export the data with join query. Now the export take time delay for same PHP code. 

Before, MYSQL DB : 20 to 30 second 
After, MariaDB DB : 50 to 60 second

Please suggest me, how to speed up the execution time.

Raj Mohan
  • 543
  • 9
  • 25
  • Where is the code that you are using? How did you move the data from MySQL to MariaDB? Are all of the same indexes in place? Does an explain of the query give you any clues? So many question so little information. – Dave May 07 '18 at 19:35
  • I exported the PHP config from old server and imported/ provisioned on new server. I did the same for my.cnf for MariaDB server. – Raj Mohan May 08 '18 at 10:14
  • is there any difference in the my.cnf variable of MySQL and MariaDB ? I have copied my.cnf of MySQL to MariaDB - will this cause any performance issue – Raj Mohan May 08 '18 at 14:09
  • Did you read the answer below provided by @ndev? He gives you a very complete answer with links for further information. – Dave May 08 '18 at 14:11
  • @RajMohan - there may be a dozen ways to export/import. We cannot guess which one you used. – Rick James May 22 '18 at 03:39

1 Answers1

1

The question is to general to answer it. You should provide some example code used for export.

To speed up the execution time you could tweak your config file.

There are some differences between MySql and MariaDB settings. Take a look here https://mariadb.com/kb/en/library/system-variable-differences-between-mariadb-101-and-mysql-56/

You should know what are you using MyISAM or InnoDB.

The most notable differences are that MariaDB includes, by default, the Aria storage engine (resulting in extra memory allocation), Galera Cluster, uses Percona's XtraDB instead of Oracle's InnoDB, and has a different thread pool implementation. For this reason, a default implementation of MariaDB 10.1 will use more memory than MySQL 5.6. MariaDB 10.1 and MySQL 5.6 also have different GTID implementations.

MariaDB's extra memory usage can be handled with the following rules of thumb:

If you are not using MyISAM and don't plan to use Aria:

Set key_buffer_size to something very low (16K) as it's not used.

Set aria_pagecache_buffer_size to what you think you need for handling internal tmp tables that didn't fit in memory.

Normally this is what before you had set for key_buffer_size (at least 1M).

If you are using MyISAM and not planning to use Aria:

Set aria_pagecache_buffer_size to what you think you need for handling internal tmp tables that didn't fit in memory.

If you are planning to use Aria, you should set aria_pagecache_buffer_size to something that fits a big part of your normal data + overflow temporary tables.

And here are the default values in MySQL 5.6 https://dev.mysql.com/doc/refman/5.6/en/server-default-changes.html

N Dev
  • 31
  • 2