Drunken M
Reputation: 2694
How to Keep Laravel SQl Query Time Under 5 seconds
My php application uses laravel 4.1.31. For database queries, it uses knockout js library through ajax. When the database size grows larger, the ajax loading becomes a problem because it takes too long and sometimes stops halfway. I'm not familiar with the libraries so I can't do any application level optimization.
I hope that I can still do optimization on the database query and keep each query under 5 seconds.
Here are some of information I can share:
RAM: 2GB 1core Debian 9. Single Innodb database.
The Slow query log:
# Time: 190611 7:49:08
# User@Host: user[user] @ localhost []
# Thread_id: 690728 Schema: user QC_hit: No
# Query_time: 9.343611 Lock_time: 0.000030 Rows_sent: 100 Rows_examined: 440481
# Rows_affected: 0
use user;
SET timestamp=1560239348;
select * from `titles` where `titles`.`type` = 'movie'
order by `tmdb_rating` desc limit 100 offset 86500;
SHOW CREATE TABLE titles;
CREATE TABLE `titles` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`type` enum('movie','series') COLLATE utf8_unicode_ci DEFAULT NULL,
`imdb_rating` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
`tmdb_rating` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
`mc_user_score` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
`mc_critic_score` smallint(5) unsigned DEFAULT NULL,
`mc_num_of_votes` int(10) unsigned DEFAULT NULL,
`imdb_votes_num` bigint(20) unsigned DEFAULT NULL,
`release_date` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`year` smallint(5) unsigned DEFAULT NULL,
`plot` text COLLATE utf8_unicode_ci,
`genre` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`tagline` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`poster` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`background` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`awards` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`runtime` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`trailer` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`budget` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`revenue` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`views` bigint(20) NOT NULL DEFAULT '1',
`tmdb_popularity` float(50,2) unsigned DEFAULT NULL,
`imdb_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`tmdb_id` bigint(20) unsigned DEFAULT NULL,
`season_number` tinyint(3) unsigned DEFAULT NULL,
`fully_scraped` tinyint(3) unsigned NOT NULL DEFAULT '0',
`allow_update` tinyint(3) unsigned NOT NULL DEFAULT '1',
`featured` tinyint(3) unsigned NOT NULL DEFAULT '0',
`now_playing` tinyint(3) unsigned NOT NULL DEFAULT '0',
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NULL DEFAULT NULL,
`temp_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`language` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`country` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`original_title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`affiliate_link` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`custom_field` text COLLATE utf8_unicode_ci,
PRIMARY KEY (`id`),
UNIQUE KEY `titles_imdb_id_unique` (`imdb_id`),
UNIQUE KEY `titles_tmdb_id_type_unique` (`tmdb_id`,`type`),
KEY `titles_mc_num_of_votes_index` (`mc_num_of_votes`),
KEY `titles_created_at_index` (`created_at`),
KEY `titles_release_date_index` (`release_date`),
KEY `titles_title_index` (`title`),
KEY `titles_mc_user_score_index` (`mc_user_score`),
KEY `titles_tmdb_popularity_index` (`tmdb_popularity`),
KEY `titles_temp_id_index` (`temp_id`),
KEY `titles_tmdb_rating_index` (`tmdb_rating`)
) ENGINE=InnoDB AUTO_INCREMENT=18712721 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
EXPLAIN select * from titles where titles.type = 'movie'
order by tmdb_rating desc limit 100 offset 86500;
+------+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
| 1 | SIMPLE | titles | ALL | NULL | NULL | NULL | NULL | 311859 | Using where; Using filesort |
+------+-------------+--------+------+---------------+------+---------+------+--------+-----------------------------+
1 row in set (0.01 sec)
my.cnf Settings:
default-storage-engine = InnoDB
symbolic-links=0
skip-external-locking
max_allowed_packet = 16M
table_open_cache = 5000
query_cache_size = 0
query_cache_type = 0
thread_cache_size = 4
tmp_table_size = 256M
max_heap_table_size = 256M
performance_schema = ON
key_buffer_size = 140k
sort_buffer_size = 256K
read_buffer_size = 128K
read_rnd_buffer_size = 256k
myisam_sort_buffer_size = 140k
join_buffer_size = 2M
innodb_file_per_table
innodb_buffer_pool_size = 512M
innodb_log_file_size = 200M
innodb_buffer_pool_instances = 1
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 4
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 32M
innodb_io_capacity = 1000
innodb_io_capacity_max = 3000
sync_binlog = 1
max_connections=100
max_user_connections=100
wait_timeout=10
interactive_timeout=30
long_query_time=5
slow-query-log =1
slow-query-log-file = /var/log/mysql/mysql-slow.log
Memory Usage:
Private + Shared = RAM used Program
224.0 KiB + 26.5 KiB = 250.5 KiB agetty (2)
248.0 KiB + 41.0 KiB = 289.0 KiB sftp-server
180.0 KiB + 124.0 KiB = 304.0 KiB anvil
404.0 KiB + 31.5 KiB = 435.5 KiB vsftpd
392.0 KiB + 104.5 KiB = 496.5 KiB dovecot
564.0 KiB + 39.5 KiB = 603.5 KiB memcached
484.0 KiB + 122.0 KiB = 606.0 KiB log
828.0 KiB + 44.0 KiB = 872.0 KiB systemd-udevd
968.0 KiB + 42.0 KiB = 1.0 MiB ntpd
732.0 KiB + 307.0 KiB = 1.0 MiB systemd-logind
684.0 KiB + 355.0 KiB = 1.0 MiB sh (8)
340.0 KiB + 754.0 KiB = 1.1 MiB vesta-nginx (2)
1.0 MiB + 58.5 KiB = 1.1 MiB dbus-daemon
880.0 KiB + 250.5 KiB = 1.1 MiB auth
760.0 KiB + 496.0 KiB = 1.2 MiB flock (6)
1.2 MiB + 244.0 KiB = 1.4 MiB config
1.7 MiB + 205.5 KiB = 1.9 MiB systemd-journald
1.9 MiB + 47.0 KiB = 1.9 MiB rsyslogd
2.1 MiB + 103.0 KiB = 2.2 MiB exim4
2.5 MiB + 76.0 KiB = 2.6 MiB bash
2.5 MiB + 1.3 MiB = 3.8 MiB cron (9)
2.2 MiB + 2.1 MiB = 4.3 MiB sshd (4)
2.2 MiB + 2.1 MiB = 4.4 MiB nginx (3)
500.0 KiB + 4.8 MiB = 5.2 MiB vesta-php (3)
4.3 MiB + 3.7 MiB = 7.9 MiB systemd (5)
15.2 MiB + 109.5 KiB = 15.3 MiB fail2ban-server
67.3 MiB + 15.4 MiB = 82.6 MiB php7.0 (8)
100.9 MiB + 67.2 MiB = 168.1 MiB apache2 (12)
758.7 MiB + 156.0 KiB = 758.9 MiB mysqld
TOP Command:
top - 14:10:43 up 7 days, 23:12, 1 user, load average: 0.79, 0.91, 0.69
Tasks: 145 total, 4 running, 141 sleeping, 0 stopped, 0 zombie
%Cpu(s): 19.1 us, 6.0 sy, 0.0 ni, 71.9 id, 2.7 wa, 0.0 hi, 0.3 si, 0.0 st
KiB Mem : 2052588 total, 501496 free, 1085916 used, 465176 buff/cache
KiB Swap: 0 total, 0 free, 0 used. 735980 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
23688 mysql 20 0 1211616 770288 0 S 7.3 37.5 543:04.35 mysqld
29889 admin 20 0 539584 53996 38624 R 3.7 2.6 0:00.74 apache2
27140 www-data 20 0 537900 64576 50864 S 3.0 3.1 0:10.99 apache2
29901 admin 20 0 539484 53356 38100 S 2.3 2.6 0:00.71 apache2
29972 admin 20 0 538404 46336 32220 S 2.3 2.3 0:00.24 apache2
29297 admin 20 0 539572 64000 48648 S 2.0 3.1 0:02.00 apache2
30084 www-data 20 0 536388 43876 31700 S 1.3 2.1 0:00.24 apache2
30063 admin 20 0 392344 34440 21948 S 1.0 1.7 0:00.54 php
30042 admin 20 0 311432 30956 21628 S 0.7 1.5 0:00.10 php
30347 root 20 0 44888 3616 3016 R 0.7 0.2 0:00.04 top
7 root 20 0 0 0 0 R 0.3 0.0 8:52.43 rcu_sched
4834 root 20 0 0 0 0 S 0.3 0.0 0:01.46 kworker/u2:2
23741 memcache 20 0 335680 424 0 S 0.3 0.0 2:41.11 memcached
29002 www-data 20 0 537740 70296 56792 S 0.3 3.4 0:03.03 apache2
29279 www-data 20 0 537504 59164 45840 S 0.3 2.9 0:01.75 apache2
29709 root 20 0 0 0 0 S 0.3 0.0 0:00.07 kworker/0:0
1 root 20 0 204556 4732 3076 S 0.0 0.2 0:21.00 systemd
2 root 20 0 0 0 0 S 0.0 0.0 0:00.01 kthreadd
3 root 20 0 0 0 0 R 0.0 0.0 8:12.40 ksoftirqd/0
Another Slow Query Example:
# Query_time: 21.742335 Lock_time: 0.000042 Rows_sent: 0 Rows_examined: 354027
# Rows_affected: 0
SET timestamp=1560293586;
select * from `titles`
where `titles`.`type` = 'movie'
and ( select count(*)
from `actors`
inner join `actors_titles` on `actors`.`id` = `actors_titles`.`actor_id`
where `actors_titles`.`title_id` = `titles`.`id`
and `name` like 'teren'
) >= 1
order by `mc_num_of_votes` asc
limit 100 offset 0;
Upvotes: 1
Views: 1714
Answers (1)
Rick James
Reputation: 142298
Ugh: limit 100 offset 86500
Don't do pagination that way. Instead "remember where you left off". This has the drawback of no providing "jump to page 864", but who does that. And who does Next-next-next... 865 times?
Further discussion about this common problem: http://mysql.rjweb.org/doc.php/pagination
And it discusses the "left off" solution in more detail.
Another potential problem: order by tmdb_rating desc -- Perhaps multiple titles have the same "rating"? If so, in what order do you want the titles listed? The simple answer is to specify some unambiguous (but somewhat arbitrary) order: order by tmdb_rating desc, id desc.
Remembering where you left off in a compound order-by is more complex, but possible.
Other notes:
WHERE type=... ORDER BY ratingcan benefit from the "composite"INDEX(type, rating), in this order.3 unique keys sounds wrong.
2 TEXT columns being fetched hurts the performance. Don't do
SELECT *unless you really need all the columns.tmp_table_size = 256Mandmax_heap_table_size = 256Mare dangerously high for a tiny 2GB of RAM. Shrink them to only 1% of RAM.Instead of
( SELECT COUNT(*) FROM...) >=1, doEXISTS ( SELECT 1 FROM ...)
Upvotes: 2
Related Questions
- Query takes to much time to execute laravel
- Laravel Slow queries
- Laravel slow simple query with Eloquent
- How to reduce loading time of sql query?
- Eloquent query so slow in Laravel
- Improve SQL queries speed
- laravel slow query issue
- Laravel Database MySQL Queries take very long
- How to speed up Query?
- mysql really simple queries took long time