Drupal and PostgreSQL - performance issues?

Started by Mikkel Høghover 17 years ago136 messagesgeneral
Jump to latest
#1Mikkel Høgh
mikkel@hoegh.org

Hi there,

I've been toying with using PostgreSQL for some of my Drupal sites for
some time, and after his session at OpenSourceDays in Copenhagen last
weekend, Magnus Hagander told me that there a quite a few in the
PostgreSQL community using Drupal.

I have been testing it a bit performance-wise, and the numbers are
worrying. In my test, MySQL (using InnoDB) had a 40% lead in
performance, but I'm unsure whether this is indicative for PostgreSQL
performance in general or perhaps a misconfiguration on my part.

In any case, if anyone has any tips, input, etc. on how best to
configure PostgreSQL for Drupal, or can find a way to poke holes in my
analysis, I would love to hear your insights :)

The performance test results can be found on my blog: http://mikkel.hoegh.org/blog/2008/drupal_database_performance_mysql_and_postgresql_compared
--
Kind regards,

Mikkel Høgh <mikkel@hoegh.org>

Attachments:

smime.p7sapplication/pkcs7-signature; name=smime.p7sDownload
#2John DeSoi
desoi@pgedit.com
In reply to: Mikkel Høgh (#1)
Re: Drupal and PostgreSQL - performance issues?

On Oct 12, 2008, at 11:57 PM, Mikkel Høgh wrote:

In any case, if anyone has any tips, input, etc. on how best to
configure PostgreSQL for Drupal, or can find a way to poke holes in
my analysis, I would love to hear your insights :)

I just came across this article about moving Drupal from MySQL to
PostgreSQL because of MyISAM data corruption and InnoDB was too slow.

http://groups.drupal.org/node/15793

John DeSoi, Ph.D.

