vicidial_log and MyISAM vs InnoDB

[bobbymc]

What is the upside and downside of using those type engine types for vicidial_log?

[williamconley]

for log files it is still a debate. the reason for myisam is SPEED. without myisam, the faster (more used) tables cannot react quickly enough.

i will note that call_log is used quite a bit, but it is not (AFAIK) changed, it is merely written to. which may allow for some discretion in speed. but if you do change it (to avoid corruption during a crash) ... Beware. heavily loaded systems require serious throughput and if that table is in high demand, it may well bring the system down. this may also apply to reporting.

best bet would be to load test it after making the change (well, not actually the best bet ... that would be to leave it myisam and not have any power outages and prune all _log tables regularly into their respective _log_archive tables.)

[bobbymc]

issue i have with myisam is that i get tons of locks.. vs innodb its row level locking.. so if someone makes a query againt vicidial_log and it takes a while it deosnt prevent the rest users from using the system

[williamconley]

test it! I'd love to hear the results of a system that is already experiencing the locks when you change to the new engine. seriously.

[mcargile]

Do not use InnoDB for the vicidial_log! You will have problems and they are hard to identify. This is especially true with larger multi server clusters.

Vicidial uses MySQL for inter process communication. Part of this communication is the table level locking in MyISAM. It acts as a semaphore between the various processes letting them know when they can proceed. InnoDB does not do table level locking by default. It does row level locking. As such various processes will run past each other causing very bizarre problems that are hard to figure out. You can enable table level locking on InnoDB, but at which point you loose all of the performance improvements you might gain from it.

About the only tables in the Vicidial database that you could use InnoDB on and not have problems are the _archive tables as only the archive process touches those.

[williamconley]

Interesting. Having never veered from myisam, I was unaware that vicidial processes use the "block" as a form of order of operations management to be sure things happen in order. Good to know. Hm.

[bobbymc]

Are you 100% sure we can't use innodb for vicidial_log ? Can we have matt comment on this please or verify that we can't ? I been using innodb on that table for a long time and I sometimes do experience wired issus but I'm not sure its related to that

[mflorell]

Of course you "can" use InnoDB for vicidial's active tables, but you shouldn't. And if you feel that you have to use it for some reason you need to change it to do table level locking, at which time it will become even more inefficient than it already is at processing Vicidial requests.

The problem is with deadlocks and the complete lack of query queue integrity when using a row-level locking engine. Vicidial is a real-time application, not a transactional application. It requires query queue integrity to function properly, and when it doesn't have that then random things will start breaking. Vicidial also does not have a middle layer of query retry loops programmed in to handle deadlocks and maintain query queue order. We looked at doing this and it is a massive undertaking. Something that is completely unnecessary if you just use MyISAM, which is also much more efficient than writing your own queueing mechanism.

This is kind of like asking if you "can" drink vodka instead of water. Sure it will hydrate you to some extent, but you'll be stumbling around running into walls all of the time.

[bobbymc]

LOL thx for the info matt =)

[williamconley]

i dunno. i used vodka instead of water almost all the time when i was in the navy. i don't remember any problems with walls. (in fact, i don't remember much of anything, now you mention it ...)

[bobbymc]

LOLOLOL great =) u made my day