Peter Marklund

Peter Marklund's Home

Thu December 04, 2008
Programming

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.

Comments

David Eriksson said over 5 years ago:

Try TRUNCATE: http://dev.mysql.com/doc/refman/5.0/en/truncate.html

--------------------------------------------------------------------------------

Jeremy said over 5 years ago:

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 Backéus said over 5 years ago:

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

--------------------------------------------------------------------------------

Mathias Stjernström said over 5 years ago:

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 http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html

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 ;-)

--------------------------------------------------------------------------------

Jarkko Laine said over 5 years ago:

"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.

--------------------------------------------------------------------------------

Tristan Juricek said over 5 years ago:

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.

--------------------------------------------------------------------------------

blog php said over 4 years ago:

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

--------------------------------------------------------------------------------