varokism

Fine tuning read_rnd_buffer_size and read_buffer_size

2011-12-21T13:08:00.000-08:00

Those variables are easy to tune.

The evidence popup here between 128 and 256K for in memory workload.

My SQL SQL layer is optimistic and think that data will come from the storage engine at the speed of the memory bus , but in practice for IO bound workload and range scan in the table order it could not be the case , for MyISAM when reading from tables on disk, IOs are aligned on the read buffer so it may be of an intrest to increase it here.

In myisam.cc my_default_record_cache_size is set to global_system_variables.read_buff_size;

To show IO size :
strace -p id_mysqld -e read -e write

If monitoring smaller IO request size from iostat -xm, it may be that you are suffering fragmentation that does not help the FS cache to serve you efficiently. Optimize table ,Alter table order is a great help in MyISAM

In InnoDB the difference come with prefetch trigger when no fragmentation. It is the case when rows are inserted in increasing primary key order. So conservative read and rnd buffer values will speed up the data move from the buffer_pool to the SQL thread with almost no impact on the disk speed rate .

Note that the read_rnd_buff will size the thread record cache when rr_sequential and rr_quick in case of full scan and some range scan

In record.cc

static int init_rr_cache(THD *thd, READ_RECORD *info)
info->cache_records= (thd->variables.read_rnd_buff_size /
(info->reclength+info->struct_length));

SUM for the 5 best models in 5 best brands

2011-12-19T14:45:00.000-08:00

As we can see in the solution:

Expression are evaluated in the column order in the select clause.

This is the same for the where clause. An advice is to test first the expression that get more chance to return false at the head of the where conditions :

SET @BRAND=0;
SET @ct=0;
SELECT
*
FROM
(SELECT
IF(@BRAND<>id_BRAND,@ct:=0 ,@ct:=@ct ) ,
@ct:=@ct+1 AS ct,
@BRAND:=id_BRAND,
t2.*
FROM
(SELECT
st.ID_BRAND,
st.ID_MODEL,
SUM(COMPTE) AS total,
AVG(totalm)
FROM
STAT st JOIN
(SELECT
ID_BRAND,
SUM(COMPTE) AS totalm
FROM
STAT
GROUP BY
ID_BRAND
ORDER BY
totalm DESC
LIMIT 5
) as t USING( ID_BRAND)
GROUP BY
st.ID_BRAND,
st.ID_MODEL
ORDER BY
st.ID_BRAND,
total DESC
) AS t2
) AS t3
WHERE
ct <=5

Back on disk temporary tables

2011-12-15T04:01:00.000-08:00

We already blog a lot on temporary tables on disk, how this could be bad for your workload and should be avoid. Each temporary table will open a file descriptor, external kernel call and by nature a well know file system slow operation.

We can point this benchmark simulating a working mail server

Monitoring of such queries can be trace via the status of

created_tmp_disk_tables 

Evidence of what is happening watching many Aria or MyISAM tables created on disk :

/usr/sbin/lsof | grep "mysql" | grep "#"
mysqld     1855     mysql 1658u      REG                8,1       8192    1505932 /tmp/#sql_73f_1.MAI
mysqld     1855     mysql 1659u      REG                8,1       8192    1505936 /tmp/#sql_73f_1.MAD
/usr/sbin/lsof | grep "mysql" | grep "#"
mysqld     1855     mysql 1650u      REG                8,1       8192    1505946 /tmp/#sql_73f_1.MAI
mysqld     1855     mysql 1654u      REG                8,1       8192    1505951 /tmp/#sql_73f_1.MAD

Now in MariaDB and Percona Server you can found which queries causing you trouble with the slow query log

set global log_slow_verbosity=2; 

And you can retieve this information in the slow log :

# User@Host: root[root] @ localhost []
# Thread_id: 93 Schema: test QC_hit: No
# Query_time: 0.00157 Lock_time: 0.000034 Rows_sent: 4 Rows_examined: 12
# Full_scan: No Full_join: No Tmp_table: Yes Tmp_table_on_disk: Yes
# Filesort: Yes Filesort_on_disk: No Merge_passes: 0
SET timestamp=1323937380;
select * from (select user_id,created, (select user_email from login2 b where b.user_id=a.user_id ) from login2 a where a.user_id IN (100005, 100002, 100004, 100003)) as a order by created;
Solutions to fixe the issue can be :

Add an index to cover the resultset order
Increasing tmp_table_size
Increasing max_heap_table_size
Removing blobs from your queries if you need to sort them in memory

But somtimes this is not enouth let's have a deeper look :

select * from (
select
user_id,
    created,
    (select user_email from login2 b where b.user_id=a.user_id ) as mail
from
    login2 a
where
    a.user_id IN (100005, 100002, 100004, 100003)
) as a order by created;
+---------+---------------------+---------------------+
| user_id | created             | mail                |
+---------+---------------------+---------------------+
| 100002 | 2011-01-11 17:16:45 | stephane@skysql.com |
| 100003 | 2011-01-11 17:16:45 | stephane@skysql.com |
| 100004 | 2011-01-11 17:16:46 | stephane@skysql.com |
| 100005 | 2011-01-11 17:16:46 | stephane@skysql.com |
+---------+---------------------+---------------------+

And inside the table :

