Kieran Lal wrote:

Tell us more about your site. Number of nodes, users, traffic. What kind of hosting environment, etc?

InnoDB is safer, but consumes more resources.

Hmm... let's see... 7,152 nodes, 4,300 users, and if I read the monthly log correctly, it seems like it averages 15,300 hits/day with a peak of 18,000 hits/day on wednesdays. But I think that might include all the files that go into making a web page (CSS, logo, etc).

We have a shared hosting environment, of the type that doesn't over-sell disk space, so it is somewhat intermediate between a typical shared hosting and a dedicated host.

The servers run RHEL, PHP 5.2.8 and MySQL 5.1.30.

Uhm... I can't think of anything else I can say about the site. Does any of this help answer my question?

Daniel.

Kieran Lal wrote:

MySQL ISAM engine is lightweight, and not transactional, but has been know to corrupt, although I think it's much more stable in MySQL 5.1.

Yeah. What prompted my initial post is that today I had to repair the accesslog table. That got me thinking "maybe I should try InnoDB".

For high performance, and transaction integrity many people move their database engine to InnoDB.

InnoDB uses ~5x the disk space and ~3x more memory. It can be faster, and more reliable. But the down side is that if big tables like node, watchdog are InnoDB you can run into problems. Based on your site size, I don't forsee moving your entire site to InnoDB to be a problem.

Thanks. That's very informative. I didn't realize that the space difference was so big. I'm not worried about the disk space. I just hope that the memory doesn't become a problem. I'll write to my web host to ask how my site is doing memory-wise.

Here's a Drupal performance tuning checklist: http://tag1consulting.com/files/checklist.pdf

Ah, that'll be useful. Thanks.

If you see a slowdown, let me know and I'll help you out.

Thanks.

Daniel.

PostgreSQL is a way better than MySQL, about all topics. The only thing on which MySQL could be better, is maybe it's faster on read operations on small databases. But, in fact, PostgreSQL can handle correctly really BIG databases, with a really big amount of data, and is easier to cluster. PostgreSQL is more stable, secure, and a more efficient and flawless with stocked procedures and triggers.

If you have to choose between MyISAM and InnoDB, if I were you, I'd choose InnoDB, it's a lot more stable. MyISAM may be faster and eat less disk space, but you may have really bad surprises with your data integrity.

In the company I work for, we used to make a MySQL intensive use, the first monthes, we abandon MyISAM for a lot of obvious reasons. And right now, we are abandoning MySQL in flavor of PostgreSQL.

We still use MySQL for Drupal, because a lot of module developpers still can't write standard SQL, but, if all modules were PostgreSQL aware, we would not use MySQL at all.

This a my opinion, from my experience, I may be wrong.

Do the right choice, don't do IT :)

Le samedi 21 mars 2009 à 21:32 +0100, Daniel Carrera a écrit :

Ivan Sergio Borgonovo wrote:

...

It really depends on what you need to do. If you don't know it I'd go for MyISAM since most of the features you may be looking for in InnoDB are not really fully exploited by stock Drupal.

