0

I have been searching through the net for some time and can not seem to find answer for this case. It would be much appreciated if I can get some advise. Here is the problem in short.

We do have 2 servers. One running with 12 cpu cores and 24GB ram on centos6/mysql5.5 and number two runs with 24 cpu cores and 32GB ram on centos7/mariadb5.5 . Both have the same data in the dbms; it was duplicated using mysqldump from mysql55 and imported in to mariadb5.5. Configuration for mysql and mariadb has been also copied from the smaller system to the bigger one; it is literally identical.

First problem is when put under the same load; mysql5.5 performs as expected with load avrg. of around 4 to 8; mariadb5.5 performs very badly with load average of 700 to 900. The command "show process list" on the mariadb system revels a lot of connections running for a long time more than 5 mins and are in state "Copying to tmp table" . The same queries are visible on the smaller system mysql5.5 but they do execute in 1-2 seconds.

I tried running maraidb with tmpdir created on a large ram partition. This reduced the load but not with much it eventually got back to 700.

Second thing I noticed was that if I do explain on one of the long running queries the one from mysql returns around 3k rows and the other server the same explain on the same query returns around 7k rows.

I am not sure what is causing this. The queries are predominantly 'select' with few 'and' and 'or' .

Thank you for your time. I would be grateful for any input on this.

  • Check that your import went correctly and that there weren't any missing rows or indexes. And make sure you've actually done some tuning on mariadb. – Michael Hampton Jul 19 '16 at 21:50
2

After some further comparing the explain of some of the longest running queries against both systems I noticed the server running mariadb was not using join_buffer when performing the operation.

When I looked for probable cause I found this variable:

https://mariadb.com/kb/en/mariadb/server-system-variables/#join_cache_level

Sure enough after changing this to 8 from the default 2; so we allow the dbms greater choice of bloack-based algorithms it can choose from when performing join operations everything started working. I am not db expert and not entirely sure what the consequences from this action could be. I suspect there would be increase in memory footprint but performance wise it seems to make the box "fly". load average is now down to 5 from 1200

My case seems to be somehow isolated because the application I am running was doing some very long selects with multiple joins. But I guess the behavior would be the same for any mysql to mariadb transition with similar sql workload.

0

I use mariadb 10 under Centos 7 linux. It work well because by default database stores every table per file and indexes per other file. Check that you create indexes for fields which are used in "where" and "join on" clauses. The right choice of fields for indexes may give you raise performance more than 100 times. How to install mariadb 10 on Centos 7 you can find there https://mariadb.com/kb/en/mariadb/yum/

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.