14. August 2008, 09:07, by Maarten Manders

Webtuesday Lightning Talk Slides

This week, there was another webtuesday with lightning talks in Zürich. Leo and I each did a little talk:

“StubidDB” by Leo

Leo still dreaming about dumber databases. His own creation StupidDB might sound familiar to you…

“I’m in Ur Browzerz…” by Maarten

Harry had the idea to locate your users based on their history. I tried it out, giving another 8 minutes of fame to an old and well-known hack. The slides can be found here.

22. June 2008, 21:03, by Silvan Mühlemann

Round Robin Data Storage in MySQL

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 how to implement it in MySQL?

Some of the heaviest tables in our database are tables which do some event logging. Let’s look at a table which log the most recent visitors on a member profile. Look at that ugly output:

mysql> SHOW TABLE STATUS like "profile_visits_log" \G;
*************************** 1. row **********************
           Name: profile_visits_log
           Rows: 6'226'066
 Avg_row_length: 21
    Data_length: 130'747'386
   Index_length: 393'205'760

It grows and grows… And the queries get slower and slower.

This is the case because we are keeping a lot of old, unused data. For example, on my member profile there’s data back to September 2007:

Profile visitor list on tilllate.com

So we have to get rid of that old data. For example we can do it like Xing and store the n most recent entries for each user. Drop the rest:

Profile visitors in Xing

Now, how do we do that in the most elegant way?


Filed under: Database,PHP
13. April 2008, 07:29, by Silvan Mühlemann

Pitfalls with MySQL and auto_increment

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 is MyISAM and has id INT NOT NULL AUTO_INCREMENT as the primary key. The position of the auto increment counter was at 112’606’834.

Filed under: Database
22. February 2008, 12:12, by Steven Varco

New DB Servers

Our Dell PowerEdge 2950 Servers for the new DB Cluster has just arrived and I’m very excited, unpacking them. ;-)

Boxed Servers


5. February 2008, 17:09, by Silvan Mühlemann

Comparing queries with mk-query-profiler and diff

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 compare the performance of two queries is to use mk-query-profiler along with diff

Here’s how you do it. As an example I take the queries from this mysql performance blog article article. Because I’d like to learn what excactly SQL_CALC_FOUND_ROWS does.


Filed under: Database
5. January 2008, 22:16, by Leo Büttiker

Why paging needs a lot of performance

On the view of your database the worst thing you can do in your web app is paging. Paging is horrible in the view of performance. To explain let me make a little example:

        [... some more fields...]
FROM member_gold_guestbook gb
LEFT JOIN users u ON u.uid=gb.uid_from
[... some more left joins...]
WHERE gb.uid_to='22152'
AND visible='1'
LIMIT 0,10;

That’s not that bad at all, but when you go to page 300 your database server will hate you for this. The database server has not only to calculate the 10 items you want to show but also all 3000 previous items.

Sure you may argue nobody will ever go to page 300. Somebody will not, but “googlebot” and his evil brothers will. And the bad thing is that you can do nothing against it, as long as you need paging. There are just a few tricks that may reduce your server load a bit.


Filed under: Database,PHP
18. November 2007, 19:29, by Silvan Mühlemann

Peter Zaitsev of the MySQL Performance Blog speaks in Zurich

Peter ZaitsevBusy 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 about query optimization for high traffic sites.

Peter Zaitsev was manager of the High Performance Group at MySQL Inc. He specializes in MySQL Server performance as well as in performance of application stacks using MySQL, especially LAMP. Web sites handling millions of visitors a day dealing with terabytes of data and hundreds of servers is king of applications he loves the most.

Filed under: Database,Webtuesday
7. November 2007, 23:04, by Silvan Mühlemann

Query Optimization Challenge

StopwatchEvery 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 the query or cache the results to avoid the query.

I prefer the former because caching means data duplication. Which is not very DRY.

Filed under: Database
28. January 2007, 21:09, by Silvan Mühlemann

Replikation mit MySQL: Tricky!

DelfinPro 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 tilllate.com vier Servergruppen mit unterschiedlichen Funktionen (= “horizontale Skalierung”): Werbung, Statistik, Chat und schliesslich der Rest der Website tilllate.com. Werbung, Statistik und Chat kommen mit einem einzelnen Datenbank-Server aus.

Der “Rest” macht 90% der Abfragen aus. Der Rest sind 27 MySQL Datenbank-Server. Auf diesen 27 Server befindet sich eine identische Kopie der Haupt-Datenbank. Damit dies so bleibt, muss jede Änderung der Datenbank (z.B. eine UPDATE-Query) wird über einen definierten Weg auf alle 27 Maschinen repliziert. Wir benutzen hier die Replikations-Features von MySQL.

Einfache Master-Slave-Replikation

7. January 2007, 18:32, by Silvan Mühlemann

Optimierung von MySQL-Abfragen: Verwendung des Index

DolphinVor 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? – Nun, das ist Euch bestimmt bekannt. Sonst hilft Wikipedia.

Schaut man sich an, wie die Dateien bei MySQL (MyISAM-Struktur) auf der Disk gespeichert ist, so ist die Index-Datei an der Endung MYI zu erkennen (MYD sind die effektiven Daten, frm die Tabellendefinition):

cameron tilllate # ls -lh users.*
-rw-rw---- 1 mysql mysql 65M Jan  7 16:51 users.MYD
-rw-rw---- 1 mysql mysql 90M Jan  7 16:51 users.MYI
-rw-rw---- 1 mysql mysql 15K Jan  6 06:20 users.frm

Die Index-Datei users.MYI wird bei MySQL im RAM gehalten, während die effektiven Daten (Usernamen, Passwörter, Adressen) auf der Disk bleiben. Ein Grund, dass der Zugriff schnell ist.

Filed under: Database
Next Page »

© 2018 tilllate Schweiz AG - Powered by WordPress