#3Scott Marlowe
scott.marlowe@gmail.com
In reply to: Mikkel Høgh (#1)
Re: [PERFORM] Drupal and PostgreSQL - performance issues?

On Sun, Oct 12, 2008 at 9:57 PM, Mikkel Høgh <mikkel@hoegh.org> wrote:

Hi there,

I've been toying with using PostgreSQL for some of my Drupal sites for some
time, and after his session at OpenSourceDays in Copenhagen last weekend,
Magnus Hagander told me that there a quite a few in the PostgreSQL community
using Drupal.

I have been testing it a bit performance-wise, and the numbers are worrying.
In my test, MySQL (using InnoDB) had a 40% lead in performance, but I'm
unsure whether this is indicative for PostgreSQL performance in general or
perhaps a misconfiguration on my part.

The test you're running is far too simple to tell you which database
will actually be faster in real world usage. No updates, no inserts,
no interesting or complex work goes into just delivering the front
page over and over. I suggest you invest some time learning how to
drive a real load testing tool like jmeter and build realistic test
cases (with insert / update / delete as well as selects) and then see
how the databases perform with 1, 2, 5, 10, 50, 100 consecutive
threads running at once.

Without a realistic test scenario and with no connection pooling and
with no performance tuning, I don't think you should make any
decisions right now about which is faster. It may well be that in a
more realistic testing that mysql keeps up through 5 or 10 client
connections then collapses at 40 or 50, while pgsql keeps climbing in
performance. This is the performance curve I'm used to seeing from
both dbs under heavy load.

In simple terms, you're kicking the tires and making a decision based on that.

#4Stephen Frost
sfrost@snowman.net
In reply to: Mikkel Høgh (#1)
Re: [PERFORM] Drupal and PostgreSQL - performance issues?

* Mikkel Høgh (mikkel@hoegh.org) wrote:

I have been testing it a bit performance-wise, and the numbers are
worrying. In my test, MySQL (using InnoDB) had a 40% lead in
performance, but I'm unsure whether this is indicative for PostgreSQL
performance in general or perhaps a misconfiguration on my part.

The comments left on your blog would probably be a good first step, if
you're not doing them already.. Connection pooling could definitely
help if you're not already doing it. Drupal's MySQL-isms don't help
things either, of course.

Also, you don't post anything about the PostgreSQL config, nor the
hardware it's running on. The default PostgreSQL config usually isn't
appropriate for decent hardware and that could be a contributing factor
here. It would also be useful to make sure you've analyze'd your tables
and didn't just do a fresh load w/o any statistics having been gathered.

We run Drupal on PostgreSQL for an internal site and it works reasonably
well. We havn't had any performance problems but it's not a terribly
large site either. The issues we've had tend to come from PostgreSQL's
somewhat less-than-supported status with Drupal.

I've been meaning to look into Drupal's PG support to see about
improving it. Perhaps this winter I'll get a chance to.

Thanks,

Stephen

#5Uwe C. Schroeder
uwe@oss4u.com
In reply to: Mikkel Høgh (#1)
Re: Drupal and PostgreSQL - performance issues?

I have been testing it a bit performance-wise, and the numbers are
worrying. In my test, MySQL (using InnoDB) had a 40% lead in
performance, but I'm unsure whether this is indicative for PostgreSQL
performance in general or perhaps a misconfiguration on my part.

In my experience the "numbers are always worrying" in a read-only environment.

I've used MySQL, but found it rather disturbing when it comes to integrity.
MySQL has just some things I can't live with (i.e. silently ignoring
overflowing charater types etc).
That aside, MySQL IS fast when it comes to read operations. That's probably
because it omits a lot of integrity checks postgres and other standard
compliant databases do.
I'm running a turbogears website with a couple million pages on postgresql and
I don't have any problems, so I guess postgres can be configured to service
Drupal just as well. Check your indexes and your work memory
(postgresql.conf). You want to have the indexes correct and in my experiene
the work memory setting is rather important. You want to have enough work
memory for sorted queries to fit the resultset into memory - as always disk
access is expensive, so I avoid that by having 2GB memory exclusively for
postgres - which allows me to do quite expensive sorts in memory, thus
cutting execution time down to a couple milliseconds.
Oh, and never forget: explain analyze your queries. That will show you whether
your indexes are correct and useful, as well as how things are handled. Once
you learn how to read the output of that, you'll be surprised what little
change to a query suddenly gives you a performance boost of 500% or more.
I had queries take 30 seconds cut down to 80 milliseconds just by setting
indexes straight.

Keep in mind: postgres will take good care of your data (the most important
asset in todays economy). I run all my customers on postgres and did so ever
since postgres became postgresql (the times way back then when postgres had
it's own query language instead of SQL). With a little care I've never seen
postgresql dump or corrupt my data - not a "pull the plug" scenario and not a
dumb user SQL injection scenario. I was always able to recover 100% of data
(but I always used decent hardware, which IMHO makes a big difference).

I've toyed with MySQL (not as deep as postgresql I must admit) and it
dumped/corruped my data on more than one occasion. Sure, it can be my
proficiency level with MySQL, but personally I doubt that. Postgresql is just
rock solid no matter what.

Uwe

#6Mikkel Høgh
mikkel@hoegh.org
In reply to: Stephen Frost (#4)
Re: [PERFORM] Drupal and PostgreSQL - performance issues?

Alright, my benchmarks might have been a bit naïve.
When it comes to hardware, my webserver is a SunFire X2100 with an
Opteron 1210 Dual Core and 4 GB DDR2 RAM, running 64-bit Ubuntu Linux
Server 8.04 LTS.

When it comes to the resource usage section of my postgresql.conf, the
only thing that are not commented out are:
shared_buffers = 24MB
max_fsm_pages = 153600

I freely admit that the reason I haven't messed with these values is
that I have next to no clue what the different things do and how they
affect performance, so perhaps an apology is in order. As Scott wrote,
"Without a realistic test scenario and with no connection pooling and
with no performance tuning, I don't think you should make any
decisions right now about which is faster". My apologies.
--
Kind regards,

Mikkel Høgh <mikkel@hoegh.org>

On 13/10/2008, at 06.54, Stephen Frost wrote:

Show quoted text

* Mikkel Høgh (mikkel@hoegh.org) wrote:

I have been testing it a bit performance-wise, and the numbers are
worrying. In my test, MySQL (using InnoDB) had a 40% lead in
performance, but I'm unsure whether this is indicative for PostgreSQL
performance in general or perhaps a misconfiguration on my part.

The comments left on your blog would probably be a good first step, if
you're not doing them already.. Connection pooling could definitely
help if you're not already doing it. Drupal's MySQL-isms don't help
things either, of course.

Also, you don't post anything about the PostgreSQL config, nor the
hardware it's running on. The default PostgreSQL config usually isn't
appropriate for decent hardware and that could be a contributing
factor
here. It would also be useful to make sure you've analyze'd your
tables
and didn't just do a fresh load w/o any statistics having been
gathered.

We run Drupal on PostgreSQL for an internal site and it works
reasonably
well. We havn't had any performance problems but it's not a terribly
large site either. The issues we've had tend to come from
PostgreSQL's
somewhat less-than-supported status with Drupal.

I've been meaning to look into Drupal's PG support to see about
improving it. Perhaps this winter I'll get a chance to.

Thanks,

Stephen

Attachments:

smime.p7sapplication/pkcs7-signature; name=smime.p7sDownload
#7Ivan Sergio Borgonovo
mail@webthatworks.it
In reply to: Uwe C. Schroeder (#5)
Re: Drupal and PostgreSQL - performance issues?

On Sun, 12 Oct 2008 22:14:53 -0700
"Uwe C. Schroeder" <uwe@oss4u.com> wrote:

I have been testing it a bit performance-wise, and the numbers
are worrying. In my test, MySQL (using InnoDB) had a 40% lead in
performance, but I'm unsure whether this is indicative for
PostgreSQL performance in general or perhaps a misconfiguration
on my part.

In my experience the "numbers are always worrying" in a read-only
environment.

I've used MySQL, but found it rather disturbing when it comes to
integrity. MySQL has just some things I can't live with (i.e.
silently ignoring overflowing charater types etc).
That aside, MySQL IS fast when it comes to read operations. That's
probably because it omits a lot of integrity checks postgres and
other standard compliant databases do.

I'm replying here but I could be replying to Scott and others...

I use nearly exclusively Postgresql. I do it mainly because it
makes me feel more comfortable as a programmer. I'm not the kind of
guy that is satisfied if things work now. I prefer to have something
that gives me higher chances they will work even when I turn my
shoulders and Postgresql give me the feeling it is easier to achieve
that result.

Anyway I don't find myself comfortable with replies in these 2 lines
of reasoning:
1) default configuration of PostgreSQL generally doesn't perform well
2) PostgreSQL may be slower but mySQL may trash your data.

I think these answers don't make a good service to PostgreSQL.

1) still leave the problem there and doesn't give any good reason
why Postgresql comes with a doggy default configuration on most
hardware. It still doesn't explain why I've to work more tuning
PostgreSQL to achieve similar performances of other DB when other DB
don't require tuning.
I know that a Skoda Fabia requires much less tuning than a Ferrari
F1... but well a Ferrari F1 will run faster than a Skoda with or
without tuning.
Making performance comparable without expert tuning will a) stop
most too easy critics about PostgreSQL performances b) give
developers much more feedback on PostgreSQL performance in "nearer
to optimal" setup.
1000 developers try PostgreSQL, 500 find it slow compared to other
DBs, 50 comes back to the list asking, 30 were looking for a magic
receipt that solved their problem, didn't find it and gave up, 10 at
least could hear they had to tune the DB but couldn't get convinced
to actually do so because it looked too expensive to them to learn.

