• RSS
  • Facebook
  • Twitter

Knowledge is Power.

  • Who you are ?

    Working on machines without understanding them ? Then you should be here..

  • Where you are ?

    Geographical location should not become a barrier to Share our knowledge.

  • What do you do ?

    Puzzles and Interview question are intended to be discussed here.

    Friday, August 23, 2013

    "Query end" state in MySql appears to consume a lot of time which becomes a bottleneck for insert operations.

    I had written a stored Procedure in Mysql which implements a cursor to iterate over the rows of a table. This cursor worked well on my machine but it took a lot of time in the live environment (POC setup).

    Initial culprit seemed to be the hardware but after analysing the procedure with "show processlist", I found that every insert would used to get stuck at query insert state.

    Incase any of you guys come across similar issue, you can edit your my.cnf file (I think my.ini for windows) and change this

    innodb_flush_log_at_trx_commit = 1
    to
    innodb_flush_log_at_trx_commit = 0 or innodb_flush_log_at_trx_commit = 2.

    This will drastically improve the performance but it also prohibits the innodb engine to serve ACID properties.

    After reading some forums I found that the above parameter can be changed if you are ready to incur a loss of one statement in case of system crash(not Mysql) or power failure.

    0 comments:

    Post a Comment