I have a query that executes less time on a dev server than on prod (the database is the same). Prod-server is much more efficient (64-gigabyte drum, 12 cores, etc.).
Here's the request:
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;
After explaining the result on the 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 |
+----+-------------+------------------------------+--------+---------------+---------+---------+--------------+-------+---------------------------------+
The profiling results on the prod server showed me something like this:
............................................
| 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 searching on Google, I decided to move the temporary tables to RAM:
/etc. / Fstab:
tmpfs /var/tmpfs tmpfs rw,uid=110,gid=115,size=16G,nr_inodes=10k,mode=0700 0 0
catalog 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 does not work. The execution time is still the same, about 5 seconds. Can you answer 2 questions:
- What happened to tmpfs configuration?
- , ? ( tmpfs, , "" , ).
.