EmergencyEMERGENCY? Get 24/7 Help Now!

Undo area size restriction needed for Innodb

 | November 9, 2006 |  Posted In: Insight for DBAs

As you can read from my Innodb Architecture and Performance Optimization presentation Innodb automatically manages undo area in system tablespace so you never need to care about it. I present it as positive feature reducing administration effort needed but it also can cause a troubles as it happened for me today: InnoDB: 11 transaction(s) which […]

Read More

Should MySQL and Web Server share the same box ?

 | October 16, 2006 |  Posted In: Insight for DBAs

This is interesting question which I thought it would be good to write about. There are obviously benefits and drawbacks for each of methods. Smaller applications usually start with single server which has both MySQL and Web server on it. In this case it is not usually the question but once application growths larger and […]

Read More

Long PRIMARY KEY for Innodb tables

 | October 3, 2006 |  Posted In: Insight for DBAs

I’ve written and spoke a lot about using short PRIMARY KEYs with Innodb tables due to the fact all other key will refer to the rows by primary key. I also recommended to use sequential primary keys so you do not end up having random primary key BTREE updates which can be very expensive. Today […]

Read More

What to tune in MySQL Server after installation

 | September 29, 2006 |  Posted In: Insight for DBAs

My favorite question during Interview for people to work as MySQL DBAs or be involved with MySQL Performance in some way is to ask them what should be tuned in MySQL Server straight after installation, assuming it was installed with default settings. I’m surprised how many people fail to provide any reasonable answer to this […]

Read More

Why Index could refuse to work ?

 | September 8, 2006 |  Posted In: Insight for DBAs

Have you ever seen index which refused to be used even if there is every reason for it to work (from the glance view):

Why on the earth index would not be used you would think, even if MySQL is mentioning it in “possible keys” ? Should you try to force it ?

Read More

Internals of InnoDB mutexes

 | September 7, 2006 |  Posted In: Insight for DBAs

InnoDB uses its own mutexes and read-write locks instead of POSIX-mutexes pthread_mutex*, the main reason for that is performance, but InnoDB’s implementation isn’t ideal and on modern SMP boxes can cause serious performance problems. Let’s look on InnoDB mutex (schematic for simplification):

Read More

Slow Query Log analyzes tools

 | September 6, 2006 |  Posted In: Insight for DBAs

(There is an updated version of this post here). MySQL has simple but quite handy feature – slow query log, which allows you to log all queries which took over define number of seconds to execute. There is also an option to enable logging queries which do not use indexes even if they take less […]

Read More

Even minor upgrades are not always safe

 | September 4, 2006 |  Posted In: Insight for DBAs

I already wrote couple of weeks ago I keep most of my systems on MySQL 4.1 still as they will not benefit from MySQL 5.0 features anyway while I do not want to likely loose a bit of performance and possibly deal with new bugs and changes introduced in MySQL 5.0 (You never know where […]

Read More