0

So I have a centralised CMS with multiple sites running from a single DB. Recently we have very occasionally been receiving a max_user_connections error which we have seen before (while on a much slower server).

Now the hosts have suggested that innodb's row locking rather than myisam's table locking could solve this. Sounds like it could be the case, however I would have though increasing the connections would be a safer bet.

I have done some reading up on innodb vs myisam, and I dont believe the loss of full-text indexing will be a problem, however I am unsure about the foreign key thing.

So my question is, with this running on a production database, with a huge CMS and various instances of front end code, are there likely to be any major issues in converting to innodb? Eg specifically what type of queries would no longer work?

Horse
  • 3,023
  • 5
  • 38
  • 65
  • http://stackoverflow.com/questions/2006053/converting-myisam-to-innodb-beneficial-consequences http://stackoverflow.com/questions/20148/myisam-versus-innodb – vulkanino Feb 09 '12 at 10:44

1 Answers1

1

Two points:

  • Increasing the connections - This will not solve your problem while having such "Stop-the-world" scenarios. Increasing the connections will only give you more parallel connections but having such issues means normally that the operation on the table is frequent and in between one single operation one or more similar requests run in a blocking state. So even if you increase the connection pool dramatically it will only be a temprorary solution.

  • InnoDB is a completely different Storage-Engine. So you need to invest some time in different configuration properties and backup scenarios. From a query perspective you may have more options with innodb rather than with myisam. Afaik the only thing what is not possible is fulltext indexing. See a comprehensive listing of pros and cons here: http://www.kavoir.com/2009/09/mysql-engines-innodb-vs-myisam-a-comparison-of-pros-and-cons.html. One hint: Get in touch with the different toolset for recovery, failsafe and backup

Alternatives: You might have no other option than resolving locking issues by switching either to a row-level locking mechanism or switching to something completely different.

There is also the option not to switch completely. You might configure the storage engine on a Table basis. So if you identify the table where locking issues occur you might simply migrate this table to a different storage engine.

fyr
  • 20,227
  • 7
  • 37
  • 53