Peter Marklund

Peter Marklund's Home


MySQL Performance

Every now and then I like to pick on MySQL and it's become something of a running theme in this blog. My session table for this blog (which runs on Ruby on Rails of course) had grown too big so I needed to clean it up. I just didn't expect it to take this long:

mysql> select count(*) from sessions;
| count(*) |
|   545797 | 
1 row in set (2.89 sec)

mysql> delete from sessions;
Query OK, 545801 rows affected (5 min 46.67 sec)

Should it really take 3 seconds to count half a million rows? I wonder if PostgreSQL would deliver better performance in this instance. As soon as I find the time I will switch over my Rails/MySQL based web application Simple Signup to PostgreSQL. I have a strong personal preference for PostgreSQL over MySQL. I'm curious to see what the transition will be like though.

7 comment(s)


blog php said 2009-09-30 21:41:

Thanks for sharing your knowledge ! very usefull and interesting for us !

Tristan Juricek said 2008-12-14 03:14:

I've been pretty happy with my PostgreSQL usage at my company - we use it extensively. But for usage like this, we almost always just do a TRUNCATE. In fact, we've been having a number of performance problems as well. This is just a symptom of growing up, I think. But the general solution was to find any batch operation and rewrite it to remove any DELETE or UPDATE statements - usually, this includes some kind of partitioning scheme. Once we simplified a few big operations like this, all other queries sped up. Just saying, you may not be approaching performance nirvana, though I haven't dealt with as nearly as much configuration crap with PostgreSQL.

Jarkko Laine said 2008-12-08 03:18:

"David: I usually store my sessions in database for performance reasons. If you have a busy site I have notice a great performance boost." Compared to what? Cookie store? That would surprise me because with cookie store there is no store on the server side which also means no lookup time.

Mathias Stjernström said 2008-12-07 14:50:

If you use InnoDB the count takes that long because InnoDB does not keep an internal count of rows in a table. You can read more at For the DELETE command i would also recommend TRUNCATE. David: I usually store my sessions in database for performance reasons. If you have a busy site I have notice a great performance boost. Personally i would go with PostgreSQL ;-)

David Backéus said 2008-12-07 11:27:

Why go through the hassle of not using the default cookie store?

Jeremy said 2008-12-04 14:28:

Truncate is a good suggestion, but I'd also wonder why are you storing sessions in a database? Or more so, why are you storing sessions at all? Most users of your site shouldn't have a session.

David Eriksson said 2008-12-04 13:27: