Recently, our CTO, Martín Farach-Colton had a chance to talk about scaling MySQL and MariaDB with Roberto Zicari of ODBMS.

In the article, Martin states “While I believe that one size fits most, claims that RDBMS can no longer keep up with modern workloads come in from all directions. When people talk about performance of databases on large systems, the root cause of their concerns is often the performance of the underlying B-tree index.” He also notes how “Fractal Tree Indexes put you on a higher-performing tradeoff curve. Query-optimal write-optimized indexing is all about making general-purpose databases faster. For some of our customers’ workloads, it’s as much as two orders of magnitude faster.”

To read the full article, and to …

Headquartered in Finland, EmblaCom Oy provides turnkey and cloud-hosted voice solutions to mobile operators around the globe. Since launching the original mobile private branch exchange (PBX) in 1998, the company has focused on helping its partners provide efficient voice communications to their key business customers. The company’s voice solutions are used by millions of subscribers, worldwide.

EmblaCom Oy needed to replace several database engines with a standardized, scalable, development-friendly database solution to maximize availability and cut costs. The company chose MySQL Cluster Carrier Grade Edition, which has maximized accessibility to EmblaCom’s services for its clients and their hundreds of thousands of subscribers. The initiative has also reduced, by half, the cost of the database solution installation for customers, as well as lowered maintenance and …

MySQL 5.6.7 RC is there, so I decided to test how it performs in tpcc-mysql workload from both performance and stability standpoints.
I can’t say that my experience was totally flawless, I bumped into two bugs:

• MySQL 5.6.7 locks itself on CREATE INDEX
• MySQL 5.6.7-rc crashed under tpcc-mysql workload

But at the end, is not this why RC for? And Oracle asked for a feedback, so I do my part.

• Benchmark date: Oct-2012
• Benchmark goal: Test how MySQL 5.6.7 performs
• Hardware specification
  • Server: Dell PowerEdge R710
  • CPU: 2x Intel(R) Xeon(R) CPU E5-2660 0 @ 2.20GHz
  …

Most of the time, we use string datatype column like char or varchar to store ip address values. There is a way to store ip address value in numeric datatype column like unsigned interger. There is miscellaneous functions INET_ATON(), INET_NTOA() using which it's possible

INET_ATON() Return the numeric value of an IP address
INET_NTOA() Return the IP address from a numeric value

SELECT INET_ATON('192.168.1.5') AS IP;
+------------+
| IP         |
+------------+
| 3232235781 |
+------------+
1 row in set (0.00 sec)

The generated number is always in network byte order. For example192x256^3 + 168x256^2 + 1x256^1 + 5

SELECT INET_NTOA('3232235781') AS IP;
+-------------+
| IP          |
+-------------+
| 192.168.1.5 |
+-------------+
1 row in set (0.00 sec)

Tags: IP address …

James Day just posted the great summary of defaults changes in MySQL 5.6 compared to MySQL 5.5
In general there are a lot of good changes and many defaults are now computed instead of hardcoded. Though some of changes are rather puzzling for me. Lets go over them:

back_log = 50 + ( max_connections / 5 ) capped at 900 – The large backlog is needed when there is a high rate (spikes) of connections which is rather unrelated to number of connections. You can get 1000s of connections a second even from as little as 100 connections, hence max_connections would be small. I think this is the case where somewhat higher fixed default (even 900) would be better than trying to be overly smart.

host_cache_size = 128 + 1 for each of the first 500 max_connections + 1 for every 20 max_connections over 500, …

For the first time since the beginning of my long long career, I decided today to use an advice tool to check my MySQL configuration.

I decided to launch pt-variable-advisor and mysqltuner V2 on one of my MySQL server.
Here are the result outputs :

Then I ask myself what to do with these results, just raw results without much explanations, these tools are supposed to be done for beginners…

Just one example from mysqltuner : Too many temporary tables are being written to disk.  Increase max_heap_table_size and tmp_table_size
If I set the size of my temporary tables at 16Mb, what that’s means, I have to increase to 32Mb, 64Mb or more ?
…

MySQL 5.6 is now RC, I suppose this means that all features that were intended to be in release are pushed, so it’s time to take a look and see what’s going to be in MySQL 5.6 GA.

I decided to look at subquery optimizations and compare them to what we’ve got in MariaDB 5.3/5.5. In case you don’t know, subquery optimizations in MySQL 5.6 and MariaDB come from a common ancestor - MySQL 6.0 alpha, which was released in spring 2009 and then abandoned because there were too many unstable features pushed into it.

Then, both MariaDB team and Oracle salvaged subquery code out of the 6.0 mess, and worked to get it in shape for release. MariaDB released its results in GA quality in April 2012 as MariaDB 5.3, which was quickly followed by MariaDB 5.5.

Inside MariaDB, we’ve …

I wanted to post a big thanks to the Chinese MySQL Bloggers.  Almost a month ago we published the  Chinese version of Planet ( my previous blog post ). In the last month, we have collected 68 different blog posts about MySQL across 8 different MySQL Bloggers.

 Thanks for the support, this is an encouraging start.

I was looking through James Day’s post on upcoming changes to MySQL configuration defaults in version 5.6, and one caught my eye in particular: innodb_autoextend_increment changes from 8 to 64MB, and now affects *.ibd files.

I don’t see any further documentation on this yet; I assume that’s in the works. I’m curious how this will actually behave. What will be the initial size of an empty InnoDB table using innodb_file_per_table? There might be some unintended consequences. Here’s a couple I can think of:

1. Create a table and it’s 64MB from the start. This would cause some people to be unable to use InnoDB. In fact, as it currently stands, an empty table is 16kb, and I’ve heard some people say that’s a problem for them because they create many small tables and the 16kb …

Choosing a good InnoDB log file size is key to InnoDB write performance. This can be done by measuring the amount of writes in the redo logs. You can find a detailed explanation in this post.

To sum up, here are the main points:

• The redo logs should be large enough to store at most an hour of logs at peak-time
• You can either use the LSN in the SHOW ENGINE INNODB STATUS OUTPUT or the Innodb_os_log_written global status variable (if you are a Percona Server user, the LSN is also given by the Innodb_lsn_current status variable)

While reviewing the recommendation I made for a customer, one of my colleagues told me I was wrong in my redo log size calculations. After each one double checked the calculations, it turned out that we experienced something not expected:

…