AlliedModders - [Bug Report] Banlist slow loading query on recent MYSQL versions

AlliedModders (https://forums.alliedmods.net/index.php)

- SourceBans / SourceBans++ (https://forums.alliedmods.net/forumdisplay.php?f=152)

- - Bug Report Banlist slow loading query on recent MYSQL versions (https://forums.alliedmods.net/showthread.php?t=289717)

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:


		
			
(_SELECT count(*) FROM ".DB_PREFIX."_bans as BH WHERE (BH.type = BA.type AND BH.type = 0 AND BH.authid = BA.authid AND BH.authid != '' AND BH.authid IS NOT NULL) OR (BH.type = BA.type AND BH.type = 1 AND BH.ip = BA.ip AND BH.ip != '' AND BH.ip IS NOT NULL)) as history_count

takes about 60 seconds on my server. It appears that the problem is the OR

PHP Code:


		
			
(_SELECT  count(*) FROM ".DB_PREFIX."_bans as BH WHERE (BH.type = BA.type AND BH.type = 0 AND BH.authid = BA.authid AND BH.authid != '' AND BH.authid IS NOT NULL)) as history_count

= 0.6s query

PHP Code:


		
			
(_SELECT  count(*) FROM ".DB_PREFIX."_bans as BH WHERE ((BH.type = BA.type AND BH.type = 1 AND BH.ip = BA.ip AND BH.ip != '' AND BH.ip IS NOT NULL)) as history_count

= 1.5s query

fix:

PHP Code:


		
			
((_SELECT count(*) FROM ".DB_PREFIX."_bans as BH WHERE (BH.type = 0  AND BA.type = 0 AND BH.authid = BA.authid AND BH.authid != '' AND BH.authid IS NOT NULL)) + (_SELECT  count(*) FROM ".DB_PREFIX."_bans as BH WHERE (BH.type = 1 AND BA.type = 1 AND BH.ip = BA.ip AND BH.ip != '' AND BH.ip IS NOT NULL))) as history_count

Doing a two subqueries and adding them = 1.8s

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,

                        SE.ip server_ip, AD.user admin_name, AD.gid, MO.icon as mod_icon,

                        CAST(MID(BA.authid, 9, 1) AS UNSIGNED) + CAST('76561197960265728' AS UNSIGNED) + CAST(MID(BA.authid, 11, 10) * 2 AS UNSIGNED) AS community_id,

                        (SELECT count(*) FROM sb_demos as DM WHERE DM.demtype='B' and DM.demid = BA.bid) as demo_count,

                        (SELECT count(*) FROM sb_bans2 as BH WHERE (BH.type = BA.type AND BH.type = 0 AND BH.authid = BA.authid AND BH.authid != '' AND BH.authid IS NOT NULL) OR (BH.type = BA.type AND BH.type = 1 AND BH.ip = BA.ip AND BH.ip != '' AND BH.ip IS NOT NULL)) as history_count

           FROM sb_bans2 AS BA

  LEFT JOIN sb_servers AS SE ON SE.sid = BA.sid

  LEFT JOIN sb_mods AS MO on SE.modid = MO.mid

  LEFT JOIN sb_admins AS AD ON BA.aid = AD.aid

  

   ORDER BY created DESC limit 50

50 rows in set (0.01 sec)

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:

+------+--------------------+-------+--------+---------------+---------+---------+-------------------------+-------+--------------------------+

| id   | select_type        | table | type   | possible_keys | key     | key_len | ref                     | rows  | Extra                    |

+------+--------------------+-------+--------+---------------+---------+---------+-------------------------+-------+--------------------------+

|    1 | PRIMARY            | BA    | ALL    | NULL          | NULL    | NULL    | NULL                    | 46136 | Using filesort           |

|    1 | PRIMARY            | SE    | eq_ref | PRIMARY       | PRIMARY | 4       | sourcebans.BA.sid       |     1 |                          |

|    1 | PRIMARY            | MO    | eq_ref | PRIMARY       | PRIMARY | 4       | sourcebans.SE.modid     |     1 | Using where              |

|    1 | PRIMARY            | AD    | eq_ref | PRIMARY       | PRIMARY | 4       | sourcebans.BA.aid       |     1 |                          |

|    3 | DEPENDENT SUBQUERY | BH    | ALL    | authid_2      | NULL    | NULL    | NULL                    | 46136 | Using where              |

|    2 | DEPENDENT SUBQUERY | DM    | eq_ref | PRIMARY       | PRIMARY | 9       | sourcebans.BA.bid,const |     1 | Using where; Using index |

+------+--------------------+-------+--------+---------------+---------+---------+-------------------------+-------+--------------------------+

And a count(*) is very fast, because mysql just reads the table value or the row count value. But also: count(myfield) is much much slower.

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:

Originally Posted by friagram (Post 2466373)

Hosting is thirdparty, unfortunately I do not have access.
Lots of people here use nfoservers for hosting, so it may affect them.

Wait, are you using their lite/free webhosting? If so, it's miserably slow, that's probably why. I use them as well and found it worthwhile to rent a single core linux vps to host my databases.

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;

5.6.30-1~bpo8+1

HTML Code:

php -v

PHP 5.6.27-0+deb8u1 (cli) (built: Oct 15 2016 15:53:28)

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,

                        SE.ip server_ip, AD.user admin_name, AD.gid, MO.icon as mod_icon,

                        CAST(MID(BA.authid, 9, 1) AS UNSIGNED) + CAST('76561197960265728' AS UNSIGNED) + CAST(MID(BA.authid, 11, 10) * 2 AS UNSIGNED) AS community_id,

                        (SELECT count(*) FROM sb_demos as DM WHERE DM.demtype='B' and DM.demid = BA.bid) as demo_count,

                        (SELECT count(*) FROM sb_bans as BH WHERE (BH.type = BA.type AND BH.type = 0 AND BH.authid = BA.authid AND BH.authid != '' AND BH.authid IS NOT NULL) OR (BH.type = BA.type AND BH.type = 1 AND BH.ip = BA.ip AND BH.ip != '' AND BH.ip IS NOT NULL)) as history_count

           FROM sb_bans AS BA

  LEFT JOIN sb_servers AS SE ON SE.sid = BA.sid

  LEFT JOIN sb_mods AS MO on SE.modid = MO.mid

  LEFT JOIN sb_admins AS AD ON BA.aid = AD.aid

   ORDER BY created DESC limit 50

An then run this:

Code:

set profiling = ON;



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,

                        SE.ip server_ip, AD.user admin_name, AD.gid, MO.icon as mod_icon,

                        CAST(MID(BA.authid, 9, 1) AS UNSIGNED) + CAST('76561197960265728' AS UNSIGNED) + CAST(MID(BA.authid, 11, 10) * 2 AS UNSIGNED) AS community_id,

                        (SELECT count(*) FROM sb_demos as DM WHERE DM.demtype='B' and DM.demid = BA.bid) as demo_count,

                        (SELECT count(*) FROM sb_bans as BH WHERE (BH.type = BA.type AND BH.type = 0 AND BH.authid = BA.authid AND BH.authid != '' AND BH.authid IS NOT NULL) OR (BH.type = BA.type AND BH.type = 1 AND BH.ip = BA.ip AND BH.ip != '' AND BH.ip IS NOT NULL)) as history_count

           FROM sb_bans AS BA

  LEFT JOIN sb_servers AS SE ON SE.sid = BA.sid

  LEFT JOIN sb_mods AS MO on SE.modid = MO.mid

  LEFT JOIN sb_admins AS AD ON BA.aid = AD.aid

   ORDER BY created DESC limit 5;



show PROFILES;

Code:

show profile for query X;

X = query ID from "show PROFILES"

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