I will be at the big anual MySQL conference end of April. This means I will meet with a lot of coworkers, students and customers, friends and many new faces. Lots of new stuff to learn. But I will also work over there.

First of all, we will deliver lots of certification exams, MySQL 5.0 for administrators and MySQL Cluster. Sarah and will give each a 3 hour session to prepare candidates for the DBA exam. We will also be around to answer any questions on certification in general.

Then, I will also give two 45 minutes talks. One will summarize my students' wishlist for MySQL features, the other will be about the Blackhole Storage Engine.

As many of you know 4.1 does not have the thread bug fix as mentioned in this post. So, throwing a new mySQL server into production can cause a spike of threads that exposes this bug. Why? Well, for the most part INNODB has not filled its buffer pool for the most part.


For example, assume you have a box with 16 GB of ram, running 64-bit Linux. Let's look at MySQL when it first starts.

Here is some sample output from top

top - 18:46:22 up 400 days, 22:30,  3 users,  load average: 0.65, 1.08, 0.73
Tasks: 132 total,   2 running, 130 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.7% us,  0.5% sy,  0.0% ni, 92.2% id,  6.5% wa,  0.0% hi,  0.2% si
Mem:  16253552k total,  3406008k used, 12847544k free,    79364k buffers
Swap:  8388600k total,      160k used,  8388440k free, …

TIP: If your UPDATEs on a read intensive environment are taking as much as 1800 seconds, and if you can afford it, try using UPDATE LOW_PRIORITY ..., it can help!

You can also restart MySQL server with --low-priority-updates to make all UPDATEs low priority.

Or, you can make your SELECTs HIGH_PRIORITY.

BTW, does any one know why my last post about MySQL performance tuning went missing on Planet MySQL?

A couple of bug fixes pushed into the team trees. It appears that Valgrind can have a race when signalling condition variables while not holding the mutex. I should get some rest - having to stay up for European hours can be quite exausting. The company-wide conf call was mostly uneventful except for a small number of people who seemed unable to mute their connection. Hopefully, next time MGM will use the "lecture" mode and ensure that all other participants are quiet: It was quite hard to concentrate with the errant echos and other noises.

Liberty Alliance Announces openLiberty Project, Liberty Alliance (Press Release)

GroundWork Exits 2006 with Phenomenal Growth in Revenue and New Customers, GroundWork Open Source (Press Release)

OpenClovis Ends Calendar Year 2006 on High Note, OpenClovis (Press Release)

Pentaho Announces Significant Customer Adoption in Q4, Pentaho (Press Release)

…

MySQL backups are essential to running a site with MySQL backend. Generally you can get away with doing nightly backups but on our site, due to couple issues we had in past, we are forced to do hourly backups of our db. Intially I was doing backup by using: mysqldump dbname > weekdayHour.dbname.sql hourly. [...]

MySQL AB today announced that OKQ8, Sweden?s largest gasoline vendor, will roll out a MySQL-based payment authorization and collection system during the first part of 2007.

Our sysadmin has a nice blog post with a few tips for using mysqldump, especially if your database is used for more than a basic site, or if you have stored procedures and/or triggers.

Problem:

One of the sites I maintain has been getting more and more traffic everyday. A very good thing for the site, not so good for the solo server which is serving those pages. The site is VERY dynamic with LAMP setup. We only have one server serving our web pages to our users. Since its a dynamic site with PHP and MySQL, it has a lot of load during peak times. Average load time of a page is between 1-2 secs during normal usage, 2-5 secs under average to heavy load.

During heavy load, we started to see our mysql stop responding to requests which is a big concern for us since we don’t show content if there is no db connection. We had to come with a solution, fast, to prevent this issue from appearing.

Solution:

So here are couple things I ended up doing on the server side to reduce load on this server without having to do much code change.

• We have couple other …