The tutorial will cover the basics, several practical scenarios, tips and tricks, and some sneak peeks of the latest enhancements being developed.

MySQL provides a number of different tools for tuning SQL statements. Some of the key SQL tuning tools include:EXPLAIN - Displays execution plans generated by the MySQL Cost Based Optimizer.Status Variables - Contains statistics on SQL run time activity.Profiling - Contains run time statistics on each phase in the execution of individual SQL statements.Main Phases in Processing a SQL

one piece of jay’s advice to mysql got me thinking about something that bugs me about drizzle development. jay said: Make all decisions open and transparent: For the non-maintenance team, make a policy that all decisions about the kernel design be done in an open forum, with the community able to participate in the discussion. Have stewards that are willing to negotiate the design decisions with the community and do everything in a transparent manner.

since jay is one of the key lieutenants in the drizzle effort, it only seems to fair to put them up against that standard. one thing i have noticed is that there is relatively little discussion on the drizzle mailing list about all the coding that is going on. …

Since I didn’t have a blog server available in the last few months, Matt offered me to use his BigDBAHead and I accepted.  I must admit, I am not a big blogger but with WaffleGrid, there is a need to communicate more.  Since I have done most of the InnoDB hacking, here is a bit of status and roadmap information.

1. Async memcached_set

Believe it or not… all our results up to now have been done with sync IO…  We were discussing the need of doing async when I discovered memcached_behavior_set and  MEMCACHED_BEHAVIOR_NO_BLOCK. Sometimes, you just feel suddenly tired.  Preliminary results are very interesting, basically, the overhead of the set vanished. We will need to verify no coherence problem are introduced and redo our tests.

2.  Cache coherence between startup

Following a suggestion from Mark Atwood, the memcached key as been …

Yves and I had the rare opportunity to share a gig this week, which means we have been spending a lot of time over meals discussing how to improve Waffle Grid.  Yves will be blogging about some of the things we talked about very soon, I will also be publishing my EC2 experiences with Waffle Grid very soon.  Stay Tuned!

The more databases you have in your cluster the greater the probability they’re going to fail.

It’s basically MTBF/N until you have one of these boxes crash. If you have 10k machines expect multiple failures per day.

If you’re performing realtime writes to these databases you can lose a DB in the middle of a write.

Now what? Do you lose transactions?

Most people using InnoDB/MyISAM have used write caching controllers to solve this problem.

You buy this expensive card with 128MB of memory which temporarily caches writes. The card has a battery so if you lose power you spin the disks until you can get your data onto the disks.

But as I mentioned before, these devices are expensive. Expect them to add another 20% on the price of your cluster.

This doesn’t sound like a ton of cash if you have one or two machines but if you’re buying 50-1000 it’s a significant …

Sun Microsystems today announced that Ladok, Sweden's largest university database will be migrated to MySQL™, the world’s most popular open source database. Five million people are registered in the Ladok system, a national system used for the documentation of academic information. University administrators as well as more than 350,000 active students use information from the Ladok-system on a regular basis in order to manage follow–up procedures, track academic results or look up grades.

Born in Northern Norway 41 years ago and Lars has lived in Trondheim for the last 22 years. He is a Physicist by education, has a master degree on semiconductor heterojunctures and has earned a PhD on high temperature superconductors. He worked briefly on nuclear power fuel optimization and petroleum related rock mechanics before joining Clustra in the year 2000, which was acquired by Sun Microsystems in 2002. Clustra was a database software vendor that specialized in clustered, high-availability databases that were required by telecoms and service providers.

So lets say you have .frm file for the table and you need to recover CREATE TABLE statement for this table. In particular when we do Innodb Recovery we often get .frm files and some mess in the Innodb tablespace from which we have to get data from. Of course we could relay on old backups (and we do ask for them for a different reason anyway) but there is never guaranty there were no schema changes in between.

So how to recover CREATE TABLE from .frm file ?

Recovering from .frm for Innodb Table

If we simply copy .frm file back to the database we will see the following MySQL creative error message:

PLAIN TEXT SQL:

1. mysql> SHOW TABLES;
…

Upgraded VirtualBox and booted up my OpenSolaris VM. VirtualBox 2.1.0 finally fixes the bug where if 127.0.0.1 was in resolv.conf on the host - no DNS for you in the guest (unless in the guest you were running a DNS server).

Haven’t tried it yet… but OpenGL Accelleration makes at least a checkbox appearance in VirtualBox 2.1…. so that could be rather awesome.

Going a lot better with OpenSolaris 2008.11 than previous releases.. It looks like it might be quite easy to get to the stage of building Drizzle on it.

Just figured out how to change to Dvorak! Yay, I can type again! (Go to Input Methods preference panel and add US/DVORAK as a language, move it to the top, and enable the input method application and do it that way).

Currently installing sunstudioexpress. Why not gcc? I’m pretty sure the version in OpenSolaris is still ancient (so won’t build drizzle) and Sun Studio does produce different warnings …