You are here: Home / 2007 / Archives for September 2007

NY Users Group – Analyzing MySQL Status and your SQL

by

This month I continued my Performance Analysis talks at the Local NY MySQL Meetup. Previous discussions can be found here.

Our focus was a more in-depth look at gathering and reviewing MySQL Status and your applications SQL statements using MySQL Proxy. Even after preparing the slides over the weekend Jan added more functionality that was particularly interesting. So today while addressing a client issues I further extended this work to do even more funky monitoring.

Today’s monitoring.lua script does:

  • Logs to file, Date/Time, Query Time, Response Time, Rows Affected, Normalized SQL and Actual SQL for each query
  • Has histogram of tables used with read/write figures.
  • Has histogram of SQL with avg and max execution times.

You get the best of both worlds, you can see SQL access live, get statistics of this, and then be able to drill down to every instance of a given SQL statement for more information.

You can get a copy of my slides Here. You can get a full tar of my demo work here. This includes MySQL Proxy, My Bench for some benchmarks and the Sakila Sample Database.

Ubuntu 7.04 Fiesty

by

I upgraded my work laptop to Ubuntu 7.04 Fiesty. The process was not as smooth as I expected. First it complained about not enough space on /var, so I cleaned up sufficient space. It continued to complain about 3 times.

The install itself I was hoping would have been automated, so when starting at night I wake up, and it’s done, however there were a number of installation errors, and prompts to keep or override configuration files, which I had to do manually at least 10 times. This of course made my laptop unavailable for a number of hours.

Today I’ve found the first real problem. My Open Office has lost all it’s fonts that I’ve loaded on in the past. Arial for example is no longer available. Of course searching on the web for installing fonts gives you several links, do you think any of these worked?

I found in reviewing my backup that the directory /usr/share/fonts/truetype/msttcorefonts/ had been removed. Re-instating this gave me back my fonts.

LAST_INSERT_ID(expr) – The lesser known usage

by

I am of the attitude, the day you stop learning something is the day you die. I’m not prepared to induce MySQL into both sides of that equation, however some days it never ceases to amaze me what little thing I didn’t know about MySQL.

Today I saw in reviewing SQL statements for an application SELECT LAST_INSERT_ID(). No big deal, that is expected, however I then saw UPDATE … SET id=LAST_INSERT_ID(id+1) WHERE …

Having never seen this syntax I was forced to review it’s usage. See MySQL Documentation


If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences:

1.Create a table to hold the sequence counter and initialize it:

mysql> CREATE TABLE sequence (id INT NOT NULL);
mysql> INSERT INTO sequence VALUES (0);

2. Use the table to generate sequence numbers like this:

mysql> UPDATE sequence SET id=LAST_INSERT_ID(id+1);
mysql> SELECT LAST_INSERT_ID();

The UPDATE statement increments the sequence counter and causes the next call to LAST_INSERT_ID() to return the updated value. The SELECT statement retrieves that value. The mysql_insert_id() C API function can also be used to get the value. See Section 17.2.3.35, “mysql_insert_id()”.

Seems like a layman’s idea of Sequence support, but you are still restricted with the problems of a manual implementation. Transactions support, and if you use MyISAM, table level locking.

MySQL Camp II – Memorable Quotes Day 1

by

Better late then never, this week I finally have the chance to catch up on some overdue posts. At the first MySQL Camp I made a list of memorable Quotes, see Day 1, Day 2, Day 3. I didn’t get as much chance as last time, however here are some pearls of the recent MySQL Camp II.

“I walked in of the street for the free food. I’m here for the free education” – Adrian.

“I’m his boss, I’m here to make sure he’s really here, and not playing hooky.”

“Mashups, collating the worse bugs from multiple API’s all in one place” – OSCON badge

“Compiles 114,000 combinations of MySQL that we are interested in.” Sandro – Skoll Project — You mean to say there are are combinations your not interested in.

“Ok, people this is the second site were are going to crash today. You have heard you have been slash dotted, now you have been camped.”

Jay turning down the lights, to the whole auditorm, not just the stage. “That won’t work. My paper isn’t backlit.” — Andrew

Jeremy: “It’s a porn site.”
Sheeri: “technocation.com it’s not a porn site, I work for a porn site.”

Jeremy: “You don’t realize how many Google properties you use, google reader was down, google search was down, google maps was down.”
Sheeri: “I had to use Yahoo maps, I feel so dirty.”

Jay: “How many people are interested in a tour of the MySQL Source Code.”
Jeremy II: “It is a guided tour, isn’t it.”

“I can’t remember if was the cold, hot or luke warm”. — Bob, In the backup talk.

“How many environments have it. They all have the presumption of it.”

DateTime vs Timestamp

by

I was asked a question today, “DATETIME vs TIMESTAMP. When to use which & why?”

It’s a good MySQL introduction question, here are some general considerations for choosing one.

Do you need Date values other then an EPOCH value? (i.e. before 1970) If the answer is yes, then DATETIME is required.
If you do not however, then TIMESTAMP is the best choice for a few reasons.

1. The TIMESTAMP columns uses 4 Bytes to record it’s value, while DATETIME uses 8 bytes. Using the smallest storage is always a best practice for all columns.
2. The TIMESTAMP column supports the CURRENT_DATE syntax in the CREATE TABLE command. This enables the column to have a default value for INSERT or for UPDATE, but not both. Indeed this is the only data type that allows for any default value that is not a constant.
3. All date functions (at least the ones I use) work equally as well with TIMESTAMP and DATETIME.

I have yet to find any benchmarking to indicate any performance differences of not selecting TIMESTAMP.

And just for a piece of trivia, the DATE datatype is 3 bytes, the TIME datetype is 3 bytes, so why is the DATETIME 8 bytes?
Yes, for those that intend to reply I do know the answer, however others readers may not. Comments please!