Read the original article at Thoughts on Upcoming MySQL 5.6 Defaults

During Oracle Open World 2012 and the parallel MySQL Connect conference, the new 5.6 version was announced. It’s only release candidate right now, but that means the GA release is just around the corner. With that James Day has posted changes to various of the new parameter defaults. Many of them you may not run [...]

For more articles like these go to Sean Hull's Scalable Startups

Related posts:

1. 5 Ways to Boost MySQL Scalability
2. 5 Ways to fortify MySQL …

MySQL tries to be smart and to cram as much performance out of available hardware, and one thing that most MySQLers knows is that opening a table (ie. opening the frm file, and depending on the storage engine, any additional files related to the table) is expensive from a performance point of view. When we see the opened_tables status variable increasing, we tend to increase the table_open_cache setting and keep as many tables as possible open and avoid reopening them.

When it comes to MyISAM though, this has a nasty side-effect. When the server crashes and there are MyISAM tables open, these might well need to be REPAIRed. And if you have big MyISAM tables, this is an issue. Let's say that your application use several MyISAM tables, with the same content, and that you create new tables after a certain time, to keep the size of each individual table down? There are other reasons why you have this effect also, say a large …

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.