[00:14:28] Can someone please remind where the tool is that explains SQL queries? Seeking to optimize some of mine. [00:14:43] musikanimal: ^ didn't you write it? [00:14:46] Cyberpower678: https://tools.wmflabs.org/sql-optimizer [00:15:00] Thanks. :-) [00:18:21] Can someone explain to my why this query has a massive explain using tables, not even in the query? [00:18:22] https://tools.wmflabs.org/sql-optimizer?use=enwiki_p&sql=Select+user_id%2C+user_name%2C+user_registration%2C+actor_id%0D%0Afrom+user%0D%0A+++++++++join+actor+on+actor_user+%3D+user_id%0D%0Awhere+user_id+%3E+0%0D%0A++AND+user_registration+%3C+20191001000000%0D%0A++AND+user_editcount+%3E%3D+150%0D%0A++AND+user_id+not+in+%28select+ipb_user%0D%0A++++++++++++++++++++++from+ipblocks%0D%0A++++++++++++++++++++++where+ipb_us [00:18:22] er+%3E+0%0D%0A++++++++++++++++++++++++AND+%28ipb_expiry+%3E+%2720191202235959%27+OR+ipb_expiry+%3D+%27infinity%27%29%29%0D%0A++AND+user_id+not+in+%28select+ug_user+from+user_groups+where+ug_group+%3D+%27bot%27%29%0D%0Aorder+by+user_id+asc%0D%0Alimit+500%3B [00:18:35] Damn that's a long url. [00:19:09] sqldb: ^ [00:21:50] The query is literally only using the user, actor, and ipblocks tables. I'm not sure why it's digging around revision, revision_actor_temp, logging, recentchanges, filearchive, oldimage, image, and archive tables [00:22:42] Cyberpower678: tbh I'm not sure. I'm about to head out the door to try a right-before-close SSD exchange [00:22:57] :-( [00:23:23] arturo: maybe you can help explain. I get the impression you know a thing or two about the replication schemas. [00:29:38] Cyberpower678: sorry, seems I can't go out atm after all. So, for what you're using that for - tbh, you don't need to worry about ipblocks, nor bot usergroups. [00:30:00] Why? [00:30:09] so that could seriously simplify your query [00:30:11] one moment [00:30:12] So I should mass message all of the blocked users and bots? [00:30:23] oh, crap LOL [00:30:24] welp [00:30:30] I forgot about that bit :P [00:30:38] I thought you were using it to seed securepoll [00:30:39] :-) [00:30:51] Both. It does it simultaneously. [00:31:17] I'd like to know what all those extra tables are being used for. [00:31:30] I'm not sure on that unfortunately [00:31:36] have you considered chunking your query? [00:31:44] Because I'm somewhat certain that's a big part of the performance hit and it has to do with the actor table. [00:32:46] To have my script go from 2 hours to run and finish to 22 hours is seriously stupid, and all I changed was the reference to the actor table from last year. [00:33:12] sqldb: that query was otherwise exactly the same. [00:33:26] Cyberpower678: what's the purpose of joining actor in that fashion? [00:33:43] I need the actor_id to query the revision table. [00:34:04] Ah, you use it on a later query? [00:34:08] And for some reason doing it individually instead of joining slows it down even further. So I joined it. [00:34:24] Yea. You can't do a revision count of a user without the actor_id [00:34:42] since rev_user has been removed and all. [00:35:09] Cyberpower678: IIRC you can use _compat [00:35:13] and use last years queries? [00:35:25] _compat? [00:35:31] e.g. revision_compat, logging_compat, etc [00:36:49] Ah look at that. When I remove the join to actor, the explain simplifies significantly. What is up with the actor table? [00:39:09] sqldb: I'd rather remain compatible with the latest deployment, and not use a backwards hack. [01:45:27] !log tools.pbbot Register plwiktMissingPolishAudio dump job [01:45:30] Logged the message at https://wikitech.wikimedia.org/wiki/Nova_Resource:Tools.pbbot/SAL [01:50:50] Cyberpower678: Try using the actor_revision view instead of actor. [01:52:34] JJMC89[m]: That does indeed help [01:52:57] The actor view has to join a bunch of tables to check if the actor has been revdel/OS [01:53:01] But what is with the actor table to do that. Why is it such an expensive table to query on. [01:53:25] It's not a table? [01:54:27] That is in each table, e.g. revision.rev_deleted [01:55:56] The extra actor and comment views are listed at https://wikitech.wikimedia.org/wiki/News/Actor_storage_changes_on_the_Wiki_Replicas in case you need them. [15:44:02] https://tools.wmflabs.org/ is timing out for me, anyone else? [15:48:28] Loading ok for me ST47 [15:52:47] Well, I started debugging it and it's fine now. I was just getting timeouts on HTTPS, but loading properly now. [15:59:58] What I'm seeing is a tool suddenly failing [15:59:59] Fatal error: Uncaught Error: Call to a member function fetchAllAssoc() on boolean in /data/project/meta/git/tool-labs/stalktoy/framework/StalktoyEngine.php:270 Stack trace: #0 /data/project/meta/git/tool-labs/stalktoy/index.php(71): StalktoyEngine->getGlobalIP('148.251.0.0/16') #1 {main} thrown in /data/project/meta/git/tool-labs/stalktoy/framework/StalktoyEngine.php on line 270 [16:00:39] Exception: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'centralauth_p.globalblocks' doesn't exist [16:00:47] and that doesn't feel right [16:01:09] arturo: any changes in the replica views? [16:02:30] login via login.tools is also slower than usual [16:41:20] andrewbogott: the maintain-views run of yesterday has caused T238480 [16:41:23] T238480: Table 'centralauth_p.globalblocks' doesn't exist - https://phabricator.wikimedia.org/T238480 [16:41:41] Is there a way to re-introduce the globalblocks table on centralauth_p? [16:41:49] thanks in advance :)