Article about database design problems is being discussed by Kristian.

Both article itself and responce cause mixed feellings so I decided it is worth commenting:

1. Using mysql_* functions directly This is probably bad but I do not like solutions proposed by original article ether. PEAR is slow as well as other complex conectors. I have not yet tested PDO but would not expect it to beat MySQLi in speed. It is however bad idea to use mysql_ functions directly as well - I would go for using mysqli object approach. The great things about objects is you can easily overload methods and get debugging and profiling tools, as well as have tools which protect you from …

Here’s a good “Addendum/Appendix/Annotation” to the original article by IBM DeveloperWorks. Along with some updated ways of doing things using MySQL 5 and stored procedures.
Updated: August 11th 11:33 AM Thanks to ds- on #php.thinktank, actual addendum is here.

Tags: php, mysql, performance

Today I've upgraded MySQL Server on the host running MySQL Performance Blog. MySQL 4.1.12 was running here for well over a year before that.

Why Have not I upgraded before ? Well because it just worked fine. Yes I know there were some security fixes but I have dedicated server with remote MySQL access closed by firewall and only trusted people having local access so I was not worried too much about it.

Why did I upgrade now ? Because new forum application was exposing one of the bugs in MySQL 4.1.12 so there was a need for upgrade.

Which Version did I upgrade to ? I Upgraded to MySQL 4.1.21. Why Did not I go with MySQL 5.0 ? There are three reasons. First there are number of changes in 5.0 some of which are incompatible with MySQL 4.1, for example regarding join syntax. I can't be 100% sure none of my applications will be affected and I see no reasons to spend time testing it carefully or downgrading if problem finally …

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 …