[04:35:13] 10DBA, 10Core Platform Team, 10MediaWiki-Database: Slow query ApiQueryRevisions on enwiki - https://phabricator.wikimedia.org/T224017 (10Marostegui) [04:52:15] 10DBA, 10Core Platform Team, 10MediaWiki-Database: Slow query ApiQueryRevisions on enwiki - https://phabricator.wikimedia.org/T224017 (10Marostegui) @Anomie bad news again, db1080 didn't change its query plan after rebuilding the table - I am now running an analyze there again just to have the same combinati... [05:05:30] 10DBA, 10Goal, 10Patch-For-Review: Productionize db11[26-38] - https://phabricator.wikimedia.org/T222682 (10Marostegui) [05:07:53] 10DBA, 10Patch-For-Review: Decommission db2040 - https://phabricator.wikimedia.org/T224079 (10Marostegui) [05:08:35] 10DBA: Decommission old coredb machines (<=db2042) - https://phabricator.wikimedia.org/T221533 (10Marostegui) [05:45:26] 10DBA: Compress and defragment tables on labsdb hosts - https://phabricator.wikimedia.org/T222978 (10Marostegui) [06:09:56] 10DBA, 10Patch-For-Review: Decommission old coredb machines (<=db2042) - https://phabricator.wikimedia.org/T221533 (10Marostegui) a:03Marostegui [07:48:04] 10DBA, 10Core Platform Team, 10MediaWiki-Database: Slow query ApiQueryRevisions on enwiki - https://phabricator.wikimedia.org/T224017 (10Marostegui) a:05Marostegui→03Anomie @Anomie db1080 didn't change its query plan after: rebuilding the table, analyzing the table, stopping and starting mysql (which is... [07:48:44] 10DBA, 10Core Platform Team, 10MediaWiki-Database: Slow query ApiQueryRevisions on enwiki - https://phabricator.wikimedia.org/T224017 (10Marostegui) [07:55:59] 10DBA, 10Core Platform Team, 10MediaWiki-Database, 10Patch-For-Review: Slow query ApiQueryRevisions on enwiki - https://phabricator.wikimedia.org/T224017 (10Marostegui) With the `IGNORE INDEX` it works instantly: ` root@db1080.eqiad.wmnet[enwiki]> pager cat > /dev/null PAGER set to 'cat > /dev/null' root@d... [08:42:20] 10DBA, 10MediaWiki-Categories: Orphaned entries in categorylinks - https://phabricator.wikimedia.org/T224209 (10Wargo) [10:30:14] 10DBA, 10Core Platform Team, 10MediaWiki-Database, 10Patch-For-Review: Slow query ApiQueryRevisions on enwiki - https://phabricator.wikimedia.org/T224017 (10Volans) I got slightly nerd-sniped into this and had a look. First I found that the optimizer choose a full table scan even without joins, as soon as... [10:37:15] 10DBA, 10Core Platform Team, 10MediaWiki-Database, 10Patch-For-Review: Slow query ApiQueryRevisions on enwiki - https://phabricator.wikimedia.org/T224017 (10Marostegui) >>! In T224017#5207608, @Volans wrote: > > So I start thinking that this might be related to the return set. I tried other IDs that have... [10:41:21] 10DBA, 10Core Platform Team, 10MediaWiki-Database: Slow query ApiQueryRevisions on enwiki - https://phabricator.wikimedia.org/T224017 (10Maintenance_bot) [10:44:25] 10DBA, 10Goal: Productionize db11[26-38] - https://phabricator.wikimedia.org/T222682 (10Maintenance_bot) [10:44:48] 10DBA: Decommission old coredb machines (<=db2042) - https://phabricator.wikimedia.org/T221533 (10Maintenance_bot) [10:45:46] 10DBA, 10Goal: Address Database infrastructure blockers on datacenter switchover & multi-dc deployment - https://phabricator.wikimedia.org/T220170 (10Maintenance_bot) [11:15:23] 10DBA, 10Core Platform Team, 10MediaWiki-Database: Slow query ApiQueryRevisions on enwiki - https://phabricator.wikimedia.org/T224017 (10Marostegui) Some more details: `'rev_page'= '35159700'` is https://en.wikipedia.org/wiki/User:Cyberpower678/RfX_Report and that page has around 80k revisions ` root@db2103... [12:45:00] 10DBA, 10Core Platform Team, 10MediaWiki-Database: Slow query ApiQueryRevisions on enwiki - https://phabricator.wikimedia.org/T224017 (10Marostegui) To add more randomness: db2062 (10.1.38) finished its table rebuilt and now it uses `page_timestamp` instead of `rev_timestmap` as it used to, so the query goes... [13:17:41] 10DBA, 10Core Platform Team, 10MediaWiki-Database: Slow query ApiQueryRevisions on enwiki - https://phabricator.wikimedia.org/T224017 (10Marostegui) db2055 finished its rebuilt and also got its query plan changed to `page_timestamp` (10.1.38) db2048 finished too and its query plan remains on `rev_timestamp`... [14:13:52] 10DBA, 10Operations: Decommission db1061-db1073 - https://phabricator.wikimedia.org/T217396 (10Marostegui) [15:28:28] 10DBA, 10Core Platform Team, 10MediaWiki-Database: Slow query ApiQueryRevisions on enwiki - https://phabricator.wikimedia.org/T224017 (10Marostegui) db2071 finished its rebuilt and its query plan changed to `page_timestamp` (10.1.38) db2092 finished its rebuilt and its query plan changed to `page_timestamp`... [16:21:12] 10DBA, 10Reading-Infrastructure-Team-Backlog, 10WikimediaEditorTasks: Choose DB/Cluster for WikimediaEditorTasks tables - https://phabricator.wikimedia.org/T218302 (10Mholloway) 05Open→03Resolved [17:01:12] 10DBA, 10Core Platform Team, 10MediaWiki-Database, 10Core Platform Team Kanban (Waiting for Review), 10Patch-For-Review: Slow query ApiQueryRevisions on enwiki - https://phabricator.wikimedia.org/T224017 (10Anomie) [17:02:57] 10DBA, 10MediaWiki-Database, 10Core Platform Team (Security, stability, performance and scalability (TEC1)), 10Core Platform Team Kanban (Waiting for Review), 10Patch-For-Review: Slow query ApiQueryRevisions on enwiki - https://phabricator.wikimedia.org/T224017 (10Anomie) [20:32:22] 10DBA, 10Operations, 10ops-codfw, 10Patch-For-Review: rack/setup/install dbproxy200[1-4] - https://phabricator.wikimedia.org/T223492 (10Papaul) [20:54:03] 10DBA, 10Operations, 10ops-codfw, 10Patch-For-Review: rack/setup/install dbproxy200[1-4] - https://phabricator.wikimedia.org/T223492 (10Papaul) oops, wrong ticket for the Gerrit comment