Tuesday, October 19, 2010

Using MySQL as a NoSQL - A story for exceeding 750,000 qps on a commodity server

UPDATE: Oracle officially released memcached daemon plugin that talks with InnoDB. I'm glad to see that NoSQL+MySQL has become an official solution. It's still preview release but will be very promising. Let's try it to make it better!

  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.
  Like many other high scale web sites, we at DeNA(*) had similar issues for years. But we reached a different conclusion. We are using "only MySQL". We still use memcached for front-end caching (i.e. preprocessed HTML, count/summary info), but we do not use memcached for caching rows. We do not use NoSQL, either. Why? Because we could get much better performance from MySQL than from other NoSQL products. In our benchmarks, we could get 750,000+ qps on a commodity MySQL/InnoDB 5.1 server from remote web clients. We also have got excellent performance on production environments.
  Maybe you can't believe the numbers, but this is a real story. In this long blog post, I'd like to share our experiences.
(*) For those who do not know.. I left Oracle in August 2010. Now I work at DeNA, one of the largest social game platform providers in Japan.

Is SQL really good for fast PK lookups?


  How many times do you need to run PK lookups per second? Our applications at DeNA need to execute lots of PK lookups, such as fetching user info by user id, fetching diary info by diary id. memcached and NoSQL certainly fit very well for such requirements. When you run simple multi-threaded "memcached get" benchmarks, you can probably execute 400,000+ get operations per second, even though memcached clients are located on remote servers. When I tested with the latest libmemcached and memcached, I could get 420,000 get per sec on a 2.5GHz x 8 core Nehalem box with a quad-port Broadcom Gigabit Ethernet card.

  How frequently can MySQL execute PK lookups? Benchmarking is easy. Just run concurrent queries from sysbench, super-smack, mysqlslap, etc.
[matsunobu@host ~]$ mysqlslap --query="select user_name,..  
from test.user where user_id=1" \
--number-of-queries=10000000 --concurrency=30 --host=xxx -uroot
You can easily check how many InnoDB rows are read per second.
[matsunobu@host ~]$ mysqladmin extended-status -i 1 -r -uroot \
| grep -e "Com_select"
...
| Com_select | 107069 |
| Com_select | 108873 |
| Com_select | 108921 |
| Com_select | 109511 |
| Com_select | 108084 |
| Com_select | 108483 |
| Com_select | 108115 |
...
  100,000+ queries per second seems not bad, but much slower than memcached. What is MySQL actually doing? From vmstat output, both %user and %system were high.
[matsunobu@host ~]$ vmstat 1
r b swpd free buff cache in cs us sy id wa st
23 0 0 963004 224216 29937708 58242 163470 59 28 12 0 0
24 0 0 963312 224216 29937708 57725 164855 59 28 13 0 0
19 0 0 963232 224216 29937708 58127 164196 60 28 12 0 0
16 0 0 963260 224216 29937708 58021 165275 60 28 12 0 0
20 0 0 963308 224216 29937708 57865 165041 60 28 12 0 0

  Oprofile output told more about where CPU resources were spent.
samples  %        app name                 symbol name
259130 4.5199 mysqld MYSQLparse(void*)
196841 3.4334 mysqld my_pthread_fastmutex_lock
106439 1.8566 libc-2.5.so _int_malloc
94583 1.6498 bnx2 /bnx2
84550 1.4748 ha_innodb_plugin.so.0.0.0 ut_delay
67945 1.1851 mysqld _ZL20make_join_statistics
P4JOINP10TABLE_LISTP4ItemP16st_dynamic_array
63435 1.1065 mysqld JOIN::optimize()
55825 0.9737 vmlinux wakeup_stack_begin
55054 0.9603 mysqld MYSQLlex(void*, void*)
50833 0.8867 libpthread-2.5.so pthread_mutex_trylock
49602 0.8652 ha_innodb_plugin.so.0.0.0 row_search_for_mysql
47518 0.8288 libc-2.5.so memcpy
46957 0.8190 vmlinux .text.elf_core_dump
46499 0.8111 libc-2.5.so malloc

  MYSQLparse() and MYSQLlex() are called during SQL parsing phase. make_join_statistics() and JOIN::optimize() are called during query optimization phase. These are "SQL" overhead. It's obvious that performance drops were caused by mostly SQL layer, not by "InnoDB(storage)" layer. MySQL has to do a lot of things like below while memcached/NoSQL do not neeed to do.

* Parsing SQL statements
* Opening, locking tables
* Making SQL execution plans
* Unlocking, closing tables

  MySQL also has to do lots of concurrency controls. For example, fcntl() are called lots of times during sending/receiving network packets. Global mutexes such as LOCK_open, LOCK_thread_count are taken/relesed very frequently. That's why my_pthread_fastmutex_lock() were ranked #2 in the oprofile output and %system were not small.
  Both MySQL development team and external community are aware of concurrency issues. Some issues have already been solved in 5.5. I'm glad to see that lots of fixes have been done so far.
  But it is also important that %user reached 60%. Mutex contentions result in %system increase, not %user increase. Even though all mutex issues inside MySQL are fixed, we can not expect 300,000 queries per second.
  You may be heard about HANDLER statement. Unfortunately HANDLER statement was not so much helpful to improve throughput because query parsing, opening/closing tables still be needed.

CPU efficiency is important for in-memory workloads


  If little active data fit in memory, SQL overheads become relatively negligible. This is simply because disk i/o costs are much much higher. We do not need to care so much about SQL costs in this case.
  But, on some of our hot MySQL servers, almost all data fit in memory and they became completely CPU bound. Profiling results were similar to what I described above: SQL layer spent most of resources. We needed to execute lots of primary key lookups(i.e. SELECT x FROM t WHERE id=?) or limited range scans. Even though 70-80% of queries were simple PK lookups from the same table (difference was just values in WHERE), every time MySQL had to parse/open/lock/unlock/close, which seemed not efficient for us.

Have you heard about NDBAPI?


  Is there any good solution to reduce CPU resources/contentions around SQL layer in MySQL? If you are using MySQL Cluster, NDBAPI would be the best solution. When I worked at MySQL/Sun/Oracle as a consultant, I had seen lots of customers who were dissapointed at SQL Node + NDB performance, then became happy after they could get N times bettern performance by writing NDBAPI clients. You can use both NDBAPI and SQL in MySQL Cluster. It's recommended using NDBAPI for frequent access patterns, and using SQL + MySQL + NDB for ad-hoc or infrequent query patterns.
  This was what we wanted. We wanted faster access APIs, but we also wanted to use SQL for ad-hoc or complex queries. But DeNA is using InnoDB, like many other web services. Switching to NDB is not trivial. Embedded InnoDB does neither support SQL nor network interface so it's not an option for us.

Developing "HandlerSocket Plugin" - a MySQL plugin that speaks NoSQL network protocols


  We thought that the best approach was implementing a NoSQL network server inside MySQL. That is, writing a network server as a MySQL plugin (daemon plugin) which listens on specific ports, accepting NoSQL protocols/APIs, then accessing to InnoDB directly by using MySQL internal storage engine APIs. This approach is similar to NDBAPI, but it can talk with InnoDB.
  This concept was initially invented and prototyped by Kazuho Oku at Cybozu Labs last year. He wrote MyCached UDF that speaks memcached protocols. My colleague Akira Higuchi implemented another plugin: HandlerSocket. The below picture shows about what Hanldersocket can do.


< Fig 1 > What is Hanldersocket?

  HandlerSocket is a MySQL daemon plugin so that applications can use MySQL like NoSQL. The biggest purpose of the HandlerSocket is that it talks with storage engines like InnoDB without SQL-related overheads. To access MySQL tables, of course HandlerSocket needs to open/close tables. But HandlerSocket does not open/close tables every time. It keeps tables opened for reuse. Opening/closing tables is very costly and causes serious mutex contentions so it's very helpful to improve performance. Of course HandlerSocket closes tables when traffics become small etc so that it won't block administrative commands (DDL) forever.
  What is different from using MySQL + memcached? By comparing Fig 1 with Fig 2, I think you'll notice lots of differences. Fig 2 shows typical memcached and MySQL usage. memcached is aggressively used for caching database records. This is mainly because memcached get operation is much faster than in-memory / on-disk PK lookups in MySQL. If HandlerSocket can fetch records as fast as memcached, we don't need memcached for caching records.


