Thanks for the information, it has been very useful to me, best regards of cimaware.com

The FromDual Ops Center for MariaDB and MySQL (focmm) is a browser-based Graphical User Interface (GUI) to operate and manage your MariaDB and MySQL database farms (2 - 99 instances) more easily.
If further provides tools to implement your own Database as a Service (DBaaS) infrastructure.

FromDual Ops Center covers the full life-cycle of a database:

• Installation of your database from different Repositories.
• Configuration of your database instance.
• Track configuration changes of your database instance.
• Managing and Operating of your database like Starting and Stopping.
• Creating Schema and User for DBaaS offerings.
• Front-end for Backup and Restore.
• Monitoring of your database instances.
• It enables you to price your databases for external users (DBaaS).

FromDual Ops Center cannot only manage your database instances it further assists you with database Clustering:

• MariaDB/MySQL Master/Slave Cluster
• MariaDB/MySQL Galera Cluster
• Starting, Stopping, Switchover and Failover of Clusters.

A database Cluster is …

Will you be at a loss having a MySQL or MariaDB database problem? Will you need urgently help from experienced FromDual staff when your production MySQL or MariaDB database stops working? Or do you need a third party opinion for one of your MySQL or MariaDB solutions?

FromDual offers you vendor independent database support for MySQL (community and enterprise edition), MariaDB (community and enterprise edition) and Percona Server for all versions starting with 4.0.

How we provide support?

Our FromDual support engineers assist you actively fixing your MySQL or MariaDB database problems. The following media are used in a support case:

• remote login (ssh, screen),
• TeamViewer,
• Skype,
• IRC,
• phone,
• our ticket system, etc.

Use of FromDual tools and monitoring is included

All FromDual support customers are additionally eligible to use the following FromDual tools for the scubscribed instances:

• MySQL environment MyEnv for multi-instance set-ups,
• FromDual Performance Monitor for MySQL and MariaDB (fpmmm) for a better …

Hello,

there are companies which earn tens of thousands of Euros per hour with their MySQL databases. Other companies operate their ERP system on MySQL, to which 1000 employees are attached to. Is the database down 1000 people are not working any more until the system is working again! Downtime costs starting at EUR 30'000.- per hour upwards.

Support through the MySQL specialists?

These companies have properly planned operations of their MySQL databases, designed their production systems redundantly and assured their support through MySQL specialists with a support contract, is oneself thinking. Far wrong: Ever and ever we can see companies, which operates their MySQL databases without planning, no redundancy and external support.

In an emergency, mostly the own employees lack in experience, lack in exercising and because they have to care about all other systems as well, they are overburdened in such a situation and would like to call a MySQL specialist for assistance.

Lower the costs!

Downtime of your MySQL …

From throughput point of view I agree. What about from response time point of view? We fire a query once and never again (the same).

Hello Øystein,

Unfortunately I have to agree! MySQL is damn fast/cool and hardware nowadays as well. And having much RAM and fast multi-core CPU's sucks from a tuner point of view ;-)

In a production system, I think what matters is the steady-state performance of the system, not what happens just after system restart.

95% of all statistics is made up …

If you consider all users of MySQL including those using it to store their record collection, I would think that more than 95% does not have any performance problem.

(100% of the statistics in this comment is made up …)

Hi Massimo,

From my last 14 years experience as DBA and DB consultant (Oracle, MySQL, MS-SQL Server, PostgreSQL). I have seen several hundreds? different DB performance problems in these years and only few of them (1 upon 20? or even less) where real throughput problems.
Most of them where single query performance problems (including optimizer/optimizing and configuration problems).

Regards,
Oli

Hello Øystein,

The high/bad standard deviation with MySQL 5.6 comes from some very high outlier in the beginning of the test. I did not delete this outlier because I have seen this as an unfair treatment to the other releases which did not show this phenomena.

I dropped the first measurement always (memory heat-up).

And I did all the test series in the same way. So more or less same fair/unfair for all releases.

By ignoring the outlier you still can see around 5% changes in performance.

About statistics: I never understood these guys. But my feeling tells me that in measurement 4 to 11 5.7 is sligthly faster than 5.6. Possibly not significant but sligthly and measureable...

