Summary: An alternate approach, offered in response to our original post, provides excellent improvements for smaller databases, but clustered indexes offer better performance as database size increases.  (This posting is by Dave.)

Jay Pipes suggested an alternate approach to improving MySQL performance of Query 17 on a TPC-H-like database.

1. 
   
2. Add the index (l_partkey, l_quantity) to the lineitem table.
   
3. Re-write the query as:
   

   select 
      sum(li.l_extendedprice) / 7.0 as avg_yearly 
   from lineitem li 
      inner join part p on li.l_partkey = p.p_partkey 
      inner join ( select 
                      l_partkey, 0.2 * avg(l_quantity) as quantity 
                   from lineitem 
                   group by l_partkey 
                 ) as quantities 
         on …

Yves pointed this out to me:

http://www.internetnews.com/software/article.php/3825426/

“Moving beyond just providing Oracle compatibility, EnterpriseDB is adding new scalability features to their database. One of them is a feature called ‘”Infinite Cache,” which is based on the open source memcached application.

Mlodgenski commented that distributed memory caches are common, though management from a system management and a database perspective is often difficult.

“What we did is we took the distributed memory cache that was sitting in front of the database and put it behind the database,” Mlodgenski said. “This allows a simple SQL (define) interface for developers that allows access to the distributed memory cache and now the system management overhead is handed by the database itself.”

Mlodgenski added that Infinite Cache leads to a 10x to 20x performance gain for read application loads. …

Today marks the official launch of Percona.tv. We'll be uploading technical screencasts, conference video, and anything else cool we can think up. If you've got ideas or requests, let us know and we'll do our best to accommodate!

Entry posted by Ryan Lowe | 7 comments

Add to: | | | …

Hi again!

Just a moment ago, I read this post by Peter Gulutzan. In this post, Peter explains a little trick that allows you to capture the SQL state in a variable whenever an error occurs in your MySQL stored routine code.MySQL CONDITIONs and HANDLERsFor the uninitiated: in MySQL stored routines, you can declare HANDLERs which are pieces of code that are executed only in case a particular CONDITION occurs. This device serves the same purpose …

There has been a significant increase in interest in the Memcached, the open source distributed memory object-caching system, in recent months, as a number of vendors look to exploit its popularity in Web 2.0 and social networking environments.

Like Hadoop, which has become the focus of a number of commercial plays, it would appear that the time is right for commercialization of Memcached. But what is it, here did it come from, and what are the chances for vendors to rake in serious cash? Here are the details.

What is it?
Pronounced mem-cash-dee, Memcached was originally created by Danga Interactive (the developer of LiveJournal, which was acquired by Six Apart in 2005) to speed up the performance of dynamic Web applications by alleviating database load. Memcached has become an industry standard for improving the performance of dynamic websites.

The code is available from the …

Executive Summary: A query like TPC-H Query 17 can be sped up by large factors by using straight_joins and clustering indexes.  (This entry posted by Dave.)

In a previous post, we wrote about queries like TPC-H query 2, and the use of straight_join to improve performance. 
This week, we consider Query 17, described by the TPC-H documentation as

“The Small-Quantity-Order Revenue Query considers parts of a given brand and with a given container type and determines the average lineitem quantity of such parts ordered for all orders (past and pending) in the 7-year database.  What would the average yearly gross (undiscounted) loss in revenue if orders for these parts with a quantity of this average were no longer taken?”

Our initial run on Q17 (same hardware as before) timed out …

I was curious about Google Fusion Tables, and gave it a try.
I uploaded the employees table from the employees test database, 16 MB of data, about 300,000 rows. Since the maximum limit per table is 100 MB, I expected interesting results.
However, one of my first tests, with aggregation was quite disappointing.
A simple group by gender was executed in about 30 seconds.

InnoDB on my laptop did a much better job:

select gender , count(*) from employees group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| M      |   179973 | 
| F      |   120051 |  …

This is the 150th edition of Log Buffer, the weekly review of database blogs. Someone accidentally left Dave Edwards‘ cage unlocked, and he escaped, thus leaving me with the pleasurable duty of compiling the 150th weekly Log Buffer.

Many people other than Dave are finding release this week. Giuseppe Maxia explains some details of MySQL’s New Release Model. Andrew Morgan announces a New MySQL Cluster Maintenance Release. Aleksandr Kuzminsky of the MySQL Performance …

In many applications, MyISAM can be used successfully if the proportion of write operations is only a small fraction of the read operations. As the tables grow, the 4 GB limitation in the key_buffer size caused performance issues and other strategies. Wait… did I miss something… From a recent comment to a post from Matt I re-read the MySQL documentation and realized I missed a big change that occurred with versions 5.0.52 and 5.1.23 (http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_key_buffer_size)

As of MySQL 5.1.23, values larger than 4GB are allowed for 64-bit platforms

So, key_buffer as large or even larger than 32 GB are possible. That is an important design consideration that needs to be taken into account when tuning …

In addition to being practical, …