< Fig 2 > Common architecture pattern for MySQL + memcached

Using HandlerSocket


  As an example, here is a "user" table. Suppose we need to fetch user information by user_id.

CREATE TABLE user (
user_id INT UNSIGNED PRIMARY KEY,
user_name VARCHAR(50),
user_email VARCHAR(255),
created DATETIME
) ENGINE=InnoDB;
In MySQL, fetching user info can be done by, of course, SELECT statements.
mysql> SELECT user_name, user_email, created FROM user WHERE user_id=101;
+---------------+-----------------------+---------------------+
| user_name | user_email | created |
+---------------+-----------------------+---------------------+
| Yukari Takeba | yukari.takeba@dena.jp | 2010-02-03 11:22:33 |
+---------------+-----------------------+---------------------+
1 row in set (0.00 sec)
  Let me show how we can do the same thing with HandlerSocket.

* Installing HandlerSocket
  Installation steps are described here. Basic steps are as below:

1. Download HandlerSocket here

2. Building HandlerSocket (both client and server codes)
./configure --with-mysql-source=... --with-mysql-bindir=... ; make; make install
3. Installing HandlerSocket into MySQL
mysql> INSTALL PLUGIN handlersocket soname 'handlersocket.so';

  Since HandlerSocket is a MySQL plugin, you can use it like other plugins such as InnoDB Plugin, Q4M, Spider, etc. That is, you do not need to modify MySQL source code itself. MySQL version has to be 5.1 or later. You need both MySQL source code and MySQL binary to build HandlerSocket.

* Writing HandlerSocket client code
  We provice C++ and Perl client libraries. Here is a sample Perl code to fetch a row by pk lookup.

#!/usr/bin/perl

use strict;
use warnings;
use Net::HandlerSocket;

#1. establishing a connection
my $args = { host => 'ip_to_remote_host', port => 9998 };
my $hs = new Net::HandlerSocket($args);

#2. initializing an index so that we can use in main logics.
# MySQL tables will be opened here (if not opened)
my $res = $hs->open_index(0, 'test', 'user', 'PRIMARY',
'user_name,user_email,created');
die $hs->get_error() if $res != 0;

#3. main logic
#fetching rows by id
#execute_single (index id, cond, cond value, max rows, offset)
$res = $hs->execute_single(0, '=', [ '101' ], 1, 0);
die $hs->get_error() if $res->[0] != 0;
shift(@$res);
for (my $row = 0; $row < 1; ++$row) {
my $user_name= $res->[$row + 0];
my $user_email= $res->[$row + 1];
my $created= $res->[$row + 2];
print "$user_name\t$user_email\t$created\n";
}

#4. closing the connection
$hs->close();
  The above code fetches user_name, user_email and created columns from user table, looking by user_id=101. So you'll get the same results as the above SELECT statement.
[matsunobu@host ~]$ perl sample.pl
Yukari Takeba yukari.takeba@dena.jp 2010-02-03 11:22:33
  For most web applications, it's a good practice to keep lightweight HandlerSocket connections established (persistent connections), so that lots of requests can focus on main logic (the #3 in the above code).
  HandlerSocket protocol is a small-sized text based protocol. Like memcached text protocol, you can use telnet to get rows through HandlerSocket.
[matsunobu@host ~]$ telnet 192.168.1.2 9998
Trying 192.168.1.2...
Connected to xxx.dena.jp (192.168.1.2).
Escape character is '^]'.
P 0 test user PRIMARY user_name,user_email,created
0 1
0 = 1 101
0 3 Yukari Takeba yukari.takeba@dena.jp 2010-02-03 11:22:33
(Green lines are request packets, fields must be separated by TAB)

Benchmarking


  Now it's good time to show our benchmarking results. I used the above user table, and tested how many PK lookup operations can be done from multi-threaded remote clients. All user data fit in memory (I tested 1,000,000 rows). I also tested memcached with similar data (I used libmemcached and memcached_get() to fetch a user data). In MySQL via SQL tests, I used a traditional SELECT statement: "SELECT user_name, user_email, created FROM user WHERE user_id=? ". Both memcached and HandlerSocket client codes were written in C/C++. All client programs were located on remote hosts, connecting to MySQL/memcached via TCP/IP.

  The highest throughput was as follows:

approx qps server CPU util
MySQL via SQL 105,000 %us 60% %sy 28%
memcached 420,000 %us 8% %sy 88%
MySQL via HandlerSocket 750,000 %us 45% %sy 53%


  MySQL via HandlerSocket could get over 7.5 times higher throughput than traditional MySQL via SQL statements, even though %us was 3/4. This shows that SQL-layer in MySQL is very costly and skipping the layer certainly improves performance dramatically. It is also interesting that MySQL via HandlerSocket was 178% faster than memcached, and memcached spent too much %system resources. Though memcached is an excellent product, there are still rooms for optimizations.

  The below is oprofile outputs, gathered during MySQL via HandlerSocket tests. CPU resources were spent on core operations such as network packets handling, fetching rows, etc (bnx2 is a network device driver program).
samples  %        app name                 symbol name
984785 5.9118 bnx2 /bnx2
847486 5.0876 ha_innodb_plugin.so.0.0.0 ut_delay
545303 3.2735 ha_innodb_plugin.so.0.0.0 btr_search_guess_on_hash
317570 1.9064 ha_innodb_plugin.so.0.0.0 row_search_for_mysql
298271 1.7906 vmlinux tcp_ack
291739 1.7513 libc-2.5.so vfprintf
264704 1.5891 vmlinux .text.super_90_sync
248546 1.4921 vmlinux blk_recount_segments
244474 1.4676 libc-2.5.so _int_malloc
226738 1.3611 ha_innodb_plugin.so.0.0.0 _ZL14build_template
P19row_prebuilt_structP3THDP8st_tablej
206057 1.2370 HandlerSocket.so dena::hstcpsvr_worker::run_one_ep()
183330 1.1006 ha_innodb_plugin.so.0.0.0 mutex_spin_wait
175738 1.0550 HandlerSocket.so dena::dbcontext::
cmd_find_internal(dena::dbcallback_i&, dena::prep_stmt const&,
ha_rkey_function, dena::cmd_exec_args const&)
169967 1.0203 ha_innodb_plugin.so.0.0.0 buf_page_get_known_nowait
165337 0.9925 libc-2.5.so memcpy
149611 0.8981 ha_innodb_plugin.so.0.0.0 row_sel_store_mysql_rec
148967 0.8943 vmlinux generic_make_request
  Since MySQL via HandlerSocket runs inside MySQL and goes to InnoDB, you can get statistics from regular MySQL commands such as SHOW GLOBAL STATUS. It's worth to see 750,000+ Innodb_rows_read.
$ mysqladmin extended-status -uroot -i 1 -r | grep "InnoDB_rows_read"
...
| Innodb_rows_read | 750192 |
| Innodb_rows_read | 751510 |
| Innodb_rows_read | 757558 |
| Innodb_rows_read | 747060 |
| Innodb_rows_read | 748474 |
| Innodb_rows_read | 759344 |
| Innodb_rows_read | 753081 |
| Innodb_rows_read | 754375 |
...
Detailed specs were as follows.
    Model: Dell PowerEdge R710
    CPU: Nehalem 8 cores, E5540 @ 2.53GHz
    RAM: 32GB (all data fit in the buffer pool)
    MySQL Version: 5.1.50 with InnoDB Plugin
    memcached/libmemcached version: 1.4.5(memcached), 0.44(libmemcached)
    Network: Broadcom NetXtreme II BCM5709 1000Base-T (Onboard, quad-port, using three ports)
    * Both memcached and HandlerSocket were network i/o bound. When I tested with a single port, I got around 260,000 qps on MySQL via HandlerSocket, 220,000 qps on memcached.

Features and Advantages of HandlerSocket


  HandlerSocket has lots of features and advantages like below. Some of them are really beneficial for us.

