Ljubljana, Slovenia

Mysql

MySQL tips for developers

Having more than a decade of experience with MySQL, there are a few tricks I picked up over the years that I believe should be in the arsenal of every developer who works with this database more than just superficially. I’m going to list a quick break-down of them from the top of my head.

Read more

Outperforming MySQL by hand
There is this SQL table, right? This is the part where you knowingly nod and prepare yourself for a long story about how a database is as good as the person designing it. Let's call this table an access log. For the sake of argument, let's also say it's 30GB in size, has a 100 million rows, and only has an auto_increment PK ID field. Your task is, should you choose to accept it, to retrieve the last hour of entries in this log table.
Read more

Optimization strategy
Sometimes it's not about just optimizing CPU time away. Looking at the details I could optimize away a badly written SQL query along with some more trivial big-O problem in regard with sorting of video clips which I got out of serialized data. All from a few carefully placed calls. Reducing network bandwidth is just as important as CPU time.
Read more

MySQL optimization and diagnostics tactics
We have all been there. There's that server, which has mysterious spikes in load, which come from nowhere and leave just as they came - quickly and unexplained. While sometimes these problems can be explained with slow queries, given enough time, development and optimization, you will run into scenarios that don't register in the slow query log. It is my opinion, that any kind of database load problem can be traced back to development.
Read more

MySQL & replication "oh, right" moments
We have been running a replicated MySQL setup for many years now. It has been working great for us, but it does make you think of some things in a different way. Not because you're working with a specific function set, but also because of the way the database daemon is constructed, from the ground up, and these little things that add up over time. A recent "oh" moment was regarding MySQL replication lag.
Read more