| Subject | Repo | Branch | Lines +/- | |
|---|---|---|---|---|
| Auto add indexes for date-time and uuid v1 type columns | eventlogging | master | +53 -3 |
Details
Details
| Status | Subtype | Assigned | Task | ||
|---|---|---|---|---|---|
| Resolved | Ottomata | T150369 Record an event every time a new content namespace page is created | |||
| Resolved | Ottomata | T170925 Ensure indexes are added to `meta_dt` and unique `meta_id` fields in eventbus MySQL tables in eventlogging databases. |
Event Timeline
Comment Actions
I manually added these indexes to the 4 eventbus tables on db1046:
CREATE TABLE otto.mediawiki_page_move_1_u AS SELECT * FROM log.mediawiki_page_move_1 GROUP BY meta_id; ALTER TABLE otto.mediawiki_page_move_1 ADD UNIQUE INDEX `ix_meta_id` (`meta_id`), ADD INDEX `ix_meta_dt` (`meta_dt`); RENAME TABLE log.mediawiki_page_move_1 TO otto.mediawiki_page_move_1_no_index; RENAME TABLE otto.mediawiki_page_move_1_u TO log.mediawiki_page_move_1; CREATE TABLE otto.mediawiki_page_delete_1_u AS SELECT * FROM log.mediawiki_page_delete_1 GROUP BY meta_id; ALTER TABLE otto.mediawiki_page_delete_1_u ADD UNIQUE INDEX `ix_meta_id` (`meta_id`), ADD INDEX `ix_meta_dt` (`meta_dt`); RENAME TABLE log.mediawiki_page_delete_1 TO otto.mediawiki_page_delete_1_no_index; RENAME TABLE otto.mediawiki_page_delete_1_u TO log.mediawiki_page_delete_1; CREATE TABLE otto.mediawiki_page_undelete_1_u AS SELECT * FROM log.mediawiki_page_undelete_1 GROUP BY meta_id; ALTER TABLE otto.mediawiki_page_undelete_1_u ADD UNIQUE INDEX `ix_meta_id` (`meta_id`), ADD INDEX `ix_meta_dt` (`meta_dt`); RENAME TABLE log.mediawiki_page_undelete_1 TO otto.mediawiki_page_undelete_1_no_index; RENAME TABLE otto.mediawiki_page_undelete_1_u TO log.mediawiki_page_undelete_1; CREATE TABLE otto.mediawiki_page_create_1_u AS SELECT * FROM log.mediawiki_page_create_1 GROUP BY meta_id; ALTER TABLE otto.mediawiki_page_create_1_u ADD UNIQUE INDEX `ix_meta_id` (`meta_id`), ADD INDEX `ix_meta_dt` (`meta_dt`); RENAME TABLE log.mediawiki_page_create_1 TO otto.mediawiki_page_create_1_no_index; RENAME TABLE otto.mediawiki_page_create_1_u TO log.mediawiki_page_create_1;
Comment Actions
Change 365999 had a related patch set uploaded (by Ottomata; owner: Ottomata):
[eventlogging@master] Auto add indexes for date-time and uuid v1 type columns
Comment Actions
What is the mysteriously named meta_id field? Don't we also need an index on database or meta_domain? It seems like the timestamp index should probably be a 2 column index with database or meta_domain as the first field (since we're always going to be looking at stats per wiki).
Comment Actions
@kaldari: https://wikitech.wikimedia.org/wiki/EventBus#Metadata
Since fields are flattened in MySQL, meta_id is meta.id, which is like the EventLogging Analytics uuids.
I needed to add indexes on these fields for maintenance purposes. I couldn't backfill properly without having indexes on meta_id.
Let's talk about database and meta_domain in T170990.
Comment Actions
Change 365999 merged by Ottomata:
[eventlogging@master] Auto add indexes for date-time and uuid v1 type columns