Many Open Source software solutions use database per user (or set of tables per user) which starts to cause problems if it is used on massive scale (blog hosting, forum hosting etc), resulting of hundreds of thousands if not millions of tables per server which can become really inefficient. It is especially inefficient with Innodb [...]
Query Profiling with MySQL: Bypassing caches
September 12, 2007 By 6 Comments
Quite frequently I run into question like this “I’m using SQL_NO_CACHE but my query is still much faster second time I run it, why is that ? The answer to this question is simple – because SQL_NO_CACHE only bypasses query cache but it has no change on other caches, which are MySQL Caches – Innodb [...]
Opening Tables scalability
November 21, 2006 By 27 Comments
I was restarting MySQL on box with 50.000 of Innodb tables and again it took couple of hours to reach decent performance because of “Opening Tables” stage was taking long. Part of the problem is Innodb is updating stats on each table open which is possibly expensive operation, but really it is only great test [...]
What to tune in MySQL Server after installation
September 29, 2006 By 118 Comments
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 [...]
MySQL Crash Recovery
July 30, 2006 By 30 Comments
MySQL is known for its stability but as any other application it has bugs so it may crash sometime. Also operation system may be flawed, hardware has problems or simply power can go down which all mean similar things – MySQL Shutdown is unexpected and there could be various inconsistences. And this is not only [...]
Are larger buffers always better ?
June 6, 2006 By 9 Comments
Sometimes I see people thinking about buffers as “larger is always better” so if “large” MySQL sample configuration is designed for 2GB and they happen to have 16, they would simply multiply all/most values by 10 and hope it will work well. Obviously it does not. The least problem would be wasting memory, allocating a [...]
