We've passed another performance milestone using MySQL Cluster. In 2002 we passed the limit of 1M reads per second. Now we've passed the milestone of 1B reads per minute. We achieved 1.05BN reads per minute on an 8-node cluster using MySQL Cluster 7.2.5.

The benchmark used the latest MySQL Cluster version, available on launchpad and to be released as MySQL Cluster 7.2.5. The benchmark program is flexAsynch. Each read is a transaction consisting of a read of an entire row consisting of 25 attributes, each 4 bytes in size. flexAsynch uses the asynchronous feature of the NDB API which enables one thread to send off multiple transactions in parallel. This is handled similarly to how Node.js works with callbacks registered that reports back when a transaction is completed.

I will in a later blog discuss how an application can be designed to make optimal use of the asynchronous API to enable the application to reach these …

70x faster joins with AQL

The new GA MySQL Cluster 7.2 Release (7.2.4) just announced by Oracle includes 2 new features which when combined can improve the performance of joins by a factor of 70x (or even higher). The first enhancement is that MySQL Cluster now provides the MySQL Server with better information on the available indexes which allows the MySQL optimizer to automatically produce better query execution plans. Previously it was up to the user to manually provide hints to the optimizer. The second new feature is Adaptive Query Localization which allows the work of the join to be distributed across the data nodes (local to the data it’s working with) rather than up in the MySQL Server; this allows more computing power to be applied to calculating the join as well as dramatically reducing the number of messages …

So lets say you upgraded from MySQL 5.1 to Percona Server 5.5 and instead of expected performance improvement you see your performance being worse. What should you do ?
First if you followed MySQL upgrade best practices such as testing your workload with pt-upgrade the chances of this happening are rather slim. But lets assume you have not followed these recommendations to the book or some things just slipped through.

First lets talk about what you should not do. You should not panic and go change all kind of configuration options in case you get lucky. Sometimes you do but more often you just waste your time. Instead try to understand what has changed and what exactly changed for worse.

I am speaking about MySQL …

I think the SQL_MODE setting is a sort-of good idea, but that the implementation of it is useless, more or less. What I would want from a feature like this is to allow me to control the quality of the data in the database, but that is not what SQL_MODE does, regrettably.

Rather, what SQL_MODE does is define a default quality checking level for an individual session. This is NOT the same thing as guaranteeing that data has certain qualities in the database.

1. To begin with, a specific session can change it. If you want to insert an invalid data, just set SQL_MODE=ALLOW_INVALID_DATES and go ahead and insert.
2. Secondly, setting this to a non-default value requires no specific privileges at all!
3. Thirdly, as anyone can bypass these checks, you would assume that there was a way to check afterwards that data follows a specific SQL_MODE, or at least a function that I could apply to a table or column …

Generally, MySQL supports connections between clients and the server using several networking protocols. Below are the main protocols which are used by client to connect with MySQL server. TCP/IP Unix socket file Named pipe Shared memory TCP/IP connections are supported by any MySQL server unless the server is started with the –skip-networking option. These connections … Continue Reading …

A tale of Oracle SQL object types, their constructors, and how you use them. This demonstrates what you can and can’t do and gives brief explanations about why.

The following creates a base SAMPLE_OBJECT data type and a sample_table
collection of the base SAMPLE_OBJECT data type.

CREATE OR REPLACE TYPE sample_object IS OBJECT
(id       NUMBER
,name     VARCHAR2(30));
/
 
CREATE OR REPLACE TYPE sample_table IS TABLE OF sample_object;
/

If the base SAMPLE_OBJECT data type were a Java object, the default constructor of an empty call parameter list would allow you to construct an instance variable. This doesn’t work for an Oracle object type because the default constructor is a formal parameter list of the object attributes in the positional order of their appearance in the declaration statement.

The test case on this concept is:

Oracle is delighted to announce the immediate availability of the production-ready, GA release of MySQL Cluster 7.2, available for download under the GPL, and as part of the commercial MySQL Cluster Carrier Grade Edition, including management tools, product certifications and 24x7 global support.

New benchmarks demonstrate MySQL Cluster's ability to support the most demanding web and telecoms workloads, while maintaining 99.999% availability. MySQL Cluster delivered 1 billion queries per minute (17.6 million queries per second), scaled-out across 8 x commodity Intel x86 server nodes, accessed by the NoSQL C++ NDB API.

I was recently talking with a few people and the concept of summary tables came up as solutions for them. The issue is, they never thought of it. Hopefully this post will help them as well as others.
Summary Tables are not a new concept. They are exactly what they sound like, basically summary of existing data. Aggregated tables, materialized views and summary tables are very dominate in data warehouses. You can also take advantage of these concepts as part of your reporting systems as well.
So summary tables are real tables. So you can take advantage of indexes and etc with them. In the examples I am using, I consider them more of a summary table than aggregated tables . Depending on your application and needs it could grow into more of an aggregated tables and or materialized views situation.
How you separate your data and tables is dependent on your reporting and application needs.
The following is a high level example of …

The story happened with a webshop application running on Amazon EC2 microinstances. Actually on two instance. Amazon business model is basically simple, they ask money for only three things: Cpu time, IOPS and network traffic. Everybody (including me) thinks for the first time network traffic will be the bottleneck until they got the first bill (it can be even after one year considering the free tier). Actually in this category the IOPS is the most expensive.

Symptoms

On the cacti diagrams I saw strange datas. The created temp tables on disk and created temp files were much higher than created temp tables. The 67% of temporary tables were created on disk. This is very far from optimal.

Temporary objects in MySQL

Quick patch

II increased the max_heap_table_size and tmp_table_size from …

Tuesday, February 21, 2012
10:30 AM – 3:00 PM
Oracle Office
520 Madison Ave, Suite 3000
New York, 10022

Register Here!

You’re Invited – Oracle’s MySQL Tech Tour Event – New York, NY

Join us for our latest Tech Tour event in New York. Get information and advice direct from the MySQL technical experts. In these sessions, we will demonstrate how to better develop, integrate, secure, and tune MySQL for your application.

Morning Session:

MySQL Fundamentals

Fundamental Capabilities
Deployment options
Development tools
Suggested next steps

Afternoon Session: Best Practices

Deploying Large & Mission Critical MySQL Applications

Architecture
High Availability & …