MySQL Forums
Forum List  »  InnoDB

COMMIT function take too much time to respond
Posted by: He Ha
Date: April 09, 2011 02:35AM

My COMMIT command is doing its work for about 20 minutes and prevent other query from being execute at all. I cannot wait so, I tried to kill the COMMIT query process then it mark as killed and auto create a new process with another COMMIT again. I also have tried to stop the mysql server but it cannot stop COMMIT too. After I CTRL+C to get out of shut down mysql, I cannot access mysql again. I have to reboot the server to get everything back to normal.

Here's process at that time with mytop:
http://dl.dropbox.com/u/773666/petswar_db_bug.png

Here's my my.cnf:

# The MySQL server
[mysqld]
port = 3306
socket = /var/run/mysqld/mysqld.sock
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
tmpdir = /tmp
log_error = /var/log/mysql/error.log
skip-external-locking
key_buffer_size = 32M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 32M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

log-bin=mysql-bin

# expire log
expire_logs_days=3

# binary logging format - mixed recommended
binlog_format=mixed

server-id = 1

innodb_data_home_dir = /usr/local/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/data
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
#innodb_thread_concurrency = 8
innodb_flush_method=O_DSYNC
innodb_change_buffering=all
#innodb_file_per_table

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

What should I do to prevent this problem again? It happen 2 times a day at this time.



Edited 2 time(s). Last edit at 04/10/2011 12:39AM by He Ha.

Options: ReplyQuote


Subject
Views
Written By
Posted
COMMIT function take too much time to respond
2609
April 09, 2011 02:35AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.