MySQL vs. InnoDB tables

[treed]

Hello gents, as I'm moving our system into production and working on unattended backups I notice that the nuBuilder system tables are in the MyISAM format. According to what I'm reading the database needs to be taken off line to get a consistent backup when using this table format. However the newer InnoDB format can be backed up live. I would like to do live backups, is there a reason the MyISAM format was chosen? And are there any know issues if I converted the system tables to InnoDB?

This is the site I'm using as my guide. https://www.labsrc.com/automating-maria ... -possible/

Thanks.

[apmuthu]

When we let nuBuilder create tables, they are in MyISAM engine by default. We are free to change field sizes and indexes and NULL to NOT NULL, INT as unsigned, unique keys, etc. using nudb. System tables are rarely changed during production use and hence should not matter as they will generally not have COMMIT,ROLLBACK transaction integrity issues except for debug and session tables. Change them to InnoDB or whatever engine type your application deems fit. Remember to change it in the nubuilder4.sql file too to persist across system updates and mod them again when you upgrade the codebase version.

[kev1n]

nuBuilder works also fine with InnoDB.

There are strengths and limitations to both storage engines. Intimate knowledge of the database structure and query practices is critical for selecting the best storage engine for your tables. MyISAM will out-perform InnoDB on large tables that require vastly more read activity versus write activity. MyISAM’s readabilities outshine InnoDB because locking the entire table is quicker than figuring out which rows are locked in the table.

Read more: https://www.liquidweb.com/kb/mysql-perf ... vs-innodb/

[stoicprogrammer]

I have read the post related to MyISAM vs Innodb issue.
In overall, it is said that sytem tables are rarely changed, so myISAM seems more suitable but nuBuilder works fine with both.

If I have correctly understood about the technical info on the web about store engines,
myISAM is very fast but is uses table level locking for write
Innodb is slower but it supports row level locking and it is more suitable for transactions.

Currently I am working on a project that Multiple users should be able to record in the same table simultaneously.
And each month around 10 000 transactions are expected to be recorded.

In this case is it better for me to use Innodb?
Or my case is nothing to do with the distinction of these engines?

[apmuthu]

Broadly, yes. InnoDB is transaction safe, ie., if you are using BEGIN...COMMIT...ROLLBACK/COMMIT constructs and CASCADES. MyISAM is good for lookup tables.

[treed]

That question now has me curious. Is transaction processing supportable through nuBuilder? If so how??? It's pretty import for invoicing and inventory management functions.

Getting back to the original subject, backups. From what I understand complete tables need to be locked for a consistent backup of MyISAM tables, which would require taking the system/database offline to do. Is this a correct understanding? For now that is not an issue, but is there an elegant way to accomplish that besides popping up an error message? At the usage level my system is seeing that is definitely not an issue right now, so this is more of a hypothetical question.

[apmuthu]

MyISAM has record locking and InnoDB has field locking.
Copying a table in use to a temporary table and backing that it is a possibility.