We recently upgraded our core servers from Percona Server for MySQL 5.6 to 5.7, running on Ubuntu 16.04. Right afterwards we experienced huge delays on our front-facing servers (also Ubuntu 16.04).
One of the query where we noted issues:
-- `users` is INTEGER NO NULL
-- `deleted_at` is DATETIME
set @rand_id=floor(rand() * 1000000); explain SELECT `users`.* FROM `users` WHERE `users`.`deleted_at` IS NULL AND `users`.`id` = @rand_id limit 1\G
Query OK, 0 rows affected (0.00 sec)
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: users
partitions: p4
type: const
possible_keys: PRIMARY,index_users_on_deleted_at
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.44 sec)
1k , which took the time for 1k queries down to ~2.5 seconds.
Can anyone explain why we’re seeing much better query times by switching from the client library for Percona Server 5.7 to that for 5.5 ?
Client App is Rails 3.2 (yes, I know; no, we can’t upgrade it ATM) using ActiveRecord, running as Passenger inside nginx.