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
theChaosCoder
Junior Member
Join Date: Oct 2015
Old 11-06-2016 , 06:49   Re: Banlist slow loading query on recent MYSQL versions
Reply With Quote #11

ok, could you now also post the output of "Data dictionary" (Click on your Sourcebans DB in phpmyadmin. Look at the bottom near Print).
Maybe save it (print) as pdf :-)

Last edited by theChaosCoder; 11-06-2016 at 07:45.
theChaosCoder is offline
TheWho
AlliedModders Donor
Join Date: Jul 2012
Old 11-06-2016 , 11:00   Re: Banlist slow loading query on recent MYSQL versions
Reply With Quote #12

Should look the same as yours - I just have over 13k bans.. but can't we rewrite the ban page to just fetch every row and print?
This query is confusing me to hell, who would do that with so many joins and subqueries..

TheWho is offline
theChaosCoder
Junior Member
Join Date: Oct 2015
Old 11-06-2016 , 12:35   Re: Banlist slow loading query on recent MYSQL versions
Reply With Quote #13

And what about sb_servers? Can you show that too. just the index.

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

Sure, only 4 servers.
TheWho is offline
theChaosCoder
Junior Member
Join Date: Oct 2015
Old 11-06-2016 , 12:55   Re: Banlist slow loading query on recent MYSQL versions
Reply With Quote #15

Same as mine...

The strange thing is: Your query doesn't use the index for SE. And I think because of that a temp table is also created and that's the reason it is so slow...

Code:
+------+--------------------+-------+--------+---------------+---------+---------+-------------------------+-------+--------------------------+
| id   | select_type        | table | type   | possible_keys | key     | key_len | ref                     | rows  | Extra                    |
+------+--------------------+-------+--------+---------------+---------+---------+-------------------------+-------+--------------------------+
|    1 | PRIMARY            | SE    | eq_ref | PRIMARY       | PRIMARY | 4       | sourcebans.BA.sid       |     1 | <- mine                  | 
|    1 | PRIMARY            | SE    | ALL    | PRIMARY       | NULL    | NULL    | NULL                    |     4 | <- yours                 |

EDIT:

Here is my "Data dictionary" (ignore bans2)

Maybe you can spot something...

The main difference I see is that the Cardinality for reason & authid_2 in sb_bans is = the Card. of PRIMARY. Whereas in your screenshot it's much smaller.
Attached Files
File Type: pdf Print view - phpMyAdmin 4.6.3.pdf (270.7 KB, 72 views)

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

https://dev.mysql.com/doc/refman/5.7...ng-tables.html

Quote:
If you are rebuilding tables because a different version of MySQL will not handle them after a binary (in-place) upgrade or downgrade, you must use the dump-and-reload method. Dump the tables before upgrading or downgrading using your original version of MySQL. Then reload the tables after upgrading or downgrading.
Quote:
In many cases, the tables from the previous version of MySQL can be used without problem by the new version. However, sometimes changes occur that require tables or table indexes to be rebuilt, as described in this section.
Quote:
In MySQL 5.6.3, the length limit for index prefix keys is increased from 767 bytes to 3072 bytes, for InnoDB tables using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED.

Basically our indexes are not usable anymore and rebuilding doesn't work, even deleting and adding the indexes shouldn't work (unsure).

What I will do now is getting a new clean sourcebans db and put in my bans, then I will try the query again.

Edit:
I fixed my indexes, now everything is working as it should.

Some statistics with 13246 ban rows.

Unchanged default query:
Query took 0.2866 sec

OP improved query which I slightly adjusted:
Query took 0.0023 sec

This is a improvement of 124,6%.

Replace the faulty part in searchText, advSearch and hideinactive.

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

Last edited by TheWho; 11-06-2016 at 17:13.
TheWho is offline
theChaosCoder
Junior Member
Join Date: Oct 2015
Old 11-06-2016 , 17:32   Re: Banlist slow loading query on recent MYSQL versions
Reply With Quote #17

Nice, glad you fixed it!
So you basically exported your tables, then truncated all (or deleted the whole db) and imported it back again?

In pma one can copy a DB to another DB and then just rename it. (It's basically a select insert into)

@friagram So maybe you have the same issue!?

Last edited by theChaosCoder; 11-06-2016 at 17:38.
theChaosCoder is offline
TheWho
AlliedModders Donor
Join Date: Jul 2012
Old 11-06-2016 , 17:36   Re: Banlist slow loading query on recent MYSQL versions
Reply With Quote #18

I exported sb_bans, DROP sb_bans, changed following and imported the table again:
Code:
PRIMARY KEY (`bid`),
  KEY `sid` (`sid`),
  KEY `authid` (`authid`),
  KEY `aid` (`aid`),
  KEY `type` (`type`),
  KEY `reason` (`reason`(255))

Last edited by TheWho; 11-06-2016 at 17:39.
TheWho is offline
friagram
Veteran Member
Join Date: Sep 2012
Location: Silicon Valley
Old 11-08-2016 , 02:31   Re: Banlist slow loading query on recent MYSQL versions
Reply With Quote #19

Deleting the tables, and re-importing does nothing for me.
Query time with old is still > 60s
__________________
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-09-2016 , 07:07   Re: Banlist slow loading query on recent MYSQL versions
Reply With Quote #20

Please provide us your "Data dictionary" as .pdf, we can see whether the index is build or not.



If you look below "sb_bans" at "indexes":



The cardinality of keyname `PRIMARY` and `authid` has to be equal otherwise your index is not working.

_________

Now one thing which confuses me and or I don't know corret:
How to use keynames of index:
Quote:
JOIN tbl_name ON tbl_name.key = expr
So you have to make sure that in you query the keynames are correct.

But which name is ment, the tablename or the keyname since you can define the name however you want.




theChaosCoder has one key called `authid_2` which makes..
Quote:
JOIN tbl_name ON tbl_name.authid = expr
useless and it will use the non indexed column.

But what if
Code:
KEY `authid` (`authid_2`)
is known as "#define authid authid_2" and the query will know what to use, maybe even both are working, then my explanation above is bs..
I can't find any documention about this..

Does this even work:
Quote:
JOIN tbl_name ON tbl_name.authid_2 = expr
It's just my mind .. I interrogate so much.
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 20:42.


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