20110326-handlersocket-110325140025-phpapp02.pdf

Transcript

1. HANDLERSOCKET - A NOSQL PLUGIN FOR MYSQL Jui-Nan Lin @OSDC.TW 2011
2. About this talk & me  URL of this Slide: http://0rz.tw/jqQqY  QRCode:  About me:  Jui-Nan Lin (jnlin)  Developer @ www.pixnet.net  Twitter: @jnlin  Github: jnlin 2
3. What is HandlerSocket?  “A NoSQL interface for MySQL”  Only supports queries which use index  Developed by DeNA (a Japanese social gaming provider)  https://github.com/ahiguti/HandlerSocket-Plugin- for-MySQL 3
4. Architecture 4 libmysql libhsclient Application (Ex. PHP, Ruby, Perl …) Listener for libmysql SQL Layer Handler Interface InnoDB MyISAM Other storage engines HandlerSocket Plugin application mysqld Reference: http://www.slideshare.net/akirahiguchi/handlersocket-plugin-for-mysql-4664154
5. NoSQL for Web Application  Most operations in web application are simple CRUD.  RDBMS is too powerful for web application.  Typical solution:  Memcached + MySQL  Cassandra  Apache CouchDB  Amazon SimpleDB 5
6. NoSQL for Web Application  Pros:  Scalable  Fast  Cons:  Unstable  Twitter drops Cassandra in 2010: http://engineering.twitter.com/2010/07/cassandra-at- twitter-today.html 6
7. HandlerSocket for MySQL  MySQL is very stable  HandlerSocket provides a NoSQL interface for MySQL  HandlerSocket is very fast. Faster than MySQL. 7
8. How fast is it  750,000 qps on a commodity server (tested by original developers)  MySQL 5.1 + E5540 2.53Ghz + 32 GB RAM  All data fits in memory  All Clients are written in C  100,000 qps with Original MySQL  About 7.5x faster than original MySQL! 8
9. How fast is it  1m PK SELECT queries each test on 1 table contains 1m rows. Client is written in PHP.  SELECT * from table1 WHERE pk = ? 9 Concurrent Connections 1 10 50 100 Handler Socket Time (sec) 256 28 15 13 QPS 3906 35714 66667 76923 MySQL Time (sec) 372 83 87 85 QPS 2688 12048 11494 11765
10. Why HandlerSocket is fast 10 from http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html
11. Why HandlerSocket is fast  HandlerSocket do not need to do:  Parsing SQL Statements  Opening, locking table  Making SQL execution plans  Index usage  Unlocking, closing tables 11
12. Why HandlerSocket is fast  HandlerSocket does do:  Plain text-based protocol  Keep table open for reuse  Event based (use epoll)  Many Connection per MySQL thread  Accept only index queries  Support range query, LIMIT, multi_get operations 12
13. Language Bindings  PHP: php-handlersocket  Java: hs4j  Python: python-handler-socket  Ruby: ruby-handlersocket  Perl: Net::HanderSocket 13
14. Programming Example  PHP  Read Operation  Port used for write operation is different 14
15. Configuration (my.cnf)  handlersocket_port = 9998  Port for reading  handlersocket_port_wr = 9999  Port for writing  handlersocket_threads = 16  No. of reader threads  Recommended Value: # of CPUs  handlersocket_threads_wr = 1  No. of writer threads  open_files_limit = 65535 15
16. Pros and Cons  Pros  Stable  High Performance  Support a lot of query types  No duplicate cache (compared with MySQL + memcached)  Invalidation Problem  Flexible  Support SQL interface. We can still use SQL statements! (ex. report generating)  Write binlog and support MySQL replication.  Included in Percona Server 16
17. Pros and Cons  Cons  No Security  Without username and password  Do not update MySQL Query Cache  Fixed in recent commits  No “INSERT … ON DUPLICATED KEY UPDATE …” Feature  Do not support auto increment column while inserting  Fixed in recent commits 17
18. Q&A  Thank you! 18