I have a query that executes less time on dev server than on prod (database is the same). Prod server is much more efficient (64gb ram, 12 cores, etc).
Here's the query:
SELECT `u`.`id`,
`u`.`user_login`,
`u`.`last_name`,
`u`.`first_name`,
`r`.`referrals`,
`pr`.`worker`,
`rep`.`repurchase`
FROM `ci_users` `u`
LEFT JOIN
(SELECT `referrer_id`,
COUNT(user_id) referrals
FROM ci_referrers
GROUP BY referrer_id) AS `r` ON `r`.`referrer_id` = `u`.`id`
LEFT JOIN
(SELECT `user_id`,
`expire`,
SUM(`quantity`) worker
FROM ci_product_11111111111111111
GROUP BY `user_id`) AS `pr` ON `pr`.`user_id` = `u`.`id`
AND (`pr`.`expire` > '2015-12-10 09:23:45'
OR `pr`.`expire` IS NULL)
LEFT JOIN `ci_settings` `rep` ON `u`.`id` = `rep`.`id`
ORDER BY `id` ASC LIMIT 100,
150;
Having following explain result on dev server:
+----+-------------+------------------------------+--------+---------------+-------------+---------+-----------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------------------+--------+---------------+-------------+---------+-----------+-------+---------------------------------+
| 1 | PRIMARY | u | index | NULL | PRIMARY | 4 | NULL | 1 | NULL |
| 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 5 | dev1.u.id | 10 | NULL |
| 1 | PRIMARY | <derived3> | ref | <auto_key1> | <auto_key1> | 5 | dev1.u.id | 15 | Using where |
| 1 | PRIMARY | rep | eq_ref | PRIMARY | PRIMARY | 4 | dev1.u.id | 1 | NULL |
| 3 | DERIVED | ci_product_11111111111111111 | ALL | NULL | NULL | NULL | NULL | 30296 | Using temporary; Using filesort |
| 2 | DERIVED | ci_referrers | ALL | NULL | NULL | NULL | NULL | 11503 | Using temporary; Using filesort |
+----+-------------+------------------------------+--------+---------------+-------------+---------+-----------+-------+---------------------------------+
And this one from prod:
+----+-------------+------------------------------+--------+---------------+---------+---------+--------------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------------------------+--------+---------------+---------+---------+--------------+-------+---------------------------------+
| 1 | PRIMARY | u | ALL | NULL | NULL | NULL | NULL | 10990 | |
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2628 | |
| 1 | PRIMARY | <derived3> | ALL | NULL | NULL | NULL | NULL | 8830 | |
| 1 | PRIMARY | rep | eq_ref | PRIMARY | PRIMARY | 4 | prod123.u.id | 1 | |
| 3 | DERIVED | ci_product_11111111111111111 | ALL | NULL | NULL | NULL | NULL | 28427 | Using temporary; Using filesort |
| 2 | DERIVED | ci_referrers | ALL | NULL | NULL | NULL | NULL | 11837 | Using temporary; Using filesort |
+----+-------------+------------------------------+--------+---------------+---------+---------+--------------+-------+---------------------------------+
Profiling results on prod server shown me something like that:
............................................
| statistics | 0.000030 |
| preparing | 0.000026 |
| Creating tmp table | 0.000037 |
| executing | 0.000008 |
| Copying to tmp table | 5.170296 |
| Sorting result | 0.001223 |
| Sending data | 0.000133 |
| Waiting for query cache lock | 0.000005 |
............................................
After googling a while I decided to move temporary tables into RAM:
/etc/fstab:
tmpfs /var/tmpfs tmpfs rw,uid=110,gid=115,size=16G,nr_inodes=10k,mode=0700 0 0
directory rules:
drwxrwxrwt 2 mysql mysql 40 Dec 15 13:57 tmpfs
/etc/mysql/my.cnf(played a lot with values):
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /var/tmpfs
lc-messages-dir = /usr/share/mysql
skip-external-locking
bind-address = 127.0.0.1
key_buffer = 16000M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 150
myisam-recover = BACKUP
tmp_table_size = 512M
max_heap_table_size = 1024M
max_connections = 100000
table_cache = 1024
innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64
query_cache_limit = 1000M
query_cache_size = 10000M
log_error = /var/log/mysql/error.log
expire_logs_days = 10
max_binlog_size = 100M
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 16M
And it doesn't work. Execution time still the same, around 5 sec. Can you please answer 2 questions:
Thanks in advance.
The explains show that on prod the query does not use indexes on u, derived1, derived2 tables, while on dev it does. Scanned row numbers are significantly higher on prod as a result. The index names on the 2 derived tables suggest that these have been created by mysql on the fly, taking advantage of materialized derived tables optimisation strategy, which is available from mysql v5.6.5. Since no such optimization is present in the explain from the prod server, prod server may have an earlier mysql version.
As @Satevg supplied in a comment, the dev and prod environments have the following mysql versions:
Dev: debian 7, Mysql 5.6.28. Prod: debian 8, Mysql 5.5.44
This subtle difference in mysql version may explain the speed difference, since the dev server can take advantage of the materialization optimization strategy, while the prod - being v5.5 only - cannot.