Raised This Month: $12 Target: $400
 3% 

Bug Report Banlist slow loading query on recent MYSQL versions


Post New Thread Reply   
 
Thread Tools Display Modes
Author Message
friagram
Veteran Member
Join Date: Sep 2012
Location: Silicon Valley
Old 10-28-2016 , 00:43   Banlist slow loading query on recent MYSQL versions
Reply With Quote #1

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 AND BH.authid BA.authid AND BH.authid != '' AND BH.authid IS NOT NULL) OR (BH.type BA.type AND BH.type 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 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 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 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 AND BA.type 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
__________________
Profile - Plugins
Add me on steam if you are seeking sp/map/model commissions.

Last edited by friagram; 10-28-2016 at 01:02.
friagram is offline
theChaosCoder
Junior Member
Join Date: Oct 2015
Old 10-28-2016 , 17:41   Re: Banlist slow loading query on recent MYSQL versions
Reply With Quote #2

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.

Last edited by theChaosCoder; 10-28-2016 at 17:52.
theChaosCoder is offline
friagram
Veteran Member
Join Date: Sep 2012
Location: Silicon Valley
Old 10-28-2016 , 23:41   Re: Banlist slow loading query on recent MYSQL versions
Reply With Quote #3

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....
__________________
Profile - Plugins
Add me on steam if you are seeking sp/map/model commissions.
friagram is offline
theChaosCoder
Junior Member
Join Date: Oct 2015
Old 10-29-2016 , 06:22   Re: Banlist slow loading query on recent MYSQL versions
Reply With Quote #4

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.

Last edited by theChaosCoder; 10-29-2016 at 06:28.
theChaosCoder is offline
friagram
Veteran Member
Join Date: Sep 2012
Location: Silicon Valley
Old 10-30-2016 , 13:35   Re: Banlist slow loading query on recent MYSQL versions
Reply With Quote #5

Hosting is thirdparty, unfortunately I do not have access.
Lots of people here use nfoservers for hosting, so it may affect them.
__________________
Profile - Plugins
Add me on steam if you are seeking sp/map/model commissions.
friagram is offline
sneaK
SourceMod Moderator
Join Date: Feb 2015
Location: USA
Old 10-30-2016 , 17:15   Re: Banlist slow loading query on recent MYSQL versions
Reply With Quote #6

Quote:
Originally Posted by friagram View Post
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.
__________________
sneaK is offline
friagram
Veteran Member
Join Date: Sep 2012
Location: Silicon Valley
Old 11-03-2016 , 01:29   Re: Banlist slow loading query on recent MYSQL versions
Reply With Quote #7

The machine is shared hosting, and they probably have some odd setup. Ssh to it and checking top shows almost no usage though.
__________________
Profile - Plugins
Add me on steam if you are seeking sp/map/model commissions.
friagram is offline
TheWho
AlliedModders Donor
Join Date: Jul 2012
Old 11-05-2016 , 04:31   Re: Banlist slow loading query on recent MYSQL versions
Reply With Quote #8

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)

Last edited by TheWho; 11-05-2016 at 04:59.
TheWho is offline
theChaosCoder
Junior Member
Join Date: Oct 2015
Old 11-05-2016 , 08:14   Re: Banlist slow loading query on recent MYSQL versions
Reply With Quote #9

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.

Last edited by theChaosCoder; 11-05-2016 at 08:22.
theChaosCoder is offline
TheWho
AlliedModders Donor
Join Date: Jul 2012
Old 11-06-2016 , 02:44   Re: Banlist slow loading query on recent MYSQL versions
Reply With Quote #10

Thank you for trying to help me.

Explain query:





And the other query (6,5 min):





Here is the one with the "fix" from OP (11 min):



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
TheWho is offline
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -4. The time now is 06:00.


Powered by vBulletin®
Copyright ©2000 - 2024, vBulletin Solutions, Inc.
Theme made by Freecode