Link to home
Start Free TrialLog in
Avatar of ArneIT
ArneIT

asked on

innodb versus myisam

Hello,

we have a dilemma about choosing table engine to use in our project. i have 2 questions to ask:

1) when using innodb instead of myisam or miysam instead of innodb is better? in our projects, select queries(included 3-4 joins) are most runners.
2) what hierarchial structure database design you should offer? traversal, recursive or something else?

thanks for your opinons.
ASKER CERTIFIED SOLUTION
Avatar of arnold
arnold
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ArneIT
ArneIT

ASKER

well, it includes several databases between 200 and 300, a database generally consists of about 75 tables and the most runned queries deals with 20000-30000 rows per one table and joins with multiple tables includes 5 to 100000 rows.
Unless you need to use transactions, you're better off with myISAM

There are less problems with rebooting the server (I once crashed an entire InnoDB database restarting mySQL using the panel at pair.com), as well as the speed issue. With InnoDB, everything is stored in a single file. with myISAM, there are individual files for each table.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ArneIT

ASKER

thanks for answers first of all;

our mysql version is 5.0.91. however, when i try to change innodb_buffer_pool_size and the other innodb values in mysql conf file, some errors raise like 'incorrect information in file' and mysql service does not start properly. i researched some ways to set innodb variables such as buffer pool size and recognized that changing these values and running mysql again healthily is a painful process(back up all the database, shut down mysql service, remove all log, ibdata and frm files, change conf, restart mysql service and import the backup. wow, isn't it hard to feel safe?). is there any EASY way to set the innodb variables(globally of course)?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ArneIT

ASKER

Thanks Wolfgang,

I've configured my /etc/my.cnf file by adding these lines below.

innodb_file_per_table
innodb_open_files  = 500
innodb_buffer_pool_size   = 6G
innodb_additional_mem_pool_size = 32M
innodb_log_file_size = 100M
innodb_log_buffer_size = 16M

But i am getting strange messages, Incorrect information in file..

110101 16:42:08 /usr/sbin/mysqld: Incorrect information in file: './*_*/*_labels.frm'
110101 16:42:08 /usr/sbin/mysqld: Incorrect information in file: './*_*/*_labels.frm'
A .frm file contains info about a particular table. Are you showing what you actually got,
or did you substitute "*" in parts? From what I see, there is an issue with your table
with a name like *_labels . Do you have table that corresponds to this name?
The table could be corrupt. Since InnoDB tables seldom get corrupted, I am assuming
that it is a MyISAM table that needs to be repaired (or better just drop it and recreate
it from scratch as an InnoDB table).

Regarding the setting of
  innodb_buffer_pool_size   = 6G

You should only do that if you have at least 8 gig of RAM on the server, it is dedicated
entirely to MySQL, and you are running 64-bit version of MySQL. Otherwise you have
set it too high.
Avatar of ArneIT

ASKER

well, the *_labels table is an innodb table actually. i think source of this issue is something else.
Avatar of ArneIT

ASKER

Thanks for your hints. Problem solved.