Wednesday, August 13, 2008

Effect of Multiple Table Scan

Let's assume that we have the following scenario happening in a database server (also assume that this is the only thing happening in it):

5 table scans started at the same time, hitting different databases but the table structure and number of rows are all identical (roughly 200 million rows).

When I do a mysqladmin extend -r -i 10 | grep Handler_read_rnd_next

It shows that this variable is being updated. The question is since there are 5 threads running, are all of them hitting the Handler* subroutine one after the other? If so, would this slow down the whole table scanning process?

I tried to test this by running 1 table scan and it took about 1 hour to finish. And if I try to run 2 table scans together, it takes about 4 1/2 hours to finish.

Wednesday, August 6, 2008

Ideas on Syncing Database Structures

I want to synchronize the table structure of two databases and both databases are not in production. Unfortunately, I've only seen SQLyog (a commercial software) to be able to offer this feature. Mk-table-sync is not able to do this as it is only concerned with synchronizing the data of two databases.

I wonder how hard it is to synchronize two databases, assuming that it is only a one-way synchronization. Database A will have the updated table structures and database B needs those 'updated schema sql' to sync itself with database A.

So, if database A has new tables, then the output would be some create table sqls.
If database A has some updated table schemas (eg. new columns added, column type modified), then the output would be some alter table sqls.
If database A has new indexes, then the output would be some alter table sqls again.
If database A removed some tables, then the output would be some drop tables sqls.

Oh, and I thinking of using a mysqldump with no data option as a starting point to compare the database structures.

Following this logic, am I missing anything else to synchronize two databases?