About an hour ago, I got back from Washington, DC after spending four days attending a very productive MySQL performance tuning training session. I really enjoyed conversations with old and new friends including:

• Tobias Asplund, Instructor
  
• Ronald Bradford, Senior Consultant MySQL Inc.
• Michael "Mike" Amedeo Alaimo, IT Manager, Distributive Networks
  
• Stephen Spitzer, Learning Resource Center, USUHS
• Nyk Cowham, Systems Architect, Forum One Communications
• Joe Mansour, Web App. Programmer, and Donnie Lynn, DBA, Computing Center, …

Kathy Sierra’s closing keynote at linux.conf.au 2007 was a rather interesting one. I took away a lot from it, and while I might not be giving a summary of my thoughts here, one of her slides had a quote about a fake book she co-authored, that made me chuckle a little.

“So does this mean Ruby programmers are more emo than, say, Perl programmers?

MySQL people are definitely 5000% more emo than PostgreSQL people.”

Some will recognize that from a comment made in her blog post, Announcing The Emo Programmer book. But I took another parallel to the statement, because in the past few weeks, the MySQL community have been taking the recent Enterprise/Community announcements in a rather unwelcoming way.

MySQL are not getting rid of binaries in the Community release, as …

I'm a big fan of what I call cookbooks. Cookbooks are step-by-step directions that if followed, will yield the desired result. In fact, most of my cookbooks are copy & past enabled so that you copy a command from the cookbook and paste it into your window to execute the command. I insist my people use these established methods where available and I get pretty ticked off when they waste a lot of time trying to figure out what has already been learned.

I've installed Oracle on Linux about 50 times over the past few months. After the second succesfull install, I created a cookbook for both 9i and 10g. If followed exactly, any DBA will be able to install Oracle correctly in my environment. I created the cookbook and I know it like the back of my hand. I don't need no stinkin cookbook and proceeded to do a fresh install of 9.2.0.8 when the installer hangs at 18% linking naeet.o.

"Hmm, …

Late in 2006 MySQL decided to move to a split distribution model, Community and Enterprise. A lot has (apparently) changed since then, yet nothing has really changed since then. These things remain true: MySQL is freely available in both source and binary form – free as in freedom, and free as in beer. The MySQL community continues to get every single bug fix for free. The only real change the MySQL community sees is less frequent official binary builds.

MySQL statement-based replication is widely discussed, but I haven’t seen much about how to design a replication setup for low downtime and easy administration. Statement-based replication has inherent shortcomings experienced MySQL users know to avoid, but rarely write about. This article explains how to avoid problems, help your replicas stay in sync with the master, and recover from disasters more quickly. By the way, I’m fairly new to MySQL replication.

We all know disk based temporary tables are bad and you should try to have implicit temporary tables created in memory where possible, do to it you should increase tmp_table_size to appropriate value and avoid using blob/text columns which force table creation on the disk because MEMORY storage engine does not support them Right ?

Wrong.

In fact setting tmp_table_size is not enough as MySQL also looks at max_heap_table_size variable and uses lower value as a limit to for in memory temporary table after which it will be converted to MyISAM.

To make things more confusing this is not what you would read in MySQL manual as far as I understand it:
From http://dev.mysql.com/doc/refman/5.0/en/memory-storage-engine.html

MEMORY table contents are stored in memory, which is a property that MEMORY tables share with internal tables that the server …

Welcome to the 28th edition of Log Buffer, the weekly review of database blogs. It was a busy week in the blogs, but then, it always is. Let’s start with some open source software (OSS) items. Stephen Walli published two far-looking “thought experiments” on Once More unto The Breach. In the first, entitled Microsoft and [...]

So, a little gotcha:

The CHAR() and VARCHAR() types are different types. MySQL silently converts any CHAR() fields to VARCHAR() when creating a table with at least 1 VARCHAR() field.

http://dev.mysql.com/doc/refman/5.0/en/silent-column-changes.html

If any column in a table has a variable length, the entire row becomes variable-length as a result. Therefore, if a table contains any variable-length columns (VARCHAR, TEXT, or BLOB), all CHAR columns longer than three characters are changed to VARCHAR columns. This does not affect how you use the columns in any way; in MySQL, VARCHAR is just a different way to store characters. MySQL performs this conversion because it saves space and makes table operations faster.

However, that’s not entirely accurate. Because according to the manual page at …

Download the video at:

http://technocation.org/videos/BostonMySQLJanUserGroupBrianAkerLg.wmv
- 520 kbps, 320 x 240, 354M. Small size, low quality, but you can still see the slides and hear everything.

http://technocation.org/videos/BostonMySQLJanUserGroupBrianAkerSm.wmv - 45 kbps, 320 x 120, 29M. Small size, low quality, but you can still see the slides and hear everything.

Technocation, Inc. received a donation of a Sony Handycam DCR SR80 (http://tinyurl.com/yvyfam ), extra-long battery, microphone (proprietary Sony that goes with the camera).

In a short sentence: I am impressed. The sound …

A while ago when I started my first job in LA, I was using MSSQL. I was told to never use count(*), and rather to use count(1). Several people insisted this performs better than count(*), and since I really didn’t care to argue or look into it, I just started doing it.

Fast forward to today, where I am still writing count(1), but on MySQL. I’m at a different company now, but working with a lot of the same people. The person who used to be my boss is now my equal, and while mytop was running, noticed the count(1) and was happy to see it. Which reminded me to wonder - does it even matter?

(Using a table called questions, 130K rows)

EXPLAIN SELECT count( 1 )
FROM questions;

Says it’s a simple select, and it’s using the index. Fantastic.


explain select count(*) from questions;


Same result as above. I’ve always assumed that the database …