In Five Common PHP database problems, Jack Herrington writes about five common database problems that occur in PHP applications -- including database schema design, database access, and the business logic code that uses the database -- as well as their solutions

My experience in the field differs slightly from his observations, and so I thought a few annotations may be in order.

Continue reading "Annotations to "Five Common PHP database problems""

Tailrank has a few large tables in our MySQL database that could benefit from a bit of truncation for (hopefully) some performance gain. The theory goes that if you have less data you'll have a faster system. This isn't always a one-to-one comparison of course because if you delete enough data MySQL will eventually just buffer the whole data in memory and you'll get an order of magnitude more performance.

All things being equal though what kind of performance boost would you get for SELECTs if you were to take a 20M node table and truncate it by 80%.

The answer is that you'd only receive a 9.3% performance boost. Since btrees are log(N) this means that in order to compute the boost you'd can just use the equation:

(log(N) - log(N_after) / log(N)) * 100

This boost obviously isn't worth the hassle and we're just going to migrate to a new table schema …

Someone at work pointed out to me this blog post about what to do when disk partitions housing MySQL databases fill up, with the caption, “An idea for [the MySQL 5.1 Partitioning] Docs?”

My reply was, “Nope”.

Well, I was wrong. In a way. This guy’s talking about disk partititions, not partitioned tables. And using symlinks to point to data or database directories is not particularly new or exciting. However, partitioning in MySQL 5.1 provides a kind of cool new way to move table data and index files pretty easily.

The DATA DIRECTORY and INDEX DIRECTORY options have been around for CREATE TABLE for quite some time, but they’re not supported for ALTER TABLE - if you try, this is what happens:

 
mysql> …

I've released another version of the innotop MySQL and InnoDB monitor. As always, you can download innotop from the original article.

It's worth upgrading to this version not only because of the new features, but also because it should handle more special cases without crashing. Of course, if it does crash, I appreciate your help fixing it; see this article about what information I need.

Hey MySQL fans. The MySQL camp wiki is officially live! Thanks to PB Wiki for offering to sponsor us.

If you'd like to participate you can sign up here. This should help us figure out what kind of turn out we could expect.

If you'd like to become a sponsor feel free to send me an email or just add yourself to the sponsors page.

If you have any ideas for a session please add an entry in Proposes Session Topics.

Today Peter Zaitsev announced launch of new forums related to MySQL Performance questions. He says:

This forum is created as free alternative to MySQL Consulting Services which we provide. If you would like to get some free help to your performance issues please use forums so everyone else could benefit from our replies. You also should get more opinions on your performance problems from other forum members. There were a lot of unrelated performance questions placed as comments and sent by email and we had to find better way to organize it.

I think that it is a great idea to start such forums ontop of great blog site because many people can find it from Google when looking for some performance related questions or discussions. So, I want to say good luck to peter and will try to participate any discussions in these forums.

Ok, there is no such thing. But here's the story...

Late one afternoon last week, I was preparing to review a presentation for a strategy meeting and I collided into a glass conference room door, dropping my laptop at the same time. (Visitors to our office will note that there are now dolphin stickers on the door.) Amazingly enough, my hard laptop continued to operate for another hour. As a slight precaution, I emailed myself the last file I was working on, in case things went wrong. As I drove home, I started to feel nauseous and my thinking was a bit cloudy.

When I got home, I realized that both me and my hard drive were suffering a mild concussion. The laptop would only boot part way, restarting mid-stream, even in safe mode. So I completed my work using my desktop machine and the file I had emailed to myself while holding an ice pack to my head.

The next day, I handed my laptop over to our IT gurus and they gave me a …

I'm happy to announce availability of MySQL Performance Forums on MySQL Performance Blog.

This forum is created as free alternative to MySQL Consulting Services which we provide. If you would like to get some free help to your performance issues please use forums so everyone else could benefit from our replies. You also should get more opinions on your performance problems from other forum members. There were a lot of unrelated performance questions placed as comments and sent by email and we had to find better way to organize it.

We will try to reply to messages on this forum with highest priority as time permits.

Yes there are great general MySQL Forums which grew huge and hard to follow fully, while on our local …

One of my colleagues recently redesigned a system to schedule work for programs to do, and needed to write a multiple-table UPDATE with ORDER BY and LIMIT, neither of which is supported for multiple-table UPDATE in MySQL. This article explains how to do it anyway, and shows how to rewrite a first attempt for hundreds of times better performance.