2

I have a pool of MariaDB servers like this :

  • master (5.5.38-MariaDB-log)
  • slave1 (5.5.38-MariaDB-log)
  • slave2 (5.5.38-MariaDB-log)

The two slaves servers run on the exact same hardware (Dell Poweredge 2950, RAM 32GB, 8 sas disk, Intel(R) Xeon(R) CPU E5420 @ 2.50GHz)

Both slaves are installed in the same way, on a debian Wheezy (up-to-date), same fs (ext4 noatime, nobarrier)...etc

Dell OMSA do not report any hardware problem on these servers.

MariaDB config is the same on both (SHOW VARIABLES differs only for hostname, server_id, timestamp, log_error path and pseudo_thread_id)

The problem is that queries on slave2 are far slower than the same queries on slave1.

Database schema and data are equal (comparing mysqldump output and pt-table-checksum)

Though, it does not appear related to database schema or data because I created a fresh test table with 10k rows like this:

CREATE DATABASE nptest;
use nptest
CREATE TABLE np (id int NOT NULL PRIMARY KEY AUTO_INCREMENT, val int);
DELIMITER $$
CREATE PROCEDURE prepare_data()
BEGIN
  DECLARE i INT DEFAULT 100;

  WHILE i < 100000 DO
    INSERT INTO np (val) VALUES (i);
    SET i = i + 1;
  END WHILE;
END$$
DELIMITER ;
CALL prepare_data();

And I still notice the performance difference.

Look at this: Here is the profiling for some simple queries on each slave:

SLAVE1 (the fast one)

nptest=# show profiles;
+----------+------------+------------------------------------+
| Query_ID | Duration   | Query                              |
+----------+------------+------------------------------------+
|        1 | 0.00032486 | SELECT * FROM np WHERE id = 123456 |
|        2 | 0.00032632 | SELECT * FROM np WHERE id = 12345  |
|        3 | 0.00030954 | SELECT * FROM np WHERE id = 12346  |
|        4 | 0.00033966 | SELECT * FROM np WHERE id = 12347  |
+----------+------------+------------------------------------+
4 rows in set (0.00 sec)

AVG = 0,000325095 sec.

SLAVE2 (the slow one)

nptest=# show profiles;
+----------+------------+------------------------------------+
| Query_ID | Duration   | Query                              |
+----------+------------+------------------------------------+
|        1 | 0.00665687 | SELECT * FROM np WHERE id = 123456 |
|        2 | 0.00302109 | SELECT * FROM np WHERE id = 12345  |
|        3 | 0.00303166 | SELECT * FROM np WHERE id = 12346  |
|        4 | 0.00448533 | SELECT * FROM np WHERE id = 12347  |
+----------+------------+------------------------------------+
4 rows in set (0.00 sec)

AVG = 0,004298737 sec.

So slave2 is 10x slower then slave1!

What can explain these performance differences? Any tips on what to investigate would be appreciated.

EDIT 26/09/2014 : Running sysbench (mysql, cpu, memory) confirm that slave2 is far slower than slave1. Something to notice in sysbench tests, is that the "max" value is always very high for slave2, comparing to slave1. Whereas "min" or "95 percentile" values are quite equal. For example:

sysbench --test=memory --memory-block-size=1M --memory-total-size=10G run


SLAVE1:
min: 0.13ms
avg: 0.13ms
max: 0.96ms
approx. 95 percentile: 0.14ms

SLAVE2:
min: 0.13ms
avg: 0.14ms
max: 12.32ms
approx. 95 percentile: 0.14ms

However, Dell OMSA reveals no CPU or memory failure.

Anyway, the server is 6 years old, he was taken out of production...

  • 1
    Some questions that might be relevant: Is there any power management or CPU frequency scaling (Intel Speedstep) enabled in the BIOS on the slow machine? Is the memory operating at the same frequency on both machines? Does the output of sysctl -a match? – James L Sep 3 '14 at 10:42
  • Also, is the EXPLAIN output the same on both machines? – James L Sep 3 '14 at 21:57
  • I checked EXPLAIN, it is the same on both machines. sysctl -a is also the same (if you omit some uuid, hostname and so). I'll check for power management. – Nicolas Payart Sep 4 '14 at 8:02
  • I checked for power management. BIOS setup is equal on both servers. "Demand-Based Power Management" is disabled. I also reinstalled slave2 (wheezy), but still have the same poor performance with mysql. Also, see my edit with sysbench tests! – Nicolas Payart Sep 26 '14 at 8:55
  • Did you solve this? I have similar problems with our servers, when one has only 90MB/s memory performance. – Daniel May 14 '16 at 8:16

Your Answer

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

Browse other questions tagged or ask your own question.