A Proxy is a software that acts as an intermediary between clients seeking resources from servers.

In the MySQL world, the most common proxies are:
- MySQL Proxy
- HAProxy

While the first one is able to understand the MySQL protocol and provides a lot of functionality, it is often not used in production as tagged as very slow, unstable and CPU hungry.
HAProxy is widely used with MySQL setups as it is said to be very efficient in term of performance and very stable, although it acts as a Layer 7 proxy and doesn’t recognize the MySQL protocol.

The adjectives slow and efficient are meaningless words if not supported by numbers and fact, so let’s try to make some real comparison.



We are going to tests performance of mysqld vs MySQL Proxy vs HAProxy running 2 very simple benchmark :
1) sysbench OLTP read-only;
2) mysqlslap …

I concluded my previous post stating that what is really missing is a proxy that:- is stable as HAProxy- scales like MySQL Proxy- is rich of features

That is what is driving my development for ProxySQL : a high performance proxy for MySQL.


Some background first.In the past I have worked with customers that, after providing them a detailed SQL review on how to improve performance rewriting queries, answer with the usual "we can't modify the queries" , and the most common reasons for this are, among others:- the queries are generated by ORM ;- they don't own the application ;- they don't have the time to dig the code and rewrite the queries .
It is a quite spread false believe that adding indexes and tuning MySQL can magically improve performance of badly written queries, but the truth is far from that. …

I was a bit wrong in my previous post. MySQL 5.6 does allow you to supply a fsp with CURRENT_TIMESTAMP (thanks Roy).

mysql> SELECT CURRENT_TIMESTAMP,CURRENT_TIMESTAMP(6);
+---------------------+----------------------------+
| CURRENT_TIMESTAMP   | CURRENT_TIMESTAMP(6)       |
+---------------------+----------------------------+
| 2013-10-27 10:38:59 | 2013-10-27 10:38:59.182530 |
+---------------------+----------------------------+
1 row in set (0.00 sec)

It however feels a bit weird to me as the CURRENT_TIMESTAMP is often used without () and doesn't look like a function. So when I tried to use a CURRENT_TIMESTAMP with a fsp of 6 it was not behaving how I expected it to be:

mysql> CREATE TABLE t1 (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP(6));
ERROR 1067 (42000): Invalid …

MySQL 5.6 includes support for microsecode timestamp resolution, which is a great new feature.

To get the current timestamp in MySQL 5.5 you could use NOW(), SYSDATE() or CURRENT_TIMESTAMP.

mysql_5.5> SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP;
+---------------------+---------------------+---------------------+
| NOW()               | SYSDATE()           | CURRENT_TIMESTAMP   |
+---------------------+---------------------+---------------------+
| 2013-10-26 15:46:24 | 2013-10-26 15:46:24 | 2013-10-26 15:46:24 |
+---------------------+---------------------+---------------------+
1 row in set (0.01 sec)

If we run the same statement in MySQL 5.6 the output is the same. This is great for compatibility, but what if we want those microsecond timestamps?

 …

Today when I was studying for the MySQL 5.6 exams.

I was studying for these two items:

• Create and utilize table partitioning

• Obtain MySQL metadata from INFORMATION_SCHEMA tables

The first step is to create a table, partition it with a hash.

mysql> CREATE TABLE pfoo (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255))
    -> PARTITION BY HASH(id) PARTITIONS 4;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO pfoo(name) VALUES('test01'),('test02'),('test03'),('test04'),
    -> ('test05'),('test06'),('test07'),('test08'),('test09'),('test10'),('test11');
Query OK, 11 rows affected (0.00 sec)
Records: 11  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM pfoo;
+----+--------+
| id | name   |
+----+--------+
|  4 | test04 |
|  8 | test08 |
|  1 | test01 |
|  5 | test05 …

For the MySQL DBA Exam, I wrote that I needed to brush up on Security and High Availability techniques. So let’s take a look at these two in more detail.

Security

There were quite a lot of enhancements to security introduced in MySQL 5.6, and I want to make sure I’m not caught off guard by new questions. The major changes (with links to manual pages) are:

• Password expiration support
• Enforce password policy such as minimum length or …

People really don’t like Oracle. Enough so that SkySQL just got $20 Million in funding from Intel to help it continue to build a MySQL alternative. Now personally I don’t have the hatred that a lot of people do for Oracle, but when I look at the pricing and service offerings around Oracle’s database, Solaris operating system, and even things like ATG, I know that we can offer them comperable solutions at half the price, with far better service, so I get why people want to try to find alternatives to Oracle.

But here is what I don’t get. This week I went to the All Things Open conference and while I was there, I happened to catch the tail end of a SkySQL talk on new MariaDB features. One of the features that he was describing apparently has …

Introduction For a long time MySQL server supported only one trigger for every action (INSERT, UPDATE, DELETE) and timing (BEFORE or AFTER). In other words, there could be at most one trigger for every pair (action, timing). It means that a user couldn’t assign for example two BEFORE INSERT triggers for the same table t1. To workaround this restriction and allow several actions to fire on some table event, a user had to implement several stored procedures (one for each activity that would be implemented as independent trigger), create trigger for a table and call this stored procedures from the trigger. As of MySQL 5.7.2 this limitation has been removed. It means that starting the MySQL 5.7.2 a user can create for example, two BEFORE INSERT triggers, three AFTER INSERT triggers and four BEFORE UPDATE triggers for table t1. And this triggers will be called in the prescribed order determined (in generally) by the sequence in which triggers were …

MySQL 5.6 introduced a new feature called extended secondary keys. We get a lot of questions about it and find that most of them come from a few incorrect assumption. In this post I’ll try to get rid of the confusion once and for all. Famous last words… here goes:
Q1: Do I need to do anything to enable extended secondary keys?No, nothing at all. It’s on by default and I can’t see any sensible reason why you would want to disable it. However, it is possible to disable it by tuning the optimizer_switch: SET optimizer_switch=’use_index_extensions={on|off}’.

Q2: Does extended secondary keys only work with InnoDB?

No, it should work with any storage engine that uses the primary key columns as reference to the row, which means most storage engines with clustered primary keys. I say “should” because it requires a minimum of work from the storage engine provider; it must …

We just announced Percona Server 5.6.14 release, and I decided to make this build with the TokuDB 7.1 engine.
The build is available here in the TESTING area.

You are welcome to play with these binaries, but keep in mind:

• This is an experimental build and not supposed to be used in production environment
• The binaries built on Ubuntu 12.04 LTS server and may not work on other Linux distributions

The post Percona Server 5.6.14 with TokuDB 7.1 Experimental build appeared first on MySQL Performance …