What is the current view on MYISAM vs INNODB when using servoy and MYSQL?
Whilst i see that many commentaries say that INNODB is slow, I am interested in whether the ability to lock individual rows in INNODB is preferable when delivering a SAAS based solution.
If you have a million rows of data, but 100 users, will INNODB’s ability to lock ROWs provide superior performance, as it will instantly leave free all of the rows of the other users, which MYISAM is locking.
A few of my more complex searching queries lock the main table of my solution for 5 seconds and it concerns me that this may be preventing 20 users from writing data.
I havent seen too much debate specifically to servoy about whether INNODB is preferable when using Saas and Servoy?
no compression and therefore both RAM and disk requirements are larger than MyISAM
When operating in fully ACID-compliant modes, InnoDB must do a flush to disk at least once per transaction, though it will combine flushes for inserts from multiple connections. For typical hard drives or arrays, this will impose a limit of about 200 update transactions per second. Higher transaction rates require disk controllers with write caching and battery backup to maintain transactional integrity. InnoDB also offers several modes which reduce this effect, naturally leading to a loss of transactional integrity.
So in short. InnoDB is the more mature storage engine of the 2.
If you want to use database transactions you need it.
The MVCC support in InnoDB should also mean your updates/inserts won’t block any SELECTs.
If you think your data is of ANY value you should use InnoDB because of it’s ACID properties (although it’s not 100% as you could read in the footnote). MyISAM is known to be crash-prone and you WILL loose your data when that happens. (MySQL even comes with a repair function for this storage type, that should tell you something…)
The tradeoff is that it doesn’t support full text searches and it uses more space (RAM and disk) and it’s a little slower (in theory, in practice it might be even faster!).
As for data integrity here is a tip:
Enable strict SQL mode. MySQL, by default, will mangle your data instead of giving an error back when your datatype can’t handle the data you want to insert/update.
So you try to insert a 60 character long string and your column can only handle 50 it will chop of the last 10 characters to make it fit. No errors, no notices.
DateTime columns even allow invalid dates like 0000-00-00 00:00:00 but also dates like the 30th of february.
But it’s worse with numbers. Lets say you have the important (to you) number 10,000 and your column is a NUMBER(5,2) then MySQL will not store it as 10,000 but as 999.99 ( )
By enabling Strict SQL these constraints will be enforced and you will get an error (as it should be) when you try to insert data that doesn’t fit in the column.
I take it that if my solution works in MYISAM but doesnt use transactions, and doesnt do any silly bits of data input, that if i switch then it will still work in INNODB and that i can slowly go through my code and build transactions in to it?
From what you say, and what i suspected is that where data is segmented by in my case ‘expert_id’, i should actually get better performance as whole table locks will need be being put on all my users data as INNODB will somehow quickly find the records pertinent to the index with expert_id and then not lock the whole table.
I recently found that pulling in a foundset by query into servoy actually did a full table scan for each record it found, thus doing 20 individual table scans to bring in 20 records even though it should have already found those records in the query. That was taking 30 seconds, and in MYISAM would be locking all users out. I suspect in INNODB, it would only be locking the individual rows.
No SELECT should lock anything but I know MyISAM does.
InnoDB uses MVCC and if implemented correctly even an UPDATE/INSERT will never lock any SELECTs either.
PostgreSQL, Informix (which is based on PostgreSQL), Berkeley DB, FireBird and Oracle uses some form of MVCC too (in fact MVCC was pioneered by the Postgres project and was later implemented into Oracle 8, although slightly changed)
In short, go with InnoDB if you want to use MySQL.