Terrible mariadb optimizer (FYI)
No One
Actions

Authored By

	jcrespo
	Dec 17 2020, 7:32 PM

Size

6 KB

Referenced Files

None

Subscribers

None

Terrible mariadb optimizer (FYI)
View Options

	root@db1133.eqiad.wmnet[mediabackups]> show create table files\G
	************************* 1. row *************************
	Table: files
	Create Table: CREATE TABLE `files` (
	`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
	`wiki` int(10) unsigned NOT NULL,
	`upload_name` varbinary(255) DEFAULT NULL,
	`swift_container` int(10) unsigned DEFAULT NULL,
	`swift_name` varbinary(270) DEFAULT NULL,
	`file_type` tinyint(3) unsigned DEFAULT NULL,
	`status` tinyint(3) unsigned DEFAULT NULL,
	`sha1` varbinary(40) DEFAULT NULL,
	`md5` varbinary(32) DEFAULT NULL,
	`size` int(10) unsigned DEFAULT NULL,
	`upload_timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
	`archived_timestamp` timestamp NULL DEFAULT NULL,
	`deleted_timestamp` timestamp NULL DEFAULT NULL,
	`backup_status` tinyint(3) unsigned DEFAULT 1,
	PRIMARY KEY (`id`), <------------------------- !!!!!!
	KEY `sha1` (`sha1`),
	KEY `file_type` (`file_type`),
	KEY `status` (`status`),
	KEY `backup_status` (`backup_status`),
	KEY `wiki` (`wiki`),
	KEY `swift_container` (`swift_container`),
	KEY `upload_name` (`upload_name`,`status`),
	KEY `upload_timestamp` (`upload_timestamp`),
	CONSTRAINT `files_ibfk_1` FOREIGN KEY (`file_type`) REFERENCES `file_types` (`id`),
	CONSTRAINT `files_ibfk_2` FOREIGN KEY (`status`) REFERENCES `file_status` (`id`),
	CONSTRAINT `files_ibfk_3` FOREIGN KEY (`wiki`) REFERENCES `wikis` (`id`),
	CONSTRAINT `files_ibfk_4` FOREIGN KEY (`backup_status`) REFERENCES `backup_status` (`id`),
	CONSTRAINT `files_ibfk_5` FOREIGN KEY (`swift_container`) REFERENCES `swift_containers` (`id`)
	) ENGINE=InnoDB AUTO_INCREMENT=4549852 DEFAULT CHARSET=binary
	1 row in set (0.001 sec)

	-- simple query, just denormalize the data so it is in "human" readable form
	root@db1133.eqiad.wmnet[mediabackups]> select wiki_name, upload_name, size, status_name, sha1, backup_status_name FROM files JOIN file_status ON file_status.id = files.status JOIN backup_status ON backup_status.id = files.backup_status JOIN wikis ON wikis.id = files.wiki ORDER BY files.id LIMIT 10;

	<---- waiting for many minutes


	^CCtrl-C -- query killed. Continuing normally.
	ERROR 1317 (70100): Query execution was interrupted

	root@db1133.eqiad.wmnet[mediabackups]> EXPLAIN select wiki_name, upload_name, size, status_name, sha1, backup_status_name FROM files JOIN file_status ON file_status.id = files.status JOIN backup_status ON backup_status.id = files.backup_status JOIN wikis ON wikis.id = files.wiki ORDER BY files.id LIMIT 10\G
	************************* 1. row *************************
	id: 1
	select_type: SIMPLE
	table: files
	type: ALL <--------------------------- WTF?????
	possible_keys: status,backup_status,wiki
	key: NULL
	key_len: NULL
	ref: NULL
	rows: 4549851
	Extra: Using where; Using temporary; Using filesort <---- WTF?
	************************* 2. row *************************
	id: 1
	select_type: SIMPLE
	table: wikis
	type: eq_ref
	possible_keys: PRIMARY
	key: PRIMARY
	key_len: 4
	ref: mediabackups.files.wiki
	rows: 1
	Extra:
	************************* 3. row *************************
	id: 1
	select_type: SIMPLE
	table: backup_status
	type: ALL
	possible_keys: PRIMARY
	key: NULL
	key_len: NULL
	ref: NULL
	rows: 4
	Extra: Using where; Using join buffer (flat, BNL join)
	************************* 4. row *************************
	id: 1
	select_type: SIMPLE
	table: file_status
	type: eq_ref
	possible_keys: PRIMARY
	key: PRIMARY
	key_len: 1
	ref: mediabackups.files.status
	rows: 1
	Extra:
	4 rows in set (0.001 sec)


	root@db1133.eqiad.wmnet[mediabackups]> analyze table files;
	+--------------------+---------+----------+-----------------------------------------+
	\| Table \| Op \| Msg_type \| Msg_text \|
	+--------------------+---------+----------+-----------------------------------------+
	\| mediabackups.files \| analyze \| status \| Engine-independent statistics collected \|
	\| mediabackups.files \| analyze \| status \| OK \|
	+--------------------+---------+----------+-----------------------------------------+
	2 rows in set (54.456 sec)

	root@db1133.eqiad.wmnet[mediabackups]> select wiki_name, upload_name, size, status_name, sha1, backup_status_name FROM files JOIN file_status ON file_status.id = files.status JOIN backup_status ON backup_status.id = files.backup_status JOIN wikis ON wikis.id = files.wiki ORDER BY files.id LIMIT 10;


	<---- waiting for many minutes


	^CCtrl-C -- query killed. Continuing normally.
	ERROR 1317 (70100): Query execution was interrupted
	root@db1133.eqiad.wmnet[mediabackups]> select wiki_name, upload_name, size, status_name, sha1, backup_status_name FROM files JOIN file_status ON file_status.id = files.status JOIN backup_status ON backup_status.id = files.backup_status JOIN wikis ON wikis.id = files.wiki WHERE files.id BETWEEN 1 AND 10; -- note the only change at the end
	+-----------+---------------------------------------------------------+--------+-----------
	\| wiki_name \| upload_name \| size \| status_nam
	+-----------+---------------------------------------------------------+--------+-----------
	\| enwiki \| !!!_(Chk_Chk_Chk)_-_One_Girl_One_Boy_cover_art.jpg \| 31850 \| public
	\| enwiki \| !!!_-_!!!_album_cover.jpg \| 43672 \| public
	\| enwiki \| !!!_-_Wallop.png \| 118745 \| public
	\| enwiki \| !0_Trombones_Like_2_Pianos.jpg \| 25319 \| public
	\| enwiki \| !ClaudiaPascoal.png \| 26203 \| public
	\| enwiki \| !Haunu.ogg \| 13450 \| public
	\| enwiki \| !Hero_(album).jpg \| 38664 \| public
	\| enwiki \| !Women_Art_Revolution_(documentary_film)_poster_art.jpg \| 15446 \| public
	\| enwiki \| !_(The_Song_Formely_Known_As)_by_Regurgitator.png \| 146525 \| public
	\| enwiki \| ""Motor-Cycle"_LP_cover-Lotti_Golden.jpg \| 17001 \| public
	+-----------+---------------------------------------------------------+--------+-----------
	10 rows in set (0.001 sec)

	:-(

File Metadata

Mime Type: text/plain; charset=utf-8
Storage Engine: blob
Storage Format: Raw Data
Storage Handle: 8803668
Default Alt Text: Terrible mariadb optimizer (FYI) (6 KB)

Terrible mariadb optimizer (FYI)No OneActions

Terrible mariadb optimizer (FYI)View Options

File Metadata

Event Timeline

Terrible mariadb optimizer (FYI)
No One
Actions

Terrible mariadb optimizer (FYI)
View Options