* Supporting lots of query patterns
  HandlerSocket supports PK/unique lookups, non-unique index lookups, range scan, LIMIT, and INSERT/UPDATE/DELETE. Operations that do not use any index are not supported. multi_get operations (similar to IN(1,2,3..), fetching multiple rows via single network round-trip) are also supported.
  See documentation for details.

* Can handle lots of concurrent connections
  HandlerSocket connection is light. Since HandlerSocket employs epoll() and worker-thread/thread-pooling architecture, the number of MySQL internal threads is limited (can be controlled by handlersocket_threads parameter in my.cnf). So you can establish thousands or tens of thousands of network connections to HandlerSocket, without losing stability(consuming too much memory, causing massive mutex contentions, etc: such as bug#26590, bug#33948, bug#49169).

* Extremely high performance
  HandlerSocket is possible to gain competitive enough performance against other NoSQL lineups, as already described. Actually I have not seen any NoSQL product that can execute 750,000+ queries on a commodity server from remote clients via TCP/IP.
  Not only HandlerSocket eliminates SQL related function calls, but also it optimizes around network/concurrency issues.

** Smaller network packets
  HandlerSocket protocol is much simpler and smaller than normal MySQL protocols. So overall network transfer size can be much smaller.

** Running limited number of MySQL internal threads
  See above.

** Grouping client requests
  When lots of concurrent requests come to HandlerSocket, each worker thread gathers as many requests as possible, then executing gathered requests at one time, and sending back results. This can improve performance greatly, by sacrificing response time a bit. For example, you can gain the following benefits. I'll explain them in depth in later posts, if anybody is interested.
*** Can reduce the number of fsync() calls
*** Can reduce replication delay

* No duplicate cache

  When you use memcached to cache MySQL/InnoDB records, records are cached in both memcached and InnoDB buffer pool. They are duplicate so less efficient (Memory is still expensive!). Since HandlerSocket plugin accesses to InnoDB storage engine, records are cached inside InnoDB buffer pool, which can be reused by other SQL statements.

* No data inconsistency
  Since data is stored at one place (inside InnoDB), data consistency check between memcached and MySQL is not needed.

* Crash-safe
  Backend storage is InnoDB. It's transactional and crash safe. Even though you use innodb-flush-log-at-trx-commit!=1, you lose only < 1s of data on server crash.

* SQL can be used from mysql clients
  In many cases people still want to use SQL (i.e to generate summary reports). This is why we can't use Embedded InnoDB. Most NoSQL products don't support SQL interface, either.
  HandlerSocket is just a plugin for MySQL. You can usually send SQL statements from MySQL clients, and use HandlerSocket protocols when you need high throughput.

* All operational benefits from MySQL
  Again, HandlerSocket runs inside MySQL, so all MySQL operations such as SQL, online backups, replication, monitoring by Nagios / EnterpriseMonitor, etc are supported. HandlerSocket activities can be monitored by regular MySQL command such as SHOW GLOBAL STAUTS, SHOW ENGINE INNODB STATUS, SHOW PROCESSLIST, etc.

* No need to modify/rebuild MySQL
  Since it's a plugin, it runs on both MySQL Community and MySQL Enterprise Servers.

* Independent from storage engines
  HandlerSocket is developed so that it can talk with any storage engine, though we have tested and used with 5.1 and 5.5 InnoDB Plugin only.


Notes and Limitations


* Need to learn HandlerSocket APIs
  You need to write a program to talk with HandlerSocket, though it's pretty easy to use. We provide C++ API and Perl bindings.

* No security
  Like other NoSQL databases, HandlerSocket does not provide any security feature. HandlerSocket's worker threads run with system user privileges, so applications can access to all tables through HandlerSocket protocols. Of course you can use firewalls to filter packets, like other NoSQL products.

* No benefit for HDD bound workloads
  For HDD i/o bound workloads, a database instance can not execute thousands of queries per second, which normally results in only 1-10% CPU usage. In such cases, SQL execution layer does not become bottleneck, so there is no benefit to use Hanldersocket. We use HandlerSocket on servers that almost all data fit in memory.

DeNA is using HandlerSocket in production


  We already use HandlerSocket plugin in our production environments. The results are great. We could have reduced lots of memcached and MySQL slave servers. Overall network traffics have been reduced, too. We haven't seen any performance problem (slow response time, stalls etc) so far. We've been very satisfied with the results.
  I think MySQL has been underrated from NoSQL/Database communities. MySQL actually has much longer history than most of other products, and lots of unique and great enhancements have been done so far by excellent my ex-colleagues. I know from NDBAPI that MySQL has very strong potentials as a NoSQL. Storage engine API and daemon plugin interface are completely unique, and they made Akira and DeNA develop HandlerSocket possible. As an ex-employee at MySQL and a long-time fun for MySQL, I'd like to see MySQL becomes better and more popular, not only as an RDBMS, but also as Yet Another NoSQL lineup.
  Since HandlerSocket plugin is Open Source, feel free to try. We'd be appreciated if you give us any feedback.

341 comments:

1 – 200 of 341   Newer›   Newest»
jaypipes said...

This is a game-changer, Yoshi. :) Awesomeness.

Good to hear from ya!

-jay

Mark Callaghan said...

This is awesome. Your work is web scale!

Mullanaphy! said...

Hmmm, very interesting. Love that there is a perl version I can mess around with.

Unfortunately none of my side projects have the scale necessary to need this, but hey it's still very intriguing and worth working with.

Thanks.

Swany said...

Nice work.

hingo said...

Yoshinori-san: I heard rumors you had a new job, nice to hear about you. This work is awesome, ever since I saw Kazuho's mycache experiment, I've been waiting for someone to build on that idea. I guess you've brought NDBAPI to InnoDB :-) Now we just need synchronous replication so we can have 750.000 writes per second too :-)

I'm currently exploring a similar quest myself: How MySQL/MariaDB can "hit back" on NoSQL. This is certainly the top of the list now!

I suppose your team has already submitted a MySQL conference talk about this?

Evan said...

How does this compare to using the prepared statement API?

Zeno Davatz said...

Excellent. How about Postgresql? Can HandlerSocket deal with that as well?

Anonymous said...

What about UPDATE and INSERT statements ?

Stewart Smith said...

Excellent!

I've been thinking that this kind of thing would work quite well for a while - excellent to see it in production!

inboulder said...

I don't understand, if you're not using MySQL Cluster, and this is an all in memory DB running on a single machine, how is this completely non durable (ACID, the D part). Aren't you one power outage or reboot away from losing the entire database?

Also, if the entire DB is just in memory, why bother with a DB at all, why not just use the programming languages native data structures via RPC or something, and not deal with the massive complexity of an ORM solution?

z said...

Cool post. Lots of questions, but I think I already know some of the answers. My understanding used to be that "noSQL" was a minority approach to data management when data requirements went beyond what a RDBMS typically did. Given the proliferation of noSQL DBs and its usage today leads me to believe that we are dealing with a much different approach to how we choose to manage our data. This is definitely a good thing because I believe at the end of the day efficient and practical designs will succeed. This is an example of one of them.

Xorlev said...

@inboulder

The entire DB may fit in memory, but that doesn't mean it's a memory-based database. It just means it's cached in memory and written to the file backend on insert/update/delete.

He even says that InnoDB ensures that any data loss is <1s of data.

Yoshinori Matsunobu said...

@Evan,
Prepared Statement is still expensive in MySQL. It avoids SQL parsing, but it still has to open/lock/unlock/close tables, make query execution plans, etc.

Yoshinori Matsunobu said...

@Zeno

PostgreSQL supports C-based UDF so it might not be impossible, but I'm not sure whether PostgreSQL has a standard/documented/safe way to access to internal data structures from UDFs.

Sunny Bains said...

"** Grouping client requests ... I'll explain them in depth in later posts, if anybody is interested."

That would be great, if you could explain this in depth. Very interesting stuff!

dani said...

is available an api for php?

tmaesaka said...

Thank you for such an Informative blog post and being open about using this in production. You deserve a Web Scale sticker!

Pascal Maniraho said...

this is really impressive, I d like to use it in one of my projects! keep it up.

Anonymous said...

interesting but not useful for me.

