Adjusting Memory Usage for MyISAM and InnoDB Handling Too-Long Or Too-Short Transactions As you transition away from  MyISAM  tables, lower the value of the  key_buffer_size configuration option to free memory no longer needed for caching results. Increase the value of the innodb_buffer_pool_size  configuration option, which performs a similar role of allocating cache memory for  InnoDB  tables. The  InnoDB  buffer pool caches both table data and index data, speeding up lookups for queries and keeping query results in memory for reuse. For guidance regarding buffer pool size configuration, see Section 8.12.3.1, “How MySQL Uses Memory” . Because  MyISAM  tables do not support transactions, you might not have paid much attention to the autocommit  configuration option and the  COMMIT  and  ROLLBACK  statements. These keywords are important to allow multiple sessions to read and write  InnoDB  tables concurrently, providing substantial scalability benefits in write-heavy workloads. While a transaction is open, the system keeps a snapshot of the data as seen at the beginning of the transaction, which can cause substantial overhead if the system inserts, updates, and deletes millions of rows while a stray transaction keeps running. Thus, take care to avoid transactions that run for too long: •If you are using a  mysql  session for interactive experiments, always  COMMIT  (to finalize the changes) or ROLLBACK  (to undo the changes) when finished. Close down interactive sessions rather than leave them open for long periods, to avoid keeping transactions open for long periods by accident. •Make sure that any error handlers in your application also  ROLLBACK  incomplete changes or  COMMIT completed changes. • ROLLBACK  is a relatively expensive operation, because  INSERT ,  UPDATE , and  DELETE  operations are written to  InnoDB  tables prior to the  COMMIT , with the expectation that most changes are committed successfully and rollbacks are rare. When experimenting with large volumes of data, avoid making changes to large numbers of rows and then rolling back those changes. •When loading large volumes of data with a sequence of  INSERT  statements, periodically  COMMIT  the results to avoid having transactions that last for hours. In typical load operations for data warehousing, if something goes wrong, you truncate the table (using  TRUNCATE TABLE ) and start over from the beginning rather than doing a  ROLLBACK . •For most operations on  InnoDB  tables, you should use the setting  autocommit=0 . From an efficiency perspective, this avoids unnecessary I/O when you issue large numbers of consecutive  INSERT , UPDATE , or  DELETE  statements. From a safety perspective, this allows you to issue a  ROLLBACK statement to recover lost or garbled data if you make a mistake on the  mysql  command line, or in an exception handler in your application. • autocommit=1  is suitable for  InnoDB  tables when running a sequence of queries for generating reports or analyzing statistics. In this situation, there is no I/O penalty related to  COMMIT  or  ROLLBACK , and  InnoDB  can automatically optimize the read-only workload. •If you make a series of related changes, finalize all the changes at once with a single  COMMIT  at the end. For example, if you insert related pieces of information into several tables, do a single  COMMIT after making all the changes. Or if you run many consecutive  INSERT  statements, do a single  COMMIT after all the data is loaded; if you are doing millions of  INSERT  statements, perhaps split up the huge transaction by issuing a  COMMIT  every ten thousand or hundred thousand records, so the transaction does not grow too large. 3030 The preceding tips save memory and disk space that can be wasted during too-long transactions. When transactions are shorter than they should be, the problem is excessive I/O. With each  COMMIT , MySQL makes sure each change is safely recorded to disk, which involves some I/O. Tables