Hi, MySQL tables of OpenSer created by the script are "MyISAM". Is there any reason for that instead of using InnoDB?
As I know:
MyISAM: - Is faster reading and writting (except writting various entries to same table since it blocks the entire table). - No transactions or rollbacks. - Small disc space requeriments.
InnoDB: - Transactions, rollbacks, restrictions in foreing keys. - Faster writting various entries in same table (entry lock instead of table lock). - More disc space and RAM requeriments.
Yes, it can be obvious that for OpenSer the engine MyISAM can be a good choice but I would like to know if there is more data for this choice.
Regards.
On 11/13/07 19:13, Iñaki Baz Castillo wrote:
Hi, MySQL tables of OpenSer created by the script are "MyISAM". Is there any reason for that instead of using InnoDB?
As I know:
MyISAM:
- Is faster reading and writting (except writting various entries to same
table since it blocks the entire table).
- No transactions or rollbacks.
- Small disc space requeriments.
InnoDB:
- Transactions, rollbacks, restrictions in foreing keys.
- Faster writting various entries in same table (entry lock instead of table
lock).
- More disc space and RAM requeriments.
Yes, it can be obvious that for OpenSer the engine MyISAM can be a good choice but I would like to know if there is more data for this choice.
...well, when the mysql modules was initially written (2001-2002), myisam was the default db engine; innodb was not available, it was introduced later. so it keeps being the same, as there was no need to change.
Daniel
Regards.
On Tuesday 13 November 2007, Iñaki Baz Castillo wrote:
Hi, MySQL tables of OpenSer created by the script are "MyISAM". Is there any reason for that instead of using InnoDB?
As I know:
MyISAM:
- Is faster reading and writting (except writting various entries to same
table since it blocks the entire table).
- No transactions or rollbacks.
- Small disc space requeriments.
InnoDB:
- Transactions, rollbacks, restrictions in foreing keys.
- Faster writting various entries in same table (entry lock instead of
table lock).
- More disc space and RAM requeriments.
Yes, it can be obvious that for OpenSer the engine MyISAM can be a good choice but I would like to know if there is more data for this choice.
Hi Iñaki Baz,
just a short note:
This can be easily changed (for 1.3) in the db/schema/entities.xml file, for 1.2 there should exists a parameter in the script.
Cheers,
Henning
El Miércoles, 14 de Noviembre de 2007, Henning Westerholt escribió:
Yes, it can be obvious that for OpenSer the engine MyISAM can be a good choice but I would like to know if there is more data for this choice.
Hi Iñaki Baz,
just a short note:
This can be easily changed (for 1.3) in the db/schema/entities.xml file, for 1.2 there should exists a parameter in the script.
Thanks, it's nice to know that. Anyway what I'd like in fact is to hear any argument to use MyISAM or InnoDB, any technical reason.
Thanks a lot.
Iñaki Baz Castillo wrote:
El Miércoles, 14 de Noviembre de 2007, Henning Westerholt escribió:
Yes, it can be obvious that for OpenSer the engine MyISAM can be a good choice but I would like to know if there is more data for this choice.
Hi Iñaki Baz,
just a short note:
This can be easily changed (for 1.3) in the db/schema/entities.xml file, for 1.2 there should exists a parameter in the script.
Thanks, it's nice to know that. Anyway what I'd like in fact is to hear any argument to use MyISAM or InnoDB, any technical reason.
There is a good article about this topic on http://dev.mysql.com/tech-resources/articles/storage-engine/part_1.html.
The basic conclusion: NDB (mysql cluster) is the fastest engine because it is a main-memory engine and has advanced locking like InnoDB.
I'd guess that InnoDB might perform better than MyISAM for an openser under high load because of its advanced locking and the fact that each openser process uses its own mysql connection.
/Christian
Christian Schlatter schrieb:
Iñaki Baz Castillo wrote:
El Miércoles, 14 de Noviembre de 2007, Henning Westerholt escribió:
Yes, it can be obvious that for OpenSer the engine MyISAM can be a good choice but I would like to know if there is more data for this choice.
Hi Iñaki Baz,
just a short note:
This can be easily changed (for 1.3) in the db/schema/entities.xml file, for 1.2 there should exists a parameter in the script.
Thanks, it's nice to know that. Anyway what I'd like in fact is to hear any argument to use MyISAM or InnoDB, any technical reason.
There is a good article about this topic on http://dev.mysql.com/tech-resources/articles/storage-engine/part_1.html.
The basic conclusion: NDB (mysql cluster) is the fastest engine because it is a main-memory engine and has advanced locking like InnoDB.
So if the cluster fails, all the data is gone?
regards klaus
I'd guess that InnoDB might perform better than MyISAM for an openser under high load because of its advanced locking and the fact that each openser process uses its own mysql connection.
/Christian
Users mailing list Users@lists.openser.org http://lists.openser.org/cgi-bin/mailman/listinfo/users
El Thursday 15 November 2007 08:13:53 Klaus Darilion escribió:
The basic conclusion: NDB (mysql cluster) is the fastest engine because it is a main-memory engine and has advanced locking like InnoDB.
So if the cluster fails, all the data is gone?
No experience with this, but I assume data is saved to disk periodically, isn't?
Not exactly, transaction information is stored in disk to restore it in case you stop a data node and restart it again.
The idea is that there are several data nodes storing replicated data and some of them can fail as long as there are nodes available (redundancy can be specified in the cluster parameters (noOfReplicas,...)). When the failed node come online again it retrieves the data from the surviving nodes so the data survives to node failures.
You can start a cluster backup from the management node to effectively write the cluster information on disk so you can afterwards recover the cluster.
From mysql 5.1 you can use ndb engine and configure it to store the data on
the disk instead of storing it on memory. However keys are still required to be stored on memory.
Summarising, Samuel.
2007/11/15, Iñaki Baz Castillo ibc@in.ilimit.es:
El Thursday 15 November 2007 08:13:53 Klaus Darilion escribió:
The basic conclusion: NDB (mysql cluster) is the fastest engine
because
it is a main-memory engine and has advanced locking like InnoDB.
So if the cluster fails, all the data is gone?
No experience with this, but I assume data is saved to disk periodically, isn't?
-- Iñaki Baz Castillo ibc@in.ilimit.es
Users mailing list Users@lists.openser.org http://lists.openser.org/cgi-bin/mailman/listinfo/users
Regarding MyISAM vs.InnoDB..... I had much higher CPU load and call delay stablishment using InnoDB and I had to switch to MyISAM. Not to mention 3x disk space, which is nowadays not "really" important.
BUT this highly depends on the implementation of your application. If they are not able to react upon transaction failure (innoDB) then you do not gain much using it and you should go for MyISAM.
I would recommend using MyISAM for fast updates (accounting,location,...) and innoDB for more "static" data but i'm just a DB newby and would really appreciate someone with knowledge to correct me!!!!!!
just test it, samuel.
El Thursday 15 November 2007 12:56:24 samuel escribió:
Regarding MyISAM vs.InnoDB..... I had much higher CPU load and call delay stablishment using InnoDB and I had to switch to MyISAM. Not to mention 3x disk space, which is nowadays not "really" important.
BUT this highly depends on the implementation of your application. If they are not able to react upon transaction failure (innoDB) then you do not gain much using it and you should go for MyISAM.
I would recommend using MyISAM for fast updates (accounting,location,...) and innoDB for more "static" data but i'm just a DB newby and would really appreciate someone with knowledge to correct me!!!!!!
Thanks for your comments, they are very useful ;)
On Nov 13, 2007 12:13 PM, Iñaki Baz Castillo ibc@in.ilimit.es wrote:
Hi, MySQL tables of OpenSer created by the script are "MyISAM". Is there any reason for that instead of using InnoDB?
As I know:
MyISAM:
- Is faster reading and writting (except writting various entries to same
table since it blocks the entire table).
- No transactions or rollbacks.
- Small disc space requeriments.
InnoDB:
- Transactions, rollbacks, restrictions in foreing keys.
- Faster writting various entries in same table (entry lock instead of
table lock).
- More disc space and RAM requeriments.
Yes, it can be obvious that for OpenSer the engine MyISAM can be a good choice but I would like to know if there is more data for this choice.
Regards.
-- Iñaki Baz Castillo ibc@in.ilimit.es
Users mailing list Users@lists.openser.org http://lists.openser.org/cgi-bin/mailman/listinfo/users
Depending on how much data you have, you should also look at mysql Cluster. Since it is an in memory database, it will be super fast.
I have used SER with Mysql Cluster and it works well.
Joe
Joseph Bajin napsal(a):
On Nov 13, 2007 12:13 PM, Iñaki Baz Castillo <ibc@in.ilimit.es mailto:ibc@in.ilimit.es> wrote:
Hi, MySQL tables of OpenSer created by the script are "MyISAM". Is there any reason for that instead of using InnoDB? As I know: MyISAM: - Is faster reading and writting (except writting various entries to same table since it blocks the entire table). - No transactions or rollbacks. - Small disc space requeriments. InnoDB: - Transactions, rollbacks, restrictions in foreing keys. - Faster writting various entries in same table (entry lock instead of table lock). - More disc space and RAM requeriments. Yes, it can be obvious that for OpenSer the engine MyISAM can be a good choice but I would like to know if there is more data for this choice. Regards. -- Iñaki Baz Castillo ibc@in.ilimit.es <mailto:ibc@in.ilimit.es> _______________________________________________ Users mailing list Users@lists.openser.org <mailto:Users@lists.openser.org> http://lists.openser.org/cgi-bin/mailman/listinfo/usersDepending on how much data you have, you should also look at mysql Cluster. Since it is an in memory database, it will be super fast.
I have used SER with Mysql Cluster and it works well.
Joe
It is very interesting for me :-) Could you tell me, please, how much users do you have and - it is what I need to know - how much SELECTs and INSERTs per second your cluster can serve and what type of SW (distribution, MySQL version) and HW (number of nodes, type of CPUs per node) you are using?
I'm facing big challenge - need to maintain about 3.000-5.000 calls per second with a lot of DB processing (say about 7 SELECT, 2 INSERTs and 3 UPDATEs per call).
Thanks a lot!
kokoska.rokoska
Users mailing list Users@lists.openser.org http://lists.openser.org/cgi-bin/mailman/listinfo/users
-
Christian Schlatter -
Daniel-Constantin Mierla -
Henning Westerholt -
Iñaki Baz Castillo -
Iñaki Baz Castillo -
Joseph Bajin -
Klaus Darilion -
kokoska rokoska -
samuel