lundi 27 décembre 2010

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


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 InnoDB280000477136
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.