Handling nearly 1mil QPS and all the load is on the disk... Good for a minimal setup, not good for load.

lava said...

woow

Antonio Ognio said...

Kudos for such interesting work. I guess this article is helping tons of us to better understand the high cost of SQL processing in almost any SQL-based datastore.

It seems to me few people will start exploring similar MySQL-based solutions with this NoSQL route, so I guess we should stay tuned for stuff like these in the following weeks/months for sure.

Narendra Dhami said...

Excellent work, it sounds interesting. Definitely, I would also like to try.

Thanks.

Andy said...

Any chance you could release a Python client? I'd LOVE to try this out!

Andy said...

"Even though you use innodb-flush-log-at-trx-commit=1, you lose only < 1s of data on server crash."

I thought when "innodb-flush-log-at-trx-commit" is set to "1", data is flushed to disk after every commit. So in that case there shouldn't be *any* data loss at all on server crash.

Are you saying HandlerSocket modifies that behavior so that now it is possible to lose data even with innodb-flush-log-at-trx-commit=1?

Yoshinori Matsunobu said...

@Andy,

> "innodb-flush-log-at-trx-commit" is set to "1"

Sorry, I meant innodb-flush-log-at-trx-commit != 1. Fixed.

Anonymous said...

DB2 and Static SQL Packages get rid of most of this overhead. Security, accessplans are already inplace, The only thing that happens when a query get's executed is the execution of the accessplan and returning the result.

So, no parsing, authority checking, creation of accessplans etc.

Zeno Davatz said...

Yoshinori-san, very interesting! We use a similar technique for our site together Postgresql. We have our own Software ODBA that writes SQL, marshals the data and puts the marshaled Data into the PGsql-DB. All of our data is always in the memory if the Application is running. ODBA is written in Ruby. You can find it here: http://scm.ywesee.com/?p=odba/.git;a=summary

Knut said...

Very interesting post. To say you are still using MySQL for this is a bit of a misnomer since what you have is an ad-hoc "nosql" database using InnoDB as the storage backend. Much in the same way that other nosql databaser are using Tyrant as the storage format. But nonetheless very cool and interesting.

Jayme Rotsaert said...

would like to see the results where you do userid= and no filecache jumps in.. :)

Anonymous said...

99% of your mysql load was preparing the adhoc sql. The lock management on a primary key lookup is not worth noting. You could have stuck with MySQL and just used some stored procs like everyone else. :/

Pedro Santos said...

Great way to keep MySQL alive ;)
Good work

Mark Callaghan said...

@Anon - I am not aware of results confirming that stored procs would provide such throughput? I doubt they would come close to this but would be thrilled to learn more.

@Anon - Are there similar results for DB2 on similar hardware? I don't always have enough time to keep up with the latest on DB2.

Tero said...

Really interesting. Maybe one day we will have PHP or Python client too :)

Eran said...

Very interesting concept and data! nicely done. I'm wondering though, since you forgo locks, how do you prevent data from becoming corrupt when multiple threads write to the same row at the same time? how do you prevent those writes from changing a row while it's being retrieved?

Lev Leontiev said...

Great. Man, you are genius.

neha said...

Super interesting! I don't see any mention of the query cache though. How big is it? If all your queries fit in the query cache, then can't it avoid sql parsing/creating access plans/locking tables?

f said...

You need to read this article carefully, all these questions are addressed. As for writing, if a db is teamed up with a pair, great if not wonderful things can start happenning...

mgrennan said...

Suffering suchita, what a concept!

Anonymous said...

I was able to get my ex back after I followed the instructions at www.saveabreakup.com I totally recommend this site, saveabreakup.com helped me a lot, all I can say is big THANKS!!! I'm so happy now...

Yoshinori Matsunobu said...

@Eran

Like regular DML statements, row locks are automatically taken by InnoDB, and released by commit. If a writer thread conflicts with DML, it blocks (or is blocked by) other DML that update the same rows.
Right now HandlerSocket writer is single threaded. So no conflict-by-multiple-writers situation happens. Single-threaded writer does not scale with CPU cores, but right now MySQL slave's SQL thread is also single threaded. Even if HandlerSocket supports multiple writer threads, it won't help so much because significant replication delay will happen. We hope that MySQL will support parallel replication threads in the near future.

Yoshinori Matsunobu said...

@neha

I disabled query cache during tests. Query cache entries are invalidated if the same table is updated, so it is not so much helpful for large tables.

krow said...

Considering that I added the daemon plugin so that I could do a quick UDP protocol for MySQL, I am pretty happy to see someone continue to use it :)

So why invent a new protocol? Why not just use the memcached protocol?

Akira Higuchi said...

krow,
the reason why I did not use the memcached protocol is that I wanted to make network transmission size small as far as possible. HandlerSocket supports more complex requests than simple KVS-like operations, and I thought it's not impotant to make HandlerSocket compatible to memcached.

mordred said...

You are all awesome people. I constantly miss NDBAPI when doing anything database related and am forced to use SQL. Thanks for writing this!

Yusuf said...

Awesome analysis. Great to know how fast mysql can be!!

Andy said...

"Right now HandlerSocket writer is single threaded. Single-threaded writer does not scale with CPU cores."

How does the insert/update performance of HandlerSocket compared to that of regular SQL/InnoDB on a commodity 8-core server?

Could you post any benchmark data in that area? I wondered if having a singlethreaded writer reduces the write performance of HandlerSocket significantly.

lsmith said...

is this a feasible alternative to storing session data inside memcache to be able to access session data from any web server in a cluster?

right now if you use memcache as the session store you have to do a fair bit of work to also be able to know who is actually currently logged in. usually in these cases i write the session id into the db when the session is created but only write to memcache in the subsequent requests.

now if i could keep the data in mysql to begin with, things become much easier.

till said...

It's pretty exciting to see new things in MySQL land and if "nosql" made some people do this, then hooray! ;-)

Also, this idea that people go "nosql" because of speed is pretty off. Most people I know looked for another approach because of the data they put into a store.

Akira Higuchi said...

Andy,
as I tested, HandlerSocket can perform more than 30000 write requests per second on a server with HDDs and a RAID controller with BBU write cache, even when sync_binlog = 1, innodb_flush_log_at_trx_commit = 1, and innodb_support_xa = 1.

HandlerSocket gathers multiple write requests and execute them inside a single transaction. This method is much (more than 100 times) faster than executing write requests in individual transactions. I guess there is no big performance gain even if we allow parallel writer threads.

I've not yet tested on SSDs.

There is a benchmark result in the page 45 of the following slide, though it is written in Japanese.
http://www.slideshare.net/akirahiguchi/handlersocket-plugin-for-mysql-4664154

Andy said...

Akira,

In your presentation on page 44, I saw "Intel X25-E" listed. Does that mean the benchmark was done using that SSD? But then you said in your comment above that it was done with HDDs. So I'm a bit confused. I don't speak Japanese so I'm just guessing here.

30,000 write req/s with sync_binlog = 1 & innodb_support_xa = 1 is VERY impressive. Can you confirm that the server was started with the "--log-bin" option?

MySQL's group commit is broken when binlog is enabled. Given the great performance HandlerSocket achieved even with binlog enabled, does that mean HandlerSocket has fixed the group commit bug?

If I write data using HandlerSocket, does MySQL replication still work?

Also will InnoDB Plugin's data compression work with HandlerSocket?

Thanks.

Akira Higuchi said...

Andy,
I had misunderstood. The result on page 45 was on my cheap PC with X25-E without RAID, as described on page 44. It was not on a server machine. Binlog was surely enabled.

HandlerSocket does not fix the group commit issue. Instead, it uses a trick; When a HandlerSocket writer receives many requests from multiple clients in parallel, it executes them all in a single transaction. This is why HandlerSocket is fast even when sync_binlog = 1.

HandlerSocket does support MySQL replication. All modifications via HandlerSocket are logged as row-based binlog entries.

I'm not sure if HandlerSocket works with InnoDB data compression.

Andy said...

Akira,

When writes to HandlerSocket are replicated in the slave, would multiple writes still be executed in a single transaction like they are in the master? If not, I'm concerned that the slave would lag far behind the master given how fast the master is.

