[07:11:51] 10DBA, 06Labs, 10Labs-Infrastructure: Explore 'Analyze' statement as substitute for Explain - https://phabricator.wikimedia.org/T141095#2486960 (10Marostegui) That is a good idea! It would be really useful and easy to use. Apart from setting the right grants, we can also try to set up some sort of chroot or... [07:16:02] 10DBA, 06Labs, 10Labs-Infrastructure: Explore 'Analyze' statement as substitute for Explain - https://phabricator.wikimedia.org/T141095#2895567 (10yuvipanda) Yep, it would! Can you explain what you mean by 'fake' mysql server? [07:18:48] 10DBA, 06Labs, 10Labs-Infrastructure: Explore 'Analyze' statement as substitute for Explain - https://phabricator.wikimedia.org/T141095#2895568 (10yuvipanda) simple API would be: POST to labsdb-analytics.eqiad.wmnet/v1/analyze (or labsdb-web.eqiad.wmnet/v1/analyze) your SQL query, and it will return ANALYZE... [07:24:36] 10DBA, 06Labs, 10Labs-Infrastructure: Explore 'Analyze' statement as substitute for Explain - https://phabricator.wikimedia.org/T141095#2895572 (10Marostegui) >>! In T141095#2895567, @yuvipanda wrote: > Yep, it would! > > Can you explain what you mean by 'fake' mysql server? If we are not completely sure a... [09:05:24] 10DBA: Defragment db1015 - https://phabricator.wikimedia.org/T153739#2895684 (10Marostegui) db1015 should be good to go for the holidays ``` root@db1015:/srv/sqldata# df -hT /srv/ Filesystem Type Size Used Avail Use% Mounted on /dev/mapper/tank-data xfs 1.6T 1.4T 266G 84% /srv ``` [09:19:32] 10DBA, 06Labs, 10Labs-Infrastructure: Explore 'Analyze' statement as substitute for Explain - https://phabricator.wikimedia.org/T141095#2895694 (10jcrespo) >>! In T141095#2894390, @yuvipanda wrote: > @jcrespo what do you think of a http service that is hosted on hardware somewhere and maintained by the labs... [09:23:00] 10DBA, 06Labs, 10Labs-Infrastructure: Explore 'Analyze' statement as substitute for Explain - https://phabricator.wikimedia.org/T141095#2895695 (10jcrespo) > If we are not completely sure about grants and possible SQL injection, we can always generate fake data to populate the tables, Unless you plan to cr... [09:37:50] 10DBA, 06Labs, 10Labs-Infrastructure: Explore 'Analyze' statement as substitute for Explain - https://phabricator.wikimedia.org/T141095#2895714 (10yuvipanda) @jynus ah, I didn't know that (re: subqueries). TIL. I think in that case we should do the same service but with a SHOW EXPLAIN. API can remain the sa... [09:40:41] 10DBA, 06Labs, 10Labs-Infrastructure: Explore 'Analyze' statement as substitute for Explain - https://phabricator.wikimedia.org/T141095#2895716 (10jcrespo) >>! In T141095#2895714, @yuvipanda wrote: > @jynus ah, I didn't know that (re: subqueries). TIL. > > I think in that case we should do the same service... [11:00:26] 10DBA, 13Patch-For-Review: Import S2,S6,S7,m3 and x1 to dbstore2001 and dbstore2002 - https://phabricator.wikimedia.org/T151552#2895769 (10Marostegui) s6 has been compressed. It went from: `719G` to `268G` [11:01:55] 10DBA: Meta ticket: Deploy InnoDB compression where possible - https://phabricator.wikimedia.org/T150438#2895770 (10Marostegui) s6 results on dbstore2001: It went from: `719G` to `268G` [13:44:22] Aparently the right syntx is "ENGINE=InnoDB row_format=compressed, FORCE, ALGORITHM=COPY;" [13:44:39] with 2 commans at the end, but not for row format [13:44:44] *commas [13:45:36] oh, good one to know for the future indeed [13:46:27] I assume force and copy are redundant [13:46:42] but wanted to get the syntax right for my original command [13:47:57] for the online algorithm, the fact that the buffer has to be finite, it meas there is a cap on how big and/or how many inserts can happen online [13:48:26] very relevant for revision alters [13:49:36] I forgot before one thing as a priority for next quarter: gtid_domain [13:50:00] we need it for when the new labsdbs crashes [13:50:11] and it is a when, not an if [13:50:12] 10DBA: Defragment db1044 - https://phabricator.wikimedia.org/T153826#2896110 (10Marostegui) This should be enough for the holidays: ``` root@db1044:/srv/sqldata# df -hT /srv/ Filesystem Type Size Used Avail Use% Mounted on /dev/sda6 xfs 1.7T 1.4T 252G 85% /srv ``` [14:27:47] marostegui, you said that nothing ongoing was running, right? [14:34:48] I see less IOPS on dbstore2001 than dbstore1001, but the first is lagging enwiki [14:41:44] unless you tell me differently, I think I will try something, starting by increasing the buffer pool size [15:32:41] 10DBA, 13Patch-For-Review: Import S2,S6,S7,m3 and x1 to dbstore2001 and dbstore2002 - https://phabricator.wikimedia.org/T151552#2896244 (10jcrespo) Important: x1 has some (wrong) filters by default on dbstores, those should be removed to not compromise data integrity on x1 when/if it is loaded (I just realized... [15:54:50] 10DBA, 13Patch-For-Review: Import S2,S6,S7,m3 and x1 to dbstore2001 and dbstore2002 - https://phabricator.wikimedia.org/T151552#2896270 (10jcrespo) dbstore2001 crashed on restart, probably because a partitioned table was imported: ``` 161222 15:45:05 [Note] Event Scheduler: scheduler thread started with id 1... [15:58:07] 10DBA, 13Patch-For-Review: Import S2,S6,S7,m3 and x1 to dbstore2001 and dbstore2002 - https://phabricator.wikimedia.org/T151552#2896278 (10jcrespo) ``` 161222 15:46:57 [Note] InnoDB: Reading tablespace information from the .ibd files... 161222 15:56:34 [ERROR] InnoDB: Attempted to open a previously opened tab... [16:23:06] 10DBA, 10Gerrit, 06Operations, 06Release-Engineering-Team, 05Security: Gerrit: Convert gerrit's db caractor encoding from utf8 to utf8mb4 to prevent truncation of astral characters - https://phabricator.wikimedia.org/T153899#2894520 (10Bawolff) [16:24:50] 10DBA, 10Gerrit, 06Operations, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2896367 (10Paladox) [16:25:18] 10DBA, 10Gerrit, 06Operations, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2644059 (10Paladox) Adding #dba and #operations as this requires changes to the db so it needs there envolvement [16:32:26] 10DBA, 10Gerrit, 06Operations, 13Patch-For-Review, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2896386 (10jcrespo) Can someone check that gerrit works with utf8mb4 before doing a destructive operations? [17:04:46] 10DBA, 10MediaWiki-Database, 07Performance: revision page_user_timestamp index problematic on large wikis - https://phabricator.wikimedia.org/T102532#2896440 (10jcrespo) [17:04:49] 10DBA: Rampant differences in indexes on enwiki.revision across the DB cluster - https://phabricator.wikimedia.org/T132416#2896439 (10jcrespo) [17:25:11] 10DBA, 13Patch-For-Review: Import S2,S6,S7,m3 and x1 to dbstore2001 and dbstore2002 - https://phabricator.wikimedia.org/T151552#2896475 (10jcrespo) p:05Normal>03High Because of the above, I have recovered dbstore2001, and I am running s2 with enwiktionary.templatelinks ignored, will reimport it when it cat... [17:25:25] 10DBA, 13Patch-For-Review: Import S2,S6,S7,m3 and x1 to dbstore2001 and dbstore2002 - https://phabricator.wikimedia.org/T151552#2896477 (10jcrespo) a:05Marostegui>03jcrespo [17:30:12] 10DBA, 10Gerrit, 06Operations, 13Patch-For-Review, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2896493 (10Paladox) @jcrespo hi, I can test it on gerrit-test. What commands do I use to convert them? I did some searching on doing that and runni... [17:39:52] 10DBA, 10Gerrit, 06Operations, 13Patch-For-Review, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2896517 (10jcrespo) > I did some searching on doing that and running that causes some error about keys being to big. While I haven't predicted that... [17:50:13] 10DBA, 10Gerrit, 06Operations, 13Patch-For-Review, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2896529 (10Paladox) root@gerrit-test:/home/paladox# mysql -p -BN reviewdb -e "SHOW TABLES" | while read table; do mysql -p reviewdb -e "ALTER TABLE... [17:53:44] 10DBA, 10Gerrit, 06Operations, 13Patch-For-Review, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2896531 (10jcrespo) > COuld that be because on gerrit-test the tables were created with latin1? No necessarily, previous indexes that took less tha... [18:02:48] 10DBA, 10Gerrit, 06Operations, 13Patch-For-Review, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2896566 (10Paladox) Oh, I have enabled innodb_large_prefix and same error, I will try it on gerrit-test3 database which is replica of prod gerrit.... [18:09:08] 10DBA, 10Gerrit, 06Operations, 13Patch-For-Review, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2896572 (10Paladox) Happens on a utf8 database too. [18:25:12] 10DBA, 10Gerrit, 06Operations, 13Patch-For-Review, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2896683 (10Paladox) {P4672} [18:28:29] 10DBA, 10Gerrit, 06Operations, 13Patch-For-Review, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2896697 (10Paladox) ah, you need to also have rowformat at ROW_FORMAT=DYNAMIC [18:34:26] 10DBA, 10Gerrit, 06Operations, 13Patch-For-Review, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2896711 (10Paladox) I converted the patch_comments table to utf8mb4, it stops the error but instead of showing the emoji it shows ??? which is bette... [18:35:36] 10DBA, 10Gerrit, 06Operations, 13Patch-For-Review, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2896714 (10jcrespo) >>! In T145885#2896697, @Paladox wrote: > ah, you need to also have rowformat at ROW_FORMAT=DYNAMIC Yes, it requires Barracuda... [18:39:17] 10DBA, 10Gerrit, 06Operations, 13Patch-For-Review, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2896742 (10jcrespo) > instead of showing the emoji it shows ??? which is better then an error At this point I would suggest setting sql_mode=TRADIT... [18:47:03] 10DBA, 10Gerrit, 06Operations, 13Patch-For-Review, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2896775 (10Paladox) Oh so we doint want to support emoji's? [18:49:30] 10DBA, 10Gerrit, 06Operations, 13Patch-For-Review, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2896811 (10demon) Emojis are the best & most important feature in any modern web application 🙃 [18:50:05] 10DBA, 10Gerrit, 06Operations, 13Patch-For-Review, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2896813 (10jcrespo) >>! In T145885#2896811, @demon wrote: > Emojis are the best & most important feature in any modern web application 🙃 Tell that... [18:51:12] 10DBA, 10Gerrit, 06Operations, 13Patch-For-Review, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2896817 (10Paladox) >>! In T145885#2896813, @jcrespo wrote: >>>! In T145885#2896811, @demon wrote: >> Emojis are the best & most important feature i... [18:57:23] 10DBA, 10Gerrit, 06Operations, 13Patch-For-Review, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2896831 (10Bawolff) >>! In T145885#2896711, @Paladox wrote: > I converted the patch_comments table to utf8mb4, it stops the error but instead of sho... [18:59:27] 10DBA, 10Gerrit, 06Operations, 13Patch-For-Review, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2896833 (10Paladox) Oh. this {P4674} shows the new update to patch_comments | patch_sets | InnoDB | 10 | Compact | 350... [19:05:03] 10DBA, 10Gerrit, 06Operations, 13Patch-For-Review, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2896841 (10Paladox) Hmm could this be the problem MariaDB [reviewdb]> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name... [19:27:41] 10DBA, 10Gerrit, 06Operations, 13Patch-For-Review, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2896901 (10Paladox) ahaha it works now. you have to set character-set-client-handshake = FALSE character-set-server = utf8mb4 collation-server = u... [19:46:24] 10DBA, 10Gerrit, 06Operations, 13Patch-For-Review, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2896928 (10Paladox) >>! In T145885#2896517, @jcrespo wrote: >> I did some searching on doing that and running that causes some error about keys bein... [19:49:23] 10DBA, 10Gerrit, 06Operations, 13Patch-For-Review, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2896945 (10Paladox) Most of the table has been converted and I carn't see any problems, it's working as before but now emoji's work :) [20:21:10] 10DBA, 10Gerrit, 06Operations, 13Patch-For-Review, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2897016 (10jcrespo) > ahaha it works now. > > you have to set > > character-set-client-handshake = FALSE > character-set-server = utf8mb4 > collat... [20:22:51] 10DBA, 10Gerrit, 06Operations, 13Patch-For-Review, 07Upstream: Gerrit shows HTTP 500 error when pasting an emoji - https://phabricator.wikimedia.org/T145885#2897017 (10Paladox) Oh, that should work too. [22:41:07] 10DBA, 10MediaWiki-Database, 07Tracking: Database replication lag issues (tracking) - https://phabricator.wikimedia.org/T3268#2897402 (10jcrespo) [23:40:07] jynus: funny, I was just looking at https://logstash.wikimedia.org/app/kibana#/doc/logstash-*/logstash-2016.12.21/mediawiki?id=AVkjs_DLEOj-wvmpr2X8&_g=(refreshInterval:(display:Off,pause:!f,value:0),time:(from:now-24h,mode:quick,to:now)) a while ago [23:46:13] Well, I wasn't told that table was going to be exposed on the api, and only queried 1 per second [23:47:03] that is not true anymore, so I have to change that table