CREATE TABLE `login2` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_name` varchar(50) DEFAULT NULL,
  `user_email` varchar(2000) CHARACTER SET utf8 DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=102048 DEFAULT CHARSET=latin1;

What you can note here is :

`user_email` varchar(2000) CHARACTER SET utf8 DEFAULT NULL

That mail column is embeded in the derivated query and sorted later on the upper query

Well for Mounty it tooks few secondes to point me that this is comming from a performance mechanism hardcoded in a constant of the source code

CONVERT_IF_BIGGER_TO_BLOB

This set to 512B and the reason is that in most case sorting would be less performant starting in memory and then converted to disk later.

It is use like this :

sql/item.cc:  If max_length > CONVERT_IF_BIGGER_TO_BLOB create a blob @n
sql/item.cc:  if (max_length/collation.collation->mbmaxlen > CONVERT_IF_BIGGER_TO_BLOB)
sql/item.cc:    if (fixed_length && max_length < CONVERT_IF_BIGGER_TO_BLOB)
sql/item_sum.h:    if (max_length/collation.collation->mbmaxlen > CONVERT_IF_BIGGER_TO_BLOB )
sql/sql_select.cc:      if ((*tmp->item)->max_length >= CONVERT_IF_BIGGER_TO_BLOB)
sql/unireg.h:#define CONVERT_IF_BIGGER_TO_BLOB 512    

So the max lenght of varchar for not touching this case would be 512

Alter table login2  modify column  user_email varchar(512) charset utf8;

# Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No

Alter table login2  modify column  user_email varchar(513) charset utf8;  

# Full_scan: Yes Full_join: No Tmp_table: Yes Tmp_table_on_disk: Yes

Now you have a solution to fixe this in MariaDB 5.3 with the work done on the optimizer :

just set :

set global optimizer_switch='derived_merge=on'

# Full_scan: Yes  Full_join: No  Tmp_table: Yes  Tmp_table_on_disk: No 

Kudo to Sergei Petrunia but wait this is not all

derived_merge=off

mysqlslap --create-schema=test -q testPV2.sql -c2 --number-of-queries=100000 -uroot -ptoto
Benchmark
    Average number of seconds to run all queries: 137.111 seconds
    Minimum number of seconds to run all queries: 137.111 seconds
    Maximum number of seconds to run all queries: 137.111 seconds
    Number of clients running queries: 2
    Average number of queries per client: 50000

derived_merge=on

mysqlslap --create-schema=test -q testPV2.sql -c2 --number-of-queries=100000 -uroot -ptoto 

Benchmark
    Average number of seconds to run all queries: 14.540 seconds
    Minimum number of seconds to run all queries: 14.540 seconds
    Maximum number of seconds to run all queries: 14.540 seconds
    Number of clients running queries: 2
    Average number of queries per client: 50000

More the is a bonus here optimizer take less cpu cycles compare to Vanilla MySQL Oracle tm

+----------------------+----------+----------+-----------+
| c2                   | ma       | my       | diff      |
+----------------------+----------+----------+-----------+
| cleaning up         | 0.000004 | 0.000004 | 0.000000 |
| closing tables      | 0.000008 | 0.000028 | -0.000020 |
| executing           | 0.000034 | 0.000036 | -0.000002 |
| freeing items       | 0.000030 | 0.000030 | 0.000000 |
| init                | 0.000118 | 0.000019 | 0.000099 |
| logging slow query | 0.000003 | 0.000003 | 0.000000 |
| Opening tables      | 0.000049 | 0.000154 | -0.000105 |
| optimizing          | 0.000061 | 0.000042 | 0.000019 |
| preparing           | 0.000085 | 0.000050 | 0.000035 |
| query end           | 0.000003 | 0.000005 | -0.000002 |
| removing tmp table | 0.000081 | 0.000058 | 0.000023 |
| Sending data        | 0.000681 | 0.000609 | 0.000072 |
| Sorting result      | 0.000264 | 0.000031 | 0.000233 |
| starting            | 0.000165 | 0.000148 | 0.000017 |
| statistics          | 0.000365 | 0.000794 | -0.000429 |
| System lock         | 0.000014 | 0.000008 | 0.000006 |
| Table lock          | 0.000015 | 0.000308 | -0.000293 |
+----------------------+----------+----------+-----------+

Alter table engine TokuDB

2011-11-18T13:00:00.000-08:00

At the le MUG some questions raised on TokuDB, i hope this post will answer more in deph details

InnoDB like any other general purpose RDBMS can be really bad for online back-office with big tables, despite following some usual practice and OLTP normalize schema.

Having indexes in memory
Touching a minimum set of rows for secondary index scan
Scanning some range in primary key order

This happen frequently when

The working dataset is growing 2 to 100 times the size of the hardware memory
Joining big tables jumping from secondary indexes to clustered table
Increase concurrency on a big tables with range scan starting from a random point in the PRIMARY KEY

It can be highlight watching random IO disks until 100% disk usage

iostat –xm 2
Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await svctm %util
sdb               0.00     0.00 305.00 101.00     4.68     0.28    25.04     1.01    2.48   2.41 97.65
sdb               0.00     0.00 285.50 110.50     4.37     0.34    24.36     1.02    2.59   2.47 98.00
sdb               0.00     0.00 361.50 158.50     5.59     2.73    32.76     1.18    2.27   1.87 97.00

iostat
Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn
sdb            1215.50     37040.00       335.00      74080        670

Today we force protection of the InnoDB buffer pool by setting all range query with a minimum constant date of today interval minus 2 month on most queries but it is not always possible and limit features

Benchmark

IBM 5030 M3 16 Nehalem cores 32G RAM XFS RAID10 4 SAS 15Ktpm

Schema extraction for the bench :

	InnoDB	TokuDB
Total Tablespace size	249G	155G
Index size	31G	192G
Buffer pool	24G	14G

Every primary key are composed of a timestamp and a bigint UUID

Multiple big tables, many rows and big avg record size

MariaDB> select table_name, table_rows/1000000 from information_schema.tables order by table_rows desc limit 20;
+--------------------+--------------------+
| table_name         |Millon rows         |
+--------------------+--------------------+
| xxx2             |          52.8748 |
| xxx1               |            44.5123 |
| xxx3l            |            31.6692 |
| xxx4           |            30.0430 |

MariaDB> select table_name,avg_row_length from information_schema.tables where table_name in('xxx1','xxx2','xxx3l','xxx4');
+-------------+----------------+
| table_name | avg_row_length |
+-------------+----------------+
| xxx2      |              237 |
| xxx3l     |              610 |
| xxx4    |               87 |
| xxx1      |              248 |

Index and data usage for the slow queries

Let’s run a test of 100 queries and see what is happening inside the storage

MariaDB> show table_statistics;
| Table_name          | Rows_read |
| xxx2                |    750284|
| xxx1                |   3118583 |

MariaDB> show index_statistics;
|Table_name     | Index_name                  | Rows_read   |
| xxx2          | apnum_idx                   |     64 475 |
| xxx1          | trsitemachdateheure_idx     |   3 023 319 |
| xxx2          | apdatesitemach_idx          |    456 567 |
| xxx1          | PRIMARY                     |     93 381 |
| xxx2          | PRIMARY                     |     228 113 |

From here we now estimate for every 100 eavy queries we touch

500M of row data ((750284*237) +(3118583*87))/1024/1024
47M of row index ((64475+3023319+456567)*12+(93381+228113)*22)/1024/1024
90% secondary indexes key read in xxx1, a 21GB table and 100G in production
10% secondary indexes key read xxx2 a 22GB table size and 100G in production

We estimate 50% of the data in memory on the bench (44G out 24G)
We estimate 10% of the data in memory on the production (200G out 24G)

BENCHMARK

We collect 1000 queries in the slow query log running beetween 10s to 30s inside InnoDB and replace the interval 2 month with 9 month.

	InnoDB key read /s	TokuDB key read /s
Full memory workload 100 slow queries concurrency 1	Flat status 1M<handler_read_nxt<1,2M 12s : 10 tps	Flat status 1M<handler_read_nxt<1,2M 9s : 10 tps
Full memory workload 100 slow queries concurrency 4	Flat status 1M<handler_read_nxt<3M 4s : 40 tps	Negative scalability 400K<handler_read_nxt<1M 14s : 7,4 tps
50% memory workload 1000 slow queries concurrency 1	Eratic status 1K <handler_read_nxt<2M 3730s : 0,26 tps	Flat status 200K<handler_read_nxt<400K 854s : 1,2 tps
10% memory workload 1000 slow queries concurrency 1 buffer_pool 5G	still waiting	Flat status 150K<handler_read_nxt<280K 1262s : 0,8 tps

Query time in second	InnoDB	TokuDB
Q1	9,05	0,22
Q2	17,56	0,3
Q3	5,95	0,29
Q4	19	0,29
Q5	0,24	0,01
Q6	9,7	0,09
Q7	2,01	0,23

InnoDB stay the reference for in memory concurency workload and this was expected as TokuDB is the challenger, but now we can already state that for any IO bound workload it will make your day.

There are many way to fixe those bad queries (de normalisation project) but nothing is more simple then the alter table engine=TokuDB on a slave and don't forget to alter secondary indexes using clustered index syntax .

Install and forget a story of stability...

2011-05-09T15:30:00.000-07:00

Are you rebooting your MySQL server at night?

I'de like to share input send to me by a leading MySQL provider, mostly deploying ecommerce and telco applications on Linux.

One instance is up since 1414 days not bad ....

13:02 up 355 days,  4:50,  0 users,  load average: 0.00, 0.00, 0.00
13:03 up 594 days,  5:04,  0 users,  load average: 1.20, 1.25, 1.26
13:03 up 48 days, 22:10,  0 users,  load average: 0.48, 0.38, 0.29
13:03 up 936 days,  7:50,  0 users,  load average: 0.00, 0.00, 0.00
13:03 up 173 days,  2:39,  0 users,  load average: 1.90, 1.79, 1.79
13:04 up 244 days,  3:25,  0 users,  load average: 3.96, 3.66, 3.90
13:04 up 643 days, 15:50,  0 users,  load average: 0.09, 0.08, 0.07
13:04 up 642 days,  5:15,  0 users,  load average: 0.06, 0.02, 0.00
13:05 up 311 days,  1:57,  1 user,  load average: 0.01, 0.03, 0.00
13:05 up 329 days,  4:43,  0 users,  load average: 1.07, 0.55, 0.34
13:05 up 95 days, 11:34,  0 users,  load average: 2.27, 1.74, 1.64
13:06 up 436 days, 23:42,  0 users,  load average: 0.09, 0.10, 0.13
13:06 up 188 days,  2:35,  0 users,  load average: 0.10, 0.19, 0.14
13:12 up 347 days, 12:57,  0 users,  load average: 0.08, 0.16, 0.22
13:12 up 81 days, 11:32,  0 users,  load average: 0.04, 0.19, 0.28
13:12 up 81 days, 11:24,  0 users,  load average: 0.33, 0.40, 0.43
13:12 up 81 days, 11:13,  0 users,  load average: 0.28, 0.35, 0.36
07:39 up 21 days,  6:40,  0 users,  load average: 0.12, 0.14, 0.10
13:13 up 643 days, 16:11,  0 users,  load average: 0.36, 0.19, 0.11
13:13 up 643 days, 16:10,  0 users,  load average: 0.09, 0.07, 0.02
13:13 up 139 days, 35 min,  0 users,  load average: 0.38, 0.35, 0.29
13:14 up 179 days, 11:41,  0 users,  load average: 0.12, 0.07, 0.01
13:14 up 634 days,  4:40,  0 users,  load average: 0.00, 0.00, 0.00
13:14 up 343 days,  3:33,  0 users,  load average: 0.02, 0.05, 0.00
13:14 up 441 days,  2:37,  0 users,  load average: 0.13, 0.31, 0.31
13:19 up 160 days,  8:19,  0 users,  load average: 0.01, 0.05, 0.01
13:20 up 83 days, 22:34,  0 users,  load average: 0.07, 0.10, 0.09
13:25 up 602 days,  1:50,  0 users,  load average: 0.01, 0.03, 0.00
13:25 up 1414 days,  6:07,  0 users,  load average: 0.00, 0.03, 0.00
13:26 up 7 days,  6:32,  0 users,  load average: 0.11, 0.17, 0.20
13:26 up 17 days,  2:38,  0 users,  load average: 0.61, 0.49, 0.40
13:26 up 17 days,  2:42,  0 users,  load average: 0.10, 0.31, 0.35
13:32 up 284 days,  5:03,  0 users,  load average: 1.12, 0.78, 0.67
13:32 up 599 days, 40 min,  0 users,  load average: 0.00, 0.00, 0.00
13:32 up 320 days,  7:44,  0 users,  load average: 0.06, 0.07, 0.02
13:33 up 28 days,  7:00,  3 users,  load average: 0.04, 0.05, 0.01
13:33 up 12 days,  8:52,  2 users,  load average: 0.31, 0.07, 0.02
13:33 up 68 days,  8:33,  0 users,  load average: 0.00, 0.02, 0.00
13:33 up 265 days,  6:36,  0 users,  load average: 0.00, 0.00, 0.00
13:34 up 515 days,  3:03,  0 users,  load average: 0.00, 0.00, 0.00
13:34 up 97 days, 22:44,  0 users,  load average: 0.06, 0.10, 0.09
13:34 up 118 days,  3:38,  1 user,  load average: 0.02, 0.02, 0.00
13:35 up 12 days, 17:07,  0 users,  load average: 0.01, 0.07, 0.04
13:35 up 18 days,  1:25,  0 users,  load average: 0.39, 0.37, 0.30
13:35 up 97 days,  6:02,  0 users,  load average: 0.00, 0.26, 0.31
13:36 up 410 days,  7:48,  0 users,  load average: 0.00, 0.00, 0.00
13:36 up 538 days, 11:38,  0 users,  load average: 0.01, 0.01, 0.00
13:36 up 207 days,  6:56,  0 users,  load average: 0.49, 0.52, 0.54
13:37 up 332 days,  8:49,  0 users,  load average: 0.01, 0.02, 0.00
13:37 up 263 days,  3:25,  0 users,  load average: 0.01, 0.02, 0.00
13:38 up 469 days, 23:05,  0 users,  load average: 0.02, 0.01, 0.00
13:38 up 927 days,  2:51,  0 users,  load average: 0.00, 0.01, 0.00
13:39 up 350 days, 17:01,  0 users,  load average: 0.01, 0.01, 0.00
13:39 up 782 days,  5:27,  0 users,  load average: 0.00, 0.00, 0.00
13:40 up 329 days,  3:50,  0 users,  load average: 0.00, 0.00, 0.00
13:40 up 192 days,  1:28,  0 users,  load average: 0.44, 0.36, 0.28
13:40 up 203 days,  4:04,  0 users,  load average: 0.10, 0.16, 0.11
13:41 up 200 days,  3:14,  0 users,  load average: 0.07, 0.06, 0.01
13:41 up 200 days,  3:14,  0 users,  load average: 0.13, 0.14, 0.16
13:41 up 200 days,  3:14,  0 users,  load average: 0.06, 0.08, 0.08
13:42 up 104 days,  6:52,  0 users,  load average: 0.02, 0.01, 0.00
13:42 up 752 days,  3:37,  0 users,  load average: 0.00, 0.00, 0.00
20:04 up 94 days,  5:56,  0 users,  load average: 0.06, 0.03, 0.01
18:43 up 94 days,  5:54,  0 users,  load average: 0.00, 0.00, 0.00
20:40 up 11 days,  7:01,  0 users,  load average: 0.04, 0.10, 0.04
13:43 up 26 days,  8:50,  0 users,  load average: 0.01, 0.01, 0.00
13:43 up 581 days,  8:54,  0 users,  load average: 0.00, 0.00, 0.00
13:43 up 430 days,  2:08,  0 users,  load average: 0.01, 0.06, 0.02
13:49 up 215 days,  3:56,  0 users,  load average: 0.01, 0.04, 0.10
13:49 up 354 days,  4:17,  0 users,  load average: 0.00, 0.00, 0.00
13:49 up 523 days,  2:55,  0 users,  load average: 0.00, 0.00, 0.00
13:50 up 301 days,  2:12,  0 users,  load average: 0.00, 0.00, 0.00
13:50 up 259 days,  9:03,  0 users,  load average: 3.93, 4.07, 4.06
13:50 up 47 days,  2:46,  0 users,  load average: 0.00, 0.00, 0.00
13:51 up 17 days,  7:42,  0 users,  load average: 0.00, 0.00, 0.00
18:10 up 363 days,  4:19,  0 users,  load average: 0.13, 0.26, 0.34
13:52 up 145 days,  8:33,  0 users,  load average: 0.23, 0.17, 0.10
13:52 up 581 days,  8:34,  0 users,  load average: 0.00, 0.00, 0.00
13:52 up 431 days,  1:41,  0 users,  load average: 0.00, 0.01, 0.00
13:54 up 396 days,  2:21,  0 users,  load average: 0.00, 0.14, 0.24

Conclusion : It also takes time to be proved a possible next generation database.

So let's make a point on the last community work to continue tradition of stability :

There have been some effort in that direction in MySQL@Sun-Oracle that goes back to the source code

MariaDB and Drizzle today compile with -Werror, which means that any compiler error is a just considered a failure.

Using buildbot, is a free and community driven build system that anyone can participate in http://buildbot.askmonty.org/buildbot/buildslaves

Buildbot is used for all MariaDB pushes, run for major OS, hardware, distribution including windows the test suite run on valgrind for debug and optimized builds to test all flavor. Number of test raise from 2236 to 2642, number of test lines from 567898 to 656864.

Buildbot is using test suite to stress for hight number of retry for better transient failures detection

Buildbot include random query generator https://launchpad.net/~randgen

Drizzle introduce dbqp for providing a generic automatic multi test framework http://docs.drizzle.org/testing/dbqp.html

In that area, competition raising quality , minimizing risks for the users.

Crossing the hills with Web 2.0

2011-03-09T09:11:00.000-08:00

Paybox the fast growing payment system, ready for new write scalability challenges, with an up to date open source stack, sharding, asynchronous replication, protobuffer, MariaDB and his new group commit feature, ready to go on fusion IO and XFS if just required.

HA and write intensive workload are really challenging to married but it can be very fun !
To follow the team Paybox on the "Rallye des Gazelles 2011" http://timlaline.over-blog.com

Muzing on NoSQL, damned ! can't get rid of InifiniDB

2011-01-17T12:35:00.000-08:00

NoSQL have been frequently used for building analytic solutions. The big picture is using some scalable client code with map reduce to distribute full data scan.

This approach have nothing new to the RDBMS world and can be considered an extension of the Kimball ROLAP normalization, just allowing more CPU power on a single query.

NoSQL or sharding take advantages on

Distributed processing

NoSQL or sharding are loosing advantages such

Fast memory communication
Per column data type optimization and deserialization cost (NoSQL)
C processing when reduced with slower language (NoSQL)

There is more non technical advantages in classic ROLAP normalization like using same well known OLTP tools and bug free storage engine, all coming with GPL licences for reducing the ownership cost. In many, many case it will allow building analytic solutions with acceptable response time.

Our existing tools in My SQL using vertical scalability contains

MyISAM for minimizing query latency
MyISAM read only pack table for sparing space and io
Deprecated merge table for partitioning
Native partitioning
InnoDB transparent bzip compression
InnoDB read ahead , and multiple io threads
Tokutek storage engine with predictable response time on table sizing

On the same front MariaDB is cutting the gap with the best commercial RDBMS, introducing new algorithms based on query plan optimization

Table elimination (MariaDB 5.1)
Batched Key Access (MariaDB 5.3)
Join buffering with outer joins
Index Condition Pushdown
Subquery optimizations (MariaDB 5.3)
NULL-aware materialization
Materialization-scan for grouping queries
Predicate caching
FROM subquery optimizations

ROLAP, despite being the most common approach, is facing some well know limitations that OLAP or NoSQL try to balance.

Single query is bound to a single server, and for My SQL single core. It is good enough in most case for a marketing or management requirements putting limited load on the analytic solution and asking data with some predefine time delay.

Putting technical team in front of the write vs read challenge, adding more indexes will speed some queries but doubtfully can cover all queries and will slow down writes and DDL.

Over time the schema is more and more linked with specific requirements, bringing aggregates and datamarts and forcing to maintain bunch of ETL assynchronus code.

NoSQL defenders tend to clams this is not acceptable? Analytic systems today are moving online and could populate events at the rate of the number of Internet sessions, clicks or network packets. This is just true, but is NoSQL the best solution ?

On the same track to NoSQL,a toolkit is emerging in MariaDB and Drizzle, to overcome those limitations

Column based OLAP storage engine
ROLAP sharding solutions with or without transparent map reduce

What is the best today? Again there is no rule of thumb but i can share some founding in my day to day job.

OLAP storage engine InfiniDB, Infobright, LucidDB provide very good results on processing a lot of records, depending on the underlying hardware and type of queries. I tend to give credit to InfiniDB to maximize resources out of a single query and Infobright to provide better response time on limited hardware resources.

Based on the top 10 queries of an existing client running an online DWH 1 Billion records on 8 cores hardware RAID 5 4*15000tpm SAS, 32G RAM

	InnoDB	InfiniDB	InfoBright
Avg number of logical records/s on all type of queries	1 598 682	12 098 102	4 527 652
Minimum response time / million logical records scan best case on a 128M rows group by	0.6	0.02	0.06

So far so good OLAP is the big winner here !

Can NoSQL come clause to OLAP, at the rate of 500 000 get/s ? It would required 128 servers to come clause to such response time and 12 of them to maintain avg read records rate. Hopefully NoSQL can use bulk data transfer covered with distributed SQL layer.

On the other side, ROLAP sharding can put more memory caching on a single query, introducing home made logical partitioning that can follow the business rules like putting most hot data on a single node, mixing huge aggregates with small data lookups. This approch is already credited with very good results for me on a 20 billion DWH build last year for a fraction of the price of commercial DB

Can ROLAP come clause to OLAP and how many servers will i need to achieve the same performance ? According to previous specific bench it is 1 to 1 if we can make the all cores work together, but for the same latency it is more 1 to 3 or 4. Can we verify this with micro benchmarking.

Let's abstract a linear parallel processing on ROLAP designed with some sharding.

With Spider storage engine allowing transparent horizontal sharding, we end up splitting the data in per day shards, giving 2M rows fact tables per partition, distributing the shard across multiple MariaDB instances. Spider make it possible to do this and reattach your data just reusing same SQL statements.

Let's now present some micro benchmark that discourage me from the brute force only approach.

Mounting the partitions on a RAMFS, let catch the time for a single 2 Million rows partition scan

InnoDB	1.3
Pbxt	0.8
MyISAM Pack	1.32
MyISAM	0.46
Aria 32K blocs	0.86

With linear scalability we can get 8 cores to perform 32M row scan/s on MyISAM.
Giving our previous query doing 128M row aggregate in 2s in InfiniDB, result would have run in 4 secondes, 2 times slower comparing with InfiniDB and this is just abstracting network, disk access and any consolidation time for each shard.

What is the magic behind OLAP, could it be per column sharding ?

Our Q10 queries is touching 3 fields out of the 12 in the giving table.This is opening the possibility to divide full scan time per 4 with additional 3 times the scan of a primary key and adding 2 times the CPU to join 2 millions rows. This operation could possibly be done transparently with the excellent VP storage engine.

Oups, join on 2M records is adding minimum 8 seconds per shard for a full scan. This POC was just a big u turn. Conclusion, sharding per column and brut force is not the solution in most case giving the cost of algorithms to rejoin the multiple peaces.

Fetch 2 INT COLUMNS 2M rows MyISAM	Join cache level	Time to scan all records
FULL SCAN 8 bytes RECORD SIZE	1	1.07
FULL SCAN 108 bytes RECORD SIZE	1	1.87
FULL JOIN NESTED LOOP	1	17.48
FULL JOIN BKAH	8	15.43
FULL JOIN BKA	5	15.3
FULL JOIN NESTED LOOP COVERING INDEX	1	8.37

OLAP engines take clever advantage of metadata per shard and pre aggregate informations it means that any ROLAP sharding or NoSQL solution should also come to the same conclusion in regard to latency, and adding hybrid mechanisms like additional metadata or pre calculated tables that can limit the cost of brute forcing.

Some great idea would be to build a transparent metadata layer,with My SQL proxy or gearman that will inject some pre calculated results per shard, execute asynchronously at insert time. I have no doubt that a map reduce solution for building such asynchronously metadata is the way to go for a ROLAP sharding with almost real time statistics.

For those already convince, I'd like to point out, a not so known feature of My SQL call WITH ROLLUP to help trigger aggregating data.

20% to 50% improvement in MariaDB 5.3 Handler Interface using prepared statement

2011-01-10T04:36:00.000-08:00

Following a previous post using MySQL as a NoSQL store with the native Handler Interface, some refactoring have now been coded in MariaDB-5.3 as described in the following worklog

On concurrency, doing less work in HANDLER READ to put more caching on HANDLER OPEN is credited with a 7% performance gain.

Source code via

bzr branch lp:~maria-captains/maria/5.3-handler

More, HANDLER READ can now be used with prepared statement to save precious CPU cycles on parsing.

handler t1 open;
prepare stmt from 'handler t1 read a=(?)';
set @a=1000;
execute stmt using @a;
deallocate prepare stmt;

On micro benchmark prepared statement show 40% better performance. On concurrency this improvement goes up to 13% better performance as demonstrated with a code in C using client side prepared statement, in regard to mysqlslap also in C but without client side prepared statement.

Handler Interface on Pached MariaDB 5.3	64
MyISAM/Client side prepared statement/PERL	313425
MyISAM/mysqlslap	436977
PBXT/mysqlslap	467880
PBXT/client side prepared statement/C client	527940
MyISAM/client side prepared statement/C client	493855

The MariaDB team also investigates some possible contentions on kernel_lock with a new server parameter that disables socket switching to non blocking mode -T8 --disable-thread-alarm, this is having so far no impact on this bench.

Code used during the run :

#include <iostream>
#include <cstdlib>
#include <cstring>
#include <cstdio>
#include "mysql.h"
using namespace std;

int main(int argc, char *argv[])
{
cout << "start up" << endl;
MYSQL * connection;
connection = mysql_init(0);
connection = mysql_real_connect(connection, 0, "stephane", "", "test", 0,
                                 "/tmp//mysql.sock", 0);
if (0 == connection)
{
   cerr << "connect failed:" << mysql_error(connection) << endl;
   return -1;
}
static const char * sql_statement ="HANDLER login READ `PRIMARY`=(?)";
MYSQL_STMT * stmt_handle = mysql_stmt_init(connection);
if (0 == stmt_handle)
{
   cerr << "stmt init failed:" << mysql_error(connection) << endl;
   return -1;
}
int mysql_return_code = mysql_stmt_prepare(stmt_handle,
                                            sql_statement,
                                            strlen(sql_statement)
                                           );
if (0 != mysql_return_code)
{
   cerr << "stmt prepare failed:" << mysql_stmt_error(stmt_handle) <<
     endl;
   mysql_stmt_close(stmt_handle);
   return -1;
}

MYSQL_BIND bind_var[1];
unsigned long var_length[3];
my_bool not_null = 0;
int int_data;

bind_var[0].buffer_type = MYSQL_TYPE_LONG;
bind_var[0].buffer = (char *) &int_data;
bind_var[0].length= 0;
bind_var[0].is_null = 0;

mysql_return_code = mysql_stmt_bind_param(stmt_handle, bind_var);
if (0 != mysql_return_code)
{
   cerr << "stmt bind failed:" << mysql_stmt_error(stmt_handle) << endl;
   mysql_stmt_close(stmt_handle);
   return -1;
}
int_data= 1000;
int user_id;
char user_name[50] = {'\0'};
char user_email[255] = {'\0'};
MYSQL_TIME created;
MYSQL_BIND result_var[4];
unsigned long result_var_length[4];
my_bool is_null[1];

for (int i=0; i<1000000; ++i)
{
mysql_return_code = mysql_stmt_execute(stmt_handle);
if (0 != mysql_return_code)
{
   cerr << "stmt execute failed:" << mysql_stmt_error(stmt_handle) <<
     endl;
   mysql_stmt_close(stmt_handle);
   return -1;
}
bzero(result_var, sizeof(result_var));
result_var[0].buffer_type= MYSQL_TYPE_LONG;
result_var[0].buffer= (char *)&user_id;
result_var[0].is_null= &is_null[0];
result_var[0].length= &result_var_length[0];
result_var[1].buffer_type = MYSQL_TYPE_VAR_STRING;
result_var[1].buffer = (char *)user_name;
result_var[1].buffer_length = sizeof(user_name)-1;
result_var[1].is_null = &is_null[1];
result_var[1].length = &result_var_length[1];
result_var[2].buffer_type = MYSQL_TYPE_VAR_STRING;
result_var[2].buffer = (char *)user_email;
result_var[2].buffer_length = sizeof(user_email)-1;
result_var[2].is_null = &is_null[2];
result_var[2].length = &result_var_length[2];
result_var[3].buffer_type = MYSQL_TYPE_DATETIME;
result_var[3].buffer = (char *)&created;
result_var[3].is_null = &is_null[3];
result_var[3].length = &result_var_length[3];

mysql_return_code = mysql_stmt_bind_result(stmt_handle, result_var);
if (0 != mysql_return_code)
{
   cerr << "stmt bind rslt failed:" << mysql_stmt_error(stmt_handle) <<
     endl;
   mysql_stmt_close(stmt_handle);
   return -1;
}

mysql_return_code = mysql_stmt_store_result(stmt_handle);
if (0 != mysql_return_code)
{
   cerr << "stmt store rslt failed:" << mysql_stmt_error(stmt_handle) <<
     endl;
   mysql_stmt_close(stmt_handle);
   return -1;
}

mysql_return_code = mysql_stmt_fetch(stmt_handle);
if (0 != mysql_return_code && MYSQL_NO_DATA != mysql_return_code)
{
   cerr << "stmt fetch failed:" << mysql_stmt_error(stmt_handle) << endl;
   mysql_stmt_free_result(stmt_handle);
   mysql_stmt_close(stmt_handle);
   return -1;
}

if (MYSQL_NO_DATA != mysql_return_code)
{
// cout << "stmt - got: " << user_name << endl;
}
} //end for
mysql_stmt_free_result(stmt_handle);
mysql_stmt_close(stmt_handle);
mysql_close(connection);
mysql_server_end();
cout << "done" << endl;
return 0;
}

Using MySQL as a NoSQL: a story for exceeding 450000 qps with MariaDB

2010-12-27T16:30:00.000-08:00

Following the excellent post of Yoshinori about exceeding 750 000 qps with handler_socket, i was curious to make my own opinion on the state of MySQL and MariaDB regarding simple primary key search.

Quoting "Most of high scale web applications use MySQL + memcached. Many of them use also NoSQL like TokyoCabinet/Tyrant. In some cases people have dropped MySQL and have shifted to NoSQL. One of the biggest reasons for such a movement is that it is said that NoSQL performs better than MySQL for simple access patterns such as primary key lookups. Most of queries from web applications are simple so this seems like a reasonable decision"

I’m a big fan of NoSQL when it comes to remove useless work put on some over loaded SQL box , for solving write scalability issues but i will try to demonstrate that in most case you can push a MySQL and MariaDB server at the same level in vertical scalability compare to NoSQL solution.

I found some of the benchmarks not always taking into account the best practice of RDBMS usage, no persistante connections (shorten authentification , socket and thread stack allocation), not using prepared statement to cache plans and limit network usage, using bad primary keys like varchar or big numeric data type.

As demontrated on InnoDB by handler_socket most of the effect of concurrency locking on the buffer pool and the key buffer are now fixed at the storage engine level. For MyISAM one should use the new segmented key buffer feature comming with MariaDB 5.2

key_cache_segments=16

key_bock_size=32K

The big picture behind handler_socket is to shortcut the SQL layer with a plugin that implement a light non blocking IO protocol and a pool of threads accessing directly the storage engine API. One should notice that in theory, the same network implementation is introduced with the new pool of thread released in MariaDB but the scalability over 4 cores for this feature is still a work in progress.

Using hanler_socket, some internal mechanisms can be removed away of normal query execution

- CPU usage for parsing and compute query plan.
- Mutex protecting memory structures: LOCK_open, LOCK_thread_count
- Global kernel mutex hit by the posix threading model

Fortunatly it exist, some not so popular feature of MySQL that can help removing a lot of the parsing and locking issues called the Handler Interface.

With that interface MyISAM and PBXT performance are looking very close to what handler_socket deliver on the two commodity harware used for my testing.

Table is 1 000 000 rows, 44 Bytes avg row size, 40MB index, 320MB data on MyISAM

CREATE TABLE test.login (

`user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`user_name` varchar(50) DEFAULT NULL,

