Transactions and MyISAM

Discussion of general topics related to the new version and its place in the world. Don't discuss new features, report bugs, ask for support, et cetera. Don't use this to spam for other boards or attack those boards!
Forum rules
Discussion of general topics related to the new release and its place in the world. Don't discuss new features, report bugs, ask for support, et cetera. Don't use this to spam for other boards or attack those boards!
Post Reply
User avatar
Prince of area51
Registered User
Posts: 133
Joined: Mon Jun 27, 2005 8:46 pm
Location: Manchester, UK
Contact:

Transactions and MyISAM

Post by Prince of area51 »

Hi,

Something I was trying to ask for some time but couldn't summon enough courage. Luckily today I'm feeling a lil bit looney so here goes. I thought MyISAM table engine in MySQL did not support transactions, yet phpBB 3.0 uses a lot of transaction commits and roll-backs. I usually run phpBB on mySQLi and while going through the code last year came across to the following statement:

Code: Select all

return @mysqli_autocommit($this->db_connect_id, false);
A quick look up on php.net revealed this:

Code: Select all

Note: This function doesn't work with non transactional table types (like MyISAM or ISAM). 
I double-checked the MySQL schema and found that no engines are specified. I'm just a little confused. No need to whip me but I certainly won't mind being dropped in a pot of honey :P

User avatar
DavidMJ
Registered User
Posts: 932
Joined: Thu Jun 16, 2005 1:14 am
Location: Great Neck, NY

Re: Transactions and MyISAM

Post by DavidMJ »

Right, all the transaction code becomes a NOP if the underlying engine does not support transactions. phpBB installs the schema using the default installed engine.
Freedom from fear

User avatar
Prince of area51
Registered User
Posts: 133
Joined: Mon Jun 27, 2005 8:46 pm
Location: Manchester, UK
Contact:

Re: Transactions and MyISAM

Post by Prince of area51 »

So, in other words, the recommended engine for phpBB 3 is InnoDB? Gosh, can't believe I didn't know that :o

And oh, thanks for replying, I'm chuffed to bits :lol:

User avatar
Highway of Life
Registered User
Posts: 1399
Joined: Tue Feb 08, 2005 10:18 pm
Location: I'd love to change the World, but they won't give me the Source Code
Contact:

Re: Transactions and MyISAM

Post by Highway of Life »

Transactions are only performed on *some* tables, so for those to work with the transactions, it’s best if they are InnoDB, but not all your tables would need to be InnoDB.
Image

User avatar
Nicholas the Italian
Registered User
Posts: 659
Joined: Mon Nov 20, 2006 11:19 pm
Location: 46°8' N, 12°13' E
Contact:

Re: Transactions and MyISAM

Post by Nicholas the Italian »

Just curious: performance-wise, how is InnoDB vs MyISAM?

User avatar
Prince of area51
Registered User
Posts: 133
Joined: Mon Jun 27, 2005 8:46 pm
Location: Manchester, UK
Contact:

Re: Transactions and MyISAM

Post by Prince of area51 »

Highway of Life wrote:Transactions are only performed on *some* tables, so for those to work with the transactions, it’s best if they are InnoDB, but not all your tables would need to be InnoDB.
I assuming that as so much work was put into making the code transaction-based, there must be some (good) reason behind that, I would see if I can compile a list of tables that should be InnoDB, just for people like me out there who like to keep everything perfect ;)
Nicholas the Italian wrote:Just curious: performance-wise, how is InnoDB vs MyISAM?
I found this benchmark really interesting, its InnoDB vs MyISAM vs Falcon. Hope this helps.

User avatar
Nicholas the Italian
Registered User
Posts: 659
Joined: Mon Nov 20, 2006 11:19 pm
Location: 46°8' N, 12°13' E
Contact:

Re: Transactions and MyISAM

Post by Nicholas the Italian »

Prince of area51 wrote:I found this benchmark really interesting, its InnoDB vs MyISAM vs Falcon. Hope this helps.
Indeed.
Kinda surprising InnoDB results practically always faster (or very close to) MyISAM, although they were all SELECTs and there was no data writing.
Just looks like InnoDB needs some more disk space (~15% in the benchmark), but that's ok.

I'm gonna look for some MySQL vs PostgreSQL benchmark at this point.

User avatar
Prince of area51
Registered User
Posts: 133
Joined: Mon Jun 27, 2005 8:46 pm
Location: Manchester, UK
Contact:

Re: Transactions and MyISAM

Post by Prince of area51 »

Yes, indeed and I think it would not be a bad idea to have all the tables use InnoDB engine.

Post Reply