Staff Database Architect in SRE data persistence team in WMF. Used to be Wikidata software engineer at WMDE
I'm also open source enthusiast, mediawiki volunteer developer, and long-term Wikipedian.
All edits on tickets about databases are in my work capacity and anything else is in my volunteer capacity unless mentioned otherwise.
Babel: fa-N, en-4, de-2, tr-1, hu-1
There was a new version of mailman deployed today. I can't reproduce this anymore. Can you check @Reedy?
FWIW, having PK is always encouraged, it helps with data integrity and replication but it doesn't need to be a dedicated auto_increment id column. If tables are getting rather big, give it a dedicated column since it helps with backups and checksum runs. If it's not, you can group a couple of integer columns that could make a unique index and call it PK. If table is going to be monstrous (like pagelinks level), then adding column costs outweighs the backup benefits. i.e.
I switched the proxy to point to codesearch9 now and shut off codesearch8. I will delete it tomorrow.
I don't have the design expertise to say it's okay or not. It's @JScherer-WMF's domain ^_^
From the schema point of view: My only note is that cewa_page_wiki seems to be redundant. If you have FK to ce_invitation_lists and that table has the wiki, you don't need to store it again here.
attached the new volume, it's being rebuilt.
This is done I think but then maybe we should drop the grant on lists1001 then?
Thanks!
Yup:
ladsgroup@codesearch8:~$ df -h Filesystem Size Used Avail Use% Mounted on udev 3.9G 0 3.9G 0% /dev tmpfs 799M 82M 717M 11% /run /dev/sda1 20G 10G 8.9G 54% / tmpfs 3.9G 0 3.9G 0% /dev/shm tmpfs 5.0M 0 5.0M 0% /run/lock tmpfs 3.9G 0 3.9G 0% /sys/fs/cgroup /dev/sdb 75G 49G 23G 68% /srv tmpfs 799M 0 799M 0% /run/user/0 tmpfs 799M 0 799M 0% /run/user/3182
ladsgroup@codesearch9:~$ sudo service hound-search status
● hound-search.service - hound-search
Loaded: loaded (/lib/systemd/system/hound-search.service; enabled; preset: enabled)
Active: active (running) since Tue 2024-06-18 06:00:30 UTC; 5h 34min ago
Main PID: 489792 (docker)
Tasks: 9 (limit: 9521)
Memory: 24.9M
CPU: 4.424s
CGroup: /system.slice/hound-search.service
└─489792 /usr/bin/docker run -p 6080:6080 --name hound-search --user=root -v /srv/hound/hound-search:/data -v /etc/hound-gitconfig:/root/.gitconfig docker-registry.wikimedia.org/wikimedia/labs-code>As it has been said before, this seems to be an issue with the files not the thumbnailing service. The upgrade of the service just exposed the issue.
If understand it correctly, that's for a private wiki with local upload enabled but this wiki doesn't have upload enabled?
Local file uploads: disabled
That's sorta intentional. The poweredby svg needs to remove that "button" part. I can try to make the svg if find the time.
So the raw SQL part has not been deprecated yet since there are many usecases left but the replacement in all cases is in place. I suggest renaming this ticket to narrow its scope to "provide replacement for raw SQL in conditions" and file a new ticket for the actual deprecation. Does that sound fine by people?
Yeah, I don't think we had any specific need for a very specific docker engine, it's just whatever works, works. It's not large-scale or specific setup in any way.
In terms of performance, it is now 3.06 + 5.5 = 8.65KB in total vs. 3.36 + 3.20 = 6.56KB (I'm counting gzip compressed) which is a small jump but not too big given that it heavily gets cached client side and it's desktop only (that should change though) and it's lazy loaded from what I'm seeing.
@Jdlrobson Actually this now needs to be adopted with dark mode:
So I used FTT (a type of DFT) to remove the noise and move the information from a time series to frequency domain.
The result is stuff like this:
We dropped several large indexes. There are some more to be checked but from a quick view nothing with large gains stood out. I call this done.
I only found it on centralauth. Ran it with replication since it's rather small and it's showing up everywhere now.
I need to figure out where is this table exactly and or there are tables like this in private wikis, etc.
After ten days the numbers are better but not much:
mysql:research@s4-analytics-replica.eqiad.wmnet [commonswiki]> select linter_template, linter_cat, linter_tag, count(*) from linter where linter_template != '' group by linter_template, linter_cat, linter_tag order by count(*) desc limit 50; +-------------------------------------------------+------------+------------+----------+ | linter_template | linter_cat | linter_tag | count(*) | +-------------------------------------------------+------------+------------+----------+ | Template:Self | 22 | | 28305118 | | Template:FlickreviewR | 22 | | 7972413 | | Template:Artwork | 22 | | 7277358 | | Template:Information | 22 | | 7072181 | | Template:Wikidata_Infobox | 22 | | 5027304 | | Template:Book | 22 | | 4724893 | | Template:Geograph_from_structured_data | 22 | | 4465691 | | Template:NASA-image | 22 | | 4225693 | | User:FlickreviewR/reviewed-pass | 22 | | 3896881 | | Template:Artwork | 23 | | 3386630 | | Template:PD-USGov-NASA | 22 | | 3051415 | | Template:Autotranslate | 22 | | 2020877 | | Template:PD-scan | 22 | | 2020711 | | Template:Information | 23 | | 2009704 |
We are improving the performance in several areas, one patch has got merged but not deployed yet and the second is being reviewed and hopefully merged and deployed soon.
I just deleted the VM.
Wrote this in tech news:
Mailing lists will be unavailable for roughly two hours on Tuesday 10:00 UTC to 12:00 UTC. This is facilitate migration to a new server and upgrade its software. [9]
Sure. I can do that and done it before. We avoid changes like this on Fridays. I will it first thing on Monday.
I will try to fix it during this weekend (to also minimize disruptions)
Thank you!
Hi, This is part of a new defense mechanism we have in place to protect production in case of overload. You can read more about the work in T360930: Section-wide circuit breaking and more about circuit breaking in general in https://en.wikipedia.org/wiki/Circuit_breaker_design_pattern
LinkBatch should split queries into batches of fifty or 100 pages. Not all in one go.
With that req id, there is this: https://logstash.wikimedia.org/app/discover#/doc/logstash-*/logstash-mediawiki-1-7.0.0-1-2024.06.13?id=2b_AEZAB_bxzc-Pm1mZU
error Got a packet bigger than 'max_allowed_packet' bytes
$wgShowExceptionDetails will just shows the user details of the error. We do have that information in logstash, we just don't show it as it might leak private information. Now that I have the req id, I can look it up in logstash.
Turning off the gadget won't make a difference. The query adds the class to the HTML and it will continue to do so regardless.
The SQL is a bit messy so I might have missed more changes, but the one I found is that you're doing straight join of RC to actor?That's technically possible but there are two problem:
I restarted sanitarium master on eqiad and codfw in s5 and it picked up the new config. From DBA point of view, this is good to go.
I restarted sanitarium master on eqiad and codfw in s5 and it picked up the new config. From DBA point of view, this is good to go.
I restarted sanitarium master on eqiad and codfw in s5 and it picked up the new config. From DBA point of view, this is good to go.
That'd be correct.
I would probably also need to implement a new query builder method ->search() or something that would then do a LIKE %% search in mysql and and tsquery() call in postgres right ?
You could probably introduce a subclass of SQB called SearchQueryBuilder and do that magic there. That would avoid introducing a new function on a class that's widely used just for one usecase.
Hmm, ok not sure if I want to get in this deep :)
Lets first finish the mysql part, and we'll keep the postgres version of this as unused zombie before trying to merge that in then, because that seems WAY more complicated.
Yes :D
I played with it and it's waaay more complicated than it looks. So I much that I gave up. If someone is willing to work on it, I'd be happy to help.
It had 31 case. I fixed it via stuff like:
$users = ['Prodego', 'Tim1357', 'Ruslik0', 'Mr.Z-man', '28bytes', 'Reaper Eternal', 'NawlinWiki', 'Beetstra', 'Shirik', 'Kww', 'Mindmatrix', 'Feezo', 'Hersfold', 'Cenarium', 'The Earwig', 'PeterSymonds', 'Xeno', 'EdoDodo', 'NuclearWarfare', 'Od Mishehu', 'Someguy1221', 'Soap', 'Nakon', 'Maxim', 'Gogo Dodo', 'Martijn Hoekstra'];
foreach ( $users as $userName ) {
$user = \MediaWiki\MediaWikiServices::getInstance()->getUserFactory()->newFromName( $userName );
var_dump( $userName);
if ( $user ) {
\MediaWiki\MediaWikiServices::getInstance()->getAuthManager()->autoCreateUser( $user, \MediaWiki\Auth\AuthManager::AUTOCREATE_SOURCE_MAINT, false );
\MediaWiki\MediaWikiServices::getInstance()->getActorStore()->acquireActorId( $user, wfGetDB( DB_PRIMARY ) );
}I think it's because it was imported from production. While the users were not imported. You can see the user id stored in af table is the production user.
Fixed commons. Let's see what's next.
Now commonswiki is broken. I hope we didn't import too many abuse filters from production.
I did some magic that involved doing stuff like this:
$user = \MediaWiki\MediaWikiServices::getInstance()->getUserFactory()->newFromName( 'Antime' ); \MediaWiki\MediaWikiServices::getInstance()->getAuthManager()->autoCreateUser( $user, \MediaWiki\Auth\AuthManager::AUTOCREATE_SOURCE_MAINT, false ); \MediaWiki\MediaWikiServices::getInstance()->getActorStore()->acquireActorId( $user, wfGetDB( DB_PRIMARY ) );
Ran this:
MariaDB [arwiki]> update abuse_filter set af_user = 72, af_actor = 70 where af_user_text = 'Meno25'; Query OK, 17 rows affected (0.018 sec) Rows matched: 17 Changed: 17 Warnings: 0
User name "Meno25" is usable, cannot create an anonymous actor for it. Run maintenance/cleanupUsersWithNoId.php to fix this situation.
I think I found why main queries are not being killed but users are reporting general slow downs.
I think all would be reproducible but I tested it on dbstore1008:3317
Nope, it didn't fix it.
One of the queries that are failing:
mysql:research@s7-analytics-replica.eqiad.wmnet [metawiki]> explain SELECT rc_id,rc_timestamp,rc_namespace,rc_title,rc_minor,rc_bot,rc_new,rc_cur_id,rc_this_oldid,rc_last_oldid,rc_type,rc_source,rc_patrolled,rc_ip,rc_old_len,rc_new_len,rc_deleted,rc_logid,rc_log_type,rc_log_action,rc_params,rc_actor,recentchanges_actor.actor_user AS `rc_user`,recentchanges_actor.actor_name AS `rc_user_text`,comment_rc_comment.comment_text AS `rc_comment_text`,comment_rc_comment.comment_data AS `rc_comment_data`,comment_rc_comment.comment_id AS `rc_comment_cid`,rc_title,rc_namespace,wl_user,wl_notificationtimestamp,we_expiry,page_latest,(SELECT GROUP_CONCAT(ctd_name SEPARATOR ',') FROM `change_tag` JOIN `change_tag_def` ON ((ct_tag_id=ctd_id)) WHERE (ct_rc_id=rc_id) ) AS `ts_tags` FROM `recentchanges` JOIN `actor` `recentchanges_actor` ON ((actor_id=rc_actor)) STRAIGHT_JOIN `comment` `comment_rc_comment` ON ((comment_rc_comment.comment_id = rc_comment_id)) LEFT JOIN `watchlist` ON (wl_user = 2134281 AND (wl_title=rc_title) AND (wl_namespace=rc_namespace)) LEFT JOIN `watchlist_expiry` ON ((wl_id = we_item)) LEFT JOIN `page` ON ((rc_cur_id=page_id)) WHERE (((actor_user IS NOT NULL))) AND rc_bot = 0 AND (rc_type != 6) AND (rc_source != 'wb') AND (rc_namespace NOT IN (1198,1199,866,867)) AND (rc_timestamp >= '20240601170235') AND rc_new IN (0,1) ORDER BY rc_timestamp DESC LIMIT 50 ; +------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+---------------------------------------------------------------------------+----------+--------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+---------------------------------------------------------------------------+----------+--------------------------------------------------------+ | 1 | PRIMARY | recentchanges_actor | range | PRIMARY,actor_user | actor_user | 5 | NULL | 11268769 | Using index condition; Using temporary; Using filesort | | 1 | PRIMARY | recentchanges | ref | rc_timestamp,rc_name_type_patrolled_timestamp,rc_ns_actor,rc_actor,rc_namespace_title_timestamp,rc_new_name_timestamp | rc_actor | 8 | metawiki.recentchanges_actor.actor_id | 42 | Using index condition; Using where | | 1 | PRIMARY | watchlist | eq_ref | wl_user,wl_user_notificationtimestamp,wl_namespace_title | wl_user | 265 | const,metawiki.recentchanges.rc_namespace,metawiki.recentchanges.rc_title | 1 | | | 1 | PRIMARY | watchlist_expiry | eq_ref | PRIMARY | PRIMARY | 4 | metawiki.watchlist.wl_id | 1 | Using where | | 1 | PRIMARY | comment_rc_comment | eq_ref | PRIMARY | PRIMARY | 8 | metawiki.recentchanges.rc_comment_id | 1 | | | 1 | PRIMARY | page | eq_ref | PRIMARY | PRIMARY | 4 | metawiki.recentchanges.rc_cur_id | 1 | | | 2 | DEPENDENT SUBQUERY | change_tag | ref | ct_rc_tag_id,ct_tag_id_id | ct_rc_tag_id | 9 | metawiki.recentchanges.rc_id | 1 | Using index | | 2 | DEPENDENT SUBQUERY | change_tag_def | eq_ref | PRIMARY | PRIMARY | 4 | metawiki.change_tag.ct_tag_id | 1 | | +------+--------------------+---------------------+--------+-----------------------------------------------------------------------------------------------------------------------+--------------+---------+---------------------------------------------------------------------------+----------+--------------------------------------------------------+ 8 rows in set (0.003 sec)
Hmm, I see #000 as background of links:
We kill any watchlist or recentchange query that takes more than 30 seconds. That's quite normal to call it with filters that could be so expensive that gets killed. So that is normal.
That is actually quite messy. Once you use the abstract schema change, it takes care of it automatically, you don't need to do anything. See http://mediawiki.org/wiki/Schema%20changes on how to use that.