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.