0

I want to increase the performance of MySQL. So I have done the configuration level changes to MySQL. I used innodb_flush_method = O_DIRECT, but insert rate is not increasing much. Normally, insertion rate is 650 inserts/sec. How do I know weather O_DIRECT is working properly.

I am using Ubuntu 14.04.1 server and MySQL v5.6. CPU Memory and Disk I/O rates are normal (I use RAID, 16 GB RAM, 8 CPU cores) I use WSO2 CEP for insertion. I have implement that part and measured using MySQL workbench. But I couldn't get much more performance though I increase the insertion rate through wSO2 CEP.

I have used following my.cnf.

my.cnf

[mysqld]
innodb_buffer_pool_size = 9G
query_cache_size = 128M
innodb_log_file_size = 1768M
innodb_flush_log_at_trx_commit = 0 
innodb_io_capacity = 1000
innodb_flush_method = O_DIRECT
max_heap_table_size = 536870912
innodb_lock_wait_timeout = 1
max_connections = 400
sort_buffer_size = 128M 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
skip-host-cache
skip-name-resolve
event_scheduler=on
Community
  • 1
  • 1
Saveendra Ekanayake
  • 3,153
  • 6
  • 34
  • 44
  • 1
    How are you doing the inserts? What is autocommit? Is the double write buffer on? SSDs? RAID? How much RAM? How are you measuring the 650? (flush_method is only one small piece of the picture) – Rick James May 10 '16 at 23:12
  • @RickJames I use RAID, 16GB RAM, I use WSO2 CEP for insertion. I have implement that part and measured using MySQL workbench. But I couldn't get much more performance though I increase the insertion rate through WSO2 CEP – Saveendra Ekanayake May 11 '16 at 05:34

1 Answers1

1

In this case if you are using Event tables, older CEP/siddhi version does not perform batch insertions.. That could be the cause for above.. In latest SNAPSHOT source (of Siddhi) we have fixed this.. And you can gain considerably good numbers in next release..

Mohanadarshan
  • 830
  • 5
  • 5