Optimize main tablespace of db1225, db1239
Open, LowPublic
Actions

Assigned To

None

Authored By

	jcrespo
	Thu, Jun 20, 12:48 PM

Description

Based on:

, optimize the size of db1225 so it has a smaller ibdata1 file. 29094

1	cumin2024@db1164.eqiad.wmnet[dbbackups]> SELECT A.file_path, A.file_name, A.size AS size_job_1, B.size AS size_job_2, ABS(CONVERT(B.size, SIGNED) - CONVERT(A.size, SIGNED)) AS difference FROM backup_files A JOIN backup_files B ON A.file_path = B.file_path AND A.file_name = B.file_name WHERE A.backup_id = 29091 AND B.backup_id = 29094 ORDER BY ABS(CONVERT(B.size, SIGNED) - CONVERT(A.size, SIGNED)) DESC; ------------+-------------------------------------------------------+-------------+--------------+--------------+ \| file_name \| size_job_1 \| size_job_2 \| difference \| ------------+-------------------------------------------------------+-------------+--------------+--------------+ \| ibdata1 \| 5546967040 \| 227310305280 \| 221763338240 \| \| change_tag.ibd \| 7675576320 \| 3829399552 \| 3846176768 \| \| change_tag.ibd \| 6157238272 \| 3120562176 \| 3036676096 \| \| change_tag.ibd \| 3837788160 \| 1937768448 \| 1900019712 \| \| change_tag.ibd \| 2797600768 \| 1392508928 \| 1405091840 \| \| change_tag.ibd \| 2835349504 \| 1451229184 \| 1384120320 \| \| categorylinks.ibd \| 8363442176 \| 9747562496 \| 1384120320 \| \| change_tag.ibd \| 2554331136 \| 1279262720 \| 1275068416 \| \| change_tag.ibd \| 2126512128 \| 1073741824 \| 1052770304 \| \| categorylinks.ibd \| 2604662784 \| 3565158400 \| 960495616 \| \| categorylinks.ibd \| 4848615424 \| 5809111040 \| 960495616 \| \| change_tag.ibd \| 1807745024 \| 922746880 \| 884998144 \| \| categorylinks.ibd \| 1656750080 \| 2411724800 \| 754974720 \| \| categorylinks.ibd \| 3938451456 \| 4638900224 \| 700448768 \| \| change_tag.ibd \| 1358954496 \| 683671552 \| 675282944 \| \| text.ibd \| 4932501504 \| 4286578688 \| 645922816 \| \| change_tag.ibd \| 1266679808 \| 633339904 \| 633339904 \| \| categorylinks.ibd \| 1191182336 \| 1728053248 \| 536870912 \| \| templatelinks.ibd \| 4852809728 \| 4324327424 \| 528482304 \| \| categorylinks.ibd \| 2063597568 \| 2583691264 \| 520093696 \| \| categorylinks.ibd \| 2608857088 \| 3112173568 \| 503316480 \| \| change_tag.ibd \| 964689920 \| 482344960 \| 482344960 \| \| change_tag.ibd \| 843055104 \| 423624704 \| 419430400 \| \| categorylinks.ibd \| 1719664640 \| 2109734912 \| 390070272 \| \| ip_changes.ibd \| 1870659584 \| 2252341248 \| 381681664 \| \| content.ibd \| 3300917248 \| 3682598912 \| 381681664 \| \| change_tag.ibd \| 759169024 \| 385875968 \| 373293056 \| \| content.ibd \| 4022337536 \| 4378853376 \| 356515840 \| \| pagelinks.ibd \| 13333692416 \| 13014925312 \| 318767104 \| \| content.ibd \| 2659188736 \| 2977955840 \| 318767104 \| \| change_tag.ibd \| 612368384 \| 306184192 \| 306184192 \| \| imagelinks.ibd \| 1346371584 \| 1040187392 \| 306184192 \| \| categorylinks.ibd \| 1732247552 \| 2017460224 \| 285212672 \| \| pagelinks.ibd \| 12826181632 \| 12566134784 \| 260046848 \| \| text.ibd \| 3355443200 \| 3095396352 \| 260046848 \| rows in set (0.165 sec) Related Objects Mentions Mentioned Here P65222 jobs 29091 & 29094 Event Timeline jcrespo created this task.Thu, Jun 20, 12:48 PM Restricted Application added a subscriber: Aklapper. · View Herald TranscriptThu, Jun 20, 12:48 PM jcrespo triaged this task as Low priority.Thu, Jun 20, 12:48 PM jcrespo renamed this task from Optimize main tablespace of db1225 to Optimize main tablespace of db1225, db1239.Thu, Jun 20, 12:55 PM Marostegui subscribed.Thu, Jun 20, 12:59 PM jcrespo added a comment.Thu, Jun 20, 1:03 PM Comment Actions For example, a freshly loaded host from backups (db1240:s3) has it as 169G in size. So the differece may come from 10.6 or some change in defaults, not the lack of optimization (?). Log In to Comment Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits

