A couple of days ago I posted about scaling writes in mysql. I didn't say much about read performance in that post because a) it was irrelevant at the time, and b) there are thousands of articles all over the web that already cover read performance.

In this post I'm going to cover some of the things that I did to improve read performance for my own application. It may be relevant to others, however, you'd still need to read a whole bunch of other articles to understand MySQL read performance.

Looking at our access patterns, it turned out that there were two classes of read queries.

1. Reads to build the daily summaries
2. Reads from the summary tables in response to user actions

The former dealt with far more data at one go, but was only run once. Queries for this pattern were slow …

We’ve been able to do MySQL 5.1 binary tarballs for a bit now (great working together with Kristian Nielsen of Monty Program), but packages are bit more tricky. Peter has been working on Debian/Ubuntu while I’ve focused on RH/CentOS. The following is from an OurDelta (trial build run) RPM install on CentOS 5 x64:

$ mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.38-maria-beta1-ourdelta (OurDelta - http://ourdelta.org/)

mysql> CREATE TABLE test.t1 (i int) ENGINE=PBXT;
Query OK, 0 rows affected (0.10 sec)

mysql> SHOW CREATE TABLE test.t1\G
*************************** 1. row ***************************
Table: test.t1
Create Table: CREATE TABLE `test.t1` (
`i` int(11) DEFAULT NULL
) ENGINE=PBXT DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> INSERT INTO test.t1 values (1);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM test.t1;
+------+
| i    |
+------+
| …

I am planning on presenting a session on PBMS at the OpenSQL camp. I am in hopes of have a chance to discuss PBMS with people and find out how they are planning on using it and what features they would like to see in it.

But even if you are not interested in PBMS you should still come if for no other reason than the free pizza!

I am proud to say the PrimeBase Technologies is one of the organizations who's sponsorship money is helping to provide the free pizza.

I will see you all there,

Barry

I pushed more patches to Launchpad for MySQL 5.0.84. We should have patches for MySQL 5.1 real soon. Another person has joined my team at work and we should begin to get more done. His first project was the query cache change. The new patches include:

• Add code to strip the leading comment from MySQL statements before checking the query cache. This is enabled by the my.cnf variable query_cache_skip_leading_comment. When disabled, MySQL does not check the query cache for SELECT statements that start with a comment. When enabled, the leading comment is stripped if it starts with /* but not if it starts with /*!. This might make the query cache useful for those of us who prepend comments to all SQL statements to support workload monitoring and debugging.
• Change mysqld to write queries to the slow query log when the execution time is >= …

Winning and losing with open source. Paranoid Android. And more.

Follow 451 CAOS Links live @caostheory on Twitter and Identi.ca
“Tracking the open source news wires, so you don’t have to.”

Winning and losing
Matt Asay stirred things up with his declaration that free software has lost and open source has won. Responding to Matt Asay, Glyn Moody argued that without free software, open source would lose its meaning, while Mark Stone explained that free versus open source is not black and white - it’s more complex than that.

Matt Asay later declared open source …

Continuing the series:

What is better to store hierarchical data: nested sets model or adjacency list (parent-child) model?

For detailed explanations of the terms, see the first article in the series:

• Adjacency list vs. nested sets: PostgreSQL

This is the last article of the series which covers MySQL.

MySQL differs from the other systems, since it is the only system of the big four that does not support recursion natively. It has neither recursive CTE’s nor CONNECT BY clause, not even rowset returning functions that help to emulate recursion in PostgreSQL 8.3.

MySQL supports a thing that all other systems …

See http://perfwork.wordpress.com/2009/09/29/oss-load-testing-tool/

Here’s the problem I currently have.

We’re looking at deploying the Intel X-25M MLC SSD in production.

The problem being that this drive has a lower number of erase cycles but is much cheaper. Than the Intel X-25E SLC drive.

However, in our situation we’re write once, read many. I’m 99% certain that we will not burn out these drives. We write data to disk once and it is never written again.

The problem is that I can’t be 100% sure that this is the case. There is btree flushing, and binary log issues that I’m worried about…

What would be really nice is an API (SMART?) that I can enumerate the erase blocks on the drive, determine the max erase cycles, and read the current number of erase cycles.

This way, I can put an SSD into production, then determine the ETA to failure.

I …

I have been doing some reading into data mining recently. It's really interesting stuff. To explain it, lets take an example:Lets say you work in a business that has been working for some years. They have their own way of running themselves and their processes. One day someone comes along and suggests that the business should automate some of their procedures by developing a website that would interact with customers and process their orders. Some nice PHP/MySQL guy or girl is hired and develops the website and the cycle of acquiring knowledge has unwittingly started.
The diagram below describes how management starts to process its own business knowledge and passes them down the chain so they can be implemented into the application.Eventually, every transaction is saved in the database. Over time, the database has more and more data and then the management of the company asks to get reports from this data.
Why would they ask that? …

See http://perfwork.wordpress.com/2009/09/29/oss-load-testing-tool/