I just wrote another post that explains my hosting environment. I am interested in speed and reliability (isn't everybody?). My understanding is that:

1. InnoDB is more reliable.
2. InnoDB reads faster, if the primary keys and indexes are set right.
3. InnoDB writes slower.
4. InnoDB requires more disk space and more RAM.

Am I right so far? I'm not very familiar with this topic.

I'm not very concerned about disk space. I'm happy to make the DB 20% bigger if it'll improve read speed and reliability. But I don't fully understand the implications of InnoDB vs MyISAM, so I thought I'd ask here.

...

The reasons that may make you chose InnoDB may be the same to consider to add to the list PostgreSQL, but it really depends on what is your specific need.

I thought that the main benefit of PostgreSQL was advanced features (procedures, triggers, etc). I don't use any of those. I'm just looking for speed an reliability.

...

Summing it up:

Drupal doesn't have support for transaction or referential integrity but you may need it if you're developing your own modules. If you've several writes or high concurrency and data integrity is important PostgreSQL may be an interesting option.

If you've a lot of read, and you don't mind having to deal with some inconsistency , MyISAM and replication may be fine.

I think InnoDB is "in between" without shining for any aspect. If I were looking for transactions and data integrity I'd take the InnoDB route just if I already invested a lot in MySQL.

Thanks. This was very informative.

Daniel.

[ Drupal support list | http://lists.drupal.org/ ]

Le lundi 23 mars 2009 à 16:32 +0100, Daniel Carrera a écrit :

Pierre Rineau wrote:

...

PostgreSQL is a way better than MySQL, about all topics.

Data integrity too? I thought the main benefit of Postgres was features (triggers, stored procedures, etc).

It's true, PostgreSQL really has a lot of cool features. But it's also true that it provides a fully ACID (Atomicity, Consistency, Isolation, Durability) compliant database, which MySQL does not until the future 6 release. In PG database, every request, even a single simple one, is executed in a transactionnal context. This is also true with triggers and procedures.

I quote "PostgreSQL is fully ACID-compliant, while MySQL's InnoDB storage engine provides engine-level ACID-compliance" (see http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL#ACID_Compliance).

Even with clustering, PostgreSQL seems to be a lot more powerfull, since you can use cross databases constraints between tables from more than one PG instance. You can also do table partitioning, and a lot of other cool features to help you when you start having *A LOT* of data :)

...

The only thing on which MySQL could be better, is maybe it's faster on read operations on small databases.

That shouldn't be underestimated. It means that MySQL is the right choice for most websites.

...

If you have to choose between MyISAM and InnoDB, if I were you, I'd choose InnoDB, it's a lot more stable. MyISAM may be faster and eat less disk space, but you may have really bad surprises with your data integrity.

Thanks. I'm leaning toward switching to InnoDB. I'll certainly move my own work to InnoDB. Our website has both a Drupal site and a custom web app that I wrote. The custom app is more critical than Drupal, so it is a better candidate for InnoDB.

...

In the company I work for, we used to make a MySQL intensive use, the first monthes, we abandon MyISAM for a lot of obvious reasons.

Actually, I would love to know what those reasons are. Is it just data integrity?

Data integrity is a sufficient reason. But, we also had a lot of problems with MySQL query cache bugs (with concurent access to the database, some data were not refreshed, but was written in database).

I think now, most of thoses bugs have been resolved. But still, we sometime continue to find some strange behaviors when you write your queries in the wrong order (performance problems most of them), things that happens less often with recent PostgreSQL releases (it remains that this is the developer responsability to write correct queries).

Our web host doesn't offer Postgres, so I can't seriously consider that option, but I would be happy to hear more about the pros and cons, just out of personal curiosity. You seem to suggest that MySQL has faster reads for small databases. How small is "small"? My boss likes speed. :)

You might want to search google for this. In fact, most of posts you'll read will take position for one of those, be aware when reading them. But, if you read a lot of them, you could do your own opinion.

I still think that testing both for a long time is the better way to do your choice.

Cheers, Daniel. -- [ Drupal support list | http://lists.drupal.org/ ]

Regards, Pierre.

On Mon, 2009-03-23 at 18:52 +0100, Ivan Sergio Borgonovo wrote:

On Mon, 23 Mar 2009 16:32:34 +0100 Daniel Carrera daniel.carrera@theingots.org wrote:

...

Actually, I would love to know what those reasons are. Is it just data integrity?

Our web host doesn't offer Postgres, so I can't seriously consider that option, but I would be happy to hear more about the pros and cons, just out of personal curiosity. You seem to suggest that MySQL has faster reads for small databases. How small is "small"? My boss likes speed. :)

MyISAM, InnoDB and PostgreSQL perform differently on different situation. Ease of development and safety of data may have an indirect impact on speed as well.

For some tasks MyISAM is reliable enough and "failure" won't impact the "average" speed. Once you add replication it may become your best solution.

I think that outside that scenario the only 2 good reason to chose InnoDB over PostgreSQL are:

• you invested a lot in MySQL (your staff is made of MySQL black belt)
• you want to lower as much as possible the cost of maintaining drupal sites and still data integrity and frequent writes aren't your first priority but you're starting to feel the pain of MyISAM. In my view most of the time this is going to be a "diplomatic" choice... still not a "we seriously would like a bit more data integrity/concurrency but we can't afford PostgreSQL" choice. Once you're in the territory of "a bit more data integrity and concurrency" you've already passed the threshold that will make maintaining MySQL cheaper.

So most of the times the real reason to keep using InnoDB vs. PostgreSQL is your knowledge of the tool.

+1

In most if not all the situations where you may chose InnoDB over MyISAM just on technical merits, PostgreSQL would be a better candidate... since there are other constraint that may influence your choice you may still prefer InnoDB.

+1

And... as Michael Prasuhn wrote most of the sites don't have to deal with the problems InnoDB try to solve and when they run into them they have built up so much (too much?) MySQL knowledge and invested so much in MySQL tuning/coding that their best move is to switch to InnoDB.

Le lundi 23 mars 2009 à 09:24 -0700, Michael Prasuhn a écrit :

On Mar 21, 2009, at 1:32 PM, Daniel Carrera wrote:

...

I just wrote another post that explains my hosting environment. I am interested in speed and reliability (isn't everybody?). My understanding is that:

1. InnoDB is more reliable.
2. InnoDB reads faster, if the primary keys and indexes are set right.
3. InnoDB writes slower.
4. InnoDB requires more disk space and more RAM.

A lot of confusion here, from others, not just Daniel. Overall the biggest benefit is that InnoDB supports row-level locking versus table- locking on writes. For high traffic sites this can make some obvious benefits for tables like access logs, sessions, users, comments, node_comment_statistics and the like.

The downside is that for reading large amounts of simple data, InnoDB is slower than MyISAM. In fact MyISAM is probably one of the fastest engines out there in terms of reading data.

I don't think speed is really the problem here. There are a lot of wide architectures that uses PostgreSQL or other DBMS's under very high load. There are now a lot of solution to support high loads, and most of them are based on redondancy and large amount of memory. Use a fast DBMS, ok, but use a REAL DBMS, they are, in fact, fast enough.

The fact MyISAM reads fast, is the concequencies of its lack of security and features. Don't kill the kitten, don't use MyISAM.

Indeed, keeping such engine alive is an heresy, because it's not ACID compliant, even if Drupal does not use transactions or referencial integrity, this is a very bad habit to continue to use it.

In short, unless you know you have large waits doing common tasks such as insert comments when the load on the site is high, or you have a way to examine how many connections are waiting on table locks before completion, you are probably best off sticking with MyISAM.

Also, while InnoDB is widely available these days, you may still run into hosts that don't support it, but do support MyISAM.

Your conclusion makes sens, some hosters continues to use outdated technos such as MyISAM. But in case you are starting a new architecture of your own somewhere, I really disencourage you to use MyISAM.

-Mike __________________ Michael Prasuhn 503.488.5433 office 714.356.0168 cell 503.661.7574 home mike@mikeyp.net http://mikeyp.net

-- [ Drupal support list | http://lists.drupal.org/ ]

On Mar 23, 2009, at 10:30 AM, Daniel Carrera wrote:

Could you confirm that row-level locking means that you can do multiple INSERTs at the same time?

Suppose that you are going to do 50 INSERTs in one transaction (my non-Drupal site does this at one place). Can two users do the 50- INSERT operation at the same time? (the application is a markbook/ gradebook - the inserts are the marks/grades for 50 students).

Well there are some gotcha conditions, if you have a WHERE clause in your update, it must be using an index, otherwise it has to do a table scan, and locks the whole table. Also, any inserts using an auto- increment column will lock the entire table to prevent duplicate values.

I thought that InnoDB was supposed to be faster for reading. At least, for reading rows that are related by index (e.g. users 1-20 assuming that "user id" is an index).

Please see http://2bits.com/articles/mysql-innodb-performance-gains-as-well-as-some-pit... for some examples.

-Mike

__________________ Michael Prasuhn 503.488.5433 office 714.356.0168 cell 503.661.7574 home mike@mikeyp.net http://mikeyp.net

Quoting Daniel Carrera daniel.carrera@theingots.org:

Michael Prasuhn wrote:

...

A lot of confusion here, from others, not just Daniel. Overall the biggest benefit is that InnoDB supports row-level locking versus table-locking on writes.

Could you confirm that row-level locking means that you can do multiple INSERTs at the same time?

Suppose that you are going to do 50 INSERTs in one transaction (my non-Drupal site does this at one place). Can two users do the 50-INSERT operation at the same time? (the application is a markbook/gradebook - the inserts are the marks/grades for 50 students).

If the operation is transactionalized and the users are updating some of the same rows the one who updates last looses because the engine is smart enough to know that the second update cannot be allowed to happen because the signature of the row has changed; the user can then be warned that the data changed prior to his update. Or, in some cases, the rows can be locked up front so the second user waits on the first user to release the data rows before he can even view the data. In the non-transactinalized case the second update wins and the original update is lost without warning; we see this in the issue queue quite often with people changing the status column from the changed value to previous value incidentally.

-- Earnie http://r-feed.com Make a Drupal difference and review core patches.

-- http://for-my-kids.com/ -- http://www.4offer.biz/

On Sun, 22 Mar 2009 11:44:43 -0400 Earnie Boyd earnie@users.sourceforge.net wrote:

Quoting Ivan Sergio Borgonovo mail@webthatworks.it:

...

It really depends on what you need to do. If you don't know it I'd go for MyISAM since most of the features you may be looking for in InnoDB are not really fully exploited by stock Drupal.

Well, not yet anyway. http://drupal.org/node/301362

Supporting MyISAM, sqlite and advanced (I'd say standard) features of serious RDBMS doesn't look a viable way to develop drupal (unless the DB abstraction layer is outsourced, but this would(?) be more suited for another flame ;) )

I consider normal to add "ON DELETE CASCADE" actions, but to replicate it transparently in PHP it is a hell of a work. And that's just one example.

My point is: - if you're not using "advanced" features and you're not constrained by concurrent writes MyISAM is good enough. I'd say MyISAM may be even better suited to Drupal than InnoDB. None of the two engines is famous for data integrity after all. - if you're using advanced features, you're constrained by concurrent writes, you can't afford data loss and you didn't invest too much in MySQL there are better engines to look at.

Choosing InnoDB would be just a matter of how much you invested in MySQL.

I don't think that once you're faced with these problems your hosting offer is a real concern.

On Mon, 23 Mar 2009 12:45:28 +0100 Daniel Carrera daniel.carrera@theingots.org wrote:

Ivan Sergio Borgonovo wrote:

...

...

Well, not yet anyway. http://drupal.org/node/301362

...

...

My point is:

• if you're not using "advanced" features and you're not constrained by concurrent writes MyISAM is good enough. I'd say MyISAM may be even better suited to Drupal than InnoDB. None of the two engines is famous for data integrity after all.

I thought the whole point of InnoDB *was* data integrity.

I'm really not a MySQL black belt so I've commented on the topic more than reasonable... but InnoDB should also have better performances for concurrent write. BTW I just learned you can't use geo-indexes on InnoDB...

Of course data integrity is not just about content of tables.

...

• if you're using advanced features, you're constrained by concurrent writes, you can't afford data loss and you didn't invest too much in MySQL there are better engines to look at.

Out of curiosity, what would you suggest? No, I'm not looking to leave MySQL but I always enjoy learning a little more about databases.

PostgreSQL... but really it depends on your needs. Drupal PostgreSQL support is not the same as MySQL support, especially in contrib but I won't get scared about it. Still you need to know what you're doing. Suggesting switching to PostgreSQL may have the same result of suggesting to switch to Linux to a Windows user. A lot of people cry in despair and will curse you... others will never come back... for many reasons...

On Mon, 23 Mar 2009 09:34:57 -0400 Earnie Boyd earnie@users.sourceforge.net wrote:

...

Of course data integrity is not just about content of tables.

This is why the issue I pointed you to is so important.

...

...

...

• if you're using advanced features, you're constrained by concurrent writes, you can't afford data loss and you didn't invest too much in MySQL there are better engines to look at.

Out of curiosity, what would you suggest? No, I'm not looking to leave MySQL but I always enjoy learning a little more about databases.

PostgreSQL... but really it depends on your needs.

If only ANSI syntax is used then it shouldn't really matter which transactional DB is used as long is the engine supports ANSI transactional syntax. A transactional DB would allow for less PHP code to be needed and a benefit would be the amount of disk i/o would be lessened.

yeah... but is putting the bar much higher in terms of requirements... that won't make InnoDB just default but required. And it is not just a matter of transactions of course. Once you're in that league you may exploit many other features...

But still if you want to keep supporting MyISAM and you want the DB to take care of relational integrity and such if possible... you're going to do twice the work if you still want to support MyISAM and sqlite.

Maybe in the future there won't be any good reason to have half-RDBMS around so support for half-RDBMS could be dropped... but right now MyISAM and sqlite have their use case, and somehow dropping support for 2 out of 4 supported DB is not going to make DB abstraction any better right now.

and you would not be allowed to delete a parent without also deleting children.

On Mon, Mar 23, 2009 at 11:24 AM, Victor Kane victorkane@gmail.com wrote:

Data integrity is understood as respecting the relationships between tables. So, if there are several tables in a one to many relationship with the node table, say, for a CCK based content type, if the node is deleted, then the child records would also be deleted.

On Mon, Mar 23, 2009 at 11:00 AM, Ivan Sergio Borgonovo < mail@webthatworks.it> wrote:

...

On Mon, 23 Mar 2009 09:34:57 -0400 Earnie Boyd earnie@users.sourceforge.net wrote:

...

...

Of course data integrity is not just about content of tables.

This is why the issue I pointed you to is so important.

...

...

...

• if you're using advanced features, you're constrained by concurrent writes, you can't afford data loss and you didn't invest too much in MySQL there are better engines to look at.

Out of curiosity, what would you suggest? No, I'm not looking to leave MySQL but I always enjoy learning a little more about databases.

PostgreSQL... but really it depends on your needs.

If only ANSI syntax is used then it shouldn't really matter which transactional DB is used as long is the engine supports ANSI transactional syntax. A transactional DB would allow for less PHP code to be needed and a benefit would be the amount of disk i/o would be lessened.

yeah... but is putting the bar much higher in terms of requirements... that won't make InnoDB just default but required. And it is not just a matter of transactions of course. Once you're in that league you may exploit many other features...

But still if you want to keep supporting MyISAM and you want the DB to take care of relational integrity and such if possible... you're going to do twice the work if you still want to support MyISAM and sqlite.

Maybe in the future there won't be any good reason to have half-RDBMS around so support for half-RDBMS could be dropped... but right now MyISAM and sqlite have their use case, and somehow dropping support for 2 out of 4 supported DB is not going to make DB abstraction any better right now. -- [ Drupal support list | http://lists.drupal.org/ ]

Quoting Ivan Sergio Borgonovo mail@webthatworks.it:

And unless support for MyISAM and sqlite is going to be dropped, and I don't think right now is a good choice, using more "advanced" DB features in core would be a double pain.

I don't know about sqlite but MyISAM would continue to work even if we code for the transactional update. It would just lock tables instead of rows and the cascading would not occur. If warned it would be up to the user to make a determination to continue with MyISAM or move to a new provider.

-- Earnie http://r-feed.com Make a Drupal difference and review core patches.

-- http://for-my-kids.com/ -- http://www.4offer.biz/