Category Archives: Database

Round Robin Data Storage in MySQL

Posted on June 22, 2008 by Silvan Mühlemann

If you want to store large amount of volatile data (e.g. log file entries) in a database with a constant storage memory footprint and no maintenance to purge the old entries, a round robin database is the best solution. But … Continue reading →

Posted in Database, PHP | Tagged | 14 Comments

Pitfalls with MySQL and auto_increment

Posted on April 13, 2008 by Silvan Mühlemann

There’s a number of pitfalls one should be aware of when working with AUTO_INCREMENT fields in MySQL. Last week, we fell in each of them: We have the table photos which contains all 15 million pictures on tilllate.com. This table … Continue reading →

Posted in Database | Tagged | 4 Comments

Comparing queries with mk-query-profiler and diff

Posted on February 5, 2008 by Silvan Mühlemann

EXPLAIN is not the only way to analyze query perfomance im MySql because some things are not being taken into account. For example LIMIT clauses or the cost of the optimizer. There is also the mk-query-profiler. An interesting way to … Continue reading →

Posted in Database | Tagged , | Comments Off on Comparing queries with mk-query-profiler and diff

Peter Zaitsev of the MySQL Performance Blog speaks in Zurich

Posted on November 18, 2007 by Silvan Mühlemann

Busy week for the open source IT pros around Zürich: On Thursday Vint Cerf will talk at Google. On Wednesday, Nov 21st tilllate is happy to announce a presentation of Peter Zaitsev of the MySQL Performance Blog. He will talk … Continue reading →

Posted in Database, Webtuesday | 2 Comments

Query Optimization Challenge

Posted on November 7, 2007 by Silvan Mühlemann

Every few months at tilllate we play the query optimization game. At this game I use the slow query log to find out those queries the most load on the servers. With the queries I found I then either: optimize … Continue reading →

Posted in Database | 3 Comments

Replikation mit MySQL: Tricky!

Posted on January 28, 2007 by Silvan Mühlemann

Pro Sekunde werden auf tilllate.com 5000 Abfragen von den Datenbank-Servern beantwortet. Wie können wir diese Last auf 30 Datenbank-Server verteilen? Mit Replikation. Aber auch nach fünf Jahren Erfahrung habe ich dieses Feature noch nicht ganz im Griff. Auf Datenbank-Ebene besitzt … Continue reading →

Posted in Database, IT Infrastructure, tilllate.com | 11 Comments

Optimierung von MySQL-Abfragen: Verwendung des Index

Posted on January 7, 2007 by Silvan Mühlemann

Vor zwei Wochen habe ich erklärt, wie man mit dem Slow-Query-Log die langsamsten Datenbank-Abfragen identifizieren kann. Nun möchte ich besprechen, wie man diese langsamen Queries beschleunigen kann. Nutze den Index ist die Zauberformel. Die erste Frage: Was ist der Index? … Continue reading →

Posted in Database | 10 Comments

Optimierung von MySQL-Abfragen: Der Slow-Query-Log

Posted on December 23, 2006 by Silvan Mühlemann

Den Feedbacks von meinem letzten Beitrag nach zu schliessen, stösst das Thema Query-Optimierung auf Interesse. Ich werde deshalb in nächster Zeit mehrere Artikel zu diesem Thema schreiben. Schritt 1: Die langsamen Queries identifizieren. Unser erste Blick geht jeweils in den … Continue reading →

Posted in Database | Comments Off on Optimierung von MySQL-Abfragen: Der Slow-Query-Log