`user_email` varchar(255) DEFAULT NULL,

`created` datetime DEFAULT NULL,

PRIMARY KEY (`user_id`)

) ENGINE=MyISAM AUTO_INCREMENT=100000 DEFAULT CHARSET=latin1;

Using mysqlslap just init the HANDLER in the configuration file and login with a user not having SUPER priviledges

init_connect='use test;HANDLER login OPEN;'

./my sqlslap -ustephane test -p --create-schema=test --query="HANDLER login READ \`PRIMARY\`=(FLOOR(RAND())*1000000)" --concurrency=24 --number-of-queries=10000000

The short winner is still handler_socket with at pick of 220 concurrent connections delivering 504 685 qps but MariaDB 5.2 with PBXT and MariaDB 5.3 MyISAM is comming very close with more then 410 000 qps

24 cores Intel(R) Xeon(R) CPU X5660 @ 2.80GHz	16	24	64
MariaDB-5.1 Handler_Socket MyISAM	-	193689	184325
MariaDB-5.1 Handler_Socket InnoDB		280000	477136
MySQL-5.5 Prepare statement InnoDB	-	31638	-
MySQL-5.5 Select Interface InnoDB	75732	41911	28647
MySQL-5.5 Handler Interface MyISAM	-	180647	195192
MySQL-5.5 Handler Interface InnoDB	249495	383976	349054
MariaDB-5.2 Handler Interface MyISAM	285961	388537	402061
MariaDB-5.2 Handler Interface MyISAM Thread Pool	111906	77391	101005
MariaDB-5.2 Handler Interface PBXT	317517	419943	436088
MariaDB-5.2 Handler Interface XtraDB	287411	394296	188557
MariaDB-5.3 Handler Interface MyISAM	286331	412769	413560
MariaDB-5.3 Handler Interface MyISAM Perl	-	289662	289942