If it is easy to write a tool that will help you to tune PostgreSQL,
it seems it would be something that will really help PostgreSQL
diffusion and improvements. If it is *complicated* to tune
PostgreSQL so that it's performance can be *comparable* (I didn't
write optimal) with other DB we have a problem.

Developer time is valuable... if it is complicated to tune
PostgreSQL to at least have comparable performances to other DB
PostgreSQL look less as a good investment.

Then other people added in the equation connection pooling as a MUST
to compare MySQL and PostgreSQL performances.
This makes the investment to have PostgreSQL in place of mySQL even
higher for many, or at least it is going to puzzle most.

Or maybe... it is false that PostgreSQL doesn't have comparable
performance to other DB with default configuration and repeating
over and over the same answer that you've to tune PostgreSQL to get
comparable performance doesn't play a good service to PostgreSQL.

2) I never saw a "trashing data benchmark" comparing reliability of
PostgreSQL to MySQL. If what I need is a fast DB I'd chose mySQL...
I think this could still not be the best decision to take based on
*real situation*.
Do we really have to trade integrity for speed? Is it a matter of
developers time or technical constraints? Is MyISAM really much
faster in read only operations?
Is Drupal a "read only" applications? Does it scale better with
PostgreSQL or MySQL?
These are answers that are hard to answer even because it is hard to
have valuable feedback.
What I get with that kind of answer is:
an admission: - PostgreSQL is slow
and a hard to prove claim: - MySQL will trash your data.
Unless you circumstantiate I'd say both things are false.

From my point of view the decision was easy. I needed transactions.
Functions would have made dealing with transactions much easier.
PostgreSQL had a much more mature transaction and function engine.
I like to sleep at night.

But is PostgreSQL competitive as a DB engine for apps like Drupal
for the "average user"?
Judging on real experience with Drupal on PostgreSQL I'd say maybe.
Judging on the replies I often read I'd say NO.
Unfortunately replies aren't turning that maybe into a NO for
any reasonable reasons.
If there are reasonable reasons to turn that maybe into a NO...
there may be some work to be done on the PostgreSQL code.
If there aren't reasonable reasons to turn that maybe into a NO...
please stop to give that kind of answers.
or both...

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

