Thursday, June 9, 2011

Performance test for Mysql SQL and handlersocket(2)

mysql version:5.1.55+innodb plugin 1.0.15+use Tmalloc
handlesocet:HandlerSocket-Plugin-for-MySQL-1.0.6-80
mysql and handlesocet configure parameters:

#handlesocket
handlersocket_threads=10
handlersocket_threads_wr=10
handlersocket_port=9998
handlersocket_port_wr=9999
handlersocket_readsize=8192

#innodb
default-storage-engine=INNODB
ignore-builtin-innodb
plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so
innodb_flush_method =O_DIRECT
innodb_file_per_table=1
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout=15
innodb_additional_mem_pool_size=100M
innodb_buffer_pool_size =20G
innodb_log_buffer_size=64M
innodb_log_file_size =256M
innodb_log_files_in_group=4
innodb_thread_concurrency=32
innodb_write_io_threads=16
innodb_read_io_threads =16
innodb_max_dirty_pages_pct=80
transaction-isolation=READ-COMMITTED
innodb_file_format=Barracuda

test resutls:
Case 1:
      Insert 1000000  records to table mt_data and mt_indexes1.
      MYSQL insert with Multi-threads=100 and batch number=2000
      HS  insert with Multi-threads=100,HS java client does not support batch commit.
Case 2:
      Update  1000000  records to table mt_data and mt_indexes1 by PK.
      MYSQL update  with Multi-threads=100 and batch number=2000
      HS  update  with Multi-threads=100,HS java client does not support batch commit.
Case 3:
     delete  1000000  records to table mt_data and mt_indexes1 by PK.
     MYSQL delete  with Multi-threads=100 and batch number=2000
     HS  update  with Multi-threads=100,HS java client does not support batch commit.
Case 4:
      Insert 1000000  records to table mt_data and mt_indexes1.
      MYSQL insert with Multi-threads=100 and batch number=1
      HS  insert with Multi-threads=100,HS java client does not support batch commit.
Case 5:
      Update  1000000  records to table mt_data and mt_indexes1 by PK.
      MYSQL update  with Multi-threads=100 and batch number=1
      HS  update  with Multi-threads=100,HS java client does not support batch commit.
Case 6:
     delete  1000000  records to table mt_data and mt_indexes1 by PK.
     MYSQL delete  with Multi-threads=100 and batch number=1
     HS  update  with Multi-threads=100,HS java client does not support batch commit.
Case 7:
      Select 1000000  records from  table mt_data and mt_indexes1 by PK.
      MYSQL Select with Multi-threads=100
      HS  Select with Multi-threads=100

Case name
HS operation
MYSQL operation
Case 1
288992 ms
59202 ms
Case 2
358555  ms
58741 ms
Case 3
267092 ms
35127 ms
Case 4
288992 ms
77414 ms
Case 5
358555 ms
93651 ms
Case 6
267092 ms
78639 ms
Case 7
18574 ms
27864 ms


Summary:
mysql insert/update/delete more quickly than handlesocket insert/update/delete,
but handlesocket select more quickly than  mysql select.

(you can get the perl client test result from following url:
http://philipzhong.blogspot.com/2011/03/performance-test-for-mysql-handlesocket.html
)

1 comment:

  1. Thanks for sharing the performance test for Mysql SQL and handlersocket. You saved my lot of time. I am very thankful to you.
    electronic signatures

    ReplyDelete