I don’t know if this is the right forum to use.
I’ve moved a Percona 5.7 database from our local premise to a Google Cloud Instance.
After the move, queries that where executed in 0.000 seconds on our local server, started immediatly to run in 0.030-0.060 seconds.
I have to optimize this. On our local server I used a (huge, 1GB) query cache, but for some mutex contention on GCE, I had to disable it totally. I had tons of query waiting due to caching but disabling it increased a lot the query execution time.
Now i’m trying with query cache enabled and just 100M or query_cache_size.
Our slowest procedure (that makes hundreds of queries) dropped from 2.5 seconds to 1.6-1.7
The best option is to disable the query_cache and focus your attention on why the queries are slow; the query cache is disabled by default for a reason, it doesn’t scale (as you experienced) and has in fact been removed from MySQL. You can read more about this on the MySQL Server Team blog ([url]https://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/[/url]).
For example, how much data do you have? There is only a 4G buffer pool and so if you have more than that amount of data then you may be needing to pull from disk. Similarly, if you are executing queries that need to sort on disk then queries once again become slower. The following resources should help you:
[LIST]
[][url]Upcoming Webinars
[][url]https://www.percona.com/doc/percona-server/5.7/diagnostics/slow_extended.html[/url].
[/LIST]
You could also use PMM ([url]Percona Monitoring and Management) to investigate what is behind the queries being slower than you expect.
The whole db is less than 500mb. Query cache solved a lot of issues, i had trouble with query cache only after moving from our server google Cloud. Keep in mind that i have about 1600-1800 selects per second VS less than 7-10 (ten) writes per second
If could be useful: i don’t have myisam tables so i can reduce as much as possible everything related to it
select * from orders where id IN (
SELECT orders_drivers.order_id
FROM orders_drivers
WHERE driver_id = 134
AND ignored = 0
AND FROM_UNIXTIME(delivery_at, GET_FORMAT(DATE,“ISO”)) = FROM_UNIXTIME(1537561200, GET_FORMAT(DATE,“ISO”))
AND delivery_at > 1537561200
) order by delivery_at asc limit 1;
If this is aiming to look for an order that is for delivery on the current day then you would be better to check that delivery_at is within a range with constants, e.g.
mysql> SET @ts := UNIX_TIMESTAMP(), @day_start_ts := UNIX_TIMESTAMP(CURDATE()), @day_end_ts := UNIX_TIMESTAMP(CURDATE() + INTERVAL 86399 SECOND);
Query OK, 0 rows affected (0.01 sec)
mysql> SELECT @ts BETWEEN @day_start_ts AND @day_end_ts, @ts, @day_start_ts, @day_end_ts;
+-------------------------------------------+------------+---------------+-------------+
| @ts BETWEEN @day_start_ts AND @day_end_ts | @ts | @day_start_ts | @day_end_ts |
+-------------------------------------------+------------+---------------+-------------+
| 1 | 1537532265 | 1537484400 | 1537570799 |
+-------------------------------------------+------------+---------------+-------------+
1 row in set (0.00 sec)
The use of variables here is just to demonstrate, but you would use delivery_at and could just use the functions in the comparison, i.e.:
delivery_at BETWEEN UNIX_TIMESTAMP(CURDATE()) AND UNIX_TIMESTAMP(CURDATE() + INTERVAL 86399 SECOND)
this was a typo, i’ve set that to 0 and log_slow_rate_limit=50 just to increase verbosity.
I’ve replaced the above query with 2 single queries like the following:
SELECT `orders_drivers`.`order_id`
FROM `orders_drivers` FORCE INDEX(`driver_id_AND_ignored_AND_delivery_at`)
WHERE `driver_id` = 46
AND `ignored` = 0
AND `delivery_at` BETWEEN UNIX_TIMESTAMP(CURDATE()) AND UNIX_TIMESTAMP(CURDATE() + INTERVAL 86399 SECOND)
AND `delivery_at` < 1537549200
ORDER BY `delivery_at` DESC
LIMIT 1
and then just a simple
SELECT * FROM `orders` WHERE `id` = :id
:id is the order_id returned by the previous query Thanks to the index “FORCE INDEX(driver_id_AND_ignored_AND_delivery_at)” I was able to remove the filesort:
+----+-------------+----------------+------------+-------+---------------------------------------+---------------------------------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------+------------+-------+---------------------------------------+---------------------------------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | orders_drivers | NULL | range | driver_id_AND_ignored_AND_delivery_at | driver_id_AND_ignored_AND_delivery_at | 11 | NULL | 3 | 100.00 | Using index condition |
+----+-------------+----------------+------------+-------+---------------------------------------+---------------------------------------+---------+------+------+----------+-----------------------+
This should be faster, right ? The aim is to look for the first delivery immediatly before the provided timestamp. (the where on CURDATE is to avoid searching across the whole table but insted just fetching the current day orders. I don’t know if this is useful in some way)
Now there are at least 2 more queries to optimize, if possible.
The biggest one (called tons of times):
EXPLAIN
select * from `delivery_zones` where ST_Contains(zone, POINT(x,y)) and `restaurant_id` = 100232 and ((`weekdays` & 16 or `weekdays` is null) and ((`hour_starts_at` is null and `hour_ends_at` is null) or (`hour_starts_at` is null and `hour_ends_at` >= '18:50:00') or (`hour_starts_at` <= '18:50:00' and `hour_ends_at` is null) or ('18:50:00' BETWEEN `hour_starts_at` AND `hour_ends_at`)) and ((`starts_at` is null and `ends_at` is null) or (`starts_at` is null and `ends_at` >= '2018-09-21 18:50:00') or (`starts_at` <= '2018-09-21 18:50:00' and `ends_at` is null) or ('2018-09-21 18:50:00' BETWEEN `starts_at` AND `ends_at`))) and `active` = 1 and `delivery_zones`.`deleted_at` is null order by `layer` asc;
+----+-------------+----------------+------------+------+-----------------------------------------------------------------+---------------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+----------------+------------+------+-----------------------------------------------------------------+---------------+---------+-------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | delivery_zones | NULL | ref | zone,active,weekdays,restaurant_id,deleted_at,hours,starts_ends | restaurant_id | 5 | const | 1 | 5.00 | Using index condition; Using where; Using filesort |
+----+-------------+----------------+------------+------+-----------------------------------------------------------------+---------------+---------+-------+------+----------+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
is making a filesort every time due to the ORDER BY layer
and
EXPLAIN
select * from `products_categories_promotions` where `products_categories_promotions`.`category_id` = 1333 and `products_categories_promotions`.`category_id` is not null and `active` = 1 and (`starts_at` is null or `starts_at` < '2018-09-21 17:08:00') and (`ends_at` is null or `ends_at` > '2018-09-21 17:08:00') and `products_categories_promotions`.`deleted_at` is null order by `created_at` desc;
+----+-------------+--------------------------------+------------+------+-------------------------------------------------------+-------------------------------------------------------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------------------------+------------+------+-------------------------------------------------------+-------------------------------------------------------+---------+-------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | products_categories_promotions | NULL | ref | FK_products_categories_promotions_products_categories | FK_products_categories_promotions_products_categories | 4 | const | 1 | 5.00 | Using index condition; Using where; Using filesort |
+----+-------------+--------------------------------+------------+------+-------------------------------------------------------+-------------------------------------------------------+---------+-------+------+----------+----------------------------------------------------+
1 row in set, 1 warning (0.05 sec)
that is still making a filesort but is called less time. Is not so bad.
Is safe to replace all occurrencies like the following
and (
(`starts_at` is null and `ends_at` is null)
or (`starts_at` is null and `ends_at` >= '2018-09-26 18:30:00')
or (`starts_at` <= '2018-09-26 18:30:00' and `ends_at` is null)
or ('2018-09-26 18:30:00' BETWEEN `starts_at` AND `ends_at`)
)
to something like this:
AND (
'2018-09-26 18:30:00' BETWEEN COALESCE(`starts_at`, (NOW() - INTERVAL 1 DAY)) AND COALESCE(`ends_at`,(NOW() + INTERVAL 1 DAY))
)
Obviously in the real code ‘2018-09-26 18:30:00’ is the current datetime.
The aim is to retrieve all records where starts_at is in the past (or is null) AND ends_at is in the future (or is null)
In other words, all records that are not explicitly expired (ends_at in the past) or still to come (starts_at in the future)
Hey there, thanks for reaching out. Hopefully someone else from the community might be able to chime in on this for you.
If you have LOTS of queries coming up one at a time, it’s not going to be practical or realistic for the Percona team to be helping you out on a case-by-case basis, as the questions are going to be too specific to your own environment. We’ve given it a shot, but really once it becomes particular to an application we’re at the end of where it’s appropriate to provide professional assistance in an ‘open source and free’ forum.
If you need some professional support, though, I am happy put you in touch with the sales engineers here. They’d be able to look your case and work out how best to help you out.