#8Greg Smith
gsmith@gregsmith.com
In reply to: Scott Marlowe (#3)
Re: Drupal and PostgreSQL - performance issues?

On Sun, 12 Oct 2008, Scott Marlowe wrote:

It may well be that in a more realistic testing that mysql keeps up
through 5 or 10 client connections then collapses at 40 or 50, while
pgsql keeps climbing in performance.

One of the best pro-PostgreSQL comparisons showing this behavior is at
http://tweakers.net/reviews/649/7 MySQL owns that benchmark until you hit
40 users, then...ouch.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

#9Mikkel Høgh
mikkel@hoegh.org
In reply to: Greg Smith (#8)
Re: Drupal and PostgreSQL - performance issues?

Well, in that benchmark, what you say is only true for the Niagara
processors. On the Opteron page, MySQL performance only drops slightly
as concurrency passes 50.

MySQL might have a problem with Niagara, but it doesn't seem like it
has the severe concurrency vulnerability you speak of.

There are many reasons to pick PostgreSQL, but this one doesn't seem
to be a general thing. In general, MySQL seems to have problems with
some kinds of threading, since their perfomance on Mac OS X is crappy
as well for that reason.
--
Kind regards,

Mikkel Høgh <mikkel@hoegh.org>

On 13/10/2008, at 10.43, Greg Smith wrote:

Show quoted text

On Sun, 12 Oct 2008, Scott Marlowe wrote:

It may well be that in a more realistic testing that mysql keeps up
through 5 or 10 client connections then collapses at 40 or 50,
while pgsql keeps climbing in performance.

One of the best pro-PostgreSQL comparisons showing this behavior is
at http://tweakers.net/reviews/649/7 MySQL owns that benchmark until
you hit 40 users, then...ouch.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com
Baltimore, MD

Attachments:

smime.p7sapplication/pkcs7-signature; name=smime.p7sDownload
#10admin
mick@mjhall.org
In reply to: Mikkel Høgh (#9)
Re: Drupal and PostgreSQL - performance issues?

I am also evaluating Drupal + PostgreSQL at the moment. We are building
a local government website/intranet that doesn't need to be lightning
fast or handle millions of hits a day, but it does need to be rock solid
and potentially needs to manage complex business processes. So
PostgreSQL seems a good choice.

However, PostgreSQL support in the PHP CMS world seems lacking. Joomla
is basically a MySQL-only shop. Drupal is *maybe* suitable, but who
really knows where it will end up?

Can anyone recommend an alternative CMS with the features and
flexibility of Drupal that supports PostgreSQL 100%? What about the
Python world, what is Plone like with PostgreSQL support?

I don't really want to kick off another round of Python vs PHP, just
looking for a CMS that is a good match for PostgreSQL.

Mick

#11Grzegorz Jaśkiewicz
gryzman@gmail.com
In reply to: admin (#10)
Re: Drupal and PostgreSQL - performance issues?

On Mon, Oct 13, 2008 at 10:08 AM, admin <mick@mjhall.org> wrote:

I am also evaluating Drupal + PostgreSQL at the moment. We are building a
local government website/intranet that doesn't need to be lightning fast or
handle millions of hits a day, but it does need to be rock solid and
potentially needs to manage complex business processes. So PostgreSQL seems
a good choice.

However, PostgreSQL support in the PHP CMS world seems lacking. Joomla is
basically a MySQL-only shop. Drupal is *maybe* suitable, but who really
knows where it will end up?

that's the thing. For whatever reason, everyone starts a web project with
mysql underneeth. They even don't structure dbs well, not to mention using
transactions, and stuff like that.
At the end of day, postgresql shares more or less the same queries - if
implemented - and same type of primitive db schema, with all its problems.

Can anyone recommend an alternative CMS with the features and flexibility

of Drupal that supports PostgreSQL 100%? W hat about the Python world, what
is Plone like with PostgreSQL support?
You seriously don't want to go that route. Plone is such a pain to support,
and slow thing as well.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
GJ

#12Greg Smith
gsmith@gregsmith.com
In reply to: Mikkel Høgh (#9)
Re: Drupal and PostgreSQL - performance issues?

On Mon, 13 Oct 2008, Mikkel H�gh wrote:

Well, in that benchmark, what you say is only true for the Niagara
processors. On the Opteron page, MySQL performance only drops slightly as
concurrency passes 50.

That's partly because the upper limit on the graph only goes to 100
concurrent processes. Since the Opterons are faster, that's not a broad
enough scale to see how fast the right edge of the MySQL curve falls.

You are right that the Niagara processors have a sharper decline than the
more traditional platforms. The MySQL 5.0.20a graphs at
http://tweakers.net/reviews/657/6 has a nice comparison graph showing a
few different architectures that's also interesting.

Anyway, you don't actually have to believe any of this; you've got a
testbed to try for yourself if you just crank the user count up. The main
thing I was trying to suggest is that MySQL being a bit faster at 5 users
is not unusual, but it's not really representative of which performs
better either.

In general, MySQL seems to have problems with some kinds of threading,
since their perfomance on Mac OS X is crappy as well for that reason.

One of the reasons (but by no means not the only one) that PostgreSQL uses
a multi-process based architecture instead of a threaded one is because
thread library quality varies so much between platforms.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

#13Ang Chin Han
ang.chin.han@gmail.com
In reply to: Mikkel Høgh (#1)
Re: [PERFORM] Drupal and PostgreSQL - performance issues?

On Mon, Oct 13, 2008 at 11:57 AM, Mikkel Høgh <mikkel@hoegh.org> wrote:

In any case, if anyone has any tips, input, etc. on how best to configure
PostgreSQL for Drupal, or can find a way to poke holes in my analysis, I
would love to hear your insights :)

It'd be more accurate to configure Drupal for PostgreSQL. We use
PostgreSQL for almost everything, including many drupal sites, but the
usage pattern of Drupal puts PostgreSQL at a disadvantage. In short,
Drupal issues a lot of small, simple SQL (100+ is the norm), that
makes tuning hard. To make it faster, you'd need to turn on Drupal's
caches (and PHP opcode caches) to reduce the number of SQLs issued. To
get even better numbers, you'd need to get Drupal to use memcached
instead of calling PostgreSQL for the simple lookups. You can use the
devel module in Drupal to have a look at the SQLs issued. Not pretty,
IMHO.

See: http://2bits.com/articles/benchmarking-postgresql-vs-mysql-performance-using-drupal-5x.html
http://2bits.com/articles/advcache-and-memcached-benchmarks-with-drupal.html

The most promising Drupal performance module for performance looks
like: http://drupal.org/project/cacherouter (900 req/s!) but I haven't
got the chance to give it a go yet.

I'm a die-hard PostgreSQL and Drupal supporter, but in this case, I
concede straight up Drupal+MySQL will always be faster than
Drupal+PostgreSQL because of the way Drupal uses the database. We
still use PostgreSQL for our Drupal sites though, because while it's
slower, it's plenty fast enough.

#14ries van Twisk
pg@rvt.dds.nl
In reply to: admin (#10)
Re: Drupal and PostgreSQL - performance issues?

On Oct 13, 2008, at 4:08 AM, admin wrote:

I am also evaluating Drupal + PostgreSQL at the moment. We are
building a local government website/intranet that doesn't need to be
lightning fast or handle millions of hits a day, but it does need to
be rock solid and potentially needs to manage complex business
processes. So PostgreSQL seems a good choice.

However, PostgreSQL support in the PHP CMS world seems lacking.
Joomla is basically a MySQL-only shop. Drupal is *maybe* suitable,
but who really knows where it will end up?

Can anyone recommend an alternative CMS with the features and
flexibility of Drupal that supports PostgreSQL 100%? What about the
Python world, what is Plone like with PostgreSQL support?

I have been running TYPO3 on PostgreSQL.
It's not easy but very very doable.

As soon as the main CMS system is MySQL based, then you always hit
problems with any other DB.

Ries

Show quoted text

I don't really want to kick off another round of Python vs PHP, just
looking for a CMS that is a good match for PostgreSQL.

Mick

#15Scott Marlowe
scott.marlowe@gmail.com
In reply to: Mikkel Høgh (#6)
Re: [PERFORM] Drupal and PostgreSQL - performance issues?

On Mon, Oct 13, 2008 at 12:00 AM, Mikkel Høgh <mikkel@hoegh.org> wrote:

Alright, my benchmarks might have been a bit naïve.
When it comes to hardware, my webserver is a SunFire X2100 with an Opteron
1210 Dual Core and 4 GB DDR2 RAM, running 64-bit Ubuntu Linux Server 8.04
LTS.

When it comes to the resource usage section of my postgresql.conf, the only
thing that are not commented out are:
shared_buffers = 24MB
max_fsm_pages = 153600

Well, 24MB is pretty small. See if you can increase your system's
shared memory and postgresql's shared_buffers to somewhere around 256M
to 512M. It likely won't make a big difference in this scenario, but
overall it will definitely help.

I freely admit that the reason I haven't messed with these values is that I
have next to no clue what the different things do and how they affect
performance, so perhaps an apology is in order. As Scott wrote, "Without a
realistic test scenario and with no connection pooling and with no
performance tuning, I don't think you should make any decisions right now
about which is faster". My apologies.

No need for apologies. You're looking for the best database for
drupal, and you're asking questions and trying to test to see which
one is best. You just need to look deeper is all. I would, however,
posit that you're putting the cart before the horse by looking at
performance first, instead of reliability.

On a machine with properly functioning hardware, postgresql is nearly
indestructable. MySQL has a lot of instances in time where, if you
pull the plug / lose power it will scramble your db / lose part or all
of your data. Databases are supposed to be durable. InnoDB, the
table handler, is pretty good, but it's surrounded by a DB that was
designed for speed not reliability.

There was a time when Microsoft was trying to cast IIS as faster than
Apache, so they released a benchmark showing IIS being twice as fast
as apache at delivering static pages. Let's say it was 10mS for
apache and 2mS for IIS. Seems really fast. Problem is, static pages
are cheap to deliver. I can buy a $500 server to serve the static
content and if I need more speed, I can throw more servers at the
problem for $500, no OS license fees.

But for dynamic content, the difference was the other way around, and
the delivery times were much higher for IIS, like 50mS for apache and
250mS for IIS. Suddenly, a handful of dynamic pages and the IIS
server was noticeably slower.

The same type of comparison tends to hold true for MySQL versus
PostgreSQL. MySQL tends to be very very fast at "select * from table
where id=5" while PostgreSQL is much faster at 4 page long reporting
queries with 5 levels of subselects and a couple of unions. Things
that make MySQL run so slow as to be useless. Also, PostgreSQL tends
to keep better read performance as the number of writes increase.
This is the real test, so the point I was making before about
realistic tests is very important.

It's about graceful degradation. PostgreSQL has it, and when your
site is getting 20 times the traffic you ever tested for, it's a
little late to figure out you might have picked the wrong DBMS. Note
I'm not saying MySQL is the wrong choice, I'm saying you don't know
because you haven't proven it capable.

#16Scott Marlowe
scott.marlowe@gmail.com
In reply to: Scott Marlowe (#15)
Re: [PERFORM] Drupal and PostgreSQL - performance issues?

On Mon, Oct 13, 2008 at 8:19 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

There was a time when Microsoft was trying to cast IIS as faster than
Apache, so they released a benchmark showing IIS being twice as fast
as apache at delivering static pages. Let's say it was 10mS for
apache and 2mS for IIS.

Dyslexia strikes again! That was supposed to be 5mS... anywho.

#17John DeSoi
desoi@pgedit.com
In reply to: admin (#10)
Re: Drupal and PostgreSQL - performance issues?

On Oct 13, 2008, at 5:08 AM, admin wrote:

However, PostgreSQL support in the PHP CMS world seems lacking.
Joomla is basically a MySQL-only shop. Drupal is *maybe* suitable,
but who really knows where it will end up?

My hope is that Drupal is moving in the right direction. With version
6 they completely abstracted the schema building API. Previously,
MySQL and PostgreSQL had to be specified separately which is the main
reason a lot of modules did not work with PostgreSQL. Things should
improve as modules are upgraded to Drupal 6.

John DeSoi, Ph.D.

#18Simon Waters
simonw@zynet.net
In reply to: Scott Marlowe (#15)
Re: [GENERAL] Drupal and PostgreSQL - performance issues?

On Monday 13 October 2008 15:19:07 Scott Marlowe wrote:

shared_buffers = 24MB
max_fsm_pages = 153600

Well, 24MB is pretty small. See if you can increase your system's
shared memory and postgresql's shared_buffers to somewhere around 256M
to 512M. It likely won't make a big difference in this scenario, but
overall it will definitely help.

I noted after reading earlier messages in the thread, that my distro documents
that the values it default to for shared_buffers is rather small.

One of our servers is fairly pressed for memory (some of the time). Is there
any way to measure the amount of churn in the shared_buffers, as a way of
demonstrating that more is needed (or at this moment more would help)?

A few very small databases on this server, and one which is 768M (still pretty
small but a lot bigger than the rest, most of which is logging information).
The only "hot" information is the session table, ~9000 lines, one index on
the session id. Can I ask Postgres to tell me, or estimate, how much memory
this table would occupy if fully cached in memory?

Half the problem in modern computing is knowing what is "slow". In this case,
counting the rows of the session table takes about 100ms. Deleting expired
session rows about 120ms, more if it hasn't done it for a while, which is I
guess evidence that table isn't being cached in memory as efficiency as it
could be.

In this case the server thinks the system I/O is zero for half the tools in
use, because of the RAID hardware, so most of the Linux based tools are
useless in this context.

At the risk of thread hijacking, for the session table I wonder if we are
handling it the most efficient way. It is just a regular table, indexed on
session_id. Each request of note to the server requires retrieval of the
session record, and often updating the expiry information. Every N requests
the application also issues a:

DELETE FROM sessions WHERE expires<NOW() OR expires IS NULL;

Since there is no index on the table, it sequentially scans, and deletes the
stale records. I'm thinking since it is indexed for regular queries, making N
larger has almost no obvious penalty except we accumulate a small number of
stale records for longer. I'm not sure if an index on expires is worth it,
probably too small to make much difference either way.

As for Drupal on Postgres, it might be worth the effort for big
implementations, I did it for a while, but doing it again I'd go with MySQL.
Nothing to do with the database, everything to do with support for 3rd party
add-ins. Till Drupal gets to the automated testing of these things routinely
against different backends and configs...... Perhaps that is all that is
needed, a service for Drupal authors that tries their plugins against
Postgres automatically and complains if it doesn't work?

#19Joshua D. Drake
jd@commandprompt.com
In reply to: John DeSoi (#17)
Re: Drupal and PostgreSQL - performance issues?

John DeSoi wrote:

On Oct 13, 2008, at 5:08 AM, admin wrote:

However, PostgreSQL support in the PHP CMS world seems lacking. Joomla
is basically a MySQL-only shop. Drupal is *maybe* suitable, but who
really knows where it will end up?

My hope is that Drupal is moving in the right direction. With version 6
they completely abstracted the schema building API. Previously, MySQL
and PostgreSQL had to be specified separately which is the main reason a
lot of modules did not work with PostgreSQL. Things should improve as
modules are upgraded to Drupal 6.

I have been working with the Drupal team on version 7. They are moving
in a much better direction. They do some things oddly due to their MySQL
heritage but for the most part it is coming along very well.

Feel free to help :)

Joshua D. Drake

#20Martin Gainty
mgainty@hotmail.com
In reply to: John DeSoi (#17)
Re: Drupal and PostgreSQL - performance issues?

I am curious as to the ability to update individual configurations

so lets say i want to change Apache maxRequests
MaxKeepAliveRequests 200

or possibly update the PHP module?
LoadModule php4_module "$APACHE_HOME/modules/php4apache2.dll"

OR staying on topic lets say i want to update postgres max connections at postgresql.conf from 100 to 200?
max_connections = 200

How does one achieve configuration for the DB or Apache updates without affecting the other components
Has drupal solved the problem of apache prefork mpm?

thanks/
Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.

CC: pgsql-general@postgresql.org
From: desoi@pgedit.com
To: mick@mjhall.org
Subject: Re: [GENERAL] Drupal and PostgreSQL - performance issues?
Date: Mon, 13 Oct 2008 10:45:17 -0400

On Oct 13, 2008, at 5:08 AM, admin wrote:

However, PostgreSQL support in the PHP CMS world seems lacking.
Joomla is basically a MySQL-only shop. Drupal is *maybe* suitable,
but who really knows where it will end up?

My hope is that Drupal is moving in the right direction. With version
6 they completely abstracted the schema building API. Previously,
MySQL and PostgreSQL had to be specified separately which is the main
reason a lot of modules did not work with PostgreSQL. Things should
improve as modules are upgraded to Drupal 6.

John DeSoi, Ph.D.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

_________________________________________________________________
See how Windows connects the people, information, and fun that are part of your life.
http://clk.atdmt.com/MRT/go/msnnkwxp1020093175mrt/direct/01/

#21Greg Smith
gsmith@gregsmith.com
In reply to: Simon Waters (#18)
#22Joshua Tolley
eggyknap@gmail.com
In reply to: Ivan Sergio Borgonovo (#7)
#23Ivan Sergio Borgonovo
mail@webthatworks.it
In reply to: Joshua Tolley (#22)
#24Scott Marlowe
scott.marlowe@gmail.com
In reply to: Ivan Sergio Borgonovo (#23)
#25Mikkel Høgh
mikkel@hoegh.org
In reply to: Ivan Sergio Borgonovo (#23)
#26Greg Smith
gsmith@gregsmith.com
In reply to: Ivan Sergio Borgonovo (#23)
#27Joshua D. Drake
jd@commandprompt.com
In reply to: Mikkel Høgh (#25)
#28Ang Chin Han
ang.chin.han@gmail.com
In reply to: Scott Marlowe (#24)
#29Greg Smith
gsmith@gregsmith.com
In reply to: Mikkel Høgh (#25)
#30Martin Gainty
mgainty@hotmail.com
In reply to: Ang Chin Han (#28)
#31Scott Marlowe
scott.marlowe@gmail.com
In reply to: Ang Chin Han (#28)
#32Mikkel Høgh
mikkel@hoegh.org
In reply to: Martin Gainty (#30)
#33Ivan Sergio Borgonovo
mail@webthatworks.it
In reply to: Joshua D. Drake (#27)
#34Kevin Murphy
murphy@genome.chop.edu
In reply to: Greg Smith (#29)
#35Ang Chin Han
ang.chin.han@gmail.com
In reply to: Martin Gainty (#30)
#36Ivan Sergio Borgonovo
mail@webthatworks.it
In reply to: Scott Marlowe (#24)
#37Mikkel Høgh
mikkel@hoegh.org
In reply to: Ivan Sergio Borgonovo (#36)
#38Mikkel Høgh
mikkel@hoegh.org
In reply to: Ang Chin Han (#35)
#39Erik Jones
ejones@engineyard.com
In reply to: admin (#10)
#40Daniel Verite
daniel@manitou-mail.org
In reply to: Mikkel Høgh (#1)
#41Mikkel Høgh
mikkel@hoegh.org
In reply to: Daniel Verite (#40)
#42Scott Marlowe
scott.marlowe@gmail.com
In reply to: Mikkel Høgh (#41)
#43Martijn van Oosterhout
kleptog@svana.org
In reply to: Daniel Verite (#40)
#44Magnus Hagander
magnus@hagander.net
In reply to: Mikkel Høgh (#41)
#45Ivan Sergio Borgonovo
mail@webthatworks.it
In reply to: Mikkel Høgh (#37)
#46Mikkel Høgh
mikkel@hoegh.org
In reply to: Magnus Hagander (#44)
#47Merlin Moncure
mmoncure@gmail.com
In reply to: Mikkel Høgh (#46)
#48Mikkel Høgh
mikkel@hoegh.org
In reply to: Merlin Moncure (#47)
#49Greg Smith
gsmith@gregsmith.com
In reply to: Kevin Murphy (#34)
#50Chris
dmagick@gmail.com
In reply to: Ang Chin Han (#35)
#51Scott Marlowe
scott.marlowe@gmail.com
In reply to: Chris (#50)
#52Chris
dmagick@gmail.com
In reply to: Scott Marlowe (#51)
#53Scott Marlowe
scott.marlowe@gmail.com
In reply to: Chris (#52)
#54Bruce Momjian
bruce@momjian.us
In reply to: Martin Gainty (#30)
#55Bruce Momjian
bruce@momjian.us
In reply to: Greg Smith (#49)
#56Tom Lane
tgl@sss.pgh.pa.us
In reply to: Bruce Momjian (#55)
#57Greg Smith
gsmith@gregsmith.com
In reply to: Bruce Momjian (#55)
#58Tom Lane
tgl@sss.pgh.pa.us
In reply to: Greg Smith (#57)
#59Matthew T. O'Connor
matthew@zeut.net
In reply to: Tom Lane (#58)
#60Tom Lane
tgl@sss.pgh.pa.us
In reply to: Matthew T. O'Connor (#59)
#61Bruce Momjian
bruce@momjian.us
In reply to: Tom Lane (#60)
#62Tomasz Ostrowski
tometzky@batory.org.pl
In reply to: Mikkel Høgh (#48)
#63Mikkel Høgh
mikkel@hoegh.org
In reply to: Tomasz Ostrowski (#62)
#64Tomasz Ostrowski
tometzky@batory.org.pl
In reply to: Mikkel Høgh (#63)
#65Ivan Sergio Borgonovo
mail@webthatworks.it
In reply to: Ivan Sergio Borgonovo (#36)
#66Tom Lane
tgl@sss.pgh.pa.us
In reply to: Ivan Sergio Borgonovo (#65)
#67Stephen Frost
sfrost@snowman.net
In reply to: Tomasz Ostrowski (#64)
#68DelGurth
delgurth@gmail.com
In reply to: Stephen Frost (#67)
#69Tomasz Ostrowski
tometzky@batory.org.pl
In reply to: Stephen Frost (#67)
#70Stephen Frost
sfrost@snowman.net
In reply to: DelGurth (#68)
#71Scott Marlowe
scott.marlowe@gmail.com
In reply to: Stephen Frost (#70)
#72Mikkel Høgh
mikkel@hoegh.org
In reply to: Scott Marlowe (#71)
#73Tomasz Ostrowski
tometzky@batory.org.pl
In reply to: Mikkel Høgh (#72)
#74Mikkel Høgh
mikkel@hoegh.org
In reply to: Tomasz Ostrowski (#73)
#75Tomasz Ostrowski
tometzky@batory.org.pl
In reply to: Mikkel Høgh (#74)
#76Mikkel Høgh
mikkel@hoegh.org
In reply to: Tomasz Ostrowski (#75)
#77Bill Moran
wmoran@collaborativefusion.com
In reply to: Mikkel Høgh (#76)
#78Mikkel Høgh
mikkel@hoegh.org
In reply to: Bill Moran (#77)
#79Bill Moran
wmoran@collaborativefusion.com
In reply to: Mikkel Høgh (#78)
#80Tom Lane
tgl@sss.pgh.pa.us
In reply to: Mikkel Høgh (#76)
#81Michael Glaesemann
grzm@seespotcode.net
In reply to: Bill Moran (#79)
#82Mikkel Høgh
mikkel@hoegh.org
In reply to: Bill Moran (#79)
#83Mikkel Høgh
mikkel@hoegh.org
In reply to: Tom Lane (#80)
#84Andrew Sullivan
ajs@commandprompt.com
In reply to: Mikkel Høgh (#76)
#85Bill Moran
wmoran@collaborativefusion.com
In reply to: Mikkel Høgh (#82)
#86Stephen Frost
sfrost@snowman.net
In reply to: Bill Moran (#85)
#87Greg Smith
gsmith@gregsmith.com
In reply to: Bill Moran (#85)
#88Collin Kidder
adderd@kkmfg.com
In reply to: Bill Moran (#85)
#89Andrew Sullivan
ajs@commandprompt.com
In reply to: Greg Smith (#87)
#90Scott Marlowe
scott.marlowe@gmail.com
In reply to: Collin Kidder (#88)
#91Mikkel Høgh
mikkel@hoegh.org
In reply to: Scott Marlowe (#90)
#92Scott Marlowe
scott.marlowe@gmail.com
In reply to: Mikkel Høgh (#91)
#93Martin Gainty
mgainty@hotmail.com
In reply to: Mikkel Høgh (#91)
#94Rainer Pruy
Rainer.Pruy@Acrys.COM
In reply to: Collin Kidder (#88)
#95Alvaro Herrera
alvherre@2ndquadrant.com
In reply to: Martin Gainty (#93)
#96Scott Marlowe
scott.marlowe@gmail.com
In reply to: Martin Gainty (#93)
#97Alvaro Herrera
alvherre@2ndquadrant.com
In reply to: Scott Marlowe (#96)
#98Ivan Sergio Borgonovo
mail@webthatworks.it
In reply to: Stephen Frost (#86)
#99Stephen Frost
sfrost@snowman.net
In reply to: Ivan Sergio Borgonovo (#98)
#100Lincoln Yeoh
lyeoh@pop.jaring.my
In reply to: Joshua D. Drake (#27)
#101Greg Smith
gsmith@gregsmith.com
In reply to: Andrew Sullivan (#89)
#102Guy Rouillier
guyr-ml1@burntmail.com
In reply to: Bill Moran (#85)
#103Alvaro Herrera
alvherre@2ndquadrant.com
In reply to: Guy Rouillier (#102)
#104Serge Fonville
serge.fonville@gmail.com
In reply to: Tomasz Ostrowski (#73)
#105Aidan Van Dyk
aidan@pepper.home.highrise.ca
In reply to: Guy Rouillier (#102)
#106Dave Coventry
dgcoventry@gmail.com
In reply to: Serge Fonville (#104)
#107Jason Long
mailing.list@supernovasoftware.com
In reply to: Dave Coventry (#106)
#108Michelle Konzack
linux4michelle@tamay-dogan.net
In reply to: Mikkel Høgh (#72)
#109Michelle Konzack
linux4michelle@tamay-dogan.net
In reply to: Mikkel Høgh (#74)
#110Michelle Konzack
linux4michelle@tamay-dogan.net
In reply to: Scott Marlowe (#90)
#111Michelle Konzack
linux4michelle@tamay-dogan.net
In reply to: Martin Gainty (#93)
#112Michelle Konzack
linux4michelle@tamay-dogan.net
In reply to: Andrew Sullivan (#84)
#113Martin Gainty
mgainty@hotmail.com
In reply to: Michelle Konzack (#111)
#114Peter Eisentraut
peter_e@gmx.net
In reply to: Guy Rouillier (#102)
#115Guy Rouillier
guyr-ml1@burntmail.com
In reply to: Aidan Van Dyk (#105)
#116Greg Smith
gsmith@gregsmith.com
In reply to: Aidan Van Dyk (#105)
#117Bruce Momjian
bruce@momjian.us
In reply to: Greg Smith (#87)
#118Alvaro Herrera
alvherre@2ndquadrant.com
In reply to: Bruce Momjian (#117)
#119Collin Kidder
adderd@kkmfg.com
In reply to: Bruce Momjian (#117)
#120ries van Twisk
pg@rvt.dds.nl
In reply to: Collin Kidder (#119)
#121Angel Alvarez
clist@uah.es
In reply to: Collin Kidder (#119)
#122Alan Hodgson
ahodgson@simkin.ca
In reply to: Collin Kidder (#119)
In reply to: Angel Alvarez (#121)
#124Collin Kidder
adderd@kkmfg.com
In reply to: Angel Alvarez (#121)
#125Angel Alvarez
clist@uah.es
In reply to: Collin Kidder (#124)
#126Collin Kidder
adderd@kkmfg.com
In reply to: Angel Alvarez (#125)
#127Andrew Sullivan
ajs@commandprompt.com
In reply to: Collin Kidder (#119)
#128Steve Atkins
steve@blighty.com
In reply to: Collin Kidder (#126)
#129Greg Smith
gsmith@gregsmith.com
In reply to: Angel Alvarez (#121)
#130Alvaro Herrera
alvherre@2ndquadrant.com
In reply to: Greg Smith (#129)
#131ries van Twisk
pg@rvt.dds.nl
In reply to: Greg Smith (#129)
#132Dave Coventry
dgcoventry@gmail.com
In reply to: Steve Atkins (#128)
#133Aarni Ruuhimäki
aarni@kymi.com
In reply to: Dave Coventry (#132)
#134Rob Wultsch
wultsch@gmail.com
In reply to: ries van Twisk (#120)
#135Guy Rouillier
guyr-ml1@burntmail.com
In reply to: Raymond O'Donnell (#123)
#136Michelle Konzack
linux4michelle@tamay-dogan.net
In reply to: ries van Twisk (#131)