When you said you were not sure if HandlerSocket works with InnoDB data compression, is it just because you haven't tried it yet, or is there some fundamental reason that the two wouldn't work together? It seems to me that since HandlerSocket sits above InnoDB, it should work fine with InnoDB's internal data compression. But I could be misunderstanding something here.

Yoshinori Matsunobu said...

@Andy

HandlerSocket works with any InnoDB data formats including compressed format because data format difference does not affect Storage Engine API. SQL/DML syntax is not affected by format/engine difference. The same thing can apply to HandlerSocket.
But we don't use InnoDB compressed format with HandlerSocket on our environments, so we haven't tested so much. In general, under CPU intensive workloads, InnoDB compressed format performs poor on slaves because compress/uncompress operations are very expensive and it occupies most of SQL thread's CPU time. So you should be very careful about replication delay.


> When writes to HandlerSocket are replicated in the slave, would multiple writes still be executed in a single transaction like they are in the master?

Of course. Slaves execute queries that are written in binlogs. Binlog events written by HandlerSocket will be "Begin; multiple DMLs(row format); COMMIT;".


BTW, InnoDB's broken group commit issue was (partially) fixed last year in 5.1 InnoDB Plugin. It works very well if sync-binlog is not set to 1. See this entry for detail (http://yoshinorimatsunobu.blogspot.com/2009/08/great-performance-effect-of-fixing.html).

Golan Zakai said...

Great job for Dena :)

I have installed HandlerSocket on centos and would like to share the installation notes.

http://golanzakai.blogspot.com/2010/10/installing-denas-handlersocket-nosql.html

Enjoy :)

sanmai said...

Really impressive.

Igor Lobanov said...

Very interesting, thank you!

The most important thing for me is that all your data is still in the mysql, so you do not need extra caching tier to optimize single hot spot.

BTW, is it possible to perform simple joins or small index-based range scans?

Jak said...

The article lists 750K qps/s w/ 3 NICs and 260K qps/s w/ a single NIC.
It also states that "Both memcached and HandlerSocket were network i/o bound".
The hardware is "Broadcom NetXtreme II BCM5709 1000Base-T", is this the quad port version?
I am interested in the NIC setup (did you change IRQ CPU affinities? did TOE help? if this is a quad port NIC, should we expect 1/4 performance w/ a single port, etc...)
I am also interested in any linux kernel parameters that may effect the thruput.
If the software is network I/O bound, then it seems like details on the NIC and its configuration are key to getting Handlersocket to 100% performance.
I would be very interested in more information on the networking side, thank you.

Yoshinori Matsunobu said...

@Jak

I tested an on-board Broadcom NetXtreme II BCM5709 that has quad-ports, and I used three ports of them. "Using three NICs" was misleading, sorry (I updated the post). BCM5709 supports TOC and I believe it helps. I used CentOS 5.5. I didn't change IRQ or any network-related kernel parameters except backlog settings.
I'm more interested in low-latency cards such as Dolphin Supersockets, but I don't have right now.

jaksprats said...

@ Yoshinori

The reason I ask about the NICs is you may be able to get a lot more speed if you multiplex the IRQ channels of NICs that support Tx/Rx queues.

Here is a post (http://bit.ly/dkugV0) where I got the NOSQL datastore redis to go from about 220 qops/s to 420K qop/s w/ a single NIC (that has Tx/Rq queues) and a quadcore @ 3.0 Ghz.

In my tests, redis (which usually performs slightly slower than memcached) was also network I/O bound and for people that need really fast performance, I have recommended using 2 cpu cores per NIC port (so using 3 ports on a 8-core machine like you did, which leaves 2 extra cores for the OS, etc... seems like an optimal setup). Do you have any opinions on the NIC port to CPU core ratio?

Yoshinori Matsunobu said...

@jak

This is interesting, thanks. Multi-queue Tx/Rx was not enabled on my Dell machine (I guess it's not supported, either). By using NIC that supports multiple Tx/Rx queues, single port performance will certainly improve on both memcached and HandlerSocket.

Talking about NIC port to CPU core ratio, so far I think 2 cpu cores per NIC port is enough for HandlerSocket read-only workloads. %us+%sy was almost 100% when using 3 ports / 8 cores so we can't expect much higher performance by just increasing ports.

sjamthe said...

Great work Yoshinori-san.

Andy said...

This looks great! I'd love to try it out. But is it possible to use HandlerSocket to atomically update a field based on existing value of that field?

I frequently need to do something like:

UPDATE table SET counter=counter+1 WHERE id=1

Is it possible to do the same using HandlerSocket?

Akira Higuchi said...

Andy,
HandlerSocket does not yet support atomic read-modify-write operations. I have a plan to implement them.
If you have more feature request, please report it as an issue.

ossip said...

Yoshi & Akira - all this sounds fantastic - while memcached+mysql works great it is architecturally stupid as you correctly point out. this sounds like a great solution! still need to test it. the Perl binding is fine for me. you might consider adding a (reduced functionality) memcached interface to get more "hype".

but yes, atomic read-modify-write would be great - I was looking for the same but Andy was faster :)

would ideally need to allow insert with default if no value found.
like using: insert .. on duplicate key update ..

Andy said...

Thanks Akira.

I'll file the feature requests.

Stephane said...

ossip,

caching is never architecturally stupid, asysnchonus caching on your dataflow will automatically improve hit ratio and brings the data closer to consumers saving lot's of cpu,

Yoshi & Akira . focusing on the worth case @MySQL we forget about the skip features and do it approch, many thanks for implementating it.

Stephane said...

Yoshi, did you bench something like SELECT FROM WHERE IN ( ?,?,?,?,?,100 times) it could probably get read of the open table mutex as well and minimize the parsing and auth cost, i supect a gearman or a mysql-proxy queing approche could also do the same goal ? it is not rare on comodity hardware to touch the 250M rows/s inside InnoDB with ugly plans especially using huge page.

Anonymous said...

@YOSHINORI: Can you provide your thoughts about the performance of NDBAPI vs. HandlerSocket?

Thomas said...

Hello Yoshi,

we are working with jboss and mysql. Between we use EHCache as a 2nd Level cache for Hibernate because latency is too high to fetch every date out of the database. The problem is, that EHCache is not 100% coherent with database state if you read/write highly concurrently.

Do you think it would be possible to enhance hibernate that way, that it makes simple queries through HandlerSocket Plugin and leave the 2nd Level Cache away?

Which steps would you go?

Do you think there will be any trouble doing that?

Thank you,
Thomas

Ashwin Jayaprakash said...

Sheer awesomeness!

Ashwin Jayaprakash said...

If only we had a clean Java NIO based client to call the server...

Ronald Bradford said...

You need to remove the first quotes in install example

INSTALL PLUGIN 'HandlerSocket' soname 'HandlerSocket.so';

should be

INSTALL PLUGIN HandlerSocket soname 'HandlerSocket.so';

Ronald Bradford said...

and for me I had to lowercase soname. So.


INSTALL PLUGIN HandlerSocket SONAME 'handlersocket.so';

Ronald Bradford said...

My working instructions for Ubuntu

http://ronaldbradford.com/blog/mysql-handlersocket-under-ubuntu-2010-11-05/

Anonymous said...

You should have enabled query cache in comparing with memcached because memcache results should also get invalidated (apples-to-apples comparison) when the data changes (or its results will be stale). Additionally for the particular use you mentioned PK lookups the data should be fairly stable (ie, change infrequently), further playing to the strengths of using query cache

Kristian Köhntopp said...

Yoshinori, I wonder how much auth checking would add in cost to HandlerSocket. This assumes a protocol modification that provides the auth info with every command in a style not unlike HTTP, to avoid additional round trips for authentication.

Yoshinori Matsunobu said...

Hi Kris!

An external contributor is now developing a simple authentication feature (sending plain auth strings when establishing connections). It will be merged and documented in the near future.

Anonymous said...

It will be so cool to add this to YCSB to compare against nosql: https://github.com/brianfrankcooper/YCSB

svenigalla said...

Has any one ported the HandlerSocket Client code to Java? Please post a link, if you have.

capttofu said...

This makes me so joyful that I am a bit verkempt! I am anxious to give this a try as I know so many organizations that want something like this.

greenido said...

Thanks for the post.
I'll give this a try in hope to get something similar in the performance.

peter said...

Great work Yoshinori!
Does memcached server have same thread count as HandlerSocket in the benchmark?

Anonymous said...

Great work Yoshinori!
I have two questions though:
(1) How can I scale to multiple server when the database size is too big for a single server to handle concurrent queries? Do you have performance results when database size is already big say 1TB?
(2) Assume all data fit in memory, what make HandlerSocket faster than memcached? When data can not fit in memory, I think comparing to membase is more fare. Have you did any comparison on membase?
Thanks!

Ben XO said...

Anon:

1) I would implement a sharding scheme. E.g. partition by ID.
2) Most likely, inefficiencies in Memcached.

