Hi all
In the next few days, I will be adding a new index to the globalimagelinks table in the commonswiki database. creating this index will take a couple of hours, and the table will be locked during that time. This also means that replication of the commonswiki database will stop for the time it takes to create the index, and may take some time to catch up again after is is finished.
This task will be repeated for each database server, since commons is replicated to each. I will try to avoid doing it at peak times, but since I don't know yet how long it takes, and I should be around to watch it, I can't promise if it will work out that way.
-- daniel
On Tue, Mar 2, 2010 at 5:13 AM, Daniel Kinzler daniel@brightbyte.de wrote:
In the next few days, I will be adding a new index to the globalimagelinks table in the commonswiki database. creating this index will take a couple of hours, and the table will be locked during that time. This also means that replication of the commonswiki database will stop for the time it takes to create the index, and may take some time to catch up again after is is finished.
Since it's InnoDB, it should still be readable even if an index is being added; only changes to the table should be blocked, no? Replication will stop, but reads should continue to work fine.
Aryeh Gregor schrieb:
Since it's InnoDB, it should still be readable even if an index is being added; only changes to the table should be blocked, no? Replication will stop, but reads should continue to work fine.
To be honest - I don't know. We'll find out, I guess :)
-- daniel
Hi all
I have started adding the index on sql-s1-fast (rosemary) to see how long it takes. I will probably do the other db servers tomorrow and/or on Wednesday.
-- daniel
Daniel Kinzler schrieb:
Hi all
In the next few days, I will be adding a new index to the globalimagelinks table in the commonswiki database. creating this index will take a couple of hours, and the table will be locked during that time. This also means that replication of the commonswiki database will stop for the time it takes to create the index, and may take some time to catch up again after is is finished.
This task will be repeated for each database server, since commons is replicated to each. I will try to avoid doing it at peak times, but since I don't know yet how long it takes, and I should be around to watch it, I can't promise if it will work out that way.
-- daniel
Toolserver-l mailing list (Toolserver-l@lists.wikimedia.org) https://lists.wikimedia.org/mailman/listinfo/toolserver-l Posting guidelines for this list: https://wiki.toolserver.org/view/Mailing_list_etiquette
Maybe some file-transfering magic can be done to add the index in the other dbs by copying it from rosemary?
Daniel Kinzler schrieb:
Hi all
I have started adding the index on sql-s1-fast (rosemary) to see how long it takes. I will probably do the other db servers tomorrow and/or on Wednesday.
-- daniel
Daniel Kinzler schrieb:
Hi all
In the next few days, I will be adding a new index to the globalimagelinks table in the commonswiki database. creating this index will take a couple of hours, and the table will be locked during that time. This also means that replication of the commonswiki database will stop for the time it takes to create the index, and may take some time to catch up again after is is finished.
This task will be repeated for each database server, since commons is replicated to each. I will try to avoid doing it at peak times, but since I don't know yet how long it takes, and I should be around to watch it, I can't promise if it will work out that way.
-- daniel
On Mon, Mar 8, 2010 at 10:20 AM, Platonides platonides@gmail.com wrote:
Maybe some file-transfering magic can be done to add the index in the other dbs by copying it from rosemary?
No, file-copying doesn't work with InnoDB. You can only copy the full contents of all databases on the server, not even a single database at a time -- let alone a single table. Even with MyISAM, it would only work if you carefully synchronized replication between the tables, which would be more trouble than it's worth.
Aryeh Gregor wrote:
On Mon, Mar 8, 2010 at 10:20 AM, Platonides wrote:
Maybe some file-transfering magic can be done to add the index in the other dbs by copying it from rosemary?
No, file-copying doesn't work with InnoDB. You can only copy the full contents of all databases on the server, not even a single database at a time -- let alone a single table. Even with MyISAM, it would only work if you carefully synchronized replication between the tables, which would be more trouble than it's worth.
You may need per-table tablespaces, but i think it worked.
On Mon, Mar 8, 2010 at 1:29 PM, Platonides platonides@gmail.com wrote:
You may need per-table tablespaces, but i think it worked.
According to http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html, the process still basically involves shutting down the server so that the file is clean (no outstanding transactions). It's a lot more complicated than just adding the index, and would require server-wide downtime instead of just lagging one table. This doesn't even account for the problems you'd get if the copied table was replicated to a different point from the table it's replacing.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
Aryeh Gregor:
According to http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html, the process still basically involves shutting down the server so that the file is clean (no outstanding transactions).
As far as I know, this still won't work to move a table between two separate MySQL instances: "If you have a "clean" backup of an .ibd file, you can restore it to the MySQL installation from which it originated as follows".
Furthermore, the only way to do this consistently would be to stop replication at the same point on both servers, create the index on one, then copy it to the other. There is no advantage to doing it this way, since replication would have to be stopped on all servers for the duration of the index creation anyway.
- river.
toolserver-l@lists.wikimedia.org