vicidial_log and MyISAM vs InnoDB

Discussions about development of VICIDIAL and astGUIclient

Moderators: gerski, enjay, williamconley, Op3r, Staydog, gardo, mflorell, MJCoate, mcargile, Kumba, Michael_N

vicidial_log and MyISAM vs InnoDB

Postby bobbymc » Fri Mar 08, 2013 4:25 pm

What is the upside and downside of using those type engine types for vicidial_log?
bobbymc
 
Posts: 425
Joined: Fri Jan 05, 2007 12:26 am

Re: vicidial_log and MyISAM vs InnoDB

Postby williamconley » Fri Mar 08, 2013 5:38 pm

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.)
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: vicidial_log and MyISAM vs InnoDB

Postby bobbymc » Fri Mar 08, 2013 5:42 pm

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
bobbymc
 
Posts: 425
Joined: Fri Jan 05, 2007 12:26 am

Re: vicidial_log and MyISAM vs InnoDB

Postby williamconley » Fri Mar 08, 2013 7:22 pm

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.
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: vicidial_log and MyISAM vs InnoDB

Postby mcargile » Mon Mar 11, 2013 10:59 am

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.
Michael Cargile | Director of Engineering | ViciDialGroup | http://www.vicidial.com

The official source for VICIDIAL services and support. 1-888-894-VICI (8424)
mcargile
Site Admin
 
Posts: 614
Joined: Tue Jan 16, 2007 9:38 am

Re: vicidial_log and MyISAM vs InnoDB

Postby williamconley » Mon Mar 11, 2013 5:23 pm

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.
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: vicidial_log and MyISAM vs InnoDB

Postby bobbymc » Wed Mar 13, 2013 2:44 am

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
bobbymc
 
Posts: 425
Joined: Fri Jan 05, 2007 12:26 am

Re: vicidial_log and MyISAM vs InnoDB

Postby mflorell » Wed Mar 13, 2013 5:58 am

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.
mflorell
Site Admin
 
Posts: 18335
Joined: Wed Jun 07, 2006 2:45 pm
Location: Florida

Re: vicidial_log and MyISAM vs InnoDB

Postby bobbymc » Wed Mar 13, 2013 10:23 am

LOL thx for the info matt =)
bobbymc
 
Posts: 425
Joined: Fri Jan 05, 2007 12:26 am

Re: vicidial_log and MyISAM vs InnoDB

Postby williamconley » Wed Mar 13, 2013 1:46 pm

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 ...)
Vicidial Installation and Repair, plus Hosting and Colocation
Newest Product: Vicidial Agent Only Beep - Beta
http://www.PoundTeam.com # 352-269-0000 # +44(203) 769-2294
williamconley
 
Posts: 20018
Joined: Wed Oct 31, 2007 4:17 pm
Location: Davenport, FL (By Disney!)

Re: vicidial_log and MyISAM vs InnoDB

Postby bobbymc » Wed Mar 13, 2013 2:12 pm

LOLOLOL great =) u made my day
bobbymc
 
Posts: 425
Joined: Fri Jan 05, 2007 12:26 am


Return to Development

Who is online

Users browsing this forum: No registered users and 53 guests