dileepstanley said...

Awesome and really interesting.Also we can use our pretty rich sql statements for more complicated quries ,at the same time we can use highspeed access to our db.It is good for service oriented architectures and twitter like systems

dennis said...

@svenigalla

http://code.google.com/p/hs4j/

this is a java client nio based.

Anonymous said...

For the 750000 result, did the handler socket client threads batch get requests? If so, how large were the batches? Thx.

svenigalla said...

@dennis
thank you very much!!

slapo said...

@Akira Higuchi
"Instead, it uses a trick; When a HandlerSocket writer receives many requests from multiple clients in parallel, it executes them all in a single transaction."

Does this not backfire when one of the queries in the transaction would cause a rollback? Does HandlerSocket return those bad queries to clients as bad ones and then tosses the out of the batch and re-runs the transaction? Does this not affect transactions executed by the client otherwise?

Raine said...

Hi! First of all congrats to this great project!

I would like to know if there is any plan to develop a pure C client lib for HandlerSocket.

Regards

Arie Kachler said...

Any chance a PHP API will be implemented?
This is what I call the future of NoSQL!
Nice job!

Anonymous said...

Hi,

thank you for your work.
Do I understand it right that HandlerSocket supports only row-based replication and not statement-based?

Pavel

Monty said...

Thanks for a very interesting and detailed post. I had not heard about HandlerSocket before and I will look more into this after reading your post.

I was however a bit confused by the statement about HANDLER:

"Unfortunately HANDLER statement was not so much helpful to improve throughput because query parsing, opening/closing tables still be needed. "

It's true that for HANDLER, there is a query parsing overhead, but as the syntax is compact and trivial, the overhead should be almost neglectable. Except for the HANDLER OPEN / HANDLER CLOSE statements (which only would be executed once per thread) there should be no open/close table overhead when using commands.

Would it be possible for you to repeat the benchmark with HANDLER calls so that we could see where the time is spent?

I am prepared to try to optimize the HANDLER calls in MariaDB to get them faster, if possible.

It's probably the case that the thread pool you get with HandlerSocket still gives it an edge, especially as the thread-pool we have in MariaDB is yet not fully optimized in MariaDB 5.2 (will be fixed in MariaDB 5.3).

Getting some useful data for HANDLER CALLS + tread pool would really help us to know where we should put our efforts in improving the performance of the base MariaDB server! It would probably not replace HandlerSocket but still help for a better overall base experience!

(PS: Some of the malloc overhead that can be seen in your prof output is already fixed in MariaDB 5.3)

tahoeblue said...

I am curious if the primary key I/O operations supported by this interface also include Index-Sequential access methods ( ISAM ).

For speed and simplicity, the fastest and simplest database package I ever used was C-ISAM, developed jointly by Relational Database Systems (became Informix) and Santa Cruz Operation (SCO).

C-ISAM was developed to be compatible with the index-sequential access methods required by COBOL, specifically MicroFocus COBOL. These packages were the first database packages running on the first microprocessor versions of UNIX ( Interactive Systems running on Dorado Microsystems Z-8000 16-bit systems, circa 1980.

Originally, INFORMIX was indeed 'no-sql', as it was all based on a direct C-ISAM interface. INFORMIX subsequently added a SQL engine -- but one which performed all of its I/O using C-ISAM. Informix went on to develop much more powerful (in terms of memory caching and multithreaded I/O) SQL engines which were not based on C-ISAM, of course, including the MPP shared-nothing XPS ).

This was a particularly powerful arrangement, as transaction-processing and other high-performance C applications could be written using C-ISAM ( read: 'no-sql' ) direct access to tables, but reports could be written using SQL-based report writers.

There is a lot more history here ... :)

skrat said...

Crazy shit, with Java/Python/Ruby libs it will make it to mainstream.

love back said...

hi,i would love to share this story on how i got my love back,my husband left me for another

woman,i was devastated and left alone in the dark,i tried all means to get him back until i

found a love spell caster online who i contacted through his email psychicpela yahoo

com..days later my love walked through the front door apologising for his misdeeds...for

those of you in this type of situation,you can try him..

Anonymous said...

if the goal is to simply avoid SQL overhead, surely stored procedures would do the trick? would be interesting to see a benchmark...

Anonymous said...

php extension:
http://mysqldba.blogspot.com/2010/12/handlersocket-mysqls-nosql-php-and.html

Stephane Varoqui said...

New set of benchmarks to compare with Handler Interface.
http://varokism.blogspot.com/2010/12/using-mysql-as-nosql-story-for_27.html

New phones said...

I think the new bench mark is on the way.

leepro said...
This comment has been removed by the author.
leepro said...

I introduced it in Korean. http://goo.gl/gLFuD

Arno said...

Great plugin!

One question... would it make sense / is it possible to write a mysql-proxy script which intercepts INSERT, UPDATE, DELETE and simple SELECT's to use HandlerSocket instead of sql to return the results?

Ruturaj Vartak said...

excellent work! Hope this helps to keep MySQL ahead of other NoSQL and Heavy-weight SQL servers

Anonymous said...

Awesome! Thanks for sharing knowledge

Massimo said...

Great Work!!!

Is you add a transparent sharding and distribution awareness you have done a killer lib.

1 stupid question: have you tested if works also with the new semi-sync replication of 5.5?

Anonymous said...

I think this is great that mysql can be optimized to work great for reads with 1ml rows. However, what about storing 1 billion rows into mysql innodb? This is where things get really unhappy and you will need a system like Hbase or bigtable.

Nitin said...

Hi,

First of all thanks for the work you have done and shared with the community. Its just great.

I have been trying to use "Mysql Handler Socket" in my project.
I am using Python for connecting.
I have installed it as requested and is working fine for inserts and get request.

But i am facing one issue in find query everytime i try to fetch the result it gives me "idxnum error" or "kpnum error".
eg. "pyhs.exceptions.OperationalError: HandlerSocket returned an error code: idxnum"

please can you tell me the issue or method to track that.

Regards,
Nitin Dhiman

Tutorials said...

I am really impressed with the online tutotial and learned alot.

Siavash Mahmoodpoor said...

Very useful article, This is power of MySql !

Music online said...

I am really impressed.

Domain hosting said...

You have done a great job in this post and i really appreciate with you. Thanks for this great information

srini said...

Apart from saving and querying data from MySQL table, is it possible to do 'create table' or 'drop table' through handler socket?

TienCD said...

Thank for good technical.

famzah said...

You really made my day :) I'm happy that whatever I though is possible, you've already tried and proved to be working. Now I have even more motivation to continue working and sharing in this area. Keep up the good work!

kaycee said...

This is great news. Thanks for the codes. I will try this tight away!

Pknerd said...

Where can I get the binary of the plugin?

Suchmaschinen Optimierung said...

Great work you have done in this post. Thanks for sharing this topic with us.

Christo Kutrovsky said...

Thanks for sharing this in such great detail.

I do have one follow up question regarding your hardware.

You mention having:

Model: Dell PowerEdge R710
CPU: Nehalem 8 cores, E5540 @ 2.53GHz

The E5540 is 4cores/8 threads. Do you have one such CPU or two E5540 totaling 8 cores/16 threads?

Sagging skin said...

Your post really grabbed my attention and interest for the reason that the content is not just informative but also simple yet meaningful.

