Making changes to many tables at once
As an alternative to another recent blog post that answered the question “how can I truncate all the tables in my database,” I thought I’d show another way to do it, which does not use the INFORMATION_SCHEMA.
$ wget http://www.maatkit.org/get/mk-find
$ perl mk-find --exec 'TRUNCATE TABLE %D.%N'
The other example is how to alter MyISAM tables to be InnoDB. That one’s easy, too. Let’s alter all MyISAM tables in the ‘test’ database:
$ wget http://www.maatkit.org/get/mk-find
$ perl mk-find test --engine MyISAM --exec 'ALTER TABLE %D.%N ENGINE=InnoDB'
If you want to print out the commands instead of executing them, you can just use –printf instead of –exec.
Why would you do it this way instead of through the INFORMATION_SCHEMA database? I don’t think this can be said too often: querying the INFORMATION_SCHEMA database on MySQL can completely lock a busy server for a long time. It can even crash it. It is very dangerous. So whenever I mention it, I mention the dangers of using it. I use it too sometimes, but only when I know the server I’m working on.
Another way to do it: find the .frm files in the directory, cut off the .frm extension, and call mysql -e from commandline….all with a simple shell script.
Sheeri K. Cabral
29 Oct 09 at 7:27 pm
BTW, I didn’t see a comment from you on Trent’s post, so I put one there about
But why would someone be truncating (or doing any action) all the tables on a *very busy* server? The caveat is good, but you didn’t put it where folks seeing Trent’s post would see it…please make sure to do that.
Along those lines, is there any open bug in Ma’atkit changing mk-table-checksum not to use the INFORMATION_SCHEMA to find all the tables?
Because that took forever on one of our client’s servers, and we had to write a wrapper script and call mk-table-checksum on one database at a time because of it.
I know you feel strongly about being able to checksum data, so I’m sure this is in the works, but I wanted to check, since I didn’t recall seeing a bug about it, but I haven’t looked recently (and I could be wrong, there could be a bug report already about that).
Sheeri K. Cabral
29 Oct 09 at 7:39 pm
Hi Sheeri,
AFAIK mk-table-checksum doesn’t touch INFORMATION_SCHEMA, can you elaborate? It does not touch it with any queries etc, and all the code that iterates over databases excludes it (and lost+found too). So if there’s a way it’s messing with it, we must stop that.
Xaprb
29 Oct 09 at 8:31 pm
Hi!
I have a lot (aprox. 1500) databases on my server. From time to time I have to alter one specific table from all databases (add coolumns, for example).
Currently we achieve this by running a simple script to print the “Alter table” command for all databases and we run the output of this script when the server is not too busy.
My question is: if I use this tool (mk-find) to do this job, does it lock all the tables while it runs?
Celso Endo
30 Oct 09 at 10:50 am
Baron — it uses SHOW commands, which can use the same code path as INFORMATION_SCHEMA.
We had the actual experience that trying to run mk-table-checksum (using chunking) on a busy server with thousands of tables was working for over an hour just on getting database and table names.
SHOW DATABASES
and
SHOW FULL TABLES FROM db
are both used….
Sheeri K. Cabral
30 Oct 09 at 11:12 am
Celso — ALTER TABLE is going to lock each table as it runs, there’s no getting around that.
Sheeri K. Cabral
30 Oct 09 at 11:15 am
Sheeri, SHOW DATABASES and SHOW TABLES shouldn’t lock the server the way a query to INFORMATION_SCHEMA.TABLES does, afaik. However, what the tool does is pretty brain dead from the user point of view: it collects all the information it needs before it starts to do anything. It should gather one table, work on it, get the next, etc. This is an open issue that we’ve started work on. [I believe] it is simply a user experience problem, not a server load problem. However I am wholly willing to be wrong — unfortunately, if I am wrong, I don’t see a thing that can be done about it.
(In some cases it’ll also use SHOW TABLE STATUS a table at a time, which I’d also like to avoid where possible. Some of the features require it, though. INFORMATION_SCHEMA.TABLES basically does SHOW TABLE STATUS for all tables in all databases, apparently with a frickin’ global mutex to boot. That is the real killer.)
Xaprb
30 Oct 09 at 1:43 pm
However, what the tool does is pretty brain dead from the user point of view: it collects all the information it needs before it starts to do anything. It should gather one table, work on it, get the next, etc.
Which is it — does it collect all the information first, then start work, or does it get the information for one table, then works on it, then gets the information for another?
It’s still a problem that it takes a long time on a busy server.
it is simply a user experience problem, not a server load problem.
Um, what does that mean? Yes, I experienced that mk-table-checksum took a very long time going through each database in SHOW DATABASES and running SHOW TABLES.
That’s why I mentioned the solution of using “ls” in data directories — you can easily do
ls $DATADIR/*.frm
and find all the tables (the same way MySQL does!). It doesn’t replace every single use case for INFORMATION_SCHEMA, and you have to have the right filesystem access, but doing an “ls” takes even less time than mk-table-checksum’s way of using SHOW DATABASES and SHOW TABLES.
I see that’s how mk-find works too, so that would take a long time on servers with thousands of tables, too. :(
Sheeri K. Cabral
30 Oct 09 at 1:52 pm
Sorry for being unclear. It currently collects everything and calculates chunks, etc etc — and then starts. It should do a table at a time.
Did you see SHOW TABLES itself running very long? I know I’ve seen that running long too, but have never seen it blocking anything else that’s going on. In directories with tens of thousands of files, even “ls” will run slowly depending on the filesystem.
Running a filesystem command is a non-starter, there are way too many scenarios where that is not possible.
Xaprb
30 Oct 09 at 2:41 pm