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 …

Today, I am in Boston for training, I am shadowing Ronald Bradford, another MySQL consultant. It is fun and I learn a lot although it is not always easy to follow someone else mind. But we needed to evaluate the performance and the stability of a SAN. Do do so, we have used the bonnie++ tool. The server has 32 GB of memory and the filesystem was mounted under /data3. The command line used was the following:

mkdir /data3/testbonnie; nohup bonnie++ -d /data3/testbonnie -s 65000 -n 10 -r 32000 -x 5 -u root -g root &

After more six hours… the results were the following:

cat nohup.out | grep proddb | bon_csv2txt

Version @version@ ——Sequential Output—— –Sequential Input- –Random-
-Per Chr- –Block– -Rewrite- -Per Chr- –Block– –Seeks–
Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP
proddb06 65000M 71639 84 67884 13 54084 7 60194 60 126326 7 572.7 0 …

SiteCrafting is in the process of phasing out some of our older servers, and as an added bonus, the clients hosted on those servers are getting a MySQL jumpstart, leapfrogging over 4.1 to go straight from 4.0.24 to 5.0.32. Tragically, it's not quite as simple as dump | import. This is what I get for bothering my bosses for a few weeks not long after coming aboard about how nice stored procedures, updatable views, and triggers could be."The wonderful thing about standards," a wiser person than me once said, "is that there are so many of them." That's not the whole of it, though. One good thing about standards is that there are certain features one can generally rely upon to work, translate, port, etc. Assuming one works within them, rather than taking advantage of loopholes allowed by their not-entirely-compliant-but-we're-getting-there-and-anyway-isn't-this-way- easier-and-faster software. When people don't (and I'm not entirely innocent here), you …

We purchased MySQL support, and I installed the advisor software that comes with it, and checked out the tuning advisors. I feel like I’m back in the days before the Oracle wait interface, tuning everything with hit ratios, etc, and basically guessing which changes might help what how much. (Tune, cross your fingers, start over, try again. Repeat.) Like before I had ever read “Optimizing Oracle Performance” by Cary Millsap, or “Oracle Insights:Tales of the Oak Table”.

My #1 wish for MySQL at this point would be that version 6 would be instrumented, so that we could tune using wait events, to identify actual wait times and what contributed to them by how much.

For now, I get recommendations like “Query Cache Has Sub-Optimal Hit Rate“, which is giving me flashbacks… (In our case, I don’t have to worry much about this in particular, and the advisors are good enough to let me know that I might “Evaluate whether …

Whenever I decide to take a holiday I can usually guarantee that something cool will occur in the IT industry in my absence.

Now this holiday has been slightly different in many ways. I had to cancel my flight due to a sudden onset of Vertigo 1 day before I was supposed to fly. If like me you have never experienced Vertigo before, thank yourself lucky!. Having no sense of balance and intense room spin whenever you open your eyes is no fun at all, it took at least 2 days for me to stop being sick.

Having been able to actually use my laptop in the last couple of days I notice that the “cool thing” I usually miss on holiday turns out to be big trouble all over OpenSourceVille, the majority of which seems to revolve around licensing and intellectual property.

Who could miss the BSD vs GPL debate that I’m sure will rage for a long time after …

Administrative note:

I had a bit of a mishap involving my hand and a glass door pane. The OurSQL podcast will be on hiatus for a few weeks as I recover. I injured my left hand, and I’m a lefty, so I’m typing one-handed these days. I apologize for the break in the show schedule, and hope you’ll be able to hear new podcasts about MySQL very soon!

I had some fun yesterday with some odd performance problems. So I did a run with oprofile and got this:

561612   25.0417  /lib64/tls/libc-2.3.4.so memset
429457   19.1491  /usr/lib/debug/lib/modules/2.6.9-34.ELsmp/vmlinux clear_page
214268    9.5540  /usr/lib/debug/lib/modules/2.6.9-34.ELsmp/vmlinux do_page_fault
144293    6.4339  /usr/lib/debug/lib/modules/2.6.9-34.ELsmp/vmlinux do_no_page
94410     4.2097  /usr/lib/debug/lib/modules/2.6.9-34.ELsmp/vmlinux buffered_rmqueue
64998     2.8982  /lib64/tls/libc-2.3.4.so memcpy
59565     2.6559  /usr/lib/debug/lib/modules/2.6.9-34.ELsmp/vmlinux __down_read_trylock
59369     2.6472  /usr/lib/debug/lib/modules/2.6.9-34.ELsmp/vmlinux handle_mm_fault
47312     2.1096  /usr/lib/debug/lib/modules/2.6.9-34.ELsmp/vmlinux free_hot_cold_page
39161     1.7462  /usr/lib/debug/lib/modules/2.6.9-34.ELsmp/vmlinux release_pages
39140 …

Jeremy Cole’s post “On Falcon and the need to feel wanted” got me thinking…

The MySQL family is gearing up for a new arrival. Falcon is still a baby in the belly, but it seems to already be getting more attention than its older and accomplished siblings (eg InnoDB). Maybe the proud mother-to-be has issues because every time she looks in InnoDB’s eyes she can’t stop thinking about the new stepmother, Oracle.

She seems to think this one will be the best baby ever, and has pinned high hopes on what it will grow up to become. Part of her excitement is because she is rightly taken with the brilliant father, Jim Starkey. If Dad could invent MVCC and BLOBs, what will baby Falcon grow up to accomplish under his care?

Hopefully the proud parents will have a keen eye open to shortcomings as well as strengths. …

MySQL has a new section on their site about MySQL 6.0, which they are now calling “ready for pre-production testing”. I’m not sure when this section appeared, but I don’t spend much time on the MySQL site outside of the manual and downloads sections. Browsing around this new section I found a real gem: “Top Reasons Falcon is Cool” (or, as alternately titled on the page itself, “Top Reasons to use Falcon for Online Applications”1 … did someone forget to rename one or the other?). This page gives a top ten list2 of reasons why one should consider using Falcon, the new “not an InnoDB replacement, not at all!” but “really, you …

One of those projects I have been meaning to do is add dtrace support
to MySQL. Since I have been working with the Sun this has been on my
list to do.

What is DTrace? It is a way to dynamically trace/measure an
application for systems supporting DTrace.
http://en.wikipedia.org/wiki/Dtrace

The project of adding these really was more about research then about
actual code. While there are a number of resources on the web for
adding dtrace probes to C, there are few that exist for C++. Also, C+
+ requires a later version of Solaris then what comes installed on
machines that Sun ships, so you will need to have a fairly recent
install of Solaris Express to make use of this.

First I am going to define a couple of probes in probes.d:

provider mysql {

…