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

Buy Cheap Microsoft Word 2007 order downloadable microsoft office 2007 enterprise Cs3 10.6.3 buy cheap software adobe premiere elements 9 Microsoft Access Download Free microsoft office visio professional 2003 software purchasing Cheap Windows Xp Operating System adobe premiere elements 3.0.2 software windows Adobe Acrobat Standard Software alternatives to microsoft office Adobe Discounts Military adobe premiere cs5 easier to learn than final cut studio Office 2007 Activation Key adobe premier & cs4 & book Buy Microsoft Windows Xp Pc microsoft office 2008 home student Windows 7 Oem Price List where to download adobe acrobat Microsoft Powerpoint 14.0 Object Library buy microsoft office 2010 professional (32-bit) price Adobe Price Students free microsoft office student How To Purchase Microsoft Word Program free safe download of adobe acrobat reader Microsoft Windows 7 Discounts For Students ms office friendly family tree Cheapest Microsoft Word buy cheap software adobe premiere elements 9 Microsoft Office Word 2007 Oem microsoft office visio professional 2003 software purchasing Cs4 Photoshop Shortcut Keys adobe premiere elements 3.0.2 software windows Upgrade Price Photoshop Cs5 alternatives to microsoft office Windows 7 Ultimate With Xp Mode adobe premiere cs5 easier to learn than final cut studio Acrobat 9.2 Dep Scanners adobe premier & cs4 & book Cs5 License microsoft office 2008 home student Office 2011 Mac Lehrer where to download adobe acrobat Microsoft Office 2010 Trial Error buy microsoft office 2010 professional (32-bit) price Adobe Cs5 For Cheap free microsoft office student Office 2010 Vlk Price free safe download of adobe acrobat reader Microstation Autocad Compatibility ms office friendly family tree Software Download Sites Autodesk Ldd buy cheap software adobe premiere elements 9 Buy Creative Suite Master microsoft office visio professional 2003 software purchasing Turbotax Discount Coupons Online adobe premiere elements 3.0.2 software windows Dvdfab 8.0.7.3 Final Oem alternatives to microsoft office Buy Microsoft Office Visio 2003 adobe premiere cs5 easier to learn than final cut studio Windows 7 Oem Price Xp adobe premier & cs4 & book Microsoft Office Starter Upgrade microsoft office 2008 home student Hp Windows 7 where to download adobe acrobat Adobe Acrobat 4 Download buy microsoft office 2010 professional (32-bit) price Adobe Volume License free microsoft office student Oem Autocad 2009 free safe download of adobe acrobat reader Microsoft 2010 Cost ms office friendly family tree Acrobat 6 buy cheap software adobe premiere elements 9 Buy Microsoft Activation Keys microsoft office visio professional 2003 software purchasing Oem Autodesk Showcase 64 Bit adobe premiere elements 3.0.2 software windows How To Purchase Microsoft Word 2010 alternatives to microsoft office Office 2007 Upgrade Price adobe premiere cs5 easier to learn than final cut studio Ms Office 2007 Purchase adobe premier & cs4 & book Adobe Photoshop Cs4 License Expired Fix microsoft office 2008 home student Reviews Windows 7 Requirements where to download adobe acrobat Building House Autocad buy microsoft office 2010 professional (32-bit) price Office 2003 Backup Download free microsoft office student Ms Office 2007 Professional Oem free safe download of adobe acrobat reader Cheap Adobe Indesign Software ms office friendly family tree Window Application buy cheap software adobe premiere elements 9 Best Pc Games For Windows 7 microsoft office visio professional 2003 software purchasing Buy Microsoft Word For Apple Mac adobe premiere elements 3.0.2 software windows Microsoft Powerpoint Christmas Templates alternatives to microsoft office Cheap Windows 7 Ultimate Ireland Price adobe premiere cs5 easier to learn than final cut studio Windows Server 2003 Price Philippines adobe premier & cs4 & book Free Photoshop Lessons microsoft office 2008 home student Windows Xp Pro For Sale Cheap where to download adobe acrobat Adobe Acrobat Standard Student Price buy microsoft office 2010 professional (32-bit) price Microsoft Excel Tabelle Erstellen free microsoft office student Microsoft Plus Superpack For Windows Xp Free Download free safe download of adobe acrobat reader Oem Autodesk 3ds Max 2008 ms office friendly family tree Microsoft Word Reader buy cheap software adobe premiere elements 9 Cakewalk Music Creator 5 microsoft office visio professional 2003 software purchasing Free 7.0 Photoshop Brushes adobe premiere elements 3.0.2 software windows Microsoft Office Starter Compare alternatives to microsoft office Valor Windows adobe premiere cs5 easier to learn than final cut studio Dreamweaver Cs5 As Xml Editor adobe premier & cs4 & book Used Windows Xp Os For Sale microsoft office 2008 home student 20 Photoshop Oem Cheap where to download adobe acrobat Reinstalling Windows Xp Pro buy microsoft office 2010 professional (32-bit) price 5.0 Mobile Upgrade Window free microsoft office student Preis Autocad Lt free safe download of adobe acrobat reader Microsoft Office For Sale ms office friendly family tree Microsoft Excel Not Equal To buy cheap software adobe premiere elements 9 Microsoft Office For Mac Lion Compatible microsoft office visio professional 2003 software purchasing Matlab Prices Amazon adobe premiere elements 3.0.2 software windows Microsoft Windows Server 2003 Price alternatives to microsoft office Cheap Autodesk Autocad Architecture 2009 adobe premiere cs5 easier to learn than final cut studio Microsoft Windows Server 2003 Download adobe premier & cs4 & book Windows 7 Ultimate Lizenz microsoft office 2008 home student Office 2008 Quer where to download adobe acrobat Windows 7 Professional 99 buy microsoft office 2010 professional (32-bit) price Buy Microsoft Projects free microsoft office student Open To Buy Software free safe download of adobe acrobat reader Dreamweaver Sale Brisbane ms office friendly family tree Adobe Digital Imaging Software buy cheap software adobe premiere elements 9 Microsoft Word To Buy microsoft office visio professional 2003 software purchasing Price Adobe Cs4 Master Collection adobe premiere elements 3.0.2 software windows Windows Xp Professional Operating System Download alternatives to microsoft office Price Upgrade Windows 7 Home Premium Professional adobe premiere cs5 easier to learn than final cut studio Licencia Windows 7 Home adobe premier & cs4 & book Price Windows Vista Retail microsoft office 2008 home student Autodesk Pro Sketchpad where to download adobe acrobat Buy Adobe Dreamweaver Cs5 Price buy microsoft office 2010 professional (32-bit) price

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

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:

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:

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

(more…)

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.
(more…)

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

(more…)

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.

(more…)

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:

SELECT SQL_CALC_FOUND_ROWS gb.*,
 u.username,
        u.uid,
        u.geschlecht,
        u.mitfoto,
        [... 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.

(more…)

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 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.
(more…)

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

I prefer the former because caching means data duplication. Which is not very DRY.
(more…)

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

(more…)

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? – 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.
(more…)