pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDrupal and PostgreSQL - performance issues?
68+ messages / 26 participants
[nested] [flat]
* Drupal and PostgreSQL - performance issues?
@ 2008-10-13 03:57 Mikkel Høgh <mikkel@hoegh.org>
0 siblings, 6 replies; 68+ messages in thread
From: Mikkel Høgh @ 2008-10-13 03:57 UTC (permalink / raw)
To: pgsql-performance; pgsql-general@postgresql.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.p7s (application/pkcs7-signature, 1.9K) download
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-13 04:37 John DeSoi <desoi@pgedit.com>
parent: Mikkel Høgh <mikkel@hoegh.org>
5 siblings, 0 replies; 68+ messages in thread
From: John DeSoi @ 2008-10-13 04:37 UTC (permalink / raw)
To: Mikkel Høgh <mikkel@hoegh.org>; +Cc: pgsql-performance; pgsql-general@postgresql.org
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.
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: [PERFORM] Drupal and PostgreSQL - performance issues?
@ 2008-10-13 04:48 Scott Marlowe <scott.marlowe@gmail.com>
parent: Mikkel Høgh <mikkel@hoegh.org>
5 siblings, 1 reply; 68+ messages in thread
From: Scott Marlowe @ 2008-10-13 04:48 UTC (permalink / raw)
To: Mikkel Høgh <mikkel@hoegh.org>; +Cc: pgsql-performance; pgsql-general@postgresql.org
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.
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: [PERFORM] Drupal and PostgreSQL - performance issues?
@ 2008-10-13 04:54 Stephen Frost <sfrost@snowman.net>
parent: Mikkel Høgh <mikkel@hoegh.org>
5 siblings, 1 reply; 68+ messages in thread
From: Stephen Frost @ 2008-10-13 04:54 UTC (permalink / raw)
To: Mikkel Høgh <mikkel@hoegh.org>; +Cc: pgsql-performance; pgsql-general@postgresql.org
* 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:
signature.asc (application/pgp-signature, 196B) download
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-13 05:14 Uwe C. Schroeder <uwe@oss4u.com>
parent: Mikkel Høgh <mikkel@hoegh.org>
5 siblings, 1 reply; 68+ messages in thread
From: Uwe C. Schroeder @ 2008-10-13 05:14 UTC (permalink / raw)
To: pgsql-general@postgresql.org; +Cc: Mikkel Høgh <mikkel@hoegh.org>; pgsql-performance
> 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
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: [PERFORM] Drupal and PostgreSQL - performance issues?
@ 2008-10-13 06:00 Mikkel Høgh <mikkel@hoegh.org>
parent: Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 68+ messages in thread
From: Mikkel Høgh @ 2008-10-13 06:00 UTC (permalink / raw)
To: pgsql-performance; pgsql-general@postgresql.org
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:
> * 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.p7s (application/pkcs7-signature, 1.9K) download
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-13 07:02 Ivan Sergio Borgonovo <mail@webthatworks.it>
parent: Uwe C. Schroeder <uwe@oss4u.com>
0 siblings, 1 reply; 68+ messages in thread
From: Ivan Sergio Borgonovo @ 2008-10-13 07:02 UTC (permalink / raw)
To: pgsql-general@postgresql.org
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
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-13 08:43 Greg Smith <gsmith@gregsmith.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 68+ messages in thread
From: Greg Smith @ 2008-10-13 08:43 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Mikkel Høgh <mikkel@hoegh.org>; pgsql-performance
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
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-13 08:51 Mikkel Høgh <mikkel@hoegh.org>
parent: Greg Smith <gsmith@gregsmith.com>
0 siblings, 1 reply; 68+ messages in thread
From: Mikkel Høgh @ 2008-10-13 08:51 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>
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:
> 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.p7s (application/pkcs7-signature, 1.9K) download
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-13 10:17 Greg Smith <gsmith@gregsmith.com>
parent: Mikkel Høgh <mikkel@hoegh.org>
0 siblings, 0 replies; 68+ messages in thread
From: Greg Smith @ 2008-10-13 10:17 UTC (permalink / raw)
To: Mikkel Høgh <mikkel@hoegh.org>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: [PERFORM] Drupal and PostgreSQL - performance issues?
@ 2008-10-13 10:36 Ang Chin Han <ang.chin.han@gmail.com>
parent: Mikkel Høgh <mikkel@hoegh.org>
5 siblings, 0 replies; 68+ messages in thread
From: Ang Chin Han @ 2008-10-13 10:36 UTC (permalink / raw)
To: Mikkel Høgh <mikkel@hoegh.org>; +Cc: pgsql-performance; pgsql-general@postgresql.org
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.
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: [PERFORM] Drupal and PostgreSQL - performance issues?
@ 2008-10-13 14:19 Scott Marlowe <scott.marlowe@gmail.com>
parent: Mikkel Høgh <mikkel@hoegh.org>
0 siblings, 2 replies; 68+ messages in thread
From: Scott Marlowe @ 2008-10-13 14:19 UTC (permalink / raw)
To: Mikkel Høgh <mikkel@hoegh.org>; +Cc: pgsql-performance; pgsql-general@postgresql.org
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.
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: [PERFORM] Drupal and PostgreSQL - performance issues?
@ 2008-10-13 14:23 Scott Marlowe <scott.marlowe@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 68+ messages in thread
From: Scott Marlowe @ 2008-10-13 14:23 UTC (permalink / raw)
To: Mikkel Høgh <mikkel@hoegh.org>; +Cc: pgsql-performance; pgsql-general@postgresql.org
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.
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: [GENERAL] Drupal and PostgreSQL - performance issues?
@ 2008-10-13 14:49 Simon Waters <simonw@zynet.net>
parent: Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 1 reply; 68+ messages in thread
From: Simon Waters @ 2008-10-13 14:49 UTC (permalink / raw)
To: pgsql-performance
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?
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: [GENERAL] Drupal and PostgreSQL - performance issues?
@ 2008-10-13 21:21 Greg Smith <gsmith@gregsmith.com>
parent: Simon Waters <simonw@zynet.net>
0 siblings, 0 replies; 68+ messages in thread
From: Greg Smith @ 2008-10-13 21:21 UTC (permalink / raw)
To: Simon Waters <simonw@zynet.net>; +Cc: pgsql-performance
On Mon, 13 Oct 2008, Simon Waters wrote:
> 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)?
If you wander to http://www.westnet.com/~gsmith/content/postgresql/ my
"Inside the PostgreSQL Buffer Cache" presentation goes over this topic in
extreme detail.
> Can I ask Postgres to tell me, or estimate, how much memory this table
> would occupy if fully cached in memory?
http://wiki.postgresql.org/wiki/Disk_Usage gives an example showing all
the biggest tables/indexes in your data, and links to an article giving
examples of how to find the size of all sorts of things. One of the
queries in my presentation even shows you what % of each table is actually
being cached by the dedicated database memory.
You also need to consider the OS buffer cache to get the full picture,
which is a bit more complicated;
http://www.kennygorman.com/wordpress/?p=250 gives an example there you
might be able to use.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 02:45 Joshua Tolley <eggyknap@gmail.com>
parent: Ivan Sergio Borgonovo <mail@webthatworks.it>
0 siblings, 1 reply; 68+ messages in thread
From: Joshua Tolley @ 2008-10-14 02:45 UTC (permalink / raw)
To: Ivan Sergio Borgonovo <mail@webthatworks.it>; +Cc: pgsql-general@postgresql.org
On Mon, Oct 13, 2008 at 1:02 AM, Ivan Sergio Borgonovo
<mail@webthatworks.it> wrote:
<snip>
> 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.
This is a useful question, but there are reasonable answers to it. The
key underlying principle is that it's impossible to know what will
work well in a given situation until that situation is tested. That's
why benchmarks from someone else's box are often mostly useless on
your box, except for predicting generalities and then only when they
agree with other people's benchmarks. PostgreSQL ships with a very
conservative default configuration because (among other things,
perhaps) 1) it's a configuration that's very unlikely to fail
miserably for most situations, and 2) it's assumed that if server
performance matters, someone will spend time tuning things. The fact
that database X performs better than PostgreSQL out of the box is
fairly irrelevant; if performance matters, you won't use the defaults,
you'll find better ones that work for you.
> 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.
Most of the complaints of PostgreSQL being really slow are from people
who either 1) use PostgreSQL assuming its MySQL and therefore don't do
things they way a real DBA would do them, or 2) simply repeat myths
they've heard about PostgreSQL performance and have no experience to
back up. While it would be nice to be able to win over such people,
PostgreSQL developers tend to worry more about pleasing the people who
really know what they're doing. (The apparent philosophical
contradiction between my statements above and the fact that I'm
writing something as inane as PL/LOLCODE doesn't cause me much lost
sleep -- yet)
> 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.
It's not easy to write such a tool; the lists talk about one every few
months, and invariable conclude it's harder than just teaching DBAs to
do it (or alternatively letting those that need help pay those that
can help to tune for them).
As to whether it's a problem that it's a complex thing to tune, sure
it would be nice if it were easier, and efforts are made along those
lines all the time (cf. GUC simplification efforts for a contemporary
example). But databases are complex things, and any tool that makes
them overly simple is only glossing over the important details.
> 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.
Anyone familiar with high-performance applications is familiar with
connection pooling.
> 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.
Why not? It's the truth, and there are good reasons for it. See above.
> 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*.
If you've got an important application (for some definition of
"important"), your considerations in choosing underlying software are
more complex than "is it the fastest option". Horror stories about
MySQL doing strange things to data, because of poor integrity
constraints, ISAM tables, or other problems are fairly common (among
PostgreSQL users, at least :) But I will also admit I have none of my
own; my particular experience in life has, thankfully, prevented me
from much MySQL exposure.
> Do we really have to trade integrity for speed?
Yes. Sanity checks take time.
> Is MyISAM really much
> faster in read only operations?
Yes. See above.
> What I get with that kind of answer is:
> an admission: - PostgreSQL is slow
People aren't saying that. They're saying it works better when someone
who knows what they're doing runs it.
> But is PostgreSQL competitive as a DB engine for apps like Drupal
> for the "average user"?
So are we talking about the "average user", or someone who needs real
performance? The average user certainly cares about performance, but
if (s)he really cares, (s)he will put time toward achieving
performance.
- Josh / eggyknap
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 09:40 Ivan Sergio Borgonovo <mail@webthatworks.it>
parent: Joshua Tolley <eggyknap@gmail.com>
0 siblings, 3 replies; 68+ messages in thread
From: Ivan Sergio Borgonovo @ 2008-10-14 09:40 UTC (permalink / raw)
To: pgsql-general@postgresql.org
On Mon, 13 Oct 2008 20:45:39 -0600
"Joshua Tolley" <eggyknap@gmail.com> wrote:
Premise:
I'm not sustaining that the "default" answers are wrong, but they are
inadequate.
BTW the OP made a direct comparison of pgsql and mysql running
drupal. That's a bit different than just asking: how can I improve
PostgreSQL performances.
I'm happy with PostgreSQL, it does what I think is important for me
better than MySQL... and I'm using it on Drupal in nearly all the
websites I developed.
> On Mon, Oct 13, 2008 at 1:02 AM, Ivan Sergio Borgonovo
> <mail@webthatworks.it> wrote:
> <snip>
> > 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.
> This is a useful question, but there are reasonable answers to it.
> The key underlying principle is that it's impossible to know what
> will work well in a given situation until that situation is
> tested. That's why benchmarks from someone else's box are often
> mostly useless on your box, except for predicting generalities and
> then only when they agree with other people's benchmarks.
> PostgreSQL ships with a very conservative default configuration
> because (among other things, perhaps) 1) it's a configuration
> that's very unlikely to fail miserably for most situations, and 2)
So your target are potential skilled DBA that have a coffe pot as
testing machine?
I don't want temporary needs of unskilled dev driving PostgreSQL
project, but they are all potential users. Users too make a project
more successful. Not every dev using a DB is a DBA, not every
project in need for a DB is mature enough to have DBA knowledge.
Still you've another DB that kick your ass in most common hardware
configuration and workload. Something has to be done about the
tuning. Again... a not tuned Ferrari can't win a F1 GP competing
with a tuned McLaren but it can stay close. A Skoda Fabia can't.
When people come here and ask why PostgreSQL is slow as a Skoda
compared to a Ferrari in some tasks and you reply they have to
tune... a) they will think you're trying to sell them a Skoda b)
they will think you're selling a Ferrari in a mounting kit.
It even doesn't help to guide people if 9 out of 10 you reply:
before we give you any advice... you've to spend half day learning
how to tune PostgreSQL. When they come back... you reply... but your
benchmark was not suited for your real work workload.
It makes helping people hard.
Remember we are talking about PostgreSQL vs. MySQL performance
running Drupal.
But still people point at benchmark where PostgreSQL outperform
MySQL.
People get puzzled.
Things like: MySQL will eat your data are hard to sustain and
explain.
I don't have direct experience on corrupted DB... but I'd say it is
easier to program PostgreSQL than MySQL once your project is over 30
lines of code because it is less sloppy.
This is easier to prove: point at the docs and to SQL standard.
> it's assumed that if server performance matters, someone will
> spend time tuning things. The fact that database X performs better
> than PostgreSQL out of the box is fairly irrelevant; if
> performance matters, you won't use the defaults, you'll find
> better ones that work for you.
The fact that out of the box on common hardware PostgreSQL
under-perform MySQL with default config would matter if few
paragraph below you wouldn't say that integrity has a *big*
performance cost even on read-only operation.
When people come back crying that PostgreSQL under-perform with
Drupal they generally show a considerable gap between the 2.
> > 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.
> Most of the complaints of PostgreSQL being really slow are from
> people who either 1) use PostgreSQL assuming its MySQL and
> therefore don't do things they way a real DBA would do them, or 2)
> simply repeat myths they've heard about PostgreSQL performance and
> have no experience to back up. While it would be nice to be able
> to win over such people, PostgreSQL developers tend to worry more
> about pleasing the people who really know what they're doing. (The
> apparent philosophical contradiction between my statements above
> and the fact that I'm writing something as inane as PL/LOLCODE
> doesn't cause me much lost sleep -- yet)
> > 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.
> It's not easy to write such a tool; the lists talk about one every
> few months, and invariable conclude it's harder than just teaching
> DBAs to do it (or alternatively letting those that need help pay
> those that can help to tune for them).
But generally the performance gap is astonishing on default
configuration. It is hard to win the myth surrounding PostgreSQL...
but again... if you've to trade integrity for speed... at least you
should have numbers to show what are you talking about. Then people
may decide.
You're using a X% slower, Y% more reliable DB.
You're using a X% slower, Y% more scalable DB. etc...
Or at least tell people they are buying a SUV, a Ferrari or a train
first.
We were talking about CMS. So we know it is not Ferrari, it is not a
Skoda and it may be a train or a SUV (sort of...).
> As to whether it's a problem that it's a complex thing to tune,
> sure it would be nice if it were easier, and efforts are made
> along those lines all the time (cf. GUC simplification efforts for
> a contemporary example). But databases are complex things, and any
> tool that makes them overly simple is only glossing over the
> important details.
You trade complexity for flexibility... so is PostgreSQL a SUV, a
Ferrari, a Skoda and a train too sold in a mounting kit?
I'd expect that if it was a Skoda I wouldn't have any tuning problem
to win a Ferrari on consumption.
> > 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*.
> If you've got an important application (for some definition of
> "important"), your considerations in choosing underlying software
> are more complex than "is it the fastest option". Horror stories
> about MySQL doing strange things to data, because of poor integrity
> constraints, ISAM tables, or other problems are fairly common
> (among PostgreSQL users, at least :) But I will also admit I have
Well horror stories about PostgreSQL being doggy slow are quite
common among MySQL users.
But while it is very easy to "prove" the later on a test config with
default on PostgreSQL it is harder to prove the former.
So it would be better to rephrase the former so it is easier to
prove or just change the term of comparison.
Anyway making easier to tune PostgreSQL even if not optimally would
be a good target.
> > What I get with that kind of answer is:
> > an admission: - PostgreSQL is slow
> People aren't saying that. They're saying it works better when
> someone who knows what they're doing runs it.
I find this a common excuse of programmers.
You user are an asshole, my software is perfect.
It's not a matter of "better". When people comes here saying
PostgreSQL perform badly serving Drupal the performance gap is not
realistically described just with "better".
> > But is PostgreSQL competitive as a DB engine for apps like Drupal
> > for the "average user"?
> So are we talking about the "average user", or someone who needs
> real performance? The average user certainly cares about
> performance, but if (s)he really cares, (s)he will put time toward
> achieving performance.
If I see a performance gap of 50% I'm going to think that's not
going to be that easy to fill it with "tuning".
That means:
- I may think that with a *reasonable* effort I could come close and
then I'll have to find other good reasons other than performances to
chose A in spite of B
- I may think I need a miracle I'm not willing to bet on/pay for.
Now... you've to tune is not the kind of answer that will help me to
take a decision in favour of PostgreSQL.
Anyway a 50% or more performance gap is something that make hard to
take any decision. It something that really makes hard even to give
advices to new people.
Reducing that gap with a set of "common cases" .conf may help.
When people will see a 10%-15% gap without too much effort they will
be more willing to listen what you've to offer more and will believe
easier that that gap can be reduced further.
Unless every camp keeps on believing in myths and new comers have to
believe faithfully.
You may think that people coming here asking for performance advices
already collected enough information on eg. PostgreSQL features...
but it may not be the case.
Think about Drupal developers coming here and asking... is it worth
to support PostgreSQL?
Let me go even further out of track...
Why do comparisons between PostgreSQL and MySQL come up so
frequently?
Because MySQL "is the DB of the Web".
Many web apps are (were) mainly "read-only" and their data integrity
is (was) not so important.
Many Web apps are (were) simple.
Web apps and CMS are a reasonably large slice of what's moving on
the net. Do these applications need the features PostgreSQL has?
Is there any trade off? Is it worth to pay that trade off?
Is it worth to conquer this audience even if they are not skilled
DBA?
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 12:56 Scott Marlowe <scott.marlowe@gmail.com>
parent: Ivan Sergio Borgonovo <mail@webthatworks.it>
2 siblings, 2 replies; 68+ messages in thread
From: Scott Marlowe @ 2008-10-14 12:56 UTC (permalink / raw)
To: Ivan Sergio Borgonovo <mail@webthatworks.it>; +Cc: pgsql-general@postgresql.org
On Tue, Oct 14, 2008 at 3:40 AM, Ivan Sergio Borgonovo
<mail@webthatworks.it> wrote:
> On Mon, 13 Oct 2008 20:45:39 -0600
> "Joshua Tolley" <eggyknap@gmail.com> wrote:
>
> Premise:
> I'm not sustaining that the "default" answers are wrong, but they are
> inadequate.
> BTW the OP made a direct comparison of pgsql and mysql running
> drupal. That's a bit different than just asking: how can I improve
> PostgreSQL performances.
Sadly, no one has run any meaningful benchmarks so far.
>> This is a useful question, but there are reasonable answers to it.
>> The key underlying principle is that it's impossible to know what
>> will work well in a given situation until that situation is
>> tested. That's why benchmarks from someone else's box are often
>> mostly useless on your box, except for predicting generalities and
>> then only when they agree with other people's benchmarks.
>> PostgreSQL ships with a very conservative default configuration
>> because (among other things, perhaps) 1) it's a configuration
>> that's very unlikely to fail miserably for most situations, and 2)
>
> So your target are potential skilled DBA that have a coffe pot as
> testing machine?
Actually a lot has been done to better tune pgsql out of the box, but
since it uses shared memory and many oses still come with incredibly
low shared mem settings we're stuck.
> Still you've another DB that kick your ass in most common hardware
> configuration and workload. Something has to be done about the
> tuning. Again... a not tuned Ferrari can't win a F1 GP competing
> with a tuned McLaren but it can stay close. A Skoda Fabia can't.
Except the current benchmark is how fast you can change the tires.
> When people come here and ask why PostgreSQL is slow as a Skoda
> compared to a Ferrari in some tasks and you reply they have to
> tune... a) they will think you're trying to sell them a Skoda b)
> they will think you're selling a Ferrari in a mounting kit.
Actually the most common answer is to ask them if they've actually
used a realistic benchmark. Then tune.
> It even doesn't help to guide people if 9 out of 10 you reply:
> before we give you any advice... you've to spend half day learning
> how to tune PostgreSQL. When they come back... you reply... but your
> benchmark was not suited for your real work workload.
> It makes helping people hard.
Getting things right is hard. Do you think any joe can get behind the
wheel of an F1 car and just start driving? Remember, for every
problem, there is a simple, easy, elegant answer, and it's wrong.
> Remember we are talking about PostgreSQL vs. MySQL performance
> running Drupal.
Yes, and the very first consideration should be, "Will the db I'm
choosing be likely to eat my data?" If you're not sure on that one
all the benchmarketing in the world won't make a difference.
> But still people point at benchmark where PostgreSQL outperform
> MySQL.
> People get puzzled.
Because they don't understand what databases are and what they do maybe?
> Things like: MySQL will eat your data are hard to sustain and
> explain.
Google is your friend. Heck, you can find account after account from
MySQL fanboys about their favorite database eating their data.
> I don't have direct experience on corrupted DB... but I'd say it is
> easier to program PostgreSQL than MySQL once your project is over 30
> lines of code because it is less sloppy.
> This is easier to prove: point at the docs and to SQL standard.
Lots of people feel MySQL's tutorial style docs are easier to
comprehend. Especially those unfamiliar with dbs. I prefer
PostgreSQL's docs, as they are more thorough better suited for a
semi-knowledgable DBA.
>> it's assumed that if server performance matters, someone will
>> spend time tuning things. The fact that database X performs better
>> than PostgreSQL out of the box is fairly irrelevant; if
>> performance matters, you won't use the defaults, you'll find
>> better ones that work for you.
>
> The fact that out of the box on common hardware PostgreSQL
> under-perform MySQL with default config would matter if few
> paragraph below you wouldn't say that integrity has a *big*
> performance cost even on read-only operation.
> When people come back crying that PostgreSQL under-perform with
> Drupal they generally show a considerable gap between the 2.
Again, this is almost always for 1 to 5 users. Real world DBs have
dozens to hundreds to even thousands of simultaneous users. My
PostgreSQL servers at work routinely have 10 or 20 queries running at
the same time, and peak at 100 or more.
> But generally the performance gap is astonishing on default
> configuration.
Only for unrealistic benchmarks. Seriously, for any benchmark with
large concurrency and / or high write percentage, postgreSQL wins.
>It is hard to win the myth surrounding PostgreSQL...
> but again... if you've to trade integrity for speed... at least you
> should have numbers to show what are you talking about. Then people
> may decide.
> You're using a X% slower, Y% more reliable DB.
> You're using a X% slower, Y% more scalable DB. etc...
It's not just integrity for speed! IT's the fact that MySQL has
serious issues with large concurrency, especially when there's a fair
bit of writes going on. This is especially true for myisam, but not
completely solved in the Oracle-owned innodb table handler.
> Well horror stories about PostgreSQL being doggy slow are quite
> common among MySQL users.
Users who run single thread benchmarks. Let them pit their MySQL
servers against my production PostgreSQL servers with a realistic
load.
> If I see a performance gap of 50% I'm going to think that's not
> going to be that easy to fill it with "tuning".
> That means:
> - I may think that with a *reasonable* effort I could come close and
> then I'll have to find other good reasons other than performances to
> chose A in spite of B
Then you are putting your cart before your horse. Choosing a db based
on a single synthetic benchmark is like buying a car based on the
color of the shift knob. Quality is far more important. And so is
your data: "MySQL mangling your data faster than any other db!" is
not a good selling point..
> Now... you've to tune is not the kind of answer that will help me to
> take a decision in favour of PostgreSQL.
Then please use MySQL. I've got a db that works well for me. When
MySQL proves incapable of handling the load, then come back and ask
for help migrating.
> Anyway a 50% or more performance gap is something that make hard to
> take any decision. It something that really makes hard even to give
> advices to new people.
Yes. You keep harping on the 50% performance gap. One you nor anyone
else has demonstrated to exist with any reasonable test.
> Why do comparisons between PostgreSQL and MySQL come up so
> frequently?
>
> Because MySQL "is the DB of the Web".
> Many web apps are (were) mainly "read-only" and their data integrity
> is (was) not so important.
> Many Web apps are (were) simple.
>
> Web apps and CMS are a reasonably large slice of what's moving on
> the net. Do these applications need the features PostgreSQL has?
Is their data important? Is downtime a bad thing for them?
> Is there any trade off? Is it worth to pay that trade off?
>
> Is it worth to conquer this audience even if they are not skilled
> DBA?
Only if they're willing to learn. I can't spend all day tuning their
pgsql servers for free. IF not, then let them go, and they'll come
back when they need to.
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 13:27 Mikkel Høgh <mikkel@hoegh.org>
parent: Ivan Sergio Borgonovo <mail@webthatworks.it>
2 siblings, 2 replies; 68+ messages in thread
From: Mikkel Høgh @ 2008-10-14 13:27 UTC (permalink / raw)
To: pgsql-general@postgresql.org
On 14/10/2008, at 11.40, Ivan Sergio Borgonovo wrote:
> On Mon, 13 Oct 2008 20:45:39 -0600
> "Joshua Tolley" <eggyknap@gmail.com> wrote:
>>
>> PostgreSQL ships with a very conservative default configuration
>> because (among other things, perhaps) 1) it's a configuration
>> that's very unlikely to fail miserably for most situations, and 2)
>
> So your target are potential skilled DBA that have a coffe pot as
> testing machine?
Yeah, I don't know why the default configuration is targetting
something at least 5 years old. I figure its kinda rare with a
completely new installation of PostgreSQL 8.3.3 on such a machine.
>>> What I get with that kind of answer is:
>>> an admission: - PostgreSQL is slow
>
>> People aren't saying that. They're saying it works better when
>> someone who knows what they're doing runs it.
>
> I find this a common excuse of programmers.
> You user are an asshole, my software is perfect.
> It's not a matter of "better". When people comes here saying
> PostgreSQL perform badly serving Drupal the performance gap is not
> realistically described just with "better".
So, let me get this right, Joshua… You are targetting DBAs using
servers with less than 512 MB RAM.
Is PostgreSQL supposed to be used by professional DBAs on enterprise
systems or is it supposed to run out of the box on my old Pentium 3?
>>> But is PostgreSQL competitive as a DB engine for apps like Drupal
>>> for the "average user"?
>> So are we talking about the "average user", or someone who needs
>> real performance? The average user certainly cares about
>> performance, but if (s)he really cares, (s)he will put time toward
>> achieving performance.
That might be true, if the only demographic you are looking for are
professional DBAs, but if you're looking to attract more developers,
not having sensible defaults is not really a good thing.
While I'll probably take the time to learn more about how to tune
PostgreSQL, the common Drupal-developer developer will probably just
say "Ah, this is slow, I'll just go back to MySQL…".
I'm not saying that PostgreSQL should (or could) be just as fast as
MySQL, and while my benchmark was naïve, it's what a Drupal developer
will see when he decides to try out PostgreSQL. A 40% drop in page
loading performance. Yikes.
Even if you don't change the default configuration, you should at
least include some examples like "If you have modern webserver, this
is a good starting point (…) for more information about tuning
PostgreSQL, see http://www.postgresql.org/docs/8.3/…"=
Attachments:
smime.p7s (application/pkcs7-signature, 1.9K) download
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 13:38 Greg Smith <gsmith@gregsmith.com>
parent: Ivan Sergio Borgonovo <mail@webthatworks.it>
2 siblings, 0 replies; 68+ messages in thread
From: Greg Smith @ 2008-10-14 13:38 UTC (permalink / raw)
To: Ivan Sergio Borgonovo <mail@webthatworks.it>; +Cc: pgsql-general@postgresql.org
On Tue, 14 Oct 2008, Ivan Sergio Borgonovo wrote:
> The fact that out of the box on common hardware PostgreSQL under-perform
> MySQL with default config would matter if few paragraph below you
> wouldn't say that integrity has a *big* performance cost even on
> read-only operation.
If you want a more detailed commentary on that subject, I'd suggest
http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL:_Comparing_Reliability_and_Speed_in_...
which hits all the major sides of the speed vs. integrity trade-offs here.
You suggest putting together a "how much are you paying for integrity?"
comparison. That's hard to do from the PostgreSQL side because most
options don't allow an unsafe mode. What would be easier is benchmarking
some application in MySQL with the optional strict mode toggled on and
off; MyISAM+loose vs. InnoDB+strict would be instructive I think.
Those of us who prefer PostgreSQL don't spend too much time working on
this area because the very concept of a non-strict mode is horrifying.
Quantifying how much full data integrity costs is like seeing how much
faster you can run if you're set on fire: while you might measure it, far
better to just avoid the whole possibility.
> Well horror stories about PostgreSQL being doggy slow are quite
> common among MySQL users.
In addition to the default PostgreSQL configuration being optimized for
size rather speed, there are a few operations that just don't execute well
at all in Postgres. The most common example is how counting things
happens; that's slow in PostgreSQL for reasons that are deeply intertwined
with the transaction implementation. I don't believe there any problems
like that in the Drupal implementation, but there's enough of that in
other web applications that some percentage of horror stories come from
that sort of thing--just not using PostgreSQL for a job it's a good choice
for. It's hard to distinguish those cases from those where it was
appropriate, but just wasn't setup properly or compared fairly.
> Anyway making easier to tune PostgreSQL even if not optimally would
> be a good target.
There were two commits to the core PostgreSQL server code last month aimed
at making it easier to build tools that interact with the server
configuration. The tool(s) that use those features are coming soon.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 13:42 Joshua D. Drake <jd@commandprompt.com>
parent: Mikkel Høgh <mikkel@hoegh.org>
1 sibling, 2 replies; 68+ messages in thread
From: Joshua D. Drake @ 2008-10-14 13:42 UTC (permalink / raw)
To: Mikkel Høgh <mikkel@hoegh.org>; +Cc: pgsql-general@postgresql.org
Mikkel Høgh wrote:
> On 14/10/2008, at 11.40, Ivan Sergio Borgonovo wrote:
> That might be true, if the only demographic you are looking for are
> professional DBAs, but if you're looking to attract more developers, not
> having sensible defaults is not really a good thing.
> While I'll probably take the time to learn more about how to tune
> PostgreSQL, the common Drupal-developer developer will probably just say
> "Ah, this is slow, I'll just go back to MySQL…".
Developers should be familiar with the platforms they develop for. If
they are not and they are not willing to learn them they shouldn't use it.
Sincerely,
Joshua D. Drake
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 13:47 Ang Chin Han <ang.chin.han@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 2 replies; 68+ messages in thread
From: Ang Chin Han @ 2008-10-14 13:47 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Ivan Sergio Borgonovo <mail@webthatworks.it>; pgsql-general@postgresql.org
On Tue, Oct 14, 2008 at 8:56 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Tue, Oct 14, 2008 at 3:40 AM, Ivan Sergio Borgonovo
> <mail@webthatworks.it> wrote:
>> On Mon, 13 Oct 2008 20:45:39 -0600
>> "Joshua Tolley" <eggyknap@gmail.com> wrote:
>>
>> Premise:
>> I'm not sustaining that the "default" answers are wrong, but they are
>> inadequate.
>> BTW the OP made a direct comparison of pgsql and mysql running
>> drupal. That's a bit different than just asking: how can I improve
>> PostgreSQL performances.
>
> Sadly, no one has run any meaningful benchmarks so far.
Not sure about "meaningful", but:
http://2bits.com/articles/benchmarking-postgresql-vs-mysql-performance-using-drupal-5x.html
Their attached config file shows a relatively untuned postgresql
config, but in *Drupal's* case, I'm not sure how else tweaking the
config would help when it shows: "Executed 99 queries in 67.81
milliseconds." which in itself is not too shabbly, but that points
towards Drupal's inclination to issue a *lot* of small, simple
queries.
> Actually the most common answer is to ask them if they've actually
> used a realistic benchmark. Then tune.
The benchmark is a mostly read-only Drupal site -- a few admins, but a
lot of readers. Drupal as a benchmark is skewed towards lots and lots
of small, simple queries, which MyISAM excels at. The long term fix
ought to be to help the Drupal team to make it
The front page of one my site, even with some caching turned on, but
with a logged in user, shows 389 queries just to generate it, mostly
consisting of queries like "SELECT dst FROM url_alias WHERE src =
'$link' AND language IN('en', '') ORDER BY language DESC". Explain
analyze shows that postgresql happily uses the index to grab the
correct value, in less than 0.04 ms. But it's still not fast enough,
esp. when Drupal stupidly issues some of the exact same queries up to
9 times!
This, to me, is clearly some thing to be fixed at Drupal's level.
Joshua Drake is on the right path -- helping the Drupal folks treat
the database as a database instead of a blind data store. This is
something I'm working on as well on Drupal's code base, but it looks
like it wouldn't be making to the mainstream Drupal release anything
soon as the changes are too drastic.
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 14:02 Greg Smith <gsmith@gregsmith.com>
parent: Mikkel Høgh <mikkel@hoegh.org>
1 sibling, 1 reply; 68+ messages in thread
From: Greg Smith @ 2008-10-14 14:02 UTC (permalink / raw)
To: Mikkel Høgh <mikkel@hoegh.org>; +Cc: pgsql-general@postgresql.org
On Tue, 14 Oct 2008, Mikkel Høgh wrote:
> You are targetting DBAs using servers with less than 512 MB RAM. Is
> PostgreSQL supposed to be used by professional DBAs on enterprise
> systems or is it supposed to run out of the box on my old Pentium 3?
Take a look at http://bugzilla.kernel.org/show_bug.cgi?id=11381
There you'll discover that the Linux default for how much memory an
application like PostgreSQL can allocate is 32MB. This is true even if
you install the OS on a system with 128GB of RAM. If PostgreSQL created a
default configuration optimized for "enterprise systems", that
configuration wouldn't even start on *any* Linux system with the default
kernel settings. The above is an attempt to change that, rejected with
the following text that aptly describes the default PostgreSQL
configuration as well: "The requirement is that SHMMAX is sane for the
user by default and that means *safe* rather than as big as possible".
The situation on most other operating systems is similarly bad.
So the dichotomy here is even worse than you think: it's not just that
the performance profile would be wrong, it's that defaults targeting
modern hardware would make it so the database won't even start on your old
Pentium 3. The best the PostgreSQL community can do is provide
documentation on how you re-tune your *operating system first*, then the
database server, to get good performance on systems with modern amounts of
RAM.
Admittedly, that documentation and related support tools should be better,
mainly by being easier to find. This discussion is well timed in that I
was planning this month to propose adding a URL with a tuning guide and/or
sample configurations to the top of the postgresql.conf file before the
next release comes out; when that comes up I can point to this thread as a
reminder of why that's needed.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 14:05 Martin Gainty <mgainty@hotmail.com>
parent: Ang Chin Han <ang.chin.han@gmail.com>
1 sibling, 3 replies; 68+ messages in thread
From: Martin Gainty @ 2008-10-14 14:05 UTC (permalink / raw)
To: Ang Chin Han <ang.chin.han@gmail.com>; Scott Marlowe <scott.marlowe@gmail.com>; +Cc: Ivan Sergio Borgonovo <mail@webthatworks.it>; pgsql-general@postgresql.org
MG>comments prefixed with MG>
> From: ang.chin.han@gmail.com
> To: scott.marlowe@gmail.com
> Subject: Re: [GENERAL] Drupal and PostgreSQL - performance issues?
> CC: mail@webthatworks.it; pgsql-general@postgresql.org
>
> On Tue, Oct 14, 2008 at 8:56 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> > On Tue, Oct 14, 2008 at 3:40 AM, Ivan Sergio Borgonovo
> > <mail@webthatworks.it> wrote:
> >> On Mon, 13 Oct 2008 20:45:39 -0600
> >> "Joshua Tolley" <eggyknap@gmail.com> wrote:
> >>
> >> Premise:
> >> I'm not sustaining that the "default" answers are wrong, but they are
> >> inadequate.
> >> BTW the OP made a direct comparison of pgsql and mysql running
> >> drupal. That's a bit different than just asking: how can I improve
> >> PostgreSQL performances.
> >
> > Sadly, no one has run any meaningful benchmarks so far.
>
> Not sure about "meaningful", but:
> http://2bits.com/articles/benchmarking-postgresql-vs-mysql-performance-using-drupal-5x.html
> Their attached config file shows a relatively untuned postgresql
> config, but in *Drupal's* case, I'm not sure how else tweaking the
> config would help when it shows: "Executed 99 queries in 67.81
> milliseconds." which in itself is not too shabbly, but that points
> towards Drupal's inclination to issue a *lot* of small, simple
> queries.
MG>default behaviour of ISAM DB's
>
> > Actually the most common answer is to ask them if they've actually
> > used a realistic benchmark. Then tune.
>
> The benchmark is a mostly read-only Drupal site -- a few admins, but a
> lot of readers. Drupal as a benchmark is skewed towards lots and lots
> of small, simple queries, which MyISAM excels at. The long term fix
> ought to be to help the Drupal team to make it
MG>What about INNODB is Drupal forgetting the default engine for 5.x?
>
> The front page of one my site, even with some caching turned on, but
> with a logged in user, shows 389 queries just to generate it, mostly
> consisting of queries like "SELECT dst FROM url_alias WHERE src =
> '$link' AND language IN('en', '') ORDER BY language DESC". Explain
> analyze shows that postgresql happily uses the index to grab the
> correct value, in less than 0.04 ms. But it's still not fast enough,
> esp. when Drupal stupidly issues some of the exact same queries up to
> 9 times!
MG>isnt this in query_cache..why is Drupal going braindead on this item?
>
> This, to me, is clearly some thing to be fixed at Drupal's level.
> Joshua Drake is on the right path -- helping the Drupal folks treat
> the database as a database instead of a blind data store. This is
> something I'm working on as well on Drupal's code base, but it looks
> like it wouldn't be making to the mainstream Drupal release anything
> soon as the changes are too drastic.
MG>From what i've been reading the author doesnt work on drupal anymore
MG>If we could get access to the php source maybe we could fix this..?
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
_________________________________________________________________
Get more out of the Web. Learn 10 hidden secrets of Windows Live.
http://windowslive.com/connect/post/jamiethomson.spaces.live.com-Blog-cns!550F681DAD532637!5295.entr...
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 14:20 Scott Marlowe <scott.marlowe@gmail.com>
parent: Ang Chin Han <ang.chin.han@gmail.com>
1 sibling, 0 replies; 68+ messages in thread
From: Scott Marlowe @ 2008-10-14 14:20 UTC (permalink / raw)
To: Ang Chin Han <ang.chin.han@gmail.com>; +Cc: Ivan Sergio Borgonovo <mail@webthatworks.it>; pgsql-general@postgresql.org
On Tue, Oct 14, 2008 at 7:47 AM, Ang Chin Han <ang.chin.han@gmail.com> wrote:
> On Tue, Oct 14, 2008 at 8:56 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
>> On Tue, Oct 14, 2008 at 3:40 AM, Ivan Sergio Borgonovo
>> <mail@webthatworks.it> wrote:
>>> On Mon, 13 Oct 2008 20:45:39 -0600
>>> "Joshua Tolley" <eggyknap@gmail.com> wrote:
>>>
>>> Premise:
>>> I'm not sustaining that the "default" answers are wrong, but they are
>>> inadequate.
>>> BTW the OP made a direct comparison of pgsql and mysql running
>>> drupal. That's a bit different than just asking: how can I improve
>>> PostgreSQL performances.
>>
>> Sadly, no one has run any meaningful benchmarks so far.
>
> Not sure about "meaningful", but:
> http://2bits.com/articles/benchmarking-postgresql-vs-mysql-performance-using-drupal-5x.html
Again, a read only benchmark against the front page hardly counts as a
meaningful. And 5 concurrent is pretty small anyway.
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 14:20 Mikkel Høgh <mikkel@hoegh.org>
parent: Martin Gainty <mgainty@hotmail.com>
2 siblings, 0 replies; 68+ messages in thread
From: Mikkel Høgh @ 2008-10-14 14:20 UTC (permalink / raw)
To: Martin Gainty <mgainty@hotmail.com>; +Cc: Ang Chin Han <ang.chin.han@gmail.com>; Scott Marlowe <scott.marlowe@gmail.com>; Ivan Sergio Borgonovo <mail@webthatworks.it>; pgsql-general@postgresql.org
On 14/10/2008, at 16.05, Martin Gainty wrote:
> > The benchmark is a mostly read-only Drupal site -- a few admins,
> but a
> > lot of readers. Drupal as a benchmark is skewed towards lots and
> lots
> > of small, simple queries, which MyISAM excels at. The long term fix
> > ought to be to help the Drupal team to make it
> MG>What about INNODB is Drupal forgetting the default engine for 5.x?
Well, my benchmark was actually running on InnoDB. With MyISAM, the
difference would probably have been even larger, but a lot less fair,
since MyISAM doesn't to any kind of integrity checks at all.
Attachments:
smime.p7s (application/pkcs7-signature, 1.9K) download
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 14:28 Ivan Sergio Borgonovo <mail@webthatworks.it>
parent: Joshua D. Drake <jd@commandprompt.com>
1 sibling, 0 replies; 68+ messages in thread
From: Ivan Sergio Borgonovo @ 2008-10-14 14:28 UTC (permalink / raw)
To: pgsql-general@postgresql.org
On Tue, 14 Oct 2008 06:42:33 -0700
"Joshua D. Drake" <jd@commandprompt.com> wrote:
> Mikkel Høgh wrote:
> > On 14/10/2008, at 11.40, Ivan Sergio Borgonovo wrote:
>
> > That might be true, if the only demographic you are looking for
> > are professional DBAs, but if you're looking to attract more
> > developers, not having sensible defaults is not really a good
> > thing. While I'll probably take the time to learn more about how
> > to tune PostgreSQL, the common Drupal-developer developer will
> > probably just say "Ah, this is slow, I'll just go back to
> > MySQL…".
>
> Developers should be familiar with the platforms they develop for.
> If they are not and they are not willing to learn them they
> shouldn't use it.
They may be willing to get familiar if they understand the platform
is suited for their needs.
Sometimes they don't know their needs ;)
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 14:59 Kevin Murphy <murphy@genome.chop.edu>
parent: Greg Smith <gsmith@gregsmith.com>
0 siblings, 1 reply; 68+ messages in thread
From: Kevin Murphy @ 2008-10-14 14:59 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: Mikkel Høgh <mikkel@hoegh.org>; pgsql-general@postgresql.org
Greg Smith wrote:
> On Tue, 14 Oct 2008, Mikkel H�gh wrote:
>
>> You are targetting DBAs using servers with less than 512 MB RAM. Is
>> PostgreSQL supposed to be used by professional DBAs on enterprise
>> systems or is it supposed to run out of the box on my old Pentium 3?
>
> you'll discover that the Linux default for how much memory an
> application like PostgreSQL can allocate is 32MB. This is true even if
> you install the OS on a system with 128GB of RAM.
One thing that might help people swallow the off-putting default "toy
mode" performance of PostgreSQL would be an explanation of why
PostgreSQL uses its shared memory architecture in the first place. How
much of a performance or stability advantage does it confer under what
database usage and hardware scenarios? How can any such claims be proven
except by writing a bare-bones database server from scratch that can use
multiple memory models?
-Kevin Murphy
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 15:28 Ang Chin Han <ang.chin.han@gmail.com>
parent: Martin Gainty <mgainty@hotmail.com>
2 siblings, 2 replies; 68+ messages in thread
From: Ang Chin Han @ 2008-10-14 15:28 UTC (permalink / raw)
To: Martin Gainty <mgainty@hotmail.com>; +Cc: Scott Marlowe <scott.marlowe@gmail.com>; Ivan Sergio Borgonovo <mail@webthatworks.it>; pgsql-general@postgresql.org
On Tue, Oct 14, 2008 at 10:05 PM, Martin Gainty <mgainty@hotmail.com> wrote:
> MG>comments prefixed with MG>
> MG>What about INNODB is Drupal forgetting the default engine for 5.x?
I don't use MySQL for drupal myself except for testing, but Drupal
just uses the default storage engine for MySQL, which happens to be
MyISAM on my Ubuntu Hardy. Doesn't seem to have much difference
between InnoDB or MyISAM, but I don't have a sizable Drupal site to
test that on.
>> esp. when Drupal stupidly issues some of the exact same queries up to
>> 9 times!
> MG>isnt this in query_cache..why is Drupal going braindead on this item?
Yes, it's rather braindead. I'd rather not worry about why, but how'd
we make Drupal use the PostgreSQL more effectively. In it's current
form (Drupal 5 and 6), it even issues a regexp for every query, even
before it hits the database because of some design decisions to use
user definable table prefix as a workaround to the lack of database
SCHEMA in MySQL: take the follow snippet as a representative Drupal
code:
$alias = db_result(db_query("SELECT dst FROM {url_alias} WHERE src =
'%s' AND language IN('%s', '') ORDER BY language DESC", $path,
$path_language));
That's one sprintf() and a number of string replace operations to
replace "{url_alias}" with "url_alias", as well as a number of regexp
to sanitize the query string.
Note this comment:
/*
* Queries sent to Drupal should wrap all table names in curly brackets. This
* function searches for this syntax and adds Drupal's table prefix to all
* tables, allowing Drupal to coexist with other systems in the same database if
* necessary.
*/
That's an MySQL-ism for working around legacy hosting sites offering
only a single MySQL db bogging postgresql down...
Also betraying MyISAM heritage (in Drupal pgsql driver):
/**
* Lock a table.
* This function automatically starts a transaction.
*/
function db_lock_table($table) {
db_query('BEGIN; LOCK TABLE {'. db_escape_table($table) .'} IN
EXCLUSIVE MODE');
}
/**
* Unlock all locked tables.
* This function automatically commits a transaction.
*/
function db_unlock_tables() {
db_query('COMMIT');
}
> MG>From what i've been reading the author doesnt work on drupal anymore
Not when Dries founded a commercial venture selling Drupal hosting and services.
http://acquia.com/products-services/acquia-frequently-asked-questions#driesrole
Doesn't bode too well though when they don't support PostgreSQL directly:
http://acquia.com/products-services/acquia-drupal-supported-platforms
> MG>If we could get access to the php source maybe we could fix this..?
Eh? Feel free: http://drupal.org/ It's an excellent CMS, with lots of
cool features, but comes with it's own set of wtf surprises.
To me, there're two ways to proceed: dive straight into the
development Drupal release and make the next version work better from
the ground up, or hack the existing Drupal postgresql driver and
frequently used SQLs and optimize them. We're working on the latter
because of some legacy code we have to support, but the former would
be the long term plan.
Optimizing PostgreSQL to make Drupal faster is not the correct way as
PostgreSQL is fast, scalable and robust enough. Just need to be used
more correctly.
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 16:44 Ivan Sergio Borgonovo <mail@webthatworks.it>
parent: Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 1 reply; 68+ messages in thread
From: Ivan Sergio Borgonovo @ 2008-10-14 16:44 UTC (permalink / raw)
To: pgsql-general@postgresql.org
On Tue, 14 Oct 2008 06:56:02 -0600
"Scott Marlowe" <scott.marlowe@gmail.com> wrote:
> >> This is a useful question, but there are reasonable answers to
> >> it. The key underlying principle is that it's impossible to
> >> know what will work well in a given situation until that
> >> situation is tested. That's why benchmarks from someone else's
> >> box are often mostly useless on your box, except for predicting
> >> generalities and then only when they agree with other people's
> >> benchmarks. PostgreSQL ships with a very conservative default
> >> configuration because (among other things, perhaps) 1) it's a
> >> configuration that's very unlikely to fail miserably for most
> >> situations, and 2)
> > So your target are potential skilled DBA that have a coffe pot as
> > testing machine?
> Actually a lot has been done to better tune pgsql out of the box,
> but since it uses shared memory and many oses still come with
> incredibly low shared mem settings we're stuck.
From my naive understanding the parameters you can tweak for major
improvements can be counted on one hand's finger:
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
Are you going to say that expert hands could squeeze other 20%
performance more after the usual, pretty simple tweaks an automatic
tool could achieve on a general workload for a cms?
From the specific test Mikkel did?
Wouldn't it be a much better starting point to discuss if PostgreSQL
is a suitable tool for a CMS if those pretty basic tweaks were done
automatically or included as example config in PostgreSQL
distribution?
> > Still you've another DB that kick your ass in most common
> > hardware configuration and workload. Something has to be done
> > about the tuning. Again... a not tuned Ferrari can't win a F1 GP
> > competing with a tuned McLaren but it can stay close. A Skoda
> > Fabia can't.
> Except the current benchmark is how fast you can change the tires.
It is not and anyway you won't reply: you've to tune PostgreSQL so
you can change tires very fast.
The test was a very low load of Drupal on pg and mysql.
Are you expecting that on a higher load pg will outperform mysql?
Then say so. Don't say you've to tune PostgreSQL.
Do you think that PostgreSQL can outperform mysql on very low load
with tuning?
> > When people come here and ask why PostgreSQL is slow as a Skoda
> > compared to a Ferrari in some tasks and you reply they have to
> > tune... a) they will think you're trying to sell them a Skoda b)
> > they will think you're selling a Ferrari in a mounting kit.
> Actually the most common answer is to ask them if they've actually
> used a realistic benchmark. Then tune.
realistic? What was not realistic about Mikkel's test?
I'd say it is not the kind of workload PostgreSQL was built for.
BTW I don't buy the idea that even with correct tuning Drupal is
going to be any faster with PostgreSQL on a mostly "read-only"
benchmark.
This makes an even more painful experience and undermine the trust
of the people coming here and asking why PostgreSQL is slow compared
to mySQL.
The best replies I've read were from Ang Chin Han.
> > Remember we are talking about PostgreSQL vs. MySQL performance
> > running Drupal.
> Yes, and the very first consideration should be, "Will the db I'm
> choosing be likely to eat my data?" If you're not sure on that one
> all the benchmarketing in the world won't make a difference.
Maybe a DB eating data is a sustainable solution to your problem.
But I think the web is mature enough so that very few web apps worth
to be used could consider their data integrity a cheap asset.
This could be a good selling point even for people that grew up with
MySQL.
> > But still people point at benchmark where PostgreSQL outperform
> > MySQL.
> > People get puzzled.
> Because they don't understand what databases are and what they do
> maybe?
Then? I doubt that pointing them at tuning docs will make them
understand if PostgreSQL is the right tool.
> > I don't have direct experience on corrupted DB... but I'd say it
> > is easier to program PostgreSQL than MySQL once your project is
> > over 30 lines of code because it is less sloppy.
> > This is easier to prove: point at the docs and to SQL standard.
> Lots of people feel MySQL's tutorial style docs are easier to
> comprehend. Especially those unfamiliar with dbs. I prefer
> PostgreSQL's docs, as they are more thorough better suited for a
> semi-knowledgable DBA.
What I meant was... I don't like a DB that silently turn my strings
into int or trim strings to make them fit into a varchar etc...
> >> it's assumed that if server performance matters, someone will
> >> spend time tuning things. The fact that database X performs
> >> better than PostgreSQL out of the box is fairly irrelevant; if
> >> performance matters, you won't use the defaults, you'll find
> >> better ones that work for you.
> > The fact that out of the box on common hardware PostgreSQL
> > under-perform MySQL with default config would matter if few
> > paragraph below you wouldn't say that integrity has a *big*
> > performance cost even on read-only operation.
> > When people come back crying that PostgreSQL under-perform with
> > Drupal they generally show a considerable gap between the 2.
> Again, this is almost always for 1 to 5 users. Real world DBs have
> dozens to hundreds to even thousands of simultaneous users. My
> PostgreSQL servers at work routinely have 10 or 20 queries running
> at the same time, and peak at 100 or more.
That's more in the right direction to help people comparing MySQL
and PostgreSQL. Still you don't point them at tuning, because it is
not going to make PostgreSQL shine anyway.
> > But generally the performance gap is astonishing on default
> > configuration.
> Only for unrealistic benchmarks. Seriously, for any benchmark with
> large concurrency and / or high write percentage, postgreSQL wins.
Then DON'T point at the default config as the culprit.
a) it is true that config plays a BIG role in MySQL
*reasonable* benchmark comparisons: offer a reasonable config!
b) it is not true: stop pointing at tuning. It is to say the least
puzzling.
> >It is hard to win the myth surrounding PostgreSQL...
> > but again... if you've to trade integrity for speed... at least
> > you should have numbers to show what are you talking about. Then
> > people may decide.
> > You're using a X% slower, Y% more reliable DB.
> > You're using a X% slower, Y% more scalable DB. etc...
>
> It's not just integrity for speed! IT's the fact that MySQL has
> serious issues with large concurrency, especially when there's a
> fair bit of writes going on. This is especially true for myisam,
> but not completely solved in the Oracle-owned innodb table handler.
But no one is going to believe you if the answer is:
MySQL is going to eat your data. That's sound like FUD.
I think that even after 1 year of:
siege -H "Cookie: drupalsessid" -c 5 "http://drupal-site.local/"; -b
-t30s
MySQL is not going to eat your data.
If it smells like FUD people may think it is.
> > Well horror stories about PostgreSQL being doggy slow are quite
> > common among MySQL users.
> Users who run single thread benchmarks. Let them pit their MySQL
> servers against my production PostgreSQL servers with a realistic
> load.
This doesn't make the default 2 lines of promoting PostgreSQL any
better:
- you've to tune
- mySQL will eat your data
> > If I see a performance gap of 50% I'm going to think that's not
> > going to be that easy to fill it with "tuning".
> > That means:
> > - I may think that with a *reasonable* effort I could come close
> > and then I'll have to find other good reasons other than
> > performances to chose A in spite of B
> Then you are putting your cart before your horse. Choosing a db
> based on a single synthetic benchmark is like buying a car based
> on the color of the shift knob. Quality is far more important.
> And so is your data: "MySQL mangling your data faster than any
> other db!" is not a good selling point..
But the reply: you've to tune and mysql will eat your data doesn't
get nearer to the kernel of the problem.
> > Now... you've to tune is not the kind of answer that will help
> > me to take a decision in favour of PostgreSQL.
> Then please use MySQL. I've got a db that works well for me. When
> MySQL proves incapable of handling the load, then come back and ask
> for help migrating.
OK... then you admit that suggesting to tune and scaring people is
just delaying the migration ;)
> > Why do comparisons between PostgreSQL and MySQL come up so
> > frequently?
> >
> > Because MySQL "is the DB of the Web".
> > Many web apps are (were) mainly "read-only" and their data
> > integrity is (was) not so important.
> > Many Web apps are (were) simple.
> >
> > Web apps and CMS are a reasonably large slice of what's moving on
> > the net. Do these applications need the features PostgreSQL has?
>
> Is their data important? Is downtime a bad thing for them?
>
> > Is there any trade off? Is it worth to pay that trade off?
> >
> > Is it worth to conquer this audience even if they are not skilled
> > DBA?
>
> Only if they're willing to learn. I can't spend all day tuning
I bet some are willing to learn. Just pointing them at tuning and
using FUD-like arguments is not a very good advertising for
PostgreSQL Kung-Fu school.
BTW I hope someone may find good use of this:
2xXeon HT CPU 3.20GHz (not dual core), 4Gb RAM, RAID 5 SCSI
* absolutely not tuned Apache
* absolutely not tuned Drupal with little content, some blocks and
some google adds
(just CSS aggregation and caching enabled)
* lightly tuned PostgreSQL 8.1
shared_buffers = 3500
work_mem = 32768
checkpoint_segments = 10
effective_cache_size = 15000
random_page_cost = 3
default_statistics_target = 30
siege -H "Cookie: drupalsessid" -c1 "localhost/d1"
-b -t30s
-c 1
Transactions: 485 hits
Availability: 100.00 %
Elapsed time: 29.95 secs
Data transferred: 5.33 MB
Response time: 0.06 secs
Transaction rate: 16.19 trans/sec
Throughput: 0.18 MB/sec
Concurrency: 1.00
Successful transactions: 485
Failed transactions: 0
Longest transaction: 0.13
Shortest transaction: 0.06
-c 5
Transactions: 1017 hits
Availability: 100.00 %
Elapsed time: 29.61 secs
Data transferred: 11.29 MB
Response time: 0.15 secs
Transaction rate: 34.35 trans/sec
Throughput: 0.38 MB/sec
Concurrency: 4.98
Successful transactions: 1017
Failed transactions: 0
Longest transaction: 0.24
Shortest transaction: 0.08
-c 20
Transactions: 999 hits
Availability: 100.00 %
Elapsed time: 30.11 secs
Data transferred: 11.08 MB
Response time: 0.60 secs
Transaction rate: 33.18 trans/sec
Throughput: 0.37 MB/sec
Concurrency: 19.75
Successful transactions: 999
Failed transactions: 0
Longest transaction: 1.21
Shortest transaction: 0.10
-c 100
Transactions: 1085 hits
Availability: 100.00 %
Elapsed time: 29.97 secs
Data transferred: 9.61 MB
Response time: 2.54 secs
Transaction rate: 36.20 trans/sec
Throughput: 0.32 MB/sec
Concurrency: 91.97
Successful transactions: 911
Failed transactions: 0
Longest transaction: 12.41
Shortest transaction: 0.07
-c 200
Transactions: 1116 hits
Availability: 100.00 %
Elapsed time: 30.02 secs
Data transferred: 9.10 MB
Response time: 4.85 secs
Transaction rate: 37.18 trans/sec
Throughput: 0.30 MB/sec
Concurrency: 180.43
Successful transactions: 852
Failed transactions: 0
Longest transaction: 15.85
Shortest transaction: 0.25
-c 400
Transactions: 1133 hits
Availability: 100.00 %
Elapsed time: 29.76 secs
Data transferred: 8.51 MB
Response time: 6.98 secs
Transaction rate: 38.07 trans/sec
Throughput: 0.29 MB/sec
Concurrency: 265.85
Successful transactions: 736
Failed transactions: 0
Longest transaction: 28.55
Shortest transaction: 0.00
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 17:05 Mikkel Høgh <mikkel@hoegh.org>
parent: Ivan Sergio Borgonovo <mail@webthatworks.it>
0 siblings, 0 replies; 68+ messages in thread
From: Mikkel Høgh @ 2008-10-14 17:05 UTC (permalink / raw)
To: Ivan Sergio Borgonovo <mail@webthatworks.it>
Hmm, those are interesting numbers. Did you use a real, logged in,
drupal session ID (anonymous users also get one, which still gives
them cached pages).
They are in the form of
"SESS6df8919ff2bffc5de8bcf0ad65f9dc0f=59f68e60a120de47c2cb5c98b010ffff"
Note how the thoughput stays in the 30-ish range from 100 to 400,
although the response time climbs steeply. That might indicate that
your Apache configuration is the main roadblock here, since that
indicates that clients are waiting for a free Apache process to handle
their request (I suppose you're using MPM_PREFORK)…
--
Kind regards,
Mikkel Høgh <mikkel@hoegh.org>
On 14/10/2008, at 18.44, Ivan Sergio Borgonovo wrote:
> BTW I hope someone may find good use of this:
>
> 2xXeon HT CPU 3.20GHz (not dual core), 4Gb RAM, RAID 5 SCSI
> * absolutely not tuned Apache
> * absolutely not tuned Drupal with little content, some blocks and
> some google adds
> (just CSS aggregation and caching enabled)
> * lightly tuned PostgreSQL 8.1
> shared_buffers = 3500
> work_mem = 32768
> checkpoint_segments = 10
> effective_cache_size = 15000
> random_page_cost = 3
> default_statistics_target = 30
>
> siege -H "Cookie: drupalsessid" -c1 "localhost/d1"
> -b -t30s
>
> -c 1
> Transactions: 485 hits
> Availability: 100.00 %
> Elapsed time: 29.95 secs
> Data transferred: 5.33 MB
> Response time: 0.06 secs
> Transaction rate: 16.19 trans/sec
> Throughput: 0.18 MB/sec
> Concurrency: 1.00
> Successful transactions: 485
> Failed transactions: 0
> Longest transaction: 0.13
> Shortest transaction: 0.06
>
> -c 5
> Transactions: 1017 hits
> Availability: 100.00 %
> Elapsed time: 29.61 secs
> Data transferred: 11.29 MB
> Response time: 0.15 secs
> Transaction rate: 34.35 trans/sec
> Throughput: 0.38 MB/sec
> Concurrency: 4.98
> Successful transactions: 1017
> Failed transactions: 0
> Longest transaction: 0.24
> Shortest transaction: 0.08
>
> -c 20
> Transactions: 999 hits
> Availability: 100.00 %
> Elapsed time: 30.11 secs
> Data transferred: 11.08 MB
> Response time: 0.60 secs
> Transaction rate: 33.18 trans/sec
> Throughput: 0.37 MB/sec
> Concurrency: 19.75
> Successful transactions: 999
> Failed transactions: 0
> Longest transaction: 1.21
> Shortest transaction: 0.10
>
> -c 100
> Transactions: 1085 hits
> Availability: 100.00 %
> Elapsed time: 29.97 secs
> Data transferred: 9.61 MB
> Response time: 2.54 secs
> Transaction rate: 36.20 trans/sec
> Throughput: 0.32 MB/sec
> Concurrency: 91.97
> Successful transactions: 911
> Failed transactions: 0
> Longest transaction: 12.41
> Shortest transaction: 0.07
>
> -c 200
> Transactions: 1116 hits
> Availability: 100.00 %
> Elapsed time: 30.02 secs
> Data transferred: 9.10 MB
> Response time: 4.85 secs
> Transaction rate: 37.18 trans/sec
> Throughput: 0.30 MB/sec
> Concurrency: 180.43
> Successful transactions: 852
> Failed transactions: 0
> Longest transaction: 15.85
> Shortest transaction: 0.25
>
> -c 400
> Transactions: 1133 hits
> Availability: 100.00 %
> Elapsed time: 29.76 secs
> Data transferred: 8.51 MB
> Response time: 6.98 secs
> Transaction rate: 38.07 trans/sec
> Throughput: 0.29 MB/sec
> Concurrency: 265.85
> Successful transactions: 736
> Failed transactions: 0
> Longest transaction: 28.55
> Shortest transaction: 0.00
Attachments:
smime.p7s (application/pkcs7-signature, 1.9K) download
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 17:14 Mikkel Høgh <mikkel@hoegh.org>
parent: Ang Chin Han <ang.chin.han@gmail.com>
1 sibling, 0 replies; 68+ messages in thread
From: Mikkel Høgh @ 2008-10-14 17:14 UTC (permalink / raw)
To: pgsql-general@postgresql.org
--
Med venlig hilsen,
Mikkel Høgh <mikkel@hoegh.org>
On 14/10/2008, at 17.28, Ang Chin Han wrote:
> On Tue, Oct 14, 2008 at 10:05 PM, Martin Gainty
> <mgainty@hotmail.com> wrote:
> Yes, it's rather braindead. I'd rather not worry about why, but how'd
> we make Drupal use the PostgreSQL more effectively. In it's current
> form (Drupal 5 and 6), it even issues a regexp for every query, even
> before it hits the database because of some design decisions to use
> user definable table prefix as a workaround to the lack of database
> SCHEMA in MySQL: take the follow snippet as a representative Drupal
> code:
>
> $alias = db_result(db_query("SELECT dst FROM {url_alias} WHERE src =
> '%s' AND language IN('%s', '') ORDER BY language DESC", $path,
> $path_language));
>
> That's one sprintf() and a number of string replace operations to
> replace "{url_alias}" with "url_alias", as well as a number of regexp
> to sanitize the query string.
Yeah, good thing this is all going away in Drupal 6 (or much of it, at
any rate), where we are converting to PHP's PDO abstraction layer.
> Note this comment:
> /*
> * Queries sent to Drupal should wrap all table names in curly
> brackets. This
> * function searches for this syntax and adds Drupal's table prefix
> to all
> * tables, allowing Drupal to coexist with other systems in the same
> database if
> * necessary.
> */
> That's an MySQL-ism for working around legacy hosting sites offering
> only a single MySQL db bogging postgresql down...
Yeah, this is one thing I wouldn't mind if they removed. I have never
used that feature, and will probably never do so. Having multiple
applications in the same database is a mess anyways.
> Also betraying MyISAM heritage (in Drupal pgsql driver):
> /**
> * Lock a table.
> * This function automatically starts a transaction.
> */
> function db_lock_table($table) {
> db_query('BEGIN; LOCK TABLE {'. db_escape_table($table) .'} IN
> EXCLUSIVE MODE');
> }
>
> /**
> * Unlock all locked tables.
> * This function automatically commits a transaction.
> */
> function db_unlock_tables() {
> db_query('COMMIT');
> }
Yeah, sadly, our PostgreSQL driver has not historically been
maintained by someone who "knows what's he's doing". Our current
database maintainer, Larry Garfield, has some knowledge of PostgreSQL,
but he's offered pleas for input a couple of times recently. I hope to
gain some more knowledge to help his efforts.
>
>> MG>From what i've been reading the author doesnt work on drupal
>> anymore
>
> Not when Dries founded a commercial venture selling Drupal hosting
> and services.
> http://acquia.com/products-services/acquia-frequently-asked-questions#driesrole
Well, Dries is still the project lead of Drupal, and he's betting his
companys future on it, so I'm quite sure he's doing the best he can :)
>
>> MG>If we could get access to the php source maybe we could fix
>> this..?
Yeah, there's cvs.drupal.org if you're interested :)
>
Attachments:
smime.p7s (application/pkcs7-signature, 1.9K) download
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 18:23 Daniel Verite <daniel@manitou-mail.org>
parent: Mikkel Høgh <mikkel@hoegh.org>
5 siblings, 2 replies; 68+ messages in thread
From: Daniel Verite @ 2008-10-14 18:23 UTC (permalink / raw)
To: Mikkel Høgh <mikkel@hoegh.org>; +Cc: pgsql-general@postgresql.org
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'm a recent Drupal user with postgres.
What I've noticed on drupal-6.4 with Ubuntu 8.04 is that the default
postgresql.conf has:
ssl=true
and since drupal doesn't allow connecting to pgsql with unix socket
paths [1], what you get by default is probably TCP + SSL encryption.
A crude test that just connects and disconnect to a local pg server
appears to me to be 18 times faster when SSL is off.
So you might want to check if setting ssl to false makes a difference
for your test.
[1] A patch has been posted here: http://drupal.org/node/26836 , but it
seems to have gotten nowhere. The comments about pg_connect() are
depressingly lame, apparently nobody had a clue how unix socket files
should be specified, including the contributor of the patch!
Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 18:39 Mikkel Høgh <mikkel@hoegh.org>
parent: Daniel Verite <daniel@manitou-mail.org>
1 sibling, 2 replies; 68+ messages in thread
From: Mikkel Høgh @ 2008-10-14 18:39 UTC (permalink / raw)
To: pgsql-general@postgresql.org
On 14/10/2008, at 20.23, Daniel Verite wrote:
> What I've noticed on drupal-6.4 with Ubuntu 8.04 is that the default
> postgresql.conf has:
> ssl=true
> and since drupal doesn't allow connecting to pgsql with unix socket
> paths [1], what you get by default is probably TCP + SSL encryption.
> A crude test that just connects and disconnect to a local pg server
> appears to me to be 18 times faster when SSL is off.
> So you might want to check if setting ssl to false makes a
> difference for your test.
Ouch, there's a gotcha. So enabling SSL gives you SSL connections by
default, even for localhost? That’s… unexpected.
> [1] A patch has been posted here: http://drupal.org/node/26836 , but
> it seems to have gotten nowhere. The comments about pg_connect() are
> depressingly lame, apparently nobody had a clue how unix socket
> files should be specified, including the contributor of the patch!
Well, I suppose no one thought about looking at a specific path
instead of just the default location. That's what we do for MySQL as
well. I suppose that is a bit silly, but that, too, is going away in
Drupal 7 (and I won't miss it). It will do the Drupal project a lot of
good not having to maintain its own database abstraction system.
I'm going to run the test again without SSL to see how much difference
it does. Thanks for the tip.=
Attachments:
smime.p7s (application/pkcs7-signature, 1.9K) download
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 18:44 Scott Marlowe <scott.marlowe@gmail.com>
parent: Mikkel Høgh <mikkel@hoegh.org>
1 sibling, 0 replies; 68+ messages in thread
From: Scott Marlowe @ 2008-10-14 18:44 UTC (permalink / raw)
To: Mikkel Høgh <mikkel@hoegh.org>; +Cc: pgsql-general@postgresql.org
On Tue, Oct 14, 2008 at 12:39 PM, Mikkel Høgh <mikkel@hoegh.org> wrote:
> On 14/10/2008, at 20.23, Daniel Verite wrote:
>>
>> What I've noticed on drupal-6.4 with Ubuntu 8.04 is that the default
>> postgresql.conf has:
>> ssl=true
>> and since drupal doesn't allow connecting to pgsql with unix socket paths
>> [1], what you get by default is probably TCP + SSL encryption.
>> A crude test that just connects and disconnect to a local pg server
>> appears to me to be 18 times faster when SSL is off.
>> So you might want to check if setting ssl to false makes a difference for
>> your test.
>
> Ouch, there's a gotcha. So enabling SSL gives you SSL connections by
> default, even for localhost? That's… unexpected.
>
>> [1] A patch has been posted here: http://drupal.org/node/26836 , but it
>> seems to have gotten nowhere. The comments about pg_connect() are
>> depressingly lame, apparently nobody had a clue how unix socket files should
>> be specified, including the contributor of the patch!
>
> Well, I suppose no one thought about looking at a specific path instead of
> just the default location. That's what we do for MySQL as well. I suppose
> that is a bit silly, but that, too, is going away in Drupal 7 (and I won't
> miss it). It will do the Drupal project a lot of good not having to maintain
> its own database abstraction system.
>
> I'm going to run the test again without SSL to see how much difference it
> does. Thanks for the tip.
Also, look at setting up memcached. It makes a world of difference.
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 19:05 Martijn van Oosterhout <kleptog@svana.org>
parent: Daniel Verite <daniel@manitou-mail.org>
1 sibling, 0 replies; 68+ messages in thread
From: Martijn van Oosterhout @ 2008-10-14 19:05 UTC (permalink / raw)
To: Daniel Verite <daniel@manitou-mail.org>; +Cc: Mikkel Høgh <mikkel@hoegh.org>; pgsql-general@postgresql.org
On Tue, Oct 14, 2008 at 08:23:47PM +0200, Daniel Verite wrote:
> [1] A patch has been posted here: http://drupal.org/node/26836 , but it
> seems to have gotten nowhere. The comments about pg_connect() are
> depressingly lame, apparently nobody had a clue how unix socket files
> should be specified, including the contributor of the patch!
Err, presumably host=/tmp would do it. It's documented in the PG docs,
perhaps someone should update the PHP docs.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.
Attachments:
signature.asc (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 19:17 Magnus Hagander <magnus@hagander.net>
parent: Mikkel Høgh <mikkel@hoegh.org>
1 sibling, 1 reply; 68+ messages in thread
From: Magnus Hagander @ 2008-10-14 19:17 UTC (permalink / raw)
To: Mikkel Høgh <mikkel@hoegh.org>; +Cc: pgsql-general@postgresql.org
Mikkel Høgh wrote:
> On 14/10/2008, at 20.23, Daniel Verite wrote:
>> What I've noticed on drupal-6.4 with Ubuntu 8.04 is that the default
>> postgresql.conf has:
>> ssl=true
>> and since drupal doesn't allow connecting to pgsql with unix socket
>> paths [1], what you get by default is probably TCP + SSL encryption.
>> A crude test that just connects and disconnect to a local pg server
>> appears to me to be 18 times faster when SSL is off.
>> So you might want to check if setting ssl to false makes a difference
>> for your test.
>
> Ouch, there's a gotcha. So enabling SSL gives you SSL connections by
> default, even for localhost? That’s… unexpected.
Yes. To avoid it, specify "hostnossl" in the pg_hba.conf file.That will
refuse all SSL connections.
//Magnus
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 21:04 Mikkel Høgh <mikkel@hoegh.org>
parent: Magnus Hagander <magnus@hagander.net>
0 siblings, 1 reply; 68+ messages in thread
From: Mikkel Høgh @ 2008-10-14 21:04 UTC (permalink / raw)
To: Magnus Hagander <magnus@hagander.net>; +Cc: pgsql-general@postgresql.org
After disabling SSL for localhost, I ran the tests again, and the
performance gap is reduced to about 30%.
--
Kind regards,
Mikkel Høgh <mikkel@hoegh.org>
On 14/10/2008, at 21.17, Magnus Hagander wrote:
> Mikkel Høgh wrote:
>> On 14/10/2008, at 20.23, Daniel Verite wrote:
>>> What I've noticed on drupal-6.4 with Ubuntu 8.04 is that the default
>>> postgresql.conf has:
>>> ssl=true
>>> and since drupal doesn't allow connecting to pgsql with unix socket
>>> paths [1], what you get by default is probably TCP + SSL encryption.
>>> A crude test that just connects and disconnect to a local pg server
>>> appears to me to be 18 times faster when SSL is off.
>>> So you might want to check if setting ssl to false makes a
>>> difference
>>> for your test.
>>
>> Ouch, there's a gotcha. So enabling SSL gives you SSL connections by
>> default, even for localhost? That’s… unexpected.
>
> Yes. To avoid it, specify "hostnossl" in the pg_hba.conf file.That
> will
> refuse all SSL connections.
>
>
> //Magnus
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Attachments:
smime.p7s (application/pkcs7-signature, 1.9K) download
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 21:22 Merlin Moncure <mmoncure@gmail.com>
parent: Mikkel Høgh <mikkel@hoegh.org>
0 siblings, 1 reply; 68+ messages in thread
From: Merlin Moncure @ 2008-10-14 21:22 UTC (permalink / raw)
To: Mikkel Høgh <mikkel@hoegh.org>; +Cc: Magnus Hagander <magnus@hagander.net>; pgsql-general@postgresql.org
On Tue, Oct 14, 2008 at 5:04 PM, Mikkel Høgh <mikkel@hoegh.org> wrote:
> After disabling SSL for localhost, I ran the tests again, and the
> performance gap is reduced to about 30%.
ok, now consider you are on a read only load, with lots (if I read the
thread correctly) repetitive queries. mysql has a feature called the
query cache which optimizes sending the same query multiple times, but
invalidates when a table changes.
postgresql has a very efficient locking engine, so it tends to beat
mysql up when you have lots of writing going on from different users.
myisam has an edge on mainly read only data in some cases...but 30% is
a small price to pay for all the extra power you get (and that 30%
will flip quickly if you have to do any real work).
merlin
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 21:57 Mikkel Høgh <mikkel@hoegh.org>
parent: Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 68+ messages in thread
From: Mikkel Høgh @ 2008-10-14 21:57 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-general@postgresql.org, Magnus Hagander <magnus@hagander.net>
On 14/10/2008, at 23.22, Merlin Moncure wrote:
> On Tue, Oct 14, 2008 at 5:04 PM, Mikkel Høgh <mikkel@hoegh.org> wrote:
>> After disabling SSL for localhost, I ran the tests again, and the
>> performance gap is reduced to about 30%.
>
> ok, now consider you are on a read only load, with lots (if I read the
> thread correctly) repetitive queries. mysql has a feature called the
> query cache which optimizes sending the same query multiple times, but
> invalidates when a table changes.
>
> postgresql has a very efficient locking engine, so it tends to beat
> mysql up when you have lots of writing going on from different users.
> myisam has an edge on mainly read only data in some cases...but 30% is
> a small price to pay for all the extra power you get (and that 30%
> will flip quickly if you have to do any real work).
>
> merlin
Well more or less. In this case, there are only two repeating queries,
at the measly cost of 0.68ms. We do however have 31 lookups in to the
same table, where one is the dreaded "SELECT COUNT(pid) FROM
url_alias" which takes PostgreSQL a whopping 70.65ms out of the
115.74ms total for 87 queries.
Also, we're not comparing to MyISAM. The 30% is against MySQLs InnoDB-
engine, which is a lot closer to PostgreSQL feature-wise than MyISAM,
but there is of course still the query cache (which is quite small in
this case, given the low default settings, but still have a hit rate
around 80%).
In any case, I like PostgreSQL better, and MySQLs future is a bit
uncertain, with Innobase taken over by Oracle and MySQL AB taken over
by Sun, so I'm going to continue to play with PostgreSQL, also to take
advantage of stuff like vsearch, which I wouldn't be able to in like a
million years with MySQL - And thank you, Magnus, for coming to
Copenhagen to tell us about it :)
--
Kind regards,
Mikkel Høgh <mikkel@hoegh.org>
Attachments:
smime.p7s (application/pkcs7-signature, 1.9K) download
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 22:23 Greg Smith <gsmith@gregsmith.com>
parent: Kevin Murphy <murphy@genome.chop.edu>
0 siblings, 1 reply; 68+ messages in thread
From: Greg Smith @ 2008-10-14 22:23 UTC (permalink / raw)
To: Kevin Murphy <murphy@genome.chop.edu>; +Cc: pgsql-general@postgresql.org
On Tue, 14 Oct 2008, Kevin Murphy wrote:
> One thing that might help people swallow the off-putting default "toy
> mode" performance of PostgreSQL would be an explanation of why
> PostgreSQL uses its shared memory architecture in the first place.
I doubt popularizing the boring technical details behind UNIX memory
allocation sematics would help do anything but reinforce PostgreSQL's
reputation for being complicated to setup. This same problem exists with
most serious databases, as everyone who has seen ORA-27123 can tell you.
What we need is a tool to help people manage those details if asked.
http://www.ibm.com/developerworks/db2/library/techarticle/dm-0509wright/
shows a good example; DB2's "probe" tool does this little bit of tuning
for you:
DB2 has automatically updated the "shmmax" kernel
parameter from "33554432" to the recommended value "268435456".
And you're off--it bumped that from the default 32MB to 256MB. The
problem for PostgreSQL is that nobody who is motivated enough to write
such magic for a large chunk of the supported platforms has had the time
to do it yet. I'll get to that myself eventually even if nobody else
does, as this is a recurring problem I'd like to make go away.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-14 22:35 Chris <dmagick@gmail.com>
parent: Ang Chin Han <ang.chin.han@gmail.com>
1 sibling, 1 reply; 68+ messages in thread
From: Chris @ 2008-10-14 22:35 UTC (permalink / raw)
To: Ang Chin Han <ang.chin.han@gmail.com>; +Cc: pgsql-general@postgresql.org
> Note this comment:
> /*
> * Queries sent to Drupal should wrap all table names in curly brackets. This
> * function searches for this syntax and adds Drupal's table prefix to all
> * tables, allowing Drupal to coexist with other systems in the same database if
> * necessary.
> */
> That's an MySQL-ism for working around legacy hosting sites offering
> only a single MySQL db bogging postgresql down...
No it's not. It's about being able to use a single db for multiple
app's. Either I do something like that, or I have to [hardcode] change
schemas after each connection because I only have a single db & a single
db user.. which postgres/oracle[I'm sure others] support but not mysql.
Shared hosts don't give you a lot of resources, so apps build stuff like
that in to make it easier.
--
Postgresql & php tutorials
http://www.designmagick.com/
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-15 00:00 Scott Marlowe <scott.marlowe@gmail.com>
parent: Chris <dmagick@gmail.com>
0 siblings, 1 reply; 68+ messages in thread
From: Scott Marlowe @ 2008-10-15 00:00 UTC (permalink / raw)
To: Chris <dmagick@gmail.com>; +Cc: Ang Chin Han <ang.chin.han@gmail.com>; pgsql-general@postgresql.org
On Tue, Oct 14, 2008 at 4:35 PM, Chris <dmagick@gmail.com> wrote:
>
>> Note this comment:
>> /*
>> * Queries sent to Drupal should wrap all table names in curly brackets.
>> This
>> * function searches for this syntax and adds Drupal's table prefix to all
>> * tables, allowing Drupal to coexist with other systems in the same
>> database if
>> * necessary.
>> */
>> That's an MySQL-ism for working around legacy hosting sites offering
>> only a single MySQL db bogging postgresql down...
>
> No it's not. It's about being able to use a single db for multiple app's.
> Either I do something like that, or I have to [hardcode] change schemas
> after each connection because I only have a single db & a single db user..
> which postgres/oracle[I'm sure others] support but not mysql.
Are you saying you have to reconnect to change schemas? In Oracle and
PostgreSQL both you can change the current schema (or schemas for
postgresql) with a single inline command.
Also, Oracle and PostgreSQL support differing default schemas for
individual users, so you can have connections pooled by user to go to
a certain schema.
> Shared hosts don't give you a lot of resources, so apps build stuff like
> that in to make it easier.
Schemas cost virtually nothing.
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-15 00:15 Chris <dmagick@gmail.com>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 68+ messages in thread
From: Chris @ 2008-10-15 00:15 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-general@postgresql.org
> Are you saying you have to reconnect to change schemas? In Oracle and
> PostgreSQL both you can change the current schema (or schemas for
> postgresql) with a single inline command.
No I meant you have to change the schema after connecting.
Some hosts only give you one db & one user. Yeh it sucks but that's all
they give you.
Before anyone says "get a new host".. from the end user POV.. you don't
know and/or don't care about the technical details, you just want
something "that works" with what you have. It's not an ideal situation
to be in but it definitely does happen.
>> Shared hosts don't give you a lot of resources, so apps build stuff like
>> that in to make it easier.
>
> Schemas cost virtually nothing.
Neither does building a smart(er) app which lets you set a "prefix" for
all of your tables so they are grouped together.
You'll get a tiny performance hit from doing the replacement of the
prefix, but it's not going to be significant compared to everything else.
--
Postgresql & php tutorials
http://www.designmagick.com/
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-15 00:21 Scott Marlowe <scott.marlowe@gmail.com>
parent: Chris <dmagick@gmail.com>
0 siblings, 0 replies; 68+ messages in thread
From: Scott Marlowe @ 2008-10-15 00:21 UTC (permalink / raw)
To: Chris <dmagick@gmail.com>; +Cc: pgsql-general@postgresql.org
On Tue, Oct 14, 2008 at 6:15 PM, Chris <dmagick@gmail.com> wrote:
>
>> Are you saying you have to reconnect to change schemas? In Oracle and
>> PostgreSQL both you can change the current schema (or schemas for
>> postgresql) with a single inline command.
>
> No I meant you have to change the schema after connecting.
>
> Some hosts only give you one db & one user. Yeh it sucks but that's all they
> give you.
Yeah, if you could at least have multiple users with pgsql it would be
ideal. connect via user1, inherit his search_path, connect via user2,
inherit the default search path, etc...
> Before anyone says "get a new host".. from the end user POV.. you don't know
> and/or don't care about the technical details, you just want something "that
> works" with what you have. It's not an ideal situation to be in but it
> definitely does happen.
Understood. We aren't all working on large corporate in house servers
for our stuff that we can configure how we want.
>>> Shared hosts don't give you a lot of resources, so apps build stuff like
>>> that in to make it easier.
>>
>> Schemas cost virtually nothing.
>
> Neither does building a smart(er) app which lets you set a "prefix" for all
> of your tables so they are grouped together.
True. Given that MySQL treats the first of three identifiers in dot
notation as a db, and pg treats them as a schema, it would be a better
solution to have pg use schemas and mysql use dbs. Except for the
hosting providers.
> You'll get a tiny performance hit from doing the replacement of the prefix,
> but it's not going to be significant compared to everything else.
Yeah, it's likely lost in the noise. I'd be more worried about ugly queries.
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-15 01:03 Gregory Stark <stark@enterprisedb.com>
parent: Martin Gainty <mgainty@hotmail.com>
2 siblings, 0 replies; 68+ messages in thread
From: Gregory Stark @ 2008-10-15 01:03 UTC (permalink / raw)
To: Martin Gainty <mgainty@hotmail.com>; +Cc: Ang Chin Han <ang.chin.han@gmail.com>; Scott Marlowe <scott.marlowe@gmail.com>; Ivan Sergio Borgonovo <mail@webthatworks.it>; pgsql-general@postgresql.org
Martin Gainty <mgainty@hotmail.com> writes:
> MG>comments prefixed with MG>
Incidentally that's a good way to make sure people don't see your comments.
There are a few variations but the common denominator is that things prefixed
with "foo>" are quotations from earlier messages. Many mailers hide such
comments or de-emphasize them to help the user concentrate on the new
material.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-15 01:38 Gregory Stark <stark@enterprisedb.com>
parent: Greg Smith <gsmith@gregsmith.com>
0 siblings, 2 replies; 68+ messages in thread
From: Gregory Stark @ 2008-10-15 01:38 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: Kevin Murphy <murphy@genome.chop.edu>; pgsql-general@postgresql.org
Greg Smith <gsmith@gregsmith.com> writes:
> DB2 has automatically updated the "shmmax" kernel
> parameter from "33554432" to the recommended value "268435456".
This seems like a bogus thing for an application to do though. The Redhat
people seem happy with the idea but I'm pretty sure it would violate several
Debian packaging rules. Generally it seems crazy for a distribution to ship
configured one way by default but have packages change that behind the user's
back. What if the admin set SHMMAX that way because he wanted it? What happens
when a new distribution package has a new default but doesn't adjust it
because it sees the "admin" has changed it -- even though it was actually
Postgres which made the change?
> And you're off--it bumped that from the default 32MB to 256MB. The problem for
> PostgreSQL is that nobody who is motivated enough to write such magic for a
> large chunk of the supported platforms has had the time to do it yet. I'll get
> to that myself eventually even if nobody else does, as this is a recurring
> problem I'd like to make go away.
ISTM the right way to make it go away is to allocate temporary files and mmap
them instead of using sysv shared memory. Then we can mmap as much as we want.
Before we lose root privileges we can even mlock as much as we want.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's RemoteDBA services!
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-15 03:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gregory Stark <stark@enterprisedb.com>
1 sibling, 0 replies; 68+ messages in thread
From: Tom Lane @ 2008-10-15 03:07 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: Greg Smith <gsmith@gregsmith.com>; Kevin Murphy <murphy@genome.chop.edu>; pgsql-general@postgresql.org
Gregory Stark <stark@enterprisedb.com> writes:
> Greg Smith <gsmith@gregsmith.com> writes:
>> DB2 has automatically updated the "shmmax" kernel
>> parameter from "33554432" to the recommended value "268435456".
> This seems like a bogus thing for an application to do though. The Redhat
> people seem happy with the idea but I'm pretty sure it would violate several
> Debian packaging rules.
FWIW, I don't think Red Hat would accept it either.
> ISTM the right way to make it go away is to allocate temporary files and mmap
> them instead of using sysv shared memory.
That's a non-starter unless you can point to a different kernel API that
(a) is as portable as SysV and (b) offers the same amount of assistance
towards preventing multiple postmasters in the same data directory.
See many, many previous discussions in -hackers.
regards, tom lane
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-15 06:20 Greg Smith <gsmith@gregsmith.com>
parent: Gregory Stark <stark@enterprisedb.com>
1 sibling, 1 reply; 68+ messages in thread
From: Greg Smith @ 2008-10-15 06:20 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: Kevin Murphy <murphy@genome.chop.edu>; pgsql-general@postgresql.org
On Wed, 15 Oct 2008, Gregory Stark wrote:
> Greg Smith <gsmith@gregsmith.com> writes:
>> DB2 has automatically updated the "shmmax" kernel
>> parameter from "33554432" to the recommended value "268435456".
>
> This seems like a bogus thing for an application to do though.
It happens when you run a utility designed to figure out if the
application is compatible with your system and make corrections as it can
to make it work properly. If you want something like that to be easy to
use, the optimal approach to achieve that is to just barrel ahead and
notify the admin what you did.
> I'm pretty sure it would violate several Debian packaging rules.
You can wander to http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=67481
to see one of the many times someone has tried to get the SHMMAX situation
straightened out at the OS level. Quoth Martin Pitt: "Debian
packages...must not alter kernel parameters at will; if they did, they
would destroy each others settings.", followed by the standard wontfix for
actually changing anything. They did at least improve the error reporting
when the server won't start because of this problem there.
> Generally it seems crazy for a distribution to ship configured one way
> by default but have packages change that behind the user's back. What if
> the admin set SHMMAX that way because he wanted it? What happens when a
> new distribution package has a new default but doesn't adjust it because
> it sees the "admin" has changed it -- even though it was actually
> Postgres which made the change?
If there were ever any Linux distributions that increased this value from
the tiny default, you might have a defensible position here (maybe
Oracle's RHEL fork does, they might do something here). I've certainly
never seen anything besides Solaris ship with a sensible SHMMAX setting
for database use on 2008 hardware out of the box. It's really quite odd,
but as numerous probes in this area (from the above in 2000 to Peter's
recent Linux bugzilla jaunt) show the resistance to making the OS default
to any higher is considerable.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-15 16:13 Tom Lane <tgl@sss.pgh.pa.us>
parent: Greg Smith <gsmith@gregsmith.com>
0 siblings, 1 reply; 68+ messages in thread
From: Tom Lane @ 2008-10-15 16:13 UTC (permalink / raw)
To: Greg Smith <gsmith@gregsmith.com>; +Cc: Gregory Stark <stark@enterprisedb.com>; Kevin Murphy <murphy@genome.chop.edu>; pgsql-general@postgresql.org
Greg Smith <gsmith@gregsmith.com> writes:
> If there were ever any Linux distributions that increased this value from
> the tiny default, you might have a defensible position here (maybe
> Oracle's RHEL fork does, they might do something here). I've certainly
> never seen anything besides Solaris ship with a sensible SHMMAX setting
> for database use on 2008 hardware out of the box. It's really quite odd,
> but as numerous probes in this area (from the above in 2000 to Peter's
> recent Linux bugzilla jaunt) show the resistance to making the OS default
> to any higher is considerable.
I think the subtext there is that the Linux kernel hackers hate the SysV
IPC APIs and wish they'd go away. They are presently constrained from
removing 'em by their desire for POSIX compliance, but you won't get
them to make any changes that might result in those APIs becoming more
widely used :-(
Mind you, I find the SysV APIs uselessly baroque too, but there is one
feature that we have to have that is not in mmap(): the ability to
detect other processes attached to a shmem block.
regards, tom lane
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-15 18:10 Matthew T. O'Connor <matthew@tocr.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 68+ messages in thread
From: Matthew T. O'Connor @ 2008-10-15 18:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Greg Smith <gsmith@gregsmith.com>; Gregory Stark <stark@enterprisedb.com>; Kevin Murphy <murphy@genome.chop.edu>; pgsql-general@postgresql.org
Tom Lane wrote:
> I think the subtext there is that the Linux kernel hackers hate the SysV
> IPC APIs and wish they'd go away. They are presently constrained from
> removing 'em by their desire for POSIX compliance, but you won't get
> them to make any changes that might result in those APIs becoming more
> widely used :-(
>
> Mind you, I find the SysV APIs uselessly baroque too, but there is one
> feature that we have to have that is not in mmap(): the ability to
> detect other processes attached to a shmem block.
Didn't we solve this problem on Windows? Can we do a similar thing in
Unix and get ride of the SysV stuff?
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-15 18:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Matthew T. O'Connor <matthew@tocr.com>
0 siblings, 1 reply; 68+ messages in thread
From: Tom Lane @ 2008-10-15 18:15 UTC (permalink / raw)
To: Matthew T. O'Connor <matthew@tocr.com>; +Cc: Greg Smith <gsmith@gregsmith.com>; Gregory Stark <stark@enterprisedb.com>; Kevin Murphy <murphy@genome.chop.edu>; pgsql-general@postgresql.org
"Matthew T. O'Connor" <matthew@tocr.com> writes:
> Tom Lane wrote:
>> Mind you, I find the SysV APIs uselessly baroque too, but there is one
>> feature that we have to have that is not in mmap(): the ability to
>> detect other processes attached to a shmem block.
> Didn't we solve this problem on Windows?
Not terribly well --- see active thread on -hackers.
regards, tom lane
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-15 19:28 Bruce Momjian <bruce@momjian.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 68+ messages in thread
From: Bruce Momjian @ 2008-10-15 19:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Matthew T. O'Connor <matthew@tocr.com>; Greg Smith <gsmith@gregsmith.com>; Gregory Stark <stark@enterprisedb.com>; Kevin Murphy <murphy@genome.chop.edu>; pgsql-general@postgresql.org
Tom Lane wrote:
> "Matthew T. O'Connor" <matthew@tocr.com> writes:
> > Tom Lane wrote:
> >> Mind you, I find the SysV APIs uselessly baroque too, but there is one
> >> feature that we have to have that is not in mmap(): the ability to
> >> detect other processes attached to a shmem block.
>
> > Didn't we solve this problem on Windows?
>
> Not terribly well --- see active thread on -hackers.
We could allocate a small shared memory area to solve this and use
mmap() for the other shared memory usage.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-16 07:34 Tomasz Ostrowski <tometzky@batory.org.pl>
parent: Mikkel Høgh <mikkel@hoegh.org>
0 siblings, 1 reply; 68+ messages in thread
From: Tomasz Ostrowski @ 2008-10-16 07:34 UTC (permalink / raw)
To: Mikkel H?gh <mikkel@hoegh.org>; +Cc: pgsql-general@postgresql.org
On 2008-10-14 23:57, Mikkel Hogh wrote:
> one is the dreaded "SELECT COUNT(pid) FROM
> url_alias" which takes PostgreSQL a whopping 70.65ms out of the
> 115.74ms total for 87 queries.
This is stupid.
The Drupal code looks like this:
// Use $count to avoid looking up paths in subsequent calls
// if there simply are no aliases
if (!isset($count)) {
$count = db_result(db_query('SELECT COUNT(pid) FROM {url_alias}'));
}
/* ... */
if ($count > 0 /* */) {
/* one simple query */
}
It is doing count(*) type query (which requires a full table scan in
Postgres) to avoid one simple, indexable query, which is also often
cached. It has to be slower in any database, but it is much, much slower
in Postgres.
Try attached patch for drupal-5.11, and rerun your benchmarks.
Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh
Attachments:
drupal-5.11-url_alias-no-count.patch (text/x-patch, 871B) download
diff -urNP drupal-5.11.orig/includes/path.inc drupal-5.11/includes/path.inc
--- drupal-5.11.orig/includes/path.inc 2006-12-23 23:04:52.000000000 +0100
+++ drupal-5.11/includes/path.inc 2008-10-16 09:26:48.000000000 +0200
@@ -42,18 +42,12 @@
function drupal_lookup_path($action, $path = '') {
// $map keys are Drupal paths and the values are the corresponding aliases
static $map = array(), $no_src = array();
- static $count;
-
- // Use $count to avoid looking up paths in subsequent calls if there simply are no aliases
- if (!isset($count)) {
- $count = db_result(db_query('SELECT COUNT(pid) FROM {url_alias}'));
- }
if ($action == 'wipe') {
$map = array();
$no_src = array();
}
- elseif ($count > 0 && $path != '') {
+ elseif ($path != '') {
if ($action == 'alias') {
if (isset($map[$path])) {
return $map[$path];
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-16 08:34 Mikkel Høgh <mikkel@hoegh.org>
parent: Tomasz Ostrowski <tometzky@batory.org.pl>
0 siblings, 1 reply; 68+ messages in thread
From: Mikkel Høgh @ 2008-10-16 08:34 UTC (permalink / raw)
To: Tomasz Ostrowski <tometzky@batory.org.pl>
It's not only to avoid one query, but to avoid one query every time
drupal_lookup_path() is called (which is every time the system builds
a link, which can be dozens of time on a page).
So, I think it's probably a worthwhile tradeoff on MyISAM, because
such queries are fast there, and you potentially save a bunch of
queries, if you're not using URL aliases.
Is there a better way to check if a table contains anything in
PostgreSQL? Perhaps just selecting one row?
--
Kind regards,
Mikkel Hřgh <mikkel@hoegh.org>
On 16/10/2008, at 09.34, Tomasz Ostrowski wrote:
> On 2008-10-14 23:57, Mikkel Hogh wrote:
>
>> one is the dreaded "SELECT COUNT(pid) FROM
>> url_alias" which takes PostgreSQL a whopping 70.65ms out of the
>> 115.74ms total for 87 queries.
>
> This is stupid.
>
> The Drupal code looks like this:
>
> // Use $count to avoid looking up paths in subsequent calls
> // if there simply are no aliases
> if (!isset($count)) {
> $count = db_result(db_query('SELECT COUNT(pid) FROM {url_alias}'));
> }
> /* ... */
> if ($count > 0 /* */) {
> /* one simple query */
> }
>
>
> It is doing count(*) type query (which requires a full table scan in
> Postgres) to avoid one simple, indexable query, which is also often
> cached. It has to be slower in any database, but it is much, much
> slower
> in Postgres.
>
> Try attached patch for drupal-5.11, and rerun your benchmarks.
>
> Regards
> Tometzky
> --
> ...although Eating Honey was a very good thing to do, there was a
> moment just before you began to eat it which was better than when you
> were...
> Winnie the Pooh
> diff -urNP drupal-5.11.orig/includes/path.inc drupal-5.11/includes/
> path.inc
> --- drupal-5.11.orig/includes/path.inc 2006-12-23 23:04:52.000000000
> +0100
> +++ drupal-5.11/includes/path.inc 2008-10-16 09:26:48.000000000 +0200
> @@ -42,18 +42,12 @@
> function drupal_lookup_path($action, $path = '') {
> // $map keys are Drupal paths and the values are the corresponding
> aliases
> static $map = array(), $no_src = array();
> - static $count;
> -
> - // Use $count to avoid looking up paths in subsequent calls if
> there simply are no aliases
> - if (!isset($count)) {
> - $count = db_result(db_query('SELECT COUNT(pid) FROM
> {url_alias}'));
> - }
>
> if ($action == 'wipe') {
> $map = array();
> $no_src = array();
> }
> - elseif ($count > 0 && $path != '') {
> + elseif ($path != '') {
> if ($action == 'alias') {
> if (isset($map[$path])) {
> return $map[$path];
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
Attachments:
smime.p7s (application/pkcs7-signature, 1.9K) download
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-16 08:54 Tomasz Ostrowski <tometzky@batory.org.pl>
parent: Mikkel Høgh <mikkel@hoegh.org>
0 siblings, 1 reply; 68+ messages in thread
From: Tomasz Ostrowski @ 2008-10-16 08:54 UTC (permalink / raw)
To: Mikkel H?gh <mikkel@hoegh.org>; +Cc: pgsql-general <pgsql-general@postgresql.org>
On 2008-10-16 10:34, Mikkel Hogh wrote:
> It's not only to avoid one query, but to avoid one query every time
> drupal_lookup_path() is called (which is every time the system builds
> a link, which can be dozens of time on a page).
Oh, $count is static. My bad. Using count for testing for empty table is
stupid nonetheless.
There is an issue report with lengthy discussion on drupal.org:
http://drupal.org/node/196862
And a proposed patch:
http://drupal.org/files/issues/drupal_lookup_path-5.x.patch.txt
which uses "limit 1". This patch is not applied though. I don't know why.
Please retest with this patch. And keep the CC to the list in your messages.
Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-16 14:40 Stephen Frost <sfrost@snowman.net>
parent: Tomasz Ostrowski <tometzky@batory.org.pl>
0 siblings, 2 replies; 68+ messages in thread
From: Stephen Frost @ 2008-10-16 14:40 UTC (permalink / raw)
To: Tomasz Ostrowski <tometzky@batory.org.pl>; +Cc: Mikkel H?gh <mikkel@hoegh.org>; pgsql-general <pgsql-general@postgresql.org>
* Tomasz Ostrowski (tometzky@batory.org.pl) wrote:
> There is an issue report with lengthy discussion on drupal.org:
> http://drupal.org/node/196862
> And a proposed patch:
> http://drupal.org/files/issues/drupal_lookup_path-5.x.patch.txt
> which uses "limit 1". This patch is not applied though. I don't know why.
I don't see 'limit 1' anywhere in that patch.. And you don't want to
use 'limit 1' *and* count(*), that doesn't do what you're expecting
(since count(*) is an aggregate and limit 1 is applied after). You
really want to do something more like:
select true from tab1 limit 1;
And then test if you got back any rows or not (which might be what
*your* patch does, hadn't looked at it yet).
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 196B) download
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-16 15:09 DelGurth <delgurth@gmail.com>
parent: Stephen Frost <sfrost@snowman.net>
1 sibling, 1 reply; 68+ messages in thread
From: DelGurth @ 2008-10-16 15:09 UTC (permalink / raw)
To: Tomasz Ostrowski <tometzky@batory.org.pl>; Mikkel H?gh <mikkel@hoegh.org>; pgsql-general <pgsql-general@postgresql.org>
On Thu, Oct 16, 2008 at 4:40 PM, Stephen Frost <sfrost@snowman.net> wrote:
> * Tomasz Ostrowski (tometzky@batory.org.pl) wrote:
> I don't see 'limit 1' anywhere in that patch.. And you don't want to
> use 'limit 1' *and* count(*), that doesn't do what you're expecting
> (since count(*) is an aggregate and limit 1 is applied after). You
> really want to do something more like:
>
> select true from tab1 limit 1;
>
> And then test if you got back any rows or not (which might be what
> *your* patch does, hadn't looked at it yet).
>
> Thanks,
>
> Stephen
Seems Tomasz linked to the wrong patch. The patch he meant was:
http://drupal.org/files/issues/drupal_lookup_path-6.x.patch.txt
Or better... he linked to the correct patch (for drupal 5.x) but the
user Earnie made a mistake or something in the 5.x patch, seeing the
comment[1] that the patches should be the same...
Also nice to see people "benchmark" differences by just executing a
query once[2][3]....
Wessel
[1] http://drupal.org/node/196862#comment-648511
[2] http://drupal.org/node/196862#comment-649791
[3] http://drupal.org/node/196862#comment-649928
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-16 15:26 Tomasz Ostrowski <tometzky@batory.org.pl>
parent: Stephen Frost <sfrost@snowman.net>
1 sibling, 0 replies; 68+ messages in thread
From: Tomasz Ostrowski @ 2008-10-16 15:26 UTC (permalink / raw)
To: pgsql-general <pgsql-general@postgresql.org>
On 2008-10-16 16:40, Stephen Frost wrote:
>> There is an issue report with lengthy discussion on drupal.org:
>> http://drupal.org/node/196862
>> And a proposed patch:
>
> I don't see 'limit 1' anywhere in that patch..
Sorry - haven't checked it - I have checked only a 6.x version
http://drupal.org/files/issues/drupal_lookup_path-6.x.patch.txt
which is sane. This Earnie guy, the author, made a mistake in 5.x version.
I've corrected 5.x based on 6.x. I've attached it. Maybe this time I'll
get this right.
Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh
Attachments:
drupal_lookup_path-5.x.patch.txt (text/plain, 1.1K) download
Index: path.inc
===================================================================
RCS file: /cvs/drupal/drupal/includes/path.inc,v
retrieving revision 1.13
diff -u -p -r1.13 path.inc
--- path.inc 23 Dec 2006 22:04:52 -0000 1.13
+++ path.inc 4 Dec 2007 21:04:51 -0000
@@ -42,18 +42,18 @@ function drupal_init_path() {
function drupal_lookup_path($action, $path = '') {
// $map keys are Drupal paths and the values are the corresponding aliases
static $map = array(), $no_src = array();
- static $count;
+ static $have_rows;
// Use $count to avoid looking up paths in subsequent calls if there simply are no aliases
- if (!isset($count)) {
- $count = db_result(db_query('SELECT COUNT(pid) FROM {url_alias}'));
+ if (!isset($have_rows)) {
+ $have_rows = db_result(db_query('SELECT pid FROM {url_alias} LIMIT 1'));
}
if ($action == 'wipe') {
$map = array();
$no_src = array();
}
- elseif ($count > 0 && $path != '') {
+ elseif ($have_rows && $path != '') {
if ($action == 'alias') {
if (isset($map[$path])) {
return $map[$path];
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-16 15:26 Stephen Frost <sfrost@snowman.net>
parent: DelGurth <delgurth@gmail.com>
0 siblings, 1 reply; 68+ messages in thread
From: Stephen Frost @ 2008-10-16 15:26 UTC (permalink / raw)
To: DelGurth <delgurth@gmail.com>; +Cc: Tomasz Ostrowski <tometzky@batory.org.pl>; Mikkel H?gh <mikkel@hoegh.org>; pgsql-general <pgsql-general@postgresql.org>
* DelGurth (delgurth@gmail.com) wrote:
> Seems Tomasz linked to the wrong patch. The patch he meant was:
> http://drupal.org/files/issues/drupal_lookup_path-6.x.patch.txt
That's much better.
> Also nice to see people "benchmark" differences by just executing a
> query once[2][3]....
This thread is insane.. Is every change to drupal run through this kind
of design-by-committee? And the paranoia of a cost difference of 0.09ms
for something which ends up getting cached for one particular storage
engine under one particular database?!
Makes me worried about drupal's future, heh.
Stephen
Attachments:
signature.asc (application/pgp-signature, 196B) download
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-16 16:27 Scott Marlowe <scott.marlowe@gmail.com>
parent: Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 68+ messages in thread
From: Scott Marlowe @ 2008-10-16 16:27 UTC (permalink / raw)
To: DelGurth <delgurth@gmail.com>; Tomasz Ostrowski <tometzky@batory.org.pl>; Mikkel H?gh <mikkel@hoegh.org>; pgsql-general <pgsql-general@postgresql.org>
On Thu, Oct 16, 2008 at 9:26 AM, Stephen Frost <sfrost@snowman.net> wrote:
> * DelGurth (delgurth@gmail.com) wrote:
>> Seems Tomasz linked to the wrong patch. The patch he meant was:
>> http://drupal.org/files/issues/drupal_lookup_path-6.x.patch.txt
>
> That's much better.
>
>> Also nice to see people "benchmark" differences by just executing a
>> query once[2][3]....
>
> This thread is insane.. Is every change to drupal run through this kind
> of design-by-committee? And the paranoia of a cost difference of 0.09ms
> for something which ends up getting cached for one particular storage
> engine under one particular database?!
>
> Makes me worried about drupal's future, heh.
In all fairness, pgsql goes through the same kind of design by
committee process. We just have a committee of very smart people
hashing things out.
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-16 21:17 Mikkel Høgh <mikkel@hoegh.org>
parent: Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 2 replies; 68+ messages in thread
From: Mikkel Høgh @ 2008-10-16 21:17 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>
On 16/10/2008, at 18.27, Scott Marlowe wrote:
> On Thu, Oct 16, 2008 at 9:26 AM, Stephen Frost <sfrost@snowman.net>
> wrote:
>> * DelGurth (delgurth@gmail.com) wrote:
>>> Seems Tomasz linked to the wrong patch. The patch he meant was:
>>> http://drupal.org/files/issues/drupal_lookup_path-6.x.patch.txt
>>
>> That's much better.
>>
>>> Also nice to see people "benchmark" differences by just executing a
>>> query once[2][3]....
>>
>> This thread is insane.. Is every change to drupal run through this
>> kind
>> of design-by-committee? And the paranoia of a cost difference of
>> 0.09ms
>> for something which ends up getting cached for one particular storage
>> engine under one particular database?!
>>
>> Makes me worried about drupal's future, heh.
>
> In all fairness, pgsql goes through the same kind of design by
> committee process. We just have a committee of very smart people
> hashing things out.
Well, I think it's a general weak spot of almost all CMS projects.
They are all run by programmers, not DBAs. In our case, it's PHP
people excel at, (sadly) not *SQL. That's a trend I've seen a lot with
web developers. They like to program, and the database is usually
abstracted away as much as possible, to avoid having to write your own
queries.
That is, at least, the picture I'm seeing. PHP PDO, ActiveRecord,
SQLAlchemy, Django DB, Hibernate. Not all of these are equally bad,
but the main reason these are so popular is that then you don't have
to write SQL.
So, we have a lot of otherwise proficient programmers who couldn't SQL
their way out a phone booth. Sigh.
P.S.: Why are e-mails from this list not sent with a Reply-To: header
of the lists e-mail-address?
Attachments:
smime.p7s (application/pkcs7-signature, 1.9K) download
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-17 09:37 Tomasz Ostrowski <tometzky@batory.org.pl>
parent: Mikkel Høgh <mikkel@hoegh.org>
1 sibling, 1 reply; 68+ messages in thread
From: Tomasz Ostrowski @ 2008-10-17 09:37 UTC (permalink / raw)
To: Mikkel Høgh <mikkel@hoegh.org>; +Cc: pgsql-general <pgsql-general@postgresql.org>
On 2008-10-16 23:17, Mikkel Høgh wrote:
> P.S.: Why are e-mails from this list not sent with a Reply-To: header
> of the lists e-mail-address?
Because it is dangerous - too easy to send to the list, when you really
mean to send to one. Most e-mail programs have two buttons for replying:
ordinary "Reply" and "Reply to all". You're supposed to use "Reply to
all" if you want to reply to the list.
You are using BCC (blind carbon copy) instead of CC (carbon copy) to the
list address in your e-mails and you break this functionality.
Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-17 10:13 Mikkel Høgh <mikkel@hoegh.org>
parent: Tomasz Ostrowski <tometzky@batory.org.pl>
0 siblings, 2 replies; 68+ messages in thread
From: Mikkel Høgh @ 2008-10-17 10:13 UTC (permalink / raw)
To: Tomasz Ostrowski <tometzky@batory.org.pl>; +Cc: pgsql-general <pgsql-general@postgresql.org>
On 17/10/2008, at 11.37, Tomasz Ostrowski wrote:
> On 2008-10-16 23:17, Mikkel Høgh wrote:
>
>> P.S.: Why are e-mails from this list not sent with a Reply-To: header
>> of the lists e-mail-address?
>
> Because it is dangerous - too easy to send to the list, when you
> really
> mean to send to one. Most e-mail programs have two buttons for
> replying:
> ordinary "Reply" and "Reply to all". You're supposed to use "Reply to
> all" if you want to reply to the list.
Well, I think the most common use case for a mailing list is to reply
back to the list, isn't that the whole point?
Personally I find it annoying that I get two copies of each reply to
one of my posts, one that is filtered into the mailinglist folder
because it has the correct X-Mailing-List header and the other just
sits there in my inbox, wasting both bandwidth and disk space in the
process.
Besides, the if the Reply-To thing is so dangerous, why do most other
mailing lists do it?=
Attachments:
smime.p7s (application/pkcs7-signature, 1.9K) download
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-17 10:24 Tomasz Ostrowski <tometzky@batory.org.pl>
parent: Mikkel Høgh <mikkel@hoegh.org>
1 sibling, 0 replies; 68+ messages in thread
From: Tomasz Ostrowski @ 2008-10-17 10:24 UTC (permalink / raw)
To: Mikkel Høgh <mikkel@hoegh.org>; +Cc: pgsql-general <pgsql-general@postgresql.org>
On 2008-10-17 12:13, Mikkel Høgh wrote:
>> You're supposed to use "Reply to all" if you want to reply to the
>> list.
>
> Well, I think the most common use case for a mailing list is to reply
> back to the list, isn't that the whole point?
It is a point of having "Reply to all" button. With "reply-to" is it
hard to reply to one person, easy to reply to the list. Without it it is
both easy.
> Personally I find it annoying that I get two copies of each reply to
> one of my posts, one that is filtered into the mailinglist folder
> because it has the correct X-Mailing-List header and the other just
> sits there in my inbox, wasting both bandwidth and disk space in the
> process.
So set reply-to in messages you send by yourself - it will be honored.
> Besides, the if the Reply-To thing is so dangerous, why do most other
> mailing lists do it?
for i in Windows MySQL IE Sweets Alcohol etc.; do
echo "If using $i is so dangerous, why do most do it?"
done
Regards
Tometzky
--
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
Winnie the Pooh
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-17 16:12 Lincoln Yeoh <lyeoh@pop.jaring.my>
parent: Joshua D. Drake <jd@commandprompt.com>
1 sibling, 0 replies; 68+ messages in thread
From: Lincoln Yeoh @ 2008-10-17 16:12 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: pgsql-general@postgresql.org
At 09:42 PM 10/14/2008, you wrote:
>Mikkel Høgh wrote:
>>On 14/10/2008, at 11.40, Ivan Sergio Borgonovo wrote:
>
>>That might be true, if the only demographic you
>>are looking for are professional DBAs, but if
>>you're looking to attract more developers, not
>>having sensible defaults is not really a good thing.
>>While I'll probably take the time to learn more
>>about how to tune PostgreSQL, the common
>>Drupal-developer developer will probably just
>>say "Ah, this is slow, I'll just go back to MySQL…".
>
>Developers should be familiar with the platforms
>they develop for. If they are not and they are
>not willing to learn them they shouldn't use it.
If they did that there'll be a lot fewer programs out there.
There have been lots of popular stuff written by incompetent/ignorant people.
If there were such a rule, it'll just be one more
important thing they weren't aware of (or choose to ignore).
Anyway, I personally think that having "small,
medium, large" configs would be useful, at least as examples.
On a related note: is it possible to have a
config where you can be certain that postgresql
will not use more than X MB of memory (and still work OK)?
Link.
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-18 01:32 Michelle Konzack <linux4michelle@tamay-dogan.net>
parent: Mikkel Høgh <mikkel@hoegh.org>
1 sibling, 0 replies; 68+ messages in thread
From: Michelle Konzack @ 2008-10-18 01:32 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Am 2008-10-16 23:17:35, schrieb Mikkel Høgh:
> P.S.: Why are e-mails from this list not sent with a Reply-To: header
> of the lists e-mail-address?
Because if I hit <Reply-To> I want to send a private message and if I
hit <Reply-To-List> it goes to the list and <Reply-To-All> the all
people get bulk-mail from me?
If you have problems with Reply-To: get a REAL MUA, which support
<Reply-To-List>
Thanks, Greetings and nice Day/Evening
Michelle Konzack
Systemadministrator
24V Electronic Engineer
Tamay Dogan Network
Debian GNU/Linux Consultant
--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack Apt. 917 ICQ #328449886
+49/177/9351947 50, rue de Soultz MSN LinuxMichi
+33/6/61925193 67100 Strasbourg/France IRC #Debian (irc.icq.com)
Attachments:
signature.pgp (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 68+ messages in thread
* Re: Drupal and PostgreSQL - performance issues?
@ 2008-10-18 01:34 Michelle Konzack <linux4michelle@tamay-dogan.net>
parent: Mikkel Høgh <mikkel@hoegh.org>
1 sibling, 0 replies; 68+ messages in thread
From: Michelle Konzack @ 2008-10-18 01:34 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Am 2008-10-17 12:13:00, schrieb Mikkel Høgh:
> Besides, the if the Reply-To thing is so dangerous, why do most other
> mailing lists do it?
Curently I am on 117 Mailinglists and ONLY 2 Winsuck lists do this crap.
So, from what are you talking about?
Thanks, Greetings and nice Day/Evening
Michelle Konzack
Systemadministrator
24V Electronic Engineer
Tamay Dogan Network
Debian GNU/Linux Consultant
--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack Apt. 917 ICQ #328449886
+49/177/9351947 50, rue de Soultz MSN LinuxMichi
+33/6/61925193 67100 Strasbourg/France IRC #Debian (irc.icq.com)
Attachments:
signature.pgp (application/pgp-signature, 188B) download
^ permalink raw reply [nested|flat] 68+ messages in thread
end of thread, other threads:[~2008-10-18 01:34 UTC | newest]
Thread overview: 68+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-10-13 03:57 Drupal and PostgreSQL - performance issues? Mikkel Høgh <mikkel@hoegh.org>
2008-10-13 04:37 ` John DeSoi <desoi@pgedit.com>
2008-10-13 04:48 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-10-13 08:43 ` Greg Smith <gsmith@gregsmith.com>
2008-10-13 08:51 ` Mikkel Høgh <mikkel@hoegh.org>
2008-10-13 10:17 ` Greg Smith <gsmith@gregsmith.com>
2008-10-13 04:54 ` Stephen Frost <sfrost@snowman.net>
2008-10-13 06:00 ` Mikkel Høgh <mikkel@hoegh.org>
2008-10-13 14:19 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-10-13 14:23 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-10-13 14:49 ` Simon Waters <simonw@zynet.net>
2008-10-13 21:21 ` Greg Smith <gsmith@gregsmith.com>
2008-10-13 05:14 ` Uwe C. Schroeder <uwe@oss4u.com>
2008-10-13 07:02 ` Ivan Sergio Borgonovo <mail@webthatworks.it>
2008-10-14 02:45 ` Joshua Tolley <eggyknap@gmail.com>
2008-10-14 09:40 ` Ivan Sergio Borgonovo <mail@webthatworks.it>
2008-10-14 12:56 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-10-14 13:47 ` Ang Chin Han <ang.chin.han@gmail.com>
2008-10-14 14:05 ` Martin Gainty <mgainty@hotmail.com>
2008-10-14 14:20 ` Mikkel Høgh <mikkel@hoegh.org>
2008-10-14 15:28 ` Ang Chin Han <ang.chin.han@gmail.com>
2008-10-14 17:14 ` Mikkel Høgh <mikkel@hoegh.org>
2008-10-14 22:35 ` Chris <dmagick@gmail.com>
2008-10-15 00:00 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-10-15 00:15 ` Chris <dmagick@gmail.com>
2008-10-15 00:21 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-10-15 01:03 ` Gregory Stark <stark@enterprisedb.com>
2008-10-14 14:20 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-10-14 16:44 ` Ivan Sergio Borgonovo <mail@webthatworks.it>
2008-10-14 17:05 ` Mikkel Høgh <mikkel@hoegh.org>
2008-10-14 13:27 ` Mikkel Høgh <mikkel@hoegh.org>
2008-10-14 13:42 ` Joshua D. Drake <jd@commandprompt.com>
2008-10-14 14:28 ` Ivan Sergio Borgonovo <mail@webthatworks.it>
2008-10-17 16:12 ` Lincoln Yeoh <lyeoh@pop.jaring.my>
2008-10-14 14:02 ` Greg Smith <gsmith@gregsmith.com>
2008-10-14 14:59 ` Kevin Murphy <murphy@genome.chop.edu>
2008-10-14 22:23 ` Greg Smith <gsmith@gregsmith.com>
2008-10-15 01:38 ` Gregory Stark <stark@enterprisedb.com>
2008-10-15 03:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-10-15 06:20 ` Greg Smith <gsmith@gregsmith.com>
2008-10-15 16:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-10-15 18:10 ` Matthew T. O'Connor <matthew@tocr.com>
2008-10-15 18:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-10-15 19:28 ` Bruce Momjian <bruce@momjian.us>
2008-10-14 13:38 ` Greg Smith <gsmith@gregsmith.com>
2008-10-13 10:36 ` Ang Chin Han <ang.chin.han@gmail.com>
2008-10-14 18:23 ` Daniel Verite <daniel@manitou-mail.org>
2008-10-14 18:39 ` Mikkel Høgh <mikkel@hoegh.org>
2008-10-14 18:44 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-10-14 19:17 ` Magnus Hagander <magnus@hagander.net>
2008-10-14 21:04 ` Mikkel Høgh <mikkel@hoegh.org>
2008-10-14 21:22 ` Merlin Moncure <mmoncure@gmail.com>
2008-10-14 21:57 ` Mikkel Høgh <mikkel@hoegh.org>
2008-10-16 07:34 ` Tomasz Ostrowski <tometzky@batory.org.pl>
2008-10-16 08:34 ` Mikkel Høgh <mikkel@hoegh.org>
2008-10-16 08:54 ` Tomasz Ostrowski <tometzky@batory.org.pl>
2008-10-16 14:40 ` Stephen Frost <sfrost@snowman.net>
2008-10-16 15:09 ` DelGurth <delgurth@gmail.com>
2008-10-16 15:26 ` Stephen Frost <sfrost@snowman.net>
2008-10-16 16:27 ` Scott Marlowe <scott.marlowe@gmail.com>
2008-10-16 21:17 ` Mikkel Høgh <mikkel@hoegh.org>
2008-10-17 09:37 ` Tomasz Ostrowski <tometzky@batory.org.pl>
2008-10-17 10:13 ` Mikkel Høgh <mikkel@hoegh.org>
2008-10-17 10:24 ` Tomasz Ostrowski <tometzky@batory.org.pl>
2008-10-18 01:34 ` Michelle Konzack <linux4michelle@tamay-dogan.net>
2008-10-18 01:32 ` Michelle Konzack <linux4michelle@tamay-dogan.net>
2008-10-16 15:26 ` Tomasz Ostrowski <tometzky@batory.org.pl>
2008-10-14 19:05 ` Martijn van Oosterhout <kleptog@svana.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox