Banlist slow loading query on recent MYSQL versions
This seems to have started with an updated MYSQL server version, and did not happen in the past (of a year or so ago)
On small databases, it doesn't matter much. After a few thousand bans, it gets very bad. the query (page.banlist.php): PHP Code:
PHP Code:
PHP Code:
fix: PHP Code:
Note that I replaced the "SELECT" keyword bercause cloudflare is retarded _SELECT |
Re: Banlist slow loading query on recent MYSQL versions
Which mysql version are you using?
I tested it on my server and it's super fast 0.001 sec for ~3400 rows (bans). I'm using MariaDB 10.1.14 (Table type InnoDB) Your "optimized" query is actually slightly slower (0.0058 ) on my server because there are now 2 queries Maybe you have a config issue? As far as I could see you have 9263 bans, which is nothing for a where query. |
Re: Banlist slow loading query on recent MYSQL versions
there are 2 counts and several joins.
the sql servers are hosted by nfoservers and its likely they fouled something up. IIRC it is innodb on mysql The query is quite slow, and there are no indexes by default for the ip and type.. worse yet ip and authid are varchars, and the index for authid is fulltext. Adding indexes helped but not too much. realize that for each ban row, it has to query the bans to get the count, so time goes up exponentially as size increases. Ip and auth should be ints, and there shoukd he a cached value for ban total.... |
Re: Banlist slow loading query on recent MYSQL versions
A cache would be indeed great.
So tested the whole query now and even added 45k more rows to sb_bans and its still very fast 0.1 sec. my query (with a test bans table sb_bans2): Code:
SELECT bid ban_id, BA.type, BA.ip ban_ip, BA.authid, BA.name player_name, created ban_created, ends ban_ends, length ban_length, reason ban_reason, BA.ureason unban_reason, BA.aid, AD.gid AS gid, adminIp, BA.sid ban_server, country ban_country, RemovedOn, RemovedBy, RemoveType row_type, If you run the query with explain, then the slowest queries should be the ones with BH and BA tables. joins have an index: Code:
+------+--------------------+-------+--------+---------------+---------+---------+-------------------------+-------+--------------------------+ Maybe a DB restart can help ^^ You can also check the "advisor-tab" for some performance hints in phpmyadmin. I have the feeling that a var is just too small in your db. |
Re: Banlist slow loading query on recent MYSQL versions
Hosting is thirdparty, unfortunately I do not have access.
Lots of people here use nfoservers for hosting, so it may affect them. |
Re: Banlist slow loading query on recent MYSQL versions
Quote:
|
Re: Banlist slow loading query on recent MYSQL versions
The machine is shared hosting, and they probably have some odd setup. Ssh to it and checking top shows almost no usage though.
|
Re: Banlist slow loading query on recent MYSQL versions
This happend to me today while upgrading to MySQL 5.6 (from 5.5).
But your fix doesn't work for me, there is a empty page, people can spam requests with this causing mysql to freeze. Please help. HTML Code:
Linux: Debian 8 HTML Code:
SELECT @@version; HTML Code:
php -v |
Re: Banlist slow loading query on recent MYSQL versions
Can you run this query:
Code:
explain SELECT bid ban_id, BA.type, BA.ip ban_ip, BA.authid, BA.name player_name, created ban_created, ends ban_ends, length ban_length, reason ban_reason, BA.ureason unban_reason, BA.aid, AD.gid AS gid, adminIp, BA.sid ban_server, country ban_country, RemovedOn, RemovedBy, RemoveType row_type, An then run this: Code:
set profiling = ON; Code:
show profile for query X; Here is a YT video for query profiling https://www.youtube.com/watch?v=0VB3rmjKjk0 You can also try phpmyadmin. It has a "profiling" checkbox. But this checkbox is only visible after you can a query! If you get a php timeout (Bad gateway - query takes too long) try to limit the query to 1 -> ORDER BY created DESC limit 1; See profiling in action: http://imgur.com/nMhY5QN Post your profiling results + the explain query result here. |
Re: Banlist slow loading query on recent MYSQL versions
Thank you for trying to help me.
Explain query: http://i.imgur.com/ytVETCM.png http://i.imgur.com/3iElUSN.png And the other query (6,5 min): http://i.imgur.com/dUZlow0.png http://i.imgur.com/GmU4IE5.png Here is the one with the "fix" from OP (11 min): http://i.imgur.com/Bmqf3c2.png Is there any other way we could get it faster? I've never had problems before upgrading to Debian 8 with MySQL version 5.4/5.5 |
All times are GMT -4. The time now is 03:43. |
Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.