[Original] handlersocket series (III): Performance and Performance Optimization

Previous two articlesArticleThis article mainly introduces handlersocket in terms of performance and performance optimization.

 

I. handlersocket Performance
The author of handlersocket tests that the QPS of handlersocket in queries is 75 K, memcached is 40 K, and MySQL is 10 K. However, you need to pay attention to its testing scenarios. Generally, it is difficult to use such scenarios. Therefore, it is difficult to use such applications as MySQL 7.5 times, however, the performance improvement is unquestionable. The author's test scenario is as follows:
1. Disable MySQL query cache: that is, each MySQL operation requires SQL parsing and other operations.

2. CPU bound instead of I/O bound: the InnoDB buffer pool is set to be relatively large, and the hit rate is close to 100%.

Therefore, we should look at the test data more objectively. Handlersocket can increase the query performance by 100% times when innodb_buffer_pool is close to 7.5 hit rate for applications of the CPU bound type rather than I/O bound type. This is not difficult to understand, because the main performance optimization of handlersocket is to save the overhead of the SQL layer, and the overhead of the SQL layer is mainly the overhead of the CPU. For an I/O bound application, the query performance of handlersocket may be less than 7.5 times, and there may be a big gap between handlersocket and 7.5 times, for handlersocket applications, we should try to increase the innodb_buffer_pool size as much as possible.

 

I have also done some benchmark tests. Basically, in the case of insertion, the performance of handlersocket can reach 3-5 times that of MySQL in the same environment. The larger the data volume, the more obvious, especially after 50 million. In the case of queries, handlersocket is 1.5-2 times that of MySQL in the same environment, which is quite different from the 7.5 times of the author's test. This is also what I mentioned above, the author's test data is large enough in innodb_buffer_pool and the hit rate is very high. Due to the limited conditions of the benchmark machine, there is not a large enough buffer pool, and the hit rate is not very high, i/O overhead is not small, which also proves that the performance improvement of I/O bound is not particularly obvious, so we should try to increase the innodb_buffer_pool size as much as possible, try to be close to the data size. In addition, I did not disable the query Cache during the test. Therefore, for MySQL testing scenarios, the execution plan and cache data can be reused.

 

As mentioned above, handlersocket has many advantages, and its performance has been greatly improved. However, we also need to look at it rationally. There are some things that need special attention when making decisions, I would like to give a brief summary here.
1. We should try our best to achieve the CPU-bound scenario, instead of IO-bound, so that we can better utilize the advantages of handlersocket. The specific method is to increase the memory and increase the innodb_buffer_pool size as much as possible.

2. Because merge operations are adopted, the response time will increase to varying degrees. You should consider whether the response meets your application scenario. You can continue to pay attention to the subsequent version optimization policies. For example, some friends may want this: The read operation is not a merge operation, but the write operation is a merge operation, of course, in this case, the overall read performance will be reduced to varying degrees, but isn't it a balance? Let's look at the specific application scenarios.

 

Ii. handlersocket Performance Optimization
As mentioned above, handlersocket performance has been significantly improved compared with traditional MySQL, but in order to better exert its advantages, some related optimization needs to be done.

 

Performance Optimization mainly involves the following three aspects: Of course, in addition to these three aspects, there are other optimization methods, such as optimizing the operating system, using direct Io, etc, the three aspects mentioned here are relatively easy to achieve and the technical cost is not high:
1. hardware environment
As mentioned above, we should try to increase the size of the InnoDB buffer pool to the hardware, that is, to increase the memory size as much as possible. Ideally, the memory size is the same as the data size. If there are resources, you can also consider using SSD, which has an SSD-based test data (http://www.percona.com/docs/wiki/benchmark:handlersocket:ssd:start), performance is still very powerful.

2. Client Optimization
The client communicates with the server based on socket. Opening and closing the connection, openindex, and other operations are resource-consuming operations. do not perform these operations as frequently as possible. Therefore, the connection pool should be established on the client, and some better communication models should be adopted, such as epoll AND NiO Based on Linux. For example, this Java client (http://code.google.com/p/hs4j/) is doing well.

3. handlersocket and InnoDB Configuration
Handlersocket Configuration:
// The number of read threads, recommended as the number of logical CPUs. For example, the number of hyper-threading threads should be * 2
Handlersocket_threads = 16
// Number of write threads. The current version is recommended to be 1.
Handlersocket_thread_wr = 1
// Read Request listening port
Handlersocket_port = 9998
// Listening port for write requests
Handlersocket_port_wr = 9999

 

InnoDB Configuration:
// InnoDB buffer pool size. The larger the size, the better.
Innodb_buffer_pool_size
// InnoDB log file size. Set the size as needed. The larger the size, the better.
Innodb_log_file_size, innodb_log_files_in_group
// The number of files that can be opened by the mysqld process. The recommended value is 65535.
Open_files_limit = 65535
// If it is set to 1, it can improve the performance, but the corresponding memory will also be consumed, which requires a good balance.
Innodb_adaptive_hash_index = 1

 

Author: Hong Xiaojun
Source:Http://www.cnblogs.com/inrie

Please indicate the source for reprinting. Thank you!