0 Replies Latest reply on Jun 8, 2007 12:22 PM by Richard Mossman

    Poor performance after altering tables to InnoDB

    Richard Mossman Level 1
      I have an application using CF MX, IIS, and MySQL 5.0.37 running on Microsoft Windows Server 2003.

      When I originally built the application, access from login to start page and page to page was very good. But, I started getting errors because tables were sometimes getting records added or deleted and sometimes not. I thought the "cftransaction" statements were protecting my transactions. Then I found out about MyISAM (the default) vs InnoDB.

      So, using MySQLAdmin, I altered the tables to InnoDB. Now, the transactions work correctly on commits and rollbacks, but the performance of the application stinks. It now takes 20 seconds to log in.

      The first page involves a fairly involved select statement, but it hasn't changed at all. It just runs very slowly. Updates also run slowly.

      Is there something else I was supposed to do in addition to the "alter table" in this environment? The data tables used to be in /data/saf_data. Now the ibdata file and log files are in /data and only the ".frm" files are still in saf_data.

      I realize I'm asking this question in a CF forum. But, people here are usually very knowledgable and helpful and I'm desperate. This is a CF application. Is there anything I need to do for a CF app to work well with MySQL InnoDB tables? Any configuration or location stuff to know about?

      Help, and Thanks!