[04:40:59] 10DBA, 10Parsing-Team, 10Patch-For-Review: Provide access to testreduce* databases on scandium + revoke from ruthenium - https://phabricator.wikimedia.org/T214740 (10ssastry) >>! In T214740#4922899, @ssastry wrote: >>>! In T214740#4920221, @jcrespo wrote: >> Grants applied, systemd restarted on ruthenium suc... [06:43:59] 10DBA, 10Analytics, 10Analytics-Kanban, 10Patch-For-Review, 10User-Banyek: Migrate dbstore1002 to a multi instance setup on dbstore100[3-5] - https://phabricator.wikimedia.org/T210478 (10Marostegui) [07:23:00] 10Blocked-on-schema-change, 10MediaWiki-Change-tagging, 10Patch-For-Review, 10User-Ladsgroup: Drop change_tag.ct_tag column in production - https://phabricator.wikimedia.org/T210713 (10Marostegui) [09:53:10] 10DBA, 10Analytics, 10Analytics-Kanban, 10Patch-For-Review, 10User-Banyek: Migrate dbstore1002 to a multi instance setup on dbstore100[3-5] - https://phabricator.wikimedia.org/T210478 (10Marostegui) @elukey after merging: https://gerrit.wikimedia.org/r/#/c/operations/puppet/+/487000/ I have done the foll... [10:02:27] 10DBA, 10CheckUser: Provide a strategy for testing the performance of queries needed to show the list of user-agents for each IP - https://phabricator.wikimedia.org/T212092 (10jcrespo) So, first of all, the USE is not necessary, it uses the right index without needing a hint. The plan for the first query is:... [10:45:37] 10DBA, 10Parsing-Team, 10Patch-For-Review: Provide access to testreduce* databases on scandium + revoke from ruthenium - https://phabricator.wikimedia.org/T214740 (10Marostegui) 05Open→03Resolved Grants revoked: ` root@db1073.eqiad.wmnet[(none)]> show grants for 'ssastry'@'10.64.16.151'; ERROR 1141 (4200... [10:54:28] 10DBA, 10AbuseFilter, 10Stewards-and-global-tools, 10Patch-For-Review: Addition of last hit date to Special:AbuseFilter table - https://phabricator.wikimedia.org/T93564 (10jcrespo) So, first of all, abuse_filter_log is a `varbinary(64)` and not a bigint, and that will cause all sort of issues, like potenti... [10:58:07] 10DBA, 10AbuseFilter, 10Stewards-and-global-tools, 10Patch-For-Review: Addition of last hit date to Special:AbuseFilter table - https://phabricator.wikimedia.org/T93564 (10jcrespo) What you are trying to do is a join combined with a [[ https://dev.mysql.com/doc/refman/8.0/en/example-maximum-column-group-ro... [11:12:32] 10DBA, 10AbuseFilter, 10Stewards-and-global-tools, 10Patch-For-Review: Addition of last hit date to Special:AbuseFilter table - https://phabricator.wikimedia.org/T93564 (10Daimona) >>! In T93564#4931100, @jcrespo wrote: > So, first of all, abuse_filter_log is a `varbinary(64)` and not a bigint, and that wi... [12:22:08] 10DBA, 10AbuseFilter, 10Stewards-and-global-tools, 10Patch-For-Review: Addition of last hit date to Special:AbuseFilter table - https://phabricator.wikimedia.org/T93564 (10jcrespo) > So IIUC we should first address T42757 before moving on, right? Please let me complete the analysis before giving a final r... [12:24:13] 10DBA, 10AbuseFilter, 10Stewards-and-global-tools, 10Patch-For-Review: Addition of last hit date to Special:AbuseFilter table - https://phabricator.wikimedia.org/T93564 (10Daimona) @jcrespo Ah, sure. In the meanwhile, I'll revamp the WIP patch, which will be needed either way at some point. [12:57:43] 10DBA, 10AbuseFilter, 10Stewards-and-global-tools, 10Patch-For-Review: Addition of last hit date to Special:AbuseFilter table - https://phabricator.wikimedia.org/T93564 (10jcrespo) The analysis will extend a bit more as I need to alter the 10G table on a spare host to check if a type change and a new index... [13:05:25] 10DBA, 10AbuseFilter, 10Stewards-and-global-tools, 10Patch-For-Review: Addition of last hit date to Special:AbuseFilter table - https://phabricator.wikimedia.org/T93564 (10jcrespo) Indeed the alter will be essential for what I see: ` root@db1106.eqiad.wmnet[enwiki]> EXPLAIN select max(afl_id) FROM abuse_fi... [13:14:47] Hi everyone, hi jynus, I'm here in case you want a quicker way to talk [13:15:09] it is ok [13:15:37] I need to do some slow testing, will report either in a few hours or tomorrow most likely [13:17:35] Ah welp [13:17:40] I'll wait, thanks :-) [13:18:06] I'm adding you as reviewer to the patch in case you want to give it a look [13:18:31] so give it a look but wait [13:18:43] because the initial testing I saw is promising [13:18:57] but we may have to do more alters [13:26:00] Sure, no hurry [13:26:16] I also noticed that I missed an index, so there's still some work to do there [13:49:53] 10DBA, 10Analytics, 10Analytics-Kanban, 10Patch-For-Review, 10User-Banyek: Migrate dbstore1002 to a multi instance setup on dbstore100[3-5] - https://phabricator.wikimedia.org/T210478 (10elukey) First of all, I just realized that all these IPs ports need to be whitelisted on the Analytics VLAN's firewall... [14:12:46] 10DBA, 10Analytics, 10Analytics-Kanban, 10Patch-For-Review, 10User-Banyek: Migrate dbstore1002 to a multi instance setup on dbstore100[3-5] - https://phabricator.wikimedia.org/T210478 (10Marostegui) Then also make sure to whitelist dbstore1003:3340 as that is where the staging database will leave. [14:19:18] 10DBA, 10Analytics, 10Analytics-Kanban, 10Patch-For-Review, 10User-Banyek: Migrate dbstore1002 to a multi instance setup on dbstore100[3-5] - https://phabricator.wikimedia.org/T210478 (10elukey) analytics-in4 diff: ` + term mysql-dbstore { + from { + destination-address { +... [14:21:14] 10DBA, 10Analytics, 10Analytics-Kanban, 10Patch-For-Review, 10User-Banyek: Migrate dbstore1002 to a multi instance setup on dbstore100[3-5] - https://phabricator.wikimedia.org/T210478 (10Marostegui) >>! In T210478#4931616, @elukey wrote: > analytics-in4 diff: > > ` > + term mysql-dbstore { > +... [14:26:23] 10DBA, 10Analytics, 10Analytics-Kanban, 10Patch-For-Review, 10User-Banyek: Migrate dbstore1002 to a multi instance setup on dbstore100[3-5] - https://phabricator.wikimedia.org/T210478 (10elukey) After a first quick test it looks good: ` elukey@stat1007:~$ mysql -e 'show databases' -P 3312 -u research -p... [16:28:28] 10DBA, 10AbuseFilter, 10Stewards-and-global-tools, 10Patch-For-Review: Addition of last hit date to Special:AbuseFilter table - https://phabricator.wikimedia.org/T93564 (10jcrespo) ` MariaDB [enwiki]> select max(afl_id) FROM abuse_filter_log WHERE afl_filter = '3'; +-------------+ | max(afl_id) | +--... [16:31:26] 10DBA, 10AbuseFilter, 10Stewards-and-global-tools, 10Patch-For-Review: Addition of last hit date to Special:AbuseFilter table - https://phabricator.wikimedia.org/T93564 (10jcrespo) `name=I don't consider the ids private, but just in case MariaDB [enwiki]> select max(afl_id) FROM abuse_filter_log WHERE afl_... [16:35:24] marostegui: around? :D [16:38:49] * addshore just tried creating a table on db1111 (which I was given access for to test wb_terms table things relating to wikidata), but I just tried to create a table there and it complains about being in read-only mode :/ [16:43:11] jynus: ^^ not sure if your still around and would be able to see what is up [16:49:06] 10DBA, 10AbuseFilter, 10Stewards-and-global-tools, 10Patch-For-Review: Addition of last hit date to Special:AbuseFilter table - https://phabricator.wikimedia.org/T93564 (10jcrespo) I think, with the alter, this should be the strategy: ` EXPLAIN SELECT * FROM abuse_filter_log JOIN abuse_filter ON af_id =... [17:07:08] db1111? [17:07:10] let me check [17:07:35] it may have failed over [17:07:59] yeah, we failed it over to update it [17:08:18] as it is a test host, it has no read_only check (plus nobody will notice) [17:08:23] ^addshore [17:08:57] I seee [17:09:02] yeah, it lacks a read-only check, so after restart it went back to failsafe mode [17:09:09] gotcha :) [17:09:11] I will put it on read-write mode [17:09:18] Thankyou! :) [17:09:20] this is my fault [17:09:25] no problem [17:09:27] it had the safeness on produciton [17:09:32] without the monitoring [17:09:33] safeness is good :D [17:09:48] we would have notice immediately on a real production host [17:10:01] I will add the check [17:27:51] 10DBA, 10Analytics, 10Analytics-Kanban, 10Patch-For-Review, 10User-Banyek: Migrate dbstore1002 to a multi instance setup on dbstore100[3-5] - https://phabricator.wikimedia.org/T210478 (10JAllemandou) Did a quick test, it's working for me (one of them) :) Thanks a milion @Marostegui and !@elukey [17:56:16] 10DBA, 10AbuseFilter, 10Stewards-and-global-tools, 10Patch-For-Review: Addition of last hit date to Special:AbuseFilter table - https://phabricator.wikimedia.org/T93564 (10jcrespo) So this is my final recommendation: 1) Prepare and deploy the alter table: ` add index (`afl_filter`, `afl_id`) ` 2) Change... [17:59:48] Daimona: I think you will be happy https://phabricator.wikimedia.org/T93564#4932358 [17:59:51] 10DBA, 10AbuseFilter, 10Stewards-and-global-tools, 10Patch-For-Review: Addition of last hit date to Special:AbuseFilter table - https://phabricator.wikimedia.org/T93564 (10Daimona) @jcrespo Thanks for the deep and useful analysis! I'll submit the patch for the schema change later or tomorrow. Shall I open... [18:00:06] jynus Indeed, I just replied there :D [18:02:47] 10DBA, 10AbuseFilter, 10Stewards-and-global-tools, 10Patch-For-Review: Addition of last hit date to Special:AbuseFilter table - https://phabricator.wikimedia.org/T93564 (10jcrespo) The schema change (code and its deployment) is up to you and how you organize yourself, for the "deployment of a schema change... [18:03:18] i will go afk, we will be on contact on phab, bye! [18:08:35] 10DBA, 10AbuseFilter, 10Stewards-and-global-tools, 10Patch-For-Review: Addition of last hit date to Special:AbuseFilter table - https://phabricator.wikimedia.org/T93564 (10jcrespo) Oh, I just realized that I did the "last edit" based on the id, not the timestamp. I hope that works, if not the idea would be... [18:31:14] 10DBA, 10AbuseFilter, 10Stewards-and-global-tools, 10Patch-For-Review, 10User-Daimona: Addition of last hit date to Special:AbuseFilter table - https://phabricator.wikimedia.org/T93564 (10Daimona) Yes, I meant a new task with #blocked-on-schema-change, thanks. As for the ID/timestamp, the ID is of course... [18:31:17] 10DBA, 10AbuseFilter, 10Stewards-and-global-tools, 10Patch-For-Review, 10User-Daimona: Addition of last hit date to Special:AbuseFilter table - https://phabricator.wikimedia.org/T93564 (10Daimona) [18:35:41] 10DBA, 10AbuseFilter, 10Stewards-and-global-tools, 10Patch-For-Review, 10User-Daimona: Addition of last hit date to Special:AbuseFilter table - https://phabricator.wikimedia.org/T93564 (10jcrespo) ^see my comment update (the table already has some index overhead). [19:55:43] 10DBA, 10Operations, 10ops-eqiad: Degraded RAID on db1073 - https://phabricator.wikimedia.org/T215050 (10Cmjohnson) The disk has been replaced but I also a bad disk on slot 6. leaving this open until tomorrow and will replace it [21:09:20] 10DBA, 10Operations, 10ops-eqiad: Degraded RAID on db1073 - https://phabricator.wikimedia.org/T215050 (10Marostegui) @Cmjohnson you can proceed with the one on slot 6. The one on slot #1 finished correctly ` Enclosure Device ID: 32 Slot Number: 1 Drive's position: DiskGroup: 0, Span: 0, Arm: 1 Enclosure pos... [22:52:50] 10DBA, 10CheckUser: Provide a strategy for testing the performance of queries needed to show the list of user-agents for each IP - https://phabricator.wikimedia.org/T212092 (10Huji) I don't like any of those three options; they are all too much cost (engineering cost, code review time cost, etc.) for little be...