Description:
gtid_executed + gtid_purged is an ever growing set to the point of bloating. While gtid_executed can be cleared on a replica via RESET MASTER, we pay the price by bloating gtid_purged. On a running master, there is no way to clean up the two.
NOTE: This is a resubmission of http://bugs.mysql.com/bug.php?id=92836, which was closed for reasons unclear, and not reopened after coimmunity feedback. In this Bug I only illustrate the first of the suggested solutions in #92836.
To emphasize what "bloat" means, this is an actual gtid_executed in a semi-production replication cluster (production environment, does not serve production traffic):
0025f4dd-7e8c-11e8-b6c9-008cfa5440e4:1-19242,
02dc9383-81be-11e8-a260-008cfa542442:1-5430702,
02ffa8d5-bbde-11e8-8e44-008cfa5440e4:1-58216,
04185125-c7b2-11e8-bc02-008cfa5440e4:1-63916,
04772ac2-93d5-11e8-aa4d-008cfa5440e4:1-54287,
...
dcda46d1-af55-11e8-aaa4-008cfa544064:1-69818,
dd720ef9-bf06-11e8-a88c-008cfa544064:1-6412662,
dde695fa-802f-11e8-be08-008cfa544064:1-63516,
de0f5d84-c482-11e8-8644-008cfa544064:1-71472,
de315cd9-d1e2-11e8-b10a-008cfa542442:1-2188,
de358dcf-ba4f-11e8-a4e8-008cfa544064:1-70939,
de983268-b598-11e8-a97f-008cfa544064:1-72787,
ded794e2-85ab-11e8-9b4c-008cfa5440e4:1-5310105,
e0a3502b-8f09-11e8-acb1-008cfa5440e4:1-643447,
e0d4ec6a-b0e1-11e8-aa5a-008cfa544064:1-71876,
e17bfcb9-b65d-11e8-b0b1-008cfa542442:1-71357,
e1a5fb6c-80f4-11e8-a561-008cfa5440e4:1-5510403,
e1c19e2e-bb14-11e8-97e9-008cfa5440e4:1-65282,
e1ec256a-8737-11e8-920e-008cfa544064:1-9417,
e1f7afac-7e8e-11e8-82fd-008cfa544064:1-64277,
e28f7a9f-c2f8-11e8-9c7c-008cfa5440e4:1-68107,
e312c2f6-c3bd-11e8-a556-008cfa542442:1-75526,
e35ef5a2-a773-11e8-bb5e-008cfa544064:1-58106,
e3bbc392-be41-11e8-b4ce-008cfa542442:1-6581231,
e3d06800-b40c-11e8-9fcf-008cfa542442:1-75904,
e403135f-88cc-11e8-957d-008cfa544064:1-39000,
e5e8e412-b4d3-11e8-96f6-008cfa544064:1-6442128,
e6674fd1-b01c-11e8-87b5-008cfa544064:1-6343336,
e66f6261-8a60-11e8-9232-008cfa544064:1-66867,
e67f443e-d11d-11e8-8d42-008cfa5440e4:1-65974,
e6a1d302-c549-11e8-bbc8-008cfa544064:1-68866,
e6c42d8f-8345-11e8-899b-008cfa544064:1-64489,
e71e9ab3-955c-11e8-9853-008cfa542442:1-63898940,
e73c3486-cc66-11e8-9a2d-008cfa544064:1-20455,
e7a40c37-802a-11e8-b8a7-008cfa5440e4:1-66008,
e89247a8-8b29-11e8-8bd9-008cfa5440e4:1-48384,
e8d63214-c7af-11e8-98ad-008cfa542442:1-70420,
e925a59c-a838-11e8-804a-008cfa544064:1-73481,
e9f19b55-bbdb-11e8-9eff-008cfa544064:1-72214,
ea016138-ac2a-11e8-8ffc-008cfa542442:1-70463,
eab84da9-81bb-11e8-901f-008cfa5440e4:1-64169,
ebea70a2-8280-11e8-baab-008cfa542442:1-62500,
ec088b38-adb6-11e8-bd96-008cfa542442:1-75788,
ec33ed21-a69a-11e8-806a-008cfa544064:1-54975,
ecb19710-ca00-11e8-9ddb-008cfa542442:1-72558,
ecdc7efa-93d2-11e8-bc91-008cfa544064:1-6453202,
ed0ea64c-b98a-11e8-b3b4-008cfa542442:1-6166922,
ed9079fc-b26d-11e8-a09f-008cfa5440e4:1-67948,
eec2f336-bfcd-11e8-9598-008cfa5440e4:1-69904,
f017ac88-c092-11e8-8d1a-008cfa542442:1-69700,
f04eaa36-ab65-11e8-96b7-008cfa542442:1-74351,
f0ea060e-a8ff-11e8-a722-008cfa5440e4:1-60803,
f0fd1daa-80f6-11e8-96e1-008cfa544064:1-66326,
f18f4e3f-b65f-11e8-ab64-008cfa5440e4:1-70044,
f245be9a-d039-11e8-bbde-008cfa542442:1-69809,
f25b83f2-c876-11e8-8b4d-008cfa5440e4:1-68992,
f2bc40f0-8672-11e8-a529-008cfa5440e4:1-9356,
f41a0596-a83a-11e8-8b36-008cfa542442:1-64949,
f42f53b7-c484-11e8-a66b-008cfa542442:1-72794,
f4927af1-a6ae-11e8-aa15-008cfa542442:1-249137,
f4b24428-c3bf-11e8-89f2-008cfa5440e4:1-6173019,
f4d1f6c1-cc68-11e8-a145-008cfa542442:1-1954220,
f50652f5-c93b-11e8-aee6-008cfa5440e4:1-65821,
f539e7cd-bf08-11e8-a732-008cfa5440e4:1-71385,
f59c119b-d11f-11e8-9927-008cfa544064:1-69835,
f7af77a9-ba51-11e8-9996-008cfa5440e4:1-6159825,
f7b7e287-a923-11e8-8893-008cfa542442:1-58453,
f823eb7e-bb16-11e8-8c90-008cfa542442:1-72813,
f82a6c91-b59a-11e8-a16f-008cfa5440e4:1-66282,
f83fc50d-8e3b-11e8-a3f8-008cfa542442:1-45447,
f922b2cf-c2fa-11e8-bf3b-008cfa542442:1-55629,
f9898d0b-c159-11e8-b974-008cfa542442:1-68487,
fa2c987d-ac2c-11e8-a9a1-008cfa544064:1-74559,
fa77ec3c-b1a8-11e8-b6ca-008cfa544064:1-70835,
faeced35-8739-11e8-9771-008cfa5440e4:1-9344,
fafde344-b0e3-11e8-bac0-008cfa5440e4:1-70828,
fb2d3df3-8a64-11e8-99a9-008cfa5440e4:1-62134,
fb6d7cc7-8282-11e8-8f48-008cfa544064:1-68014,
fced661b-5cf0-11e8-9b43-008cfa544064:1-34661946,
fd11ec6e-acf1-11e8-992c-008cfa5440e4:1-73512,
fd2a8a29-bca2-11e8-8406-008cfa544064:1-73243,
fe156d76-be43-11e8-958b-008cfa544064:1-72716,
fe804eb5-8fde-11e8-a2f4-008cfa5440e4:1-1059033,
fe8f6bb1-ca02-11e8-a059-008cfa544064:1-73859,
febed7be-c610-11e8-b337-008cfa544064:1-71551,
fef321ba-b4d5-11e8-9ed1-008cfa542442:1-72082,
ffbe008f-85ad-11e8-8f11-008cfa542442:1-62782,
ffefe288-b01e-11e8-b852-008cfa5440e4:1-70914,
fff4e4f9-8347-11e8-a135-008cfa542442:1-67163
There is total 561 lines and I've in fact had to remove some so as to be able to submit this report.
gtid_purged is similarly bloated.
Operations such as SHOW MASTER STATUS, SHOW SLAVE STATUS yield with hundreds of lines of text. We suspect there may be a scenario where gtid_executed overflows some text capacity.
The vast majority of UUID entries in the above belong to servers no longer in existence. Servers that used to be masters, failed over and got reprovisioned. This kind of operation is increasingly popular with kubernetes deployments and I suspect other users are similarly impacted, and certainly more will come and with greater urgency.
How to repeat:
Keep failing over your master, reprovision it with a new UUID, and gitd_executed will grow.
Suggested fix:
Requesting to add functionality to allow a DBA to prune gtid_executed and gtid_purged.
One suggestion by Mark Leith is to provide a new function in the form:
GTID_FORGET("<gtid_set>")
Such that I could `SELECT GTID_FORGET("fafde344-b0e3-11e8-bac0-008cfa5440e4:1-70828")`
Return value could be 0/1 for "found and deleted" and "not found so did nothing".
