After SugarCRM was deployed, we were experiencing some lock ups. Not frequent or dangerous, but annoying. About once a week or every ten days or so, SugarCRM would lock up and won’t answer any queries at all. Not even the login was possible. A brief investigation showed that somehow it was locking up the MySQL database – about 15 processes (using “show full processlist”) in Locked state, with no data being sent back or forth. All locked queries were rather complex, with several JOINs. The load on the system was somewhat high, since we have about a few dozen operators working on it at the same time.
A similarly brief Google search suggested (see here and here) and explained converting MySQL tables from InnoDB to InnoDB. A test has been performed and everything went OK. Our SugarCRM database is about 600 MBytes and it was converted from MyISAM to InnoDB in under 20 minutes. The best part is that it takes even less to convert back to MyISAM, in case you change your mind.
It’s been a few days now since we did the conversion and it looks OK. Also, the CRM itself feels a bit faster.
Would like to know if you implemented something related to register locking in the databases. I am having an issue with a custom module (reservations), that uses autonum fields; the problem is that sometimes these numbers are not unique as needed by the customer, because two executives execute “save” and both get the next (same) number. So it would be desirable to make some kind of register locking. Thanks!
Hey Daniel, it sounds like you are using the auto increment for the ID field. Have you tried using UUID instead? These are practically impossible to overlap.