Natural laxative foods Stomach gas Hard stool treatment Health benefits of almonds White spots on skin
Cure ear infection Under eye wrinkles Baby ear infection Prevent prostate cancer Prevent prostate cancer Lemon tea healh benefits Lemon tea healh benefits Prevent candida yeast infection Prevent candida yeast infection Health benefits of papaya Health benefits of papaya Benefits of peppermint oil Benefits of peppermint oil Stop hiccups Stop hiccups Water and weight loss Water and weight loss Wart Removal Wart Removal Health benefits of oatmeal

web design company bangalore said...

Excellent job, Thanks for updating this post.

android tablet said...

Good post. Very impressive. Thanks for sharing.

android tablet said...

Good post. Very impressive. Thanks for sharing.

Nelly said...

Thanks, a really nice write up. I've been struggling with low ndb performance and even if this didn't fully solve my issues, it did help alot.

Anonymous said...

I am looking to load tables with 300m rows+ from MySQL into a java process. We are hitting some limits in MySQL which is causing the load to take a longer time than we need. I though using HandlerSocket would help by simply making the condition >0 but HandlerSocket is also slow for a couple of reasons

1. when using a large value for limit the complete response is buffered in a string_buffer before starting to send it. This leads to large pauses when using a large limit.

2. when setting limit quite large (say 20 or 50 million) and then using offset to skip over pages for each query the queries slow down considerably. Seems the time to skip to the start offset gets worse since it has to skip more rows as progress is made. Since we don't have an autoincrement so we could adjust the >X as we go we have to use >0 for each query which causes the problem.

