we have a 3 node cluster (with only two nodes with MySQL running and one with only garbd daemon running for quorum management) on RHEL 6.2 with 8GB RAB and 4GB swap.
On this cluster, we have a 16 databases fot 160GB of data (more or less) and we use only one server as master to execute our application’s query.
Against the DB’s, are executed a lot of query’s kinds, but on delete query we suffer for server’s swapping till the saturation of swap partition and the consequent mysql crash.
Every transaction delete 20.000 rows.
We have reduced the innodb-buffer-pool-size but the problem still occur.
Here’s part of our my.cnf related to InnoDB and memory:
innodb-buffer-pool-size=3584M innodb-additional-mem-pool-size=32M innodb-log-buffer-size=16M innodb-flush-log-at-trx_commit=1 innodb-file-per-table=1 innodb_data_file_path = ibdata1:100M:autoextend ## You may want to tune the below depending on number of cores and disk sub innodb_read_io_threads=4 innodb_write_io_threads=4 innodb-doublewrite=1 innodb_log_file_size=512M innodb-log-files-in-group=2 innodb-buffer-pool-instances=2 innodb-thread-concurrency=0 #innodb-file-format=barracuda innodb-flush-method = O_DIRECT innodb_locks_unsafe_for_binlog=1 innodb_autoinc_lock_mode=2 ## avoid statistics update when doing e.g show tables innodb_stats_on_metadata=0 engine-condition-pushdown=1 key_buffer_size = 24M tmp_table_size = 32M max_heap_table_size = 32M max-allowed-packet = 16M #sort-buffer-size = 512K #read-buffer-size = 256K #read-rnd-buffer-size = 512K myisam-sort-buffer_size = 8M skip-name-resolve memlock=0 sysdate-is-now=1 max-connections=500 thread-cache-size=50 query-cache-type = 0 query-cache-size = 0 table-open_cache=4096 lower-case-table-names=0 open_files_limit=65535
Are the transactions too many large?
What are the max transactions size suggested?
Thanks in advance for any suggestion!