We presented a webinar along with MySQL last week. The topic was MySQL Backup from the perspective of an Oracle DBA. We got some really good questions at the end of the webinar. Here is a transcript of Q&A:

Q from Trey: Will this Webinar be available online for viewing later?

A from MySQL: Yes. It will be on our site in a few days at http://www.mysql.com/news-and-events/on-demand-webinars/

Q from Randy: Is a copy of presentation available on web? Where?

A from Zmanda : slides are available on Zmanda Network: http://network.zmanda.com/

Q from Jing: Converting database from oracle to MySQL, any limitation regards the version compatibility?

A from MySQL: We provide a free GUI migration tool that helps migrate Oracle data objects to MySQL. For a complete and very detailed …

I made a mistake in using the delete command in our production environment. I had 17 million rows and I only needed to keep 3 million rows, so I issued a delete sql to erase the 15 million rows. Bad move! Because I immediately saw how the I/O of the machine went up very high and the delete sql caused a huge load spike. On top of that, I had to run an optimize table command because MySQL did not free the deleted space and this caused another load spike.

In retrospect, I should have done the following items:

1. Instead of delete sql, I should have issued a create new table and select the 3 million rows from the old table. This would have been faster and it would not be necessary to run optimize table.

2. If I have to use delete and and optimize table, I could have copied the table to a non-production database and run the commands there. After it has finished executing all the processes, I can copy the new …

Sun’s Allan Packer (author of Configuring and Tuning Databases on the Solaris Platform) has published a lengthy and fascinating blog post on the future of proprietary databases in which he discusses the likelihood of MySQL, PostgreSQL et al challenging Oracle, IBM and Microsoft.

Packer’s multi-part post covers a number of areas including:

Feature Stagnation In The Traditional Database Market
License Costs: the Soft Underbelly of Proprietary Databases
…

This is a quiz that has a aha! solution. Not so trivial, though. It requires some thinking.

Given this table:

create table t1 (
dt datetime not null,
primary key (dt)
);

Task: insert exactly 1 million records in table t1, with the following constraints:

• Use a maximum of 5 (five) SQL statements;
• Use only the MySQL interactive command line client;
• No shell commands;
  
• No loading of scripts;
• No inserts from existing tables in your system. You must assume that t1 is the only table in your entire database;
  
• No MySQL Proxy;
• No stored routines, triggers or events;
• Each statement must be not longer than 75 characters;
• UPDATE. No modification of table t1;
• No LOAD DATA.
  

Prize: fame and fortune (i.e. your name quoted in these …

A lot of you may already know this, but I am willing to bet there are more that don’t. I’m talking about the tee command in the bash shell, and in MySQL. For our purposes, we’ll talk about the tee command in MySQL.

Problem: You have a series of SQL statements whose results take up a few screens worth of output, and you need to take this output and send it to someone else (A DBA, MySQL Support, your mentor). You could just do a copy/paste from your terminal, but what if you realized in the end that your scroll back buffer isn’t as large as you thought it was?

Solution: Tee. Apparently, the mysql client comes with tee.

mysqlshell> tee mysqlog.sql ;
Logging to file ‘mysqlog.sql’
use dbname;
select foo from bar;
….
mysqlshell> notee;
…

I believe I found a new method to calculate the median in MySQL. I would not be surprised if this method has been figured out by somebody else already. However, I can't seem to find any resources on the internet describing this method, so for now I flatter myself by assuming the method is original.

(Please do post your comments to this blog to correct me on that should I be wrong so I have a chance to rectify.)

The method I'm describing is a one-pass, pure SQL method. It does not require subqueries, cursors or user variables. However, it does rely on the MySQL specific functions GROUP_CONCAT() and SUBSTRING_INDEX()

I'll be maintaining …

Last week we’ve found that our application runs not so fast as we wished. This was a complicated webservice application with many services/methods and each serivce method is accessing MySQL (InnoDB) database through the Hibernate layer.
In Hibernate layer we used Criteria API to make queries, so in the source code is impossible to view exact queries which will run.
So I’ve found two solutions how can we profile our application.

1. It is possible to turn on hibernate option “hibernate.show_sql=false” and check each query in the mysql console with MySQL query profiling

2. And the second solution was Elvyx.

I’ve chose the second one.

It is needed just to replace jdbc driver with the Elvyx’s one, setup Elvyx driver to use the driver you …

This release fixes a few bugs, adds minor features, and adds some debugging support to shared code. I'm working on the Nibble sync algorithm for mk-table-sync, and someone has found a few more bugs with mk-parallel-dump, but those might take me a while to complete.

With InnoDB, I was trying to figure out if ANALYZE TABLE blocks other clients or if it uses MVCC ("in theory" it probably could). The answer is that it does seem to lock for a small amount of time, but the results are not as expected. A second execution of ANALYZE TABLE takes considerably longer than the first:


mysql> show processlist;
+----+------+-----------+------+---------+------+-------------------+-----------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info
|
+----+------+-----------+------+---------+------+-------------------+-----------------------------------------------------------+
| 1 | root | localhost | test | Query | 235 | Sending data | select count(*) from …

I was coincidentally doing a show table status on couple of tables when I saw that one of the MyISAM tables has reach the 4 GB default limit. The table stores all my show processlist parsed data and apparently my parsing and importing script did not notify me of any errors like the table was full or something. Anyway, I solved the problem by running the command below.

ALTER TABLE MAX_ROWS = 1000000000;

A good discussion on MyISAM 4 GB default limit can be found on this link:

http://jeremy.zawodny.com/blog/archives/000796.html
http://dev.mysql.com/doc/refman/5.0/en/full-table.html