Is there a better way using HandlerSocket to suck across all data from a table which is faster than using straight SQL through the java connector? Would be nice if results were streamed instead of buffered (I've modified the code to test it to flush the buffer out each time it reaches 4MB to see if that helps - not tested yet).

Taylan Karaoglu said...

Brilliant improvement, i have to agree. Good job.

That also shows us, mysql also need some improvement about cached sql compiler statements. Generally programs using same sql statements with different variables. If mysql can increase its parsing model precached opcodes maybe and query ways.or when to cache query or not cache depends on write time differences. Mysql itself also can be much better then NoSQL database speeds.

Anonymous said...

I think in production system, you need some kind of serialization?

E.g. FriendFeed use pickle
http://bret.appspot.com/entry/how-friendfeed-uses-mysql

cctv said...

These information helps me consider some useful things, keep up the good work.

security systems said...

Great post article is informative its use full and posting was cool

ccna chennai said...

I don’t know what to say except that I have enjoyed reading. Nice blog. I will keep visiting this blog very often.

Domain Registration said...

These information helps me consider some useful things, keep up the good work.

ccna chennai said...

Excellent information.. which I believe will be very useful for me.

Baby ear infection said...

Hello! I just wanted to take time to make a comment and say I’ve really enjoyed reading your site.

Cure ear infection said...

I was very pleased to find this site.Wow, this is really nice article!

Sinus headache said...

I found this is an informative and interesting post so i think so it is very useful and knowledgeable.Raw food diet Toenail fungus infection Common digestive disorders

Bianka Zen said...

I'm glad that I found your blog.I didn't knew all this things about NoSQL+MySQL .

Cam Gigandet Workout said...

I was very pleased to find this site.Wow, this is really nice article!

Remedies for sunburn said...

Excellent information. Thanks for sharing this post Home remedies for sunburn

hostingraja said...

Its really good message for every one. Is SQL really good for fast PK lookups. i agree with this points. because this is very important to this time, when i use this it has been god futures. domain registration

Singapore Jobs said...

Thanks for the great posting. Very informative blog.

giv bani online fara investitie said...

Interesting story.It's the first time that I hear about Nosql.

CNA Classes said...

After all, we weren’t even public with the site yet, so each of us continued to add value just by using it with each other.

LPN Programs in Florida said...

I found your blog, is really hard to find info about them, maybe you can belive me but I have spent a lot of hours searching on biggest search engines and couldn´t find anything uhaul

p90x said...

when setting limit quite large (say 20 or 50 million) and then using offset to skip over pages for each query the queries slow down considerably. Seems the time to skip to the start offset gets worse since it has to skip more rows as progress is made. Since we don't have an autoincrement so we could adjust the >X as we go we have to use >0 for each query which causes the problem

Paint Zoom Reviews said...

That also shows us, mysql also need some improvement about cached sql compiler statements. Generally programs using same sql statements with different variables. If mysql can increase its parsing model precached opcodes maybe and query ways.

WOW Gold said...

I found your blog, is really hard to find info about them, maybe you can belive me but I have spen

WOW Gold said...

ou ask? By the simple process of networking.
Networking is not about meeting hundreds of people interact with them and then never find again. It is, but to get to know and whether you are able to make the relationship
buy eden gold
eden gold
cheap eden gold

Runescape Gold said...

Excellent. How about Postgresql? Can HandlerSocket deal with that as well?


Buy RS Gold
Cheap Eden Gold
Buy WOW Gold
Tera Money
Tera Gold

London escorts said...

when setting limit quite large (say 20 or 50 million) and then using offset to skip over pages for each query the queries slow down considerably. Seems the time to skip to the start offset gets worse since it has to skip more rows as progress is made.After all, we weren’t even public with the site yet, so each of us continued to add value just by using it with each other.

London escorts said...

nice frnd

scary halloween said...

very thorough post, looking forward your post with mariadb

QHYAPPLE said...

Thanks for sharing and letting us aware about this information. This is a great thread, so much info.it ls a good article and love your words , so charming and make people learn a lot , thanks !
http://terafunn.blogspot.com/2011/10/make-sure-video-game-facts-are-straight.html

http://terafunn.qblog.it/2011/10/15/it-has-been-a-little-over-two-months-since-the-playstation-move-was-released/

http://terafunn.onsugar.com/best-win-out-fight-19994707

helmet chennai said...

Amazing blog, I am regular visitor of this website, keep up the good work.

rent a car chennai said...

Thanks a lot for sharing this useful and attractive information and I will be waiting for other interesting posts from you in the nearest future.keep it up.

Domain Registration With Debit Card said...

Excellent information.. which I believe will be very useful for me.

RS Gold said...

Is there a better way to use the socket handler to suck all the data in the table, which is faster than SQL directly through Java Connector? It would be nice if the results were buffered stream instead (I modified the code to check the flush the buffer every time you reach 4MB to see if it helps - not yet determined

Do you like paly aion,do you often Buy Aion Gold,Buy Aion Kinah and Buy Aion Kina? I am always boring about it,because i am afraid not fast or not safe,i also care about the price,I hope i can get Cheap Aion Gold,Cheap Aion Kinah and Cheap Aion Kina,but i know it not easy, No doubte that Aion Gold,Aion Kinah and Aion Kina can make our account much more stronger!

Anonymous said...

When I alter table,I found some strange in mysqld.err log:
HNDSOCK failed to lock tables 0x3cd13f0
HNDSOCK failed to lock tables 0x3cd13f0
HNDSOCK failed to lock tables 0x3cd13f0
HNDSOCK failed to lock tables 0x3cd13f0
HNDSOCK failed to open 0x3cc9df0 [database] [table] [1]
.......
Should do to solve

Cheap domain registration said...

Nice blog. I will keep visiting this blog very often.

domain booking through debit card said...

Thanks a lot for sharing this useful and attractive information and I will be waiting for other interesting posts from you in the nearest future.keep it up.

hermes birkin said...

Thank you for another essential article. Where else could anyone get that kind of information in such a complete way of writing? I have a presentation incoming week, and I am on the lookout for such information. hermes birkin | Hermes replica | hermes birkin replica

Free Space Interiors said...

Thanks for the information...

joomla development said...

Several things in here' haven't considered before.Thank you for making this type of awesome publish that is really perfectly written, is going to be mentioning lots of buddies relating to this. Maintain blogging.

Domain Registration With Debit Card said...

Amazing blog, I am regular visitor of this website, keep up the good work.

Cheap fitted hats said...

Cheap fitted hats
cheap caps
cheap baseball hats
cheap sports hats
New Era Hats
Baseball Hats
Red Bull Hats
Monster Energy Hats
DC Hats

WOW Gold said...

Leaving Cheap Runescape Money, but have yet sorry. Mustard Superman, or below it change I do not want that talent Eden Gold again next time you are better than they always die first. Very bad, but despite the dominated OT rate is too high. Ja, ja. I've been meaning to ask you to send me books level 7 is to buy bread Tera Gold? Small new, really sorry, I can not go with brush bread book, it takes 40 to 60, but as the first layer 7 of the bread. It has been true not feel that the girl is Buy Tera Gold, as stated in the game that actually match. Ja, ja. The tutor, book reached 100 arcane crystals I can not choose for you ....... In fact, finally give you the three that is bought at auction, but only three. I know I'll be sure to have the wind of the sword. I have confidence Cheap WOW Gold. Finally and lost you, I want to inform PK enemies Maple Story US.

horoskop said...

I'm enjoyed reading this article and whole website, thanks a lot for this informations!
dnevni horoskop
sanovnik

self drive cars chennai said...

These information helps me consider some useful things, keep up the good work.

domain booking through debit card said...

Nice blog. I will keep visiting this blog very often.

Chemistry tutor Banglore said...

Great site and The Article is really awesome and valuable, I fond of this Article very much, and if only all bloggers offered the same
content as you, the internet could be a much better place. Please keep it up!

Work from home said...

Wow! Great that you wrote this summary record - it really helps to refer back to this great event.

home tutor uk said...

the post is really great i found it really useful

seo company said...

the post is really good

Everett Jenquin said...

ken griffey shoes 2011 shoes are generally a person of the hottest sandals inside world. Such footwear usually are recognized for his or her longevity, comfort not to mention extensive variety possibilities. You may come across scores of Nike shoes in the marketplace. Golf ball sneakers by air max tailwind 2009 consist of functions you won't locate throughout additional makes. Typically the Nike shoes intended for ball include the newest attributes not to mention shoe technological know-how love, from the situation from air max 97 Basketball sneaker, flyfit engineering at the higher side which will presents greatest assistance.

how to register a website said...

Yes, some companies or some servers have the limit of qps.. In this case, i hope this will be very helpful one.. Great work!

ps0ram said...

wow what a breif explanation .very useful keep it up
http://www.esyncsecurity.com

Runescape Gold said...

http://newgameden.blogdetik.com/2011/10/31/updating-the-contents-that-are-not-new-halloween-themed-obstacles/
http://www.thoughts.com/gamereden/spirit-is-the-night-of-carnival-lovers-under-the-stars-more-easily-induced-by-a-romantic-spark
http://bloguay.com/playereden4u/2011/10/31/as-players-in-the-game-in-the-modeling-of-a-beautiful-collection-of-sugar-pumpkin/
http://all4wow.blogspot.com/2011/10/you-can-increase-blood-volume-of-500000.html
http://eden4u.blog.fc2blog.net/blog-entry-8.html
http://eden2good.canalblog.com/archives/2011/10/31/22525286.html
http://newgamereden.bloguez.com/newgamereden/3128954/Spirit-is-the-night-of-Carnival-lovers-under-the-stars-more-easily-induced-by-a-romantic-spark
http://neweden4u.over-blog.com/article-veteran-national-popular-games-of-the-gods-2-houshin-engi-online-87631020.html
http://all4wow.podbean.com/2011/10/30/cdc-games-group-games-cdc-operated-in-the-worlds-first-successful-deputy/
http://neweden.blog.com/2011/10/31/the-western-tradition-of-halloween-halloween-is-strange-to-the-spread-of-an-atmosphere/

buygold said...

  Buy Cheapest WOW Gold on the web is risky, you make payment for care about many smaller factors, like the amount of seriously precious metal you have to buy. In this article, we'll examine that question, to investigate how much Tera Goldyou can purchase once without being prohibited.

carl can said...

Nice post. I was checking continuously this blog and I am impressed! Extremely useful information specifically the last part :) ambex

website registration india said...

You made certain good points there. I did a search on the subject matter and found the majority of people will have the same opinion with your blog.

Kuhinje po meri said...

You have done a great job in this post and i really appreciate with you. Thanks for this great information

Natura Bisse said...

I am quite sure they will learn lots of new stuff here than anybody else!....There is also a lot of visual symbolism that is sometimes subtle, sometimes not so subtle..



Amore Pacific |Sisley

Nandhini said...

very useful keep it up thank u

domain registration india said...

Nice blog. I will keep visiting this blog very often.

Anonymous said...

Examples of these top brand Hublot Watches Sale bags are Hermes bags, Fendi handbags, Gucci bags, and Replica Watche Prada bags. If you are one of these ladies, who are always on the lookout for Hermes Mini kelly cheap deals, you probably know which boutique or store has the greatest finds. Furthermore, you probably also know what to look into, when buying discounted handbags.

Buy Generic Viagra online said...

Hey, I had been searching on this topic for a long while but I was not able to find great resources like that. Now I feel very confidence by your tips about that, I think you have choosen a great way to write some info on this topic.

china beads said...

zacoo provides you a wide range of china beads, jewelry making findings, wires, tools and other jewelry making supplies for your beading needs, china beads wholesale at zacoo online store is a wide choose.

Herschel Muehlbach said...

Fashion is the actually altering idea connected with incorporating more recent plus better developments. moncler jackets array with wardrobe has got achieved the agreement involving guys in addition to females both similar. The actual spencer are also sewed within a particular fashion which usually provides a lot more durability for the usage of moncler sale overcoats. The discolored along with lemon coloured moncler shoes for men spencer were by far the most preferred bit of clothing then, and they grew to become probably the most searched for after Spencer around The eu.

JOJO said...

What's the difference between handlersocket and the officially released memcached daemon plugin? Which is better?

pepa prase said...

I'm so excited. I really appreciate sharing this great post. Keep up your excellent work.
bojanke za decu
bojanke
gledanje u solju

Latest fashion said...

Thank you so much for sharing this post. Your views truly open my mind.

International Calls from UK said...

I suggest this site to my friends so it could be useful for them also. Great effort
Free International Calls | Inclusive International calls From UK | Cheap International Calls

domain registration india said...

Thanks a lot for sharing this useful and attractive information. keep it up.

20W FM Transmitter said...

Thanks for always giving us informative articles like this .

baby monitor said...

I've found a website on Baby Monitors. The website www.anextraeye.com has gone out to provide what we feel are the best for price in each category of video baby monitors.
The selection includes wireless baby monitors, wireless digital Baby monitors
as well as wireless usb monitors to help you keep an extra eye on your child.
All of their Video Baby monitors are wireless systems that can help you stay connected and aware of your baby's needs.
They can alert you to times when your baby is crying or needs your help even if you are not in the same room. It is like having an extra set of eyes that help you keep a closer watch on your baby for safety and peace of mind.

Domain Registration India said...

These information helps me consider some useful things, keep up the good work.

Swingset Slides said...

Interesting article and one which should be more widely known about in my view. Your level of detail is good and the clarity of writing is excellent. I have bookmarked it for you so that others will be able to see what you have to say

Swing Set Accessories | Accessories for Swing Sets

Atlanta Tux Rentals said...

This is a very intriguing post, I was looking for this knowledge. Just so you know I found your web site when I was searching for blogs like mine, so please check out my site sometime and leave me a comment to let me know what you think

Atlanta Tuxedo | Tuxedo Rentals Atlanta

Stereotactic Surgery in Tennessee said...

Great blog article about the Great blog article about this topic, I have been lately in your blog once or twice now. I just wanted to say hi and show my thanks for the information provided.

Carpal Tunnel Surgeon in Kentucky | Spinal Surgeon in Tennessee

Buckhead Corporate Event Space said...

Its always good to have tips on good blog posting. As I just started posting comments for blog and faced a lot of rejections. I think your suggestion would be helpful for me. I will let you know if this works for me.

Buckhead Garden Weddings | Buckhead Wedding Venue

banja srbija said...

Excellent post. I want to thank you for this informative read. I will bookmark this site and visit again..
Banje u Srbiji
Banje Srbije
dnevni horoskop

«Oldest ‹Older   1 – 200 of 341   Newer› Newest»

Post a Comment