Strategy for Understanding Data in a Database

I recently started in a new company where a database does not have any data dictionary at all. I'm now doing a brute force work where I am trying to create my own data dictionary but I was wondering what are your strategies in learning the data inside a database?

Missing Speakers in MySQL Conference 2009

I was looking through the list of speakers in this year's MySQL Conference and I just noticed that some good speakers will not be presenting in the conference. Well, the term 'good speakers' is relative, but I enjoyed listening to their presentations.

This is my list of the 'Missing' speakers for this year.

1. Baron Schwartz
2. Peter Zaitsev
3. Jay Pipes
4. Jeremy Cole
5. Arjen Lentz

I guess they are too busy with other stuff.

Flush Tables with Select Query

I just realized that "Flush tables with Read Lock" will not work when a Select query is not yet done.

What happened was there was a runaway query where it was doing a self-join and aggregating the result. The table was about 10 million rows.

When the backup script ran, it issued the flush tables first but since it saw that a select query was still running, the flush table also waited until the query finished which it never did.

In the end, the backup did not work because of the runaway query.

I'm pretty sure that I have read the previous statement in the manual before but it is interesting when an actual warning / caution from the manual actually happens in production.

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.

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?

Integrated Backup Plan

So... last night couple of our application servers crashed. It was apparently due to some new codes that were released although I'm not yet sure. Here is the summarize version of what transpired:

1. Sysadmin got paged that the application servers were not responding.

2. Sysadmin called the DBA team to check the database connections... We said everything looks okay on our end.

3. Sysadmin called the developers next and they found out that the new codes caused the system to go haywire.

4. When the developers tried to revert back the old codes, apparently some of them did not checked-in the older version and it caused a couple missing database privileges to occur. Also, some needed tables were also missing.

5. From that time on, we were really in trouble and sufficient to say it was a very very long night.

The lesson: maybe it is nice to test an integrated backup plan once in awhile. We always think of doing database backups but it is just one component of the whole system. Anyway, that was what I was thinking during the time and the number of hours I have lost because of the incident.

MySQL SandBox Gem

Consider these scenarios:

1. You need to test some new configurations in your my.cnf and you need to reboot MySQL.
2. You want to run some benchmarking on your database.
3. You want to try some new replication ideas that involves creating new replication structure, stopping the slaves, doing failovers, or any ideas related to replication.

And, most importantly, you want to do all of the above without disturbing your production databases.

Answer:

MySQL Sandbox. A lot of people have written about this gem but until you have use it, you will not realize how simple and yet valuable this tool is.