This week I have the honer of write Log Buffer. If you’re a fan of everything new and shiny, just sit back, because you’ll enjoy this weeks edition.

For those who’ve never heard of Arnold Daniels. I’m a software engineer at Javeline, an active member of the MySQL community and the author of lib_mysqludf_xql which is part of the MySQL UDF Repository. Well thats more than enough about me. I’m not here to get a job or a date, but to bring you the latest news.

Dreaming of Oracle features
Oracle has recently brought out version 11g of their database server. Just when you thought other DBMSs were catching …

A few days ago someone made the comment that Pentaho Data Integration (Kettle) was a bit too hard to use. The person on the chat was someone that tried to load a text file into a database table and he was having a hard time doing just that.

So let’s go back to basics in this blog post and load a delimited text file into a MySQL table.

If you want to see how it’s done, click on this link to watch a real-time (non-edited) flash movie. It’s 11MB to download and is about 2-3 minutes long.

Until next time!

Matt

My quiz about SQL mode was a bit more difficult than I expected. The question was which ones of the following statements are affected by a change from the default SQL mode to ANSI mode.

I used the INFORMATION_SCHEMA tables for examples throughout. This is to make it easy to try for yourself, without needing to create tables etc. If you use the Query Browser to try this out you should beware that setting the sql_mode might not work, since the Query Browser by default creates a new database connection for every statement. The easiest way to check this si to use the 'set global sql_mode' command, but you should not do this on a production database, since strange things might happen....

If you want to read the documentation, you find it here.

Now for the solution...

a) SELECT concat(table_name,' …

The NetBeans IDE has simplified the steps to deploy JRuby on Rails application on GlassFish. This blog explains the steps and is an update to screencast #web6.

1. Download the install the latest NetBeans 6 Nightly. I downloaded the Ruby pack by clicking on the "Download" button in the Ruby column.
2. Create a new Rails Application
   1. Right-click in the Project window and select "New Project...". Take all the defaults as shown below:
      
      
   2. Click on …

Returning to my post about memory consumption with Stored Function at this moment I want to annul that post. Trying to repeat described behavoir I cannot repeat that again and probably there was something wrong with the whole box, not MySQL. I apologize if it was confusing. Although I am almost sure this topic will popup somewhen.

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     1.7452 …

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 …