And in parctice. I understand your skipping of 10 queries before measuring. But how realistic is this in real DB life? The first shot must be fast...

you wrote: " But most of the MySQL users (95%) do not have a troughput problem but a single query performance problem (I assume here that this is true also for Oracle, MS-SQL Server, DB2, PostgreSQL, etc.)."

where the 95% come from?

Hi,

With the given standard deviation for the MySQL 5.6.15 run, the 95% confidence interval for the average is between 5.85 and 8.59, if my rusty statistics knowledge serves me right. Hence, the difference between 5.6 and 5.7 is not statiscally significant.

How did you run this test? Did you run some warmup queries before starting the measurements? I have found that the InnoDB adaptive hash index will gradually be built during the first runs of a query. Hence, I usually run a query 10 times before starting measurements.

Thanks, Morgan!

Hello Stephane,

Sorry, I cannot completely follow you what you want me to do. But please feel free to reproduce. All needed information are available I think!

Oli

Good post.

You are raising a real topic here.

This Query is just in memory full table scan with thread state transition to make a join.

I think MariaDB found the issue that your test case perfectly match http://kristiannielsen.livejournal.com/17598.html

Can you try to validate this ? Why do you see so much latency variation of the same in memory query what is wrong with the hardware ?

Now if you think of it, how many people will read that blob and just think MySQL is bad doing a simple join in 6s, I think it’s a lot :)Have you also try to play with nmap barrier of you hardware ?

/steph

MySQL single query performance - the truth!

As suggested by morgo I did a little test for the same query and the same data-set mentioned in Impact of column types on MySQL JOIN performance but looking into an other dimension: the time (aka MySQL versions).

The answer

To make it short. As a good consultant the answer must be: “It depends!” :-)

The test

The query was again the following:

SELECT *
  FROM a
  JOIN b ON b.a_id = a.id
 WHERE a.id BETWEEN 10000 AND 15000
;

The Query Execution Plan was the same for all tested releases.

The relevant MySQL variables where used as follows where possible. Should I have considered join buffer, or any other of those local per session buffers (read_buffer_size, read_rnd_buffer_size, join_buffer_size)?

innodb_buffer_pool_size        = 768M
innodb_buffer_pool_instances   = 1
innodb_file_per_table          = 1

The results

Thanks for running the test.

Hi Joffrey,

The easy answer first: 5 - 15 iterations until it got a stable response time and then I took the most optimistic value. Not very scientific, I know... But I think good enough for a reliable statement.

About InnoDB 4 byte alignment. No, it is not.

Some proves beside consulting various documentations from InnoDB table monitor:

CREATE TABLE `align` (
  `id` int(11) NOT NULL DEFAULT '0',
  `tiny` tinyint(4) DEFAULT NULL,
  `small` smallint(6) DEFAULT NULL,
  `medium` mediumint(9) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

+----+------+-------+--------+
| id | tiny | small | medium |
+----+------+-------+--------+
|  1 |    1 |     1 |      1 |
|  2 |    2 |     2 |      2 |
|  3 |    3 |     3 |      3 |
+----+------+-------+--------+

TABLE: name test/align, id 97, flags 1, columns 7, indexes 1, appr.rows 3

COLUMNS: id: DATA_INT DATA_BINARY_TYPE DATA_NOT_NULL len 4;
         tiny: DATA_INT DATA_BINARY_TYPE len 1;
         small: DATA_INT …

Hi Joffrey,

I think NDB tables use 4-byte alignment not InnoDB:

NDB tables use 4-byte alignment; all NDB data storage is done in multiples of 4 bytes. Thus, a column value that would typically take 15 bytes requires 16 bytes in an NDB table. For example, in NDB tables, the TINYINT, SMALLINT, MEDIUMINT, and INTEGER (INT) column types each require 4 bytes storage per record due to the alignment factor.

Check this manual page for reference.

Thanks,
Abdel-Mawla

Hi Oli,

thanks for the nice comparison chart ! In InnoDB, isn’t (Tiny|Small|Medium)-int internally aligned to 4 bytes, and thus stored as 4bytes int ? How many iterations did you run the query ?

Thanks in advance! /Joffrey