A SELECT statement with COUNT returns the number of rows retrieved by the SELECT statement (see mysql select count). For performance reasons, the desired result is to limit that count. Including a LIMIT clause in the SELECT statement will not work since it only restricts the number of rows returned, which is always one. The […]
May
13
2010
May
12
2010
Last month while being at the MySQL Conference, several people asked me about the status of BlitzDB. Specifically, they were interested in when I’ll release BlitzDB. Fair enough – I’ve been working on this project long enough for people to start questioning this.
The answer is, BlitzDB is done in terms of implementing the design. Right now it’s about finding bugs, fixing it and testing BlitzDB’s stability under concurrent load. Thanks to the motivation boost I gained at the conference, I’ve now fixed the bugs that were slowing me down and I’m gradually adding more tests into BlitzDB’s test suite. I consider BlitzDB’s initial release to be the day it gets merged into Drizzle’s trunk. This is almost ready as BlitzDB seems to be building fine on Drizzle’s Build Farm infrastructure. However, I won’t move to the next step until I’m satisfied with BlitzDB’s stability.
Yesterday I spent some time doing some …
[Read more]
May
11
2010
Not part of my Don’t Assume series, but when a client states “Why is my database slow”", you need to determine if indeed the database is slow.
Some simple tools come to the rescue here, one is Firebug. If a web page takes 5 seconds to load, but the .htm file takes 400ms, and the 100+ assets being downloaded from one base url, then is the database actually slow? Tuning the database will only improve the 400ms portion of 5,000ms download.
There some very simple tips here. MySQL is my domain expertise and I will not profess to improving the entire stack however perception is everything to a user and you can often do a lot. Some simple points include:
- Know about blocking assets in your <head> element, e.g. .js files.
- Streamline .js, .css and images to what’s needed. .e.g. download a 100k image only to resize to a thumbnail via style …
May
11
2010
Lately in the MySQL community, we only hear about scalability or performance improvements of storage engines, but nothing about query engine itself. For example, one classic example being InnoDB; if[...]
May
10
2010
In How to tune MySQL’s sort_buffer_size, Baron gives a condescending viewpoint on how to tune the sort_buffer_size variable in MySQL. In a much-nicer-nutshell, his advice is “do not change sort_buffer_size from the default.”
Baron did not explain the logic behind his reasoning, he handwaves that “people utterly ruin their server performance and stability with it,” but does not explain how changing the sort_buffer_size kills performance and stability. Regardless of how respected and knowledgeable the source, NEVER take any advice that tells you what to do or how to do it without understanding WHY.
This article will explain the “why” of Baron’s point, and it will also talk more about understanding why, an integral part against the “Battle against any guess.” Baron’s recommendation to leave …
[Read more]
May
06
2010
TO_DAYS() prunes two partitions instead of one
If you are partitioning by date, chances are that you are using
TO_DAYS(). And depending on how you have partitioned
your table, your queries are as fast as you expect them to be.
However, there are cases where your query takes twice as long as
it should, and of course this will not make you happy.
For example, in a table partitioned by month, when …
May
01
2010
Forecasting Oracle Performance
Forecasting Oracle Performance. By Craig Shallahamer, Apress 2007. Page count: about 250 pages. (Here’s a link to the publisher’s site). Short version: buy it and read it, but make sure you don’t rely on it alone; deepen your knowledge through other sources.
I bought and read this book because I’m interested in performance, performance forecasting, and capacity planning. I’m not interested in forecasting Oracle performance per se. However, I have noticed that there is a lot of good literature in the Oracle arena that can apply to other databases (*cough* …
[Read more]
Apr
27
2010
SPJ (preview, not production ready) is a new feature allowing
some types of JOINs to be pushed down and executed inside the
data nodes! This allows for, in many cases, much faster JOIN
execution.
Now we would love to get your feedback on this new feature:
- Does what we have right now improve performance for you?
- Are there other types of JOINs we should support in order to improve performance in you application (currently only eq_ref is supported)?
- What application are you using?
There are some limitations currently:
- node failure handling of SPJ is not complete, so if a data node crash, there are side-effects.
- only eq_ref is supported - other JOINs are executed as normal.
- Don't put this in production.
Obtaining the MySQL Cluster SPJ preview version:
- You must know how to build MySQL Cluster …
Apr
25
2010
A special extended edition of Tech Messages for 2010-04-15 through 2010-04-24:
-
The Onion Uses Django, And Why It Matters To
Us
I love Django too. - Open Source Network Monitoring and Systems Management - Zenoss Blog: No Node Left Behind: Datacenter Barometer: BitNami Simplifies Stacks
-
mysql-cacti-templates
Offers complete templates and a method for adding your own. Highly recommended. -
Increase maximum size of PST files
Microsoft Outlook …
Apr
23
2010
This is the third and final article in a series about group commit in MySQL. The first article discussed the background: group commit in MySQL does not work when the binary log is enabled. The second article explained the part of the InnoDB code that is responsible for the problem.
So how do we fix group commit in MySQL? As we saw in the second article of this series, we can just
eliminate the prepare_commit_mutex from InnoDB,
extend the binary logging to do group commit by itself, and that
would solve the problem.
However, we might be able to do even better. As explained in the first article, with …
[Read more]