Notice that InnoDB is making 2 handler_read_key for 1 primary key index search, monitoring the load should be taken from the status of Innodb_rows_read

For the most curious, profiling made during that bench on MyISAM, MariaDB 5.2 and MySQL 5.5

% cumulative self self total

time seconds seconds calls s/call s/call name

12.79 62.18 62.18 __read_nocancel

10.78 114.60 52.42 pthread_mutex_lock

10.56 165.94 51.34 __write_nocancel

5.76 193.94 28.00 __pthread_mutex_unlock_usercnt

4.03 213.52 19.58 __recvmsg_nocancel

3.58 230.92 17.40 302956 0.00 0.00 MYSQLparse(void*)

2.95 245.27 14.35 __lll_unlock_wake

2.37 256.81 11.54 __lll_lock_wait

2.18 267.42 10.61 __pread_nocancel

2.18 278.03 10.61 __profile_frequency

1.83 286.94 8.91 3615646 0.00 0.00 MYSQLlex(void*, void*)

1.82 295.80 8.86 974119 0.00 0.00 find_key_block

1.70 304.07 8.27 __lseek_nocancel

1.44 311.05 6.98 confstr

1.29 317.32 6.27 __pthread_enable_asynccancel

1.21 323.22 5.90 pthread_setschedprio

