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.

Creation of Temp Table Anomaly

One of our replication slave broke last night. This is another strange one. I have multiple slaves running under version 4.1.19 (yup, this is an old one, hehe). But for some strange reason, only one replication slave exhibited the following behavior: It would seem like it cannot create a temporary table while other replication slaves have no problem with it. See below for the example.

mysql> create temporary table abc (id int);
ERROR 1046 (3D000): No database selected

mysql> \u customer
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> create temporary table abc (id int);
ERROR 1049 (42000): Unknown database 'customer'

mysql> create table abc (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> \u test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> create temporary table abc (id int);
ERROR 1049 (42000): Unknown database 'test'

mysql> create table abc (id int);
Query OK, 0 rows affected (0.01 sec)


As you can see, creating permanent tables is not a problem. But creating temporary tables on different databases is an issue. I wonder if anyone has seen this? And take note that only one replication slave is showing this error, while other slaves are working just fine.

Weird SQL Error while Replicating

I encountered this weird SQL error / anomaly.

I have 5 replication slaves with one master. On the master, an insert statement was issued and it was replicated to all of the slaves except to one which I will call weird_slave. The weird_slave (MyISAM) complained that there was some SQL syntax error. I checked the insert statement and it looks pretty simple.

Also, the weird_slave has already been running for more than a couple of months now and it has received some variation of the insert statement.

I was thinking that the table could be corrupt and issued a check table and everything returned okay.

So, the question is why would the weird_slave complain about an SQL syntax error while the other slaves were okay with it. Sadly, I could not reproduce the error again. Also, I've tried to re-insert the same SQL statement on the weird_slave again and again and it was accepting it without complaining.

Cause of Replication Delay? I/O or SQL Thread?

I was thinking of this and could not figure it out or maybe I am just confused... Hehehe.

If MySQL is lagging behind the master (7,000 secs.), would it be caused by the I/O thread being slow or by the SQL thread (of course, this is assuming that I only have these two options to choose from)? If it is the I/O thread, what should I be looking for?

I've always assumed that the I/O thread would not cause a huge replication delay as it is just reading the binary logs from the master. So, if the Master_Log_File and Relay_Master_Log_File are the same and the Exec_Master_Log_Pos and Read_Master_Log_Pos are the same too, then I can assume that the replication delay is not based on the I/O thread, right?

And does anyone know how to compute the Seconds_Behind_Master???

MySQL Conference 2008

It's my first time here and it is really nice. I find it interesting to see so many people interested in MySQL.

MySQL - Sun Party

I attended the MySQL-Sun cocktail party yesterday (March 25, 2008) at the Jillian's @ The Metreon (101 Fourth Street, San Francisco, CA).

Live JasperSoft Seminar

I was lucky enough to attend the JasperSoft Seminar held at their headquarters in San Francisco yesterday (March 19, 2008). There were basically 3 sessions and I personally did not get much from the seminar.

What I found interesting was that I was able to talk to Guilio Toffoli who created the iReport which is basically the GUI software to create JasperReport xml files. We talked about performance limits of the software, problems that I had with designing the queries using SQLeonardo, and other stuff.

What's the Point?

The point is part of the line.
The line is part of the circle.
The circle is the wheel of the car.
The point is that we will never know what the point is.

10 traits to look for when you’re hiring a programmer

I found this interesting article that talks about things to look for in a programmer which I think is also suitable for a DBA.

http://blogs.techrepublic.com.com/10things/?p=319

JapserServer Evaluation - Part 2

It's been almost a month now since I have been evaluating JasperServer. And it has been quite hard to evaluate the software without a proper documentation. Fortunately, I was able to convince my boss to buy the books Definite Guide to iReport, Definite Guide to JasperReport and JasperServer Ultimate Guide.

What I found interesting in all this experience is that just like any open software, it is connected to a lot of different applications. I had to study Tomcat and Ant in a thorough manner to get through JasperServer. In the end, I'm actually quite happy that I had to go through this experience.

Anyway, I am now quite comfortable with JasperServer. I am now able to create reports on the fly and I'm able to understand what it is now doing in a more in-depth manner. In fact, I will be giving a demo to our boss sometime soon and who knows we might use JasperServer in our company.

MySQL Documentation

Sometimes I don't like MySQL for the way it documents its software. And I don't like people who would try to define something and get the definition from the MySQL documentation. Let me give an example, MySQL defines Handler_read_key server status variable as the number of requests to read a row based on a fixed position. This value is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan entire tables or you have joins that don't use keys properly.

If you search around the web, people will also use this definition. But what does this actually? No one would try to go deeper because they actually don't know. Don't get me wrong, I also don't know what this actually means.

Let me dissect the way this definition is written, what does number of requests means? Is it the number of queries? What does a "fixed position" mean? And how is this related to being high when a lot of sorting is done? And finally how is this related to table scan?

MySQL Re-packaged RPM

Sometimes it would be handy if you know how to re-package a MySQL rpm. You might wonder how this will benefit your system. Well, for starters, consider if you have custom-built scripts that needs to be in all the database servers. Also, consider if you have a standard way of organizing your database structure such as separating your logs with your data files.

A re-packaged MySQL rpm would come very handy in these situation. I've used this a lot in my companies and if you don't have a Red Hat OS, then consider writing a script to handle your automated MySQL installation.