cumin2024@db1164.eqiad.wmnet[dbbackups]> SELECT A.file_path, A.file_name, A.size AS size_job_1, B.size AS size_job_2, ABS(CONVERT(B.size, SIGNED) - CONVERT(A.size, SIGNED)) AS difference FROM backup_files A JOIN backup_files B ON A.file_path = B.file_path AND A.file_name = B.file_name WHERE A.backup_id = 29091 AND B.backup_id = 29094 ORDER BY ABS(CONVERT(B.size, SIGNED) - CONVERT(A.size, SIGNED)) DESC; ------------+-------------------------------------------------------+-------------+--------------+--------------+ | file_name | size_job_1 | size_job_2 | difference | ------------+-------------------------------------------------------+-------------+--------------+--------------+ | ibdata1 | 5546967040 | 227310305280 | 221763338240 | | change_tag.ibd | 7675576320 | 3829399552 | 3846176768 | | change_tag.ibd | 6157238272 | 3120562176 | 3036676096 | | change_tag.ibd | 3837788160 | 1937768448 | 1900019712 | | change_tag.ibd | 2797600768 | 1392508928 | 1405091840 | | change_tag.ibd | 2835349504 | 1451229184 | 1384120320 | | categorylinks.ibd | 8363442176 | 9747562496 | 1384120320 | | change_tag.ibd | 2554331136 | 1279262720 | 1275068416 | | change_tag.ibd | 2126512128 | 1073741824 | 1052770304 | | categorylinks.ibd | 2604662784 | 3565158400 | 960495616 | | categorylinks.ibd | 4848615424 | 5809111040 | 960495616 | | change_tag.ibd | 1807745024 | 922746880 | 884998144 | | categorylinks.ibd | 1656750080 | 2411724800 | 754974720 | | categorylinks.ibd | 3938451456 | 4638900224 | 700448768 | | change_tag.ibd | 1358954496 | 683671552 | 675282944 | | text.ibd | 4932501504 | 4286578688 | 645922816 | | change_tag.ibd | 1266679808 | 633339904 | 633339904 | | categorylinks.ibd | 1191182336 | 1728053248 | 536870912 | | templatelinks.ibd | 4852809728 | 4324327424 | 528482304 | | categorylinks.ibd | 2063597568 | 2583691264 | 520093696 | | categorylinks.ibd | 2608857088 | 3112173568 | 503316480 | | change_tag.ibd | 964689920 | 482344960 | 482344960 | | change_tag.ibd | 843055104 | 423624704 | 419430400 | | categorylinks.ibd | 1719664640 | 2109734912 | 390070272 | | ip_changes.ibd | 1870659584 | 2252341248 | 381681664 | | content.ibd | 3300917248 | 3682598912 | 381681664 | | change_tag.ibd | 759169024 | 385875968 | 373293056 | | content.ibd | 4022337536 | 4378853376 | 356515840 | | pagelinks.ibd | 13333692416 | 13014925312 | 318767104 | | content.ibd | 2659188736 | 2977955840 | 318767104 | | change_tag.ibd | 612368384 | 306184192 | 306184192 | | imagelinks.ibd | 1346371584 | 1040187392 | 306184192 | | categorylinks.ibd | 1732247552 | 2017460224 | 285212672 | | pagelinks.ibd | 12826181632 | 12566134784 | 260046848 | | text.ibd | 3355443200 | 3095396352 | 260046848 | rows in set (0.165 sec)

Related Objects

Mentioned Here: P65222 jobs 29091 & 29094

Event Timeline

jcrespo created this task.Thu, Jun 20, 12:48 PM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptThu, Jun 20, 12:48 PM

jcrespo triaged this task as Low priority.Thu, Jun 20, 12:48 PM

jcrespo renamed this task from Optimize main tablespace of db1225 to Optimize main tablespace of db1225, db1239.Thu, Jun 20, 12:55 PM

Marostegui subscribed.Thu, Jun 20, 12:59 PM

For example, a freshly loaded host from backups (db1240:s3) has it as 169G in size. So the differece may come from 10.6 or some change in defaults, not the lack of optimization (?).

Optimize main tablespace of db1225, db1239Open, LowPublicActions

Description

Related Objects

Event Timeline

Optimize main tablespace of db1225, db1239
Open, LowPublic
Actions