1.20 329.05 5.83 __pthread_disable_asynccancel

1.15 334.64 5.59 cfree

1.13 340.13 5.49 200787 0.00 0.00 Protocol::send_fields()

1.09 345.41 5.28 malloc

1.00 350.26 4.85 1525265 0.00 0.00 bmove512

0.93 354.77 4.51 5901865 0.00 0.00 ha_key_cmp

0.85 358.88 4.11 5554910 0.00 0.00 Protocol::net_store_data()

0.79 362.72 3.84 7464391 0.00 0.00 copy_and_convert()

0.73 366.25 3.53 pthread_rwlock_unlock

0.72 369.74 3.49 242753 0.00 0.00 mysql_ha_read()

0.71 373.18 3.45 792885 0.00 0.00 simple_key_cache_read

0.67 376.46 3.28 pthread_rwlock_rdlock

0.66 379.67 3.21 memcpy

Inspecting the new performance_schema on MySQL 5.5

SELECT EVENT_NAME, COUNT_STAR FROM events_waits_summary_global_by_event_name ORDER BY COUNT_STAR DESC LIMIT 10;

% Performance Schema	InnoDB	MyISAM
wait/synch/mutex/mysys/KEY_CACHE::cache_lock	-	39
wait/synch/mutex/sql/THD::LOCK_thd_data	45	22
wait/synch/mutex/innodb/kernel_mutex	27	-
wait/io/file/myisam/dfile	-	14
wait/synch/mutex/myisam/MYISAM_SHARE::intern_lock	-	10
wait/synch/mutex/mysys/THR_LOCK::mutex	18	10
wait/synch/rwlock/innodb/btr_search_latch	9	-
wait/synch/rwlock/myisam/MYISAM_SHARE::key_root_lock	-	5

There are still some contentions like those fixed by the glorious facebook team but the dev team at Monty Program is working hard to remove some of them, and have already made some good progress in 5.2 and 5.3,

A special can do for the PBXT team that again bring there storage on top knowing that they came with some handler_socket implementation into pbms a long time ago. It should be very intresting to compare PBMS with Handler_socket in a next post.

Stay tune for some improvements on the handler interface on future releases.