4

We recently upgraded mysql database which is running on mysql server version 5.5.8. After the upgrade sometimes we are seeing the simple upgrade staements taking more than 10 seconds. And when i looked at the processlist it shows that all of them are in "query end" state. Does any body has any idea on this.

 UPDATE Accounts SET IPadd = 'xx.xx.xx.xx', updatedtime = NULL WHERE Id=xx;
triclosan
  • 5,578
  • 6
  • 26
  • 50
Chandu
  • 41
  • 2
  • 5

2 Answers2

0

I've checked some solutions here and I came up to this link.

Add this line (if it exists edit it):

innodb_flush_log_at_trx_commit = 0

in the /etc/my.cnf

However

On Pedro Werneck comment:

Setting innodb_flush_log_at_trx_commit naively can be dangerous

It's your choice if you're doing transactional queries to it.

Community
  • 1
  • 1
Freddie Fabregas
  • 1,162
  • 1
  • 7
  • 17
  • Log files are a definite candidate. But I would consider purging the logs first. Disabling flush_log could have adverse consequences. And the OP hasn't even said if he's using innodb. See my links above. IMHO... – paulsm4 Mar 01 '13 at 00:30
  • I disagree on purging the log files, I'd rather move it to other directory. However, in his questions. We can't determine what table engine he used. I've arrived on this answer, maybe he's using InnoDb. – Freddie Fabregas Mar 01 '13 at 00:36
0

Suggestions:

1) Check the size of your log files

LINK: Lots of "Query End" states in MySQL, all connections used in a matter of minutes

2) Make sure "id" has an index

3) Look at various MySql tuning and configuration options. For example: innodb_flush_log_at_trx_commit = 0

LINK: "query end" step very long at random times

WARNING: consider carefully before disabling log flushing. Look here for more details:

LINK: https://dba.stackexchange.com/questions/12611/is-it-safe-to-use-innodb-flush-log-at-trx-commit-2

Community
  • 1
  • 1
paulsm4
  • 114,292
  • 17
  • 138
  • 190
  • Thanks for the response. And as you mentioned i did check the log files, we have enough space available and those files are not too big. id is a primary key in that table, so i think we dont need index on that. And i am trying out the 3rd one. – Chandu Mar 01 '13 at 02:38