MyISAM, InnoDB and Foreign Keys

Discussions on developing plugins for the Cacti Plugin Architecture

Moderators: Developers, Moderators

Post Reply
Voiper99
Cacti User
Posts: 276
Joined: Thu Mar 01, 2007 5:43 pm
Location: Melbourne, Australia

MyISAM, InnoDB and Foreign Keys

Post by Voiper99 »

Hi all, I'd like to create a new table to store information for my plugin. The information will be related to the hosts already created in Cacti, so the best way I can think of this is to create an InnoDB table and use Foreign Keys to reference the data in the 'host' table, however, the 'host' table is a MyISAM, and that leads me to two questions:

1. Can I even do this (use a MyISAM primary key to act as a foreign key for InnoDB)?
2. I have noticed that all of the tables in my installation of Cacti is MyISAM. Is there any reason for this apart from MyISAM being the fastest type to retreive information from?
User avatar
TheWitness
Developer
Posts: 17047
Joined: Tue May 14, 2002 5:08 pm
Location: MI, USA
Contact:

Post by TheWitness »

Cacti's real old and so is MyISAM. Innodb complicates things for a system whose users are not too complicated. On the key issue, use the "explain select" mysql command to see if your joins are optimized.

TheWitness
True understanding begins only when we realize how little we truly understand...

Life is an adventure, let yours begin with Cacti!

Author of dozens of Cacti plugins and customization's. Advocate of LAMP, MariaDB, IBM Spectrum LSF and the world of batch. Creator of IBM Spectrum RTM, author of quite a bit of unpublished work and most of Cacti's bugs.
_________________
Official Cacti Documentation
GitHub Repository with Supported Plugins
Percona Device Packages (no support)
Interesting Device Packages


For those wondering, I'm still here, but lost in the shadows. Yearning for less bugs. Who want's a Cacti 1.3/2.0? Streams anyone?
raine
Posts: 4
Joined: Tue Feb 08, 2011 12:09 pm

Re: MyISAM, InnoDB and Foreign Keys

Post by raine »

InnoDB store engine scales a lot better than deprecated MyISAM.

See: http://blogs.oracle.com/mysql/2011/01/c ... mance.html
User avatar
Howie
Cacti Guru User
Posts: 5508
Joined: Thu Sep 16, 2004 5:53 am
Location: United Kingdom
Contact:

Re: MyISAM, InnoDB and Foreign Keys

Post by Howie »

Also, the 'foreign keys' in the Cacti schema are pretty wacky - often back to the same table for things like templates.
Weathermap 0.98a is out! & QuickTree 1.0. Superlinks is over there now (and built-in to Cacti 1.x).
Some Other Cacti tweaks, including strip-graphs, icons and snmp/netflow stuff.
(Let me know if you have UK DevOps or Network Ops opportunities, too!)
User avatar
TheWitness
Developer
Posts: 17047
Joined: Tue May 14, 2002 5:08 pm
Location: MI, USA
Contact:

Re: MyISAM, InnoDB and Foreign Keys

Post by TheWitness »

I now use memory, myisam, and innodb.. and both adhoc and builtin partitioning.

They each have their strong points.
True understanding begins only when we realize how little we truly understand...

Life is an adventure, let yours begin with Cacti!

Author of dozens of Cacti plugins and customization's. Advocate of LAMP, MariaDB, IBM Spectrum LSF and the world of batch. Creator of IBM Spectrum RTM, author of quite a bit of unpublished work and most of Cacti's bugs.
_________________
Official Cacti Documentation
GitHub Repository with Supported Plugins
Percona Device Packages (no support)
Interesting Device Packages


For those wondering, I'm still here, but lost in the shadows. Yearning for less bugs. Who want's a Cacti 1.3/2.0? Streams anyone?
User avatar
gandalf
Developer
Posts: 22383
Joined: Thu Dec 02, 2004 2:46 am
Location: Muenster, Germany
Contact:

Re: MyISAM, InnoDB and Foreign Keys

Post by gandalf »

Howie wrote:Also, the 'foreign keys' in the Cacti schema are pretty wacky - often back to the same table for things like templates.
Yep, ugly. Any ideas for a better table layout?
R.
denstock
Posts: 1
Joined: Fri Jul 15, 2011 1:17 am
Contact:

Re: MyISAM, InnoDB and Foreign Keys

Post by denstock »

Howie wrote:Also, the 'foreign keys' in the Cacti schema are pretty wacky - often back to the same table for things like templates.
seems to be the only way...
Post Reply

Who is online

Users browsing this forum: No registered users and 3 guests