So there I was, gesticulating in front of the chalkboard, lecturing to 120 students. Topic: the beauty of advanced data structures. The door opens and the department chair walks up the aisle onto the stage and comes up to me. 242 eyes follow her progress through the room. She whispers to me that I must stop speaking in English. Officially, the course must be conducted entirely in Spanish.

Entirely in Spanish… I turn back to face the students. 120 students are waiting for me to speak. I realize that I don’t even know how to say simple constructions like “x plus y” let alone “advanced data structure” or “order log log amortized time complexity.”

Is this a newfangled anxiety dream? Can this replace the tried and true: I’m in front of the class but I forgot to put on my pants? Can this replace the classic: I’m back in high school and have one just more class to take; but I haven’t been attending and don’t know …

We have been testiing the MySQL RDS Service from Amazon here at RF, and so far it looks good for what we use MySQL for. We don't have that much data left in MySQL though, but some of the services are still mission critical, and we have had a couple running on RDS for a week or so.

I have to say I am pretty happy so far, but there are a couple of things you should know before you get to use the services. To begin with, you don't get a proper MySQL root account, i.e. no SUPER privileges. No big deal I guess, but this means there are a few things you cannot do. I was planning to migrate off the EC2 based server to RDS by setting up the RDS service as a slave, and then switching to the RDS service, but as SUPER privilege is required for CHANGE MASTER TO, I could not do this. And before you ask: Amazon DOES support slaves among the RDS servers, but this is set up using their own methods. Easy to use, yes, but not as flexible as the real …

Changes (as compared to 9.51) include:

Features:
* When performing CREATE/ALTER TABLE operation from inside the Schema Designer now a dialog with a tabbed interface similar to the CREATE/ALTER TABLE tab will open.
* It is now optional if Query Builder will create statements with ‘fully qualified tablenames’ or not.
* Implemented Alt+Ins, Alt+Del, Alt+Up and Alt+Down for inserting, deleting, moving up/down the rows in CREATE|ALTER TABLE interface when the focus is on respective grids. The tooltip for the toolbar buttons will now show this shortcuts.

Bug fixes:
* In Data Sync a low setting of ’max_allowed_packet’ on source server could result in CONCAT_WS() used for checksums’ calculation returning NULL . We did not consider this and as a consequence rows could fail to sync without warning. We will now abort the job with an error message (if user …

1. Create a separate data and log directories


# mkdir /var/lib/mysql2

# chown -R mysql.mysql /var/lib/mysql2

# mkdir /var/log/mysql2

# chown -R mysql.mysql /var/log/mysql2

2. Create a new mysql configuration file


# cp -R /etc/my.cnf /etc/my2.cnf

For Ubuntu Linux


# cp -R /etc/mysql /etc/mysql2


3. Edit the new configuration file


# nano /etc/my2.cnf

# change/add the following options

port = 3307

datadir = /var/lib/mysql2

socket = /var/lib/mysql2/mysql.sock

# add [mysql_safe]

log-error = /var/log/mysql2/mysql.log

pid-file = /var/lib/mysql2/hostname.pid

OR (For Ubuntu Linux)
…

Turn on the Slow Query Log (if it is not already turned on) and monitor what goes into it. Statements that are logged there are candidates for tuning. Note, however, that statements will only be logged there if they take longer than long_query_time seconds to run, so statements triggering full scans of small tables that execute very quickly may not show up. Once you have found tuning candidates, use the EXPLAIN statement on the queries to see which tables should have indexes added to them.
If you are using MySQL 4.1 or later you can use the --log-queries-not-using-indexes option to log all statements that do a full table scan, even if they would not otherwise qualify for the slow query log.
Note that full table scans are not necessarily bad, as long as they are confined to very small tables, so be sure to take table size into account as you review your queries and their EXPLAIN plans.

As always, I am a little late, but I want to jump on the bandwagon and mention the recent MySQL Cluster milestone of passing 1 billion queries per minute. Apart from echoing the arbitrarily large ransom demand of Dr Evil, what does this mean?

Obviously 1 billion is only of interest to us humans as we generally happen to have 10 fingers, and seem to name multiples in steps of 10^3 for some reason. Each processor involved in this benchmark is clocked at several billion cycles per second, so a single billion is not so vast or fast.

Measuring over a minute also feels unnatural for a computer performance benchmark - we are used to lots of things happening every second! A minute is a long time in silicon.

What's more, these …

Baron Schwartz from Percona has  just re-invented the SQLyog Query Profiler.
Refer: http://www.mysqlperformanceblog.com/2012/02/20/how-to-convert-show-profiles-into-a-real-profile/

The SQLyog Query Profiler was introduced in SQLyog 8.o in February 2009 – exactly 3 years ago. The funny thing is actually that Baron Schwartz reviewed it before release (at friendly commercial terms) at the time. The SUM-aggregation and the ORDERING that we did (and it was solely our decision – not Baron’s advice) in SQLyog 8.0 to the result from SELECT FROM I_S.PROFILES is the same as Baron now announces as “something I developed”.

Well ..

1) First let me make it clear that I am not complaing.  I believe Baron when he tells that he does not remember details of the SQLyog Query Profiler …

In a previous post, I demonstrated a simple Python database API with a syntax similar to jQuery. The goal was to provide a simple API that would allow Python programmers to use a database without having to resort to SQL, nor having to use any of the good, but quite heavy, ORM implementations that exist. The code was just an experimental implementation, and I was considering putting it up on Launchpad.
I did some basic cleaning of the code, turned it into a Python package, and pushed it to Launchpad. I also added some minor changes, such as introducing a define function to define new tables instead of automatically creating one when an insert was executed. Automatically constructing a table from values seems neat, but in reality it is quite difficult to ensure …

In my previous post I explained how it could be possible to recover, on some specific cases, a single table from a full backup in order to save time and make the recovery process more straightforward. Now the scenario is worse because we don’t have a backup or the backup restore process doesn’t work. How can I recover deleted rows?

We’re going to follow the same example as in my previous post so we need to delete the records of the employee 10008 from the table “salaries”. After the “accidental” deletion of rows you should stop MySQL, take a copy of the salaries.ibd and start it again. Later, we’ll extract those deleted rows from the ibd file and import them into the database. The time between the deletion of rows and the database stop is crucial. If pages are reused you can’t recover the …

I recently received a question asking about InnoDB and the ability to autoextend the ibdata files.

The question was basically this:
If the database has 1 ibdata file that is using autoextend but also has a max value set, will it start a new file once the max value is reached.

The answer is no it will not create a new file.You are able to use autoextend and have more than 1 ibdate file though.

For example I made a simple example via a virtual box.

Started using:
innodb_data_file_path            = ibdata1:20M:autoextend:max:100M
innodb_autoextend_increment        = 50M

 I downloaded the full employees dataset from launchpad.

Started the server …