Newsgroup: it.comp.os.linux.software
Titolo: POSTGRES Vs MySQL [Molto Lungo]

Sto cercando di raccogliere dati per scegliere su quale database free
far girare la mia applicazione, che dovrebbe gestire un db per la
spedizione di pacchi di un coriere espresso.
Tanto per capirci, ogni pacco deve tenere tracccia di tutte le tappe che
fà, e a volte sono anche 20, e in media si mandano dai 50 ai 100 pacchi
al giorno.
Con query che non sempre sono semplici.
Le richieste quindi sono:
Possibilità di utilizzare TRIGGER, 
Alta affidabilità ( in termini di sicurezza del dato )
Possibilità di gestire tabelle complesse e voluminose.
In base alle vostre esperienze che cosa mi consigliate? Io vi posto un
articolo preso in rete, che compare POSTGRES e MySQL, e spero non annoi
troppo.
Mi piacerebbe avere anche altri termini di paragone, sopratutto per
quanto riguarda i limiti fisici dei due db.
---------------------------------------------------------------
MySQL and PostgreSQL Compared
Methodology
To try  to make  this as  realistic as  possible, I  took an  actual
page from a
website and  made it  portable across  both MySQL  and Postgres.  This
basically
meant replacing all mysql_query() calls with pg_exec(). This page
involves a lot
of selects and joins, as probably most pages on a typical website do.
Once  the  test  page  was  up  and  debugged,  I  then  ran  "ab",  the
"Apache
Benchmarking" utility, from  my workstation across  my 100-mbit LAN  to
the quad
-xeon  machine.  To  get  an  idea  of  scalability  under  load,  I
varied the
"concurrent connections"  on ab  from 10-120,  while leaving  the number
of page
views steady at 1000.
To more closely simulate real-world use,  I set up a random-number
generator  in
the script that inserts a row into the database on 10% of the page
views. My own
numbers on PHPBuilder show that about 10% of all pages in the discussion
forums
are for posting new messages.
Further, as mentioned above,  I used real data  from a production
database.  You
can't get a whole lot more realistic than this scenario.
The Numbers
[Raw Test Results]
The most interesting thing about my test  results was to see how much of
a load
Postgres could withstand before giving  any errors. In fact, Postgres
seemed to
scale 3 times higher  than MySQL before giving  any errors at all.
MySQL begins
collapsing  at  about  40-50 concurrent  connections,  whereas  Postgres
handily
scaled to 120  before balking. My  guess is, that  Postgres could have
gone far
past 120 connections with enough memory and CPU.
On the surface, this can appear to be  a huge win for Postgres, but if
you  look
at the results  in more detail,  you'll see that  Postgres took up  to
2-3 times
longer to  generate each  page, so  it needs  to scale  2-3 times higher
just to
break  even  with  MySQL.  So  in  terms  of  max  numbers  of  pages
generated
concurrently without giving errors, it's pretty much a dead heat between
the two
databases. In terms of generating  one page at a time,  MySQL does it up
to  2-3
times faster.
Another interesting  point was  that MySQL  crumbles faster  in the "10%
insert"
test described above. Research reveals that MySQL locks the entire table
when an
insert occurs, while Postgres has a pretty nifty "better than row-level
locking"
feature. This difference quickly causes MySQL to pile up concurrent
connections
and thus collapse. The  same is true if  you are doing a  large select
out of  a
database  while  another  process  is inserting  into  that  table.
Postgres is
completely unfazed, while MySQL piles up connections until it falls
apart like a
house of cards.
For  those of  you wondering  about persistent  connections in  PHP,
they  don't
appear to benefit MySQL that much,  whereas they are a clear boon  for
Postgres.
In fact, Postgres  benchmarked as much  as 30% faster  just by using
persistent
connections. That tells me that Postgres has a tremendous amount of
overhead  in
its connection-opening and authentication process. Some of this may be
the fault
of Linux and its relatively lame process scheduler. Still, MySQL on the
same box
beat it handily no matter how you look at it. 
[Raw Test Results]
---------------------------------------------------------- --
Test Results
concurrency w/pconnects:
10 cli - 10.27 pg/sec 333.69 kb/s
20 cli - 10.24 pg/sec 332.86 kb/s
30 cli - 10.25 pg/sec 333.01 kb/s
40 cli - 10.0  pg/sec 324.78 kb/s
50 cli - 10.0  pg/sec 324.84 kb/s
75 cli - 9.58  pg/sec 311.43 kb/s
90 cli - 9.48  pg/sec 307.95 kb/s
100 cli - 9.23 pg/sec 300.00 kb/s
110 cli - 9.09 pg/sec 295.20 kb/s
120 cli - 9.28 pg/sec 295.02 kb/s (2.2% failure)
concurrency w/10% inserts & pconnects:
30 cli - 9.97  pg/sec 324.11 kb/s
40 cli - 10.08 pg/sec 327.40 kb/s
75 cli - 9.51  pg/sec 309.13 kb/s
MySQL
Concurrency Tests w/pconnects:
30 cli - 16.03 pg/sec   521.01 kb/s
40 cli - 15.64 pg/sec   507.18 kb/s
50 cli - 15.43 pg/sec   497.88 kb/s
75 cli - 14.70 pg/sec   468.64 kb/s
90  - mysql dies
110 - mysql dies
120 - mysql dies
Concurrency Tests w/o pconnects:
10 cli - 16.55 pg/sec 537.63 kb/s
20 cli - 15.99 pg/sec 519/51 kb/s
30 cli - 15.55 pg/sec 505.19 kb/s
40 cli - 15.46 pg/sec 490.01 kb/s 47 failures
50 cli - 15.59 pg/sec 482.24 kb/s 82 failures
75 cli - 17.65 pg/sec 452.08 kb/s 363 failures
90 cli - mysql dies
concurrency w/10% inserts & pconnects:
20 cli - 16.37 pg/sec 531.79 kb/s
30 cli - 16.15 pg/sec 524.64 kb/s
40 cli - 22.04 pg/sec 453.82 kb/sec 378 failures
---------------------------------------------------------- -- [Raw Test
Results]
MySQL
The numbers for MySQL ring true with what most people already know: it's
a fast,
although  lightweight  database  that  will probably  serve  well  for
the vast
majority of web sites. However, if you plan on having a high-traffic
site  (say,
greater than 500,000 pages per day), then forget MySQL as it can tend to
fold up
and die  under load.  Anyone who  has ever  visited slashdot  can attest
to the
fragility of its setup (mod_perl and MySQL).
But again, the vast, vast majority of web sites fall well under the
15-pages per
second demonstrated by MySQL here. If you ever surpass a sustained 15
pages  per
second, you'll  be delighted  to fork  over the  cash for  a bigger
server or an
Oracle license.
Wins
Obviously, the  advantage MySQL  has over  Postgres is  performance. It
also has
some more powerful admin tools  included in the distribution (mysqladmin
allows
you  to  watch processes  and  queries in-progress),  like  hot backup,
a  file
corruption recovery tool and a couple others.
I'm also a  fan of MySQL's  command-line tools. You  can see database
and table
structures using describe and show commands. Postgres' commands are less
obvious
( \d to show a list of tables for instance).
Limitations
The first  thing you  hear from  hard-core database  gurus is  that
MySQL  lacks
transactions,  rollbacks, and  subselects. You'll  really miss
transactions if
you're trying to write a banking application, accounting application, or
trying
to maintain some  sort of counter  that needs to  increment linearly
over  time.
Forget attempting any  of those with  released versions of  MySQL (it
should  be
noted  that  the  unstable  3.23.x  series  of  MySQL  now  includes
transaction
support).
For many, if not most, web sites out there, MySQL's limitations can be
overcome
with a little  elbow grease on  the part of  the developer. The  primary
feature
you'll miss  in MySQL  is powerful  subselect syntax  that is  present
in almost
every other production  database. If I  had a nickle  for every time  I
could've
used subselects in MySQL,  I'd be able to  buy a case or  two of beer.
In  other
words, this missing feature can be a pain in the neck, but it can be
overcome.
Stability
MySQL loses  points in  the long-term  stability department.  Simply
put,  MySQL
gives up the  ghost randomly and  for no obvious  reason after running
for semi
-long  periods of  time (say  30-60 days).  Many developers  will
compile  MySQL
"statically" for just that reason, and doing so has helped some people.
That problem again can be overcome with  a good pager or a simple
crontab  entry
that kills and restarts MySQL monthly.  Not that I find that at  all
acceptable,
but it is a solution.
Where MySQL loses points in the daemon robustness department, it makes
up for it
by apparently never corrupting its data  files. The last thing you want
is your
precious data files fouled randomly, and MySQL does well here. In over a
year of
running  MySQL,  I  haven't  ever  seen  a  single  case  of  database
or index
corruption. In the  same timeframe, I  have done 2  or 3 recoveries  of
a couple
different  Postgres  databases.  (irregardless,  backups  are  always
your best
friend, as shown by the database fiasco here on PHPBuilder) 
PostgreSQL
The results for Postgres might surprise  a few people, as Postgres has
somewhat
of a negative reputation among some web developers (initial releases of
Postgres
had widely-rumored issues in addition  to laggard performance).
According to  my
experience, and these benchmarks, most of that reputation is unfounded.
In fact,
it appears  that PostgreSQL  withstands up  to 3  times the  load that
MySQL can
before throwing any errors -- on the same hardware/OS combination.
Postgres happily chugs along at  roughly 10 pages/second, enough to
serve about
400,000 pages/day,  assuming a  regular traffic  curve with  the peak
at 2x the
bottom. That's an awful lot of pages and is far beyond what most people
will see
on their websites. In addition,  most of the pages on  your site will
not be  as
complex as the one  in this test. As  with MySQL, you'll be  happy to
pay for  a
hardware upgrade if you pass this ceiling. Because of Postgres'
architecture, it
could probably continue to scale up the more processors and RAM you give
it.
Wins
Well, postgres has  some extremely advances  features when shown  next
to MySQL.
While I don't use most of the features myself, they are available for
the  truly
-hardcore developers out there. Many developers don't even realize what
they're
missing by not having some of these features available.
An example of where you should be  using a transaction is if you are
doing more
than one update/insert/delete in a sequence. For instance, your script
inserts a
new user into your user table, then also inserts a row in another table,
and you
update a flag somewhere  else. In this case,  if the first insert
succeeds, but
the second fails, what do you  do? With Postgres, you could Rollback
the entire
operation and show  an appropriate error.  With MySQL, you  would wind
up  in an
invalid state, unless you program in  a bunch of logic to handle  the
situation.
In real-world use, most queries don't fail unless you're a lousy
programmer, and
if the second query did fail, the results may not be dire (unless we're
talking
about an accounting/banking/critical application where  there can be no
risk  of
incorrect data).
Anyway, foreign-key support is now in  Postgres 7.0+, which means that
when  you
insert a row, the database can do some fairly impressive validation
checks. Same
if you delete a row - it just plain won't let you delete a row if
another  table
is depending on it. I love this idea and can envision rewriting entire
websites
just to take advantage of this feature.
Triggers  and views  are interesting  and powerful  tools that  can be
used in
Postgres, but not MySQL. I haven't used either one, but I can think of a
hundred
uses for Views if I were to redesign SourceForge from the ground up on
Postgres.
Limitations
The primary limitation with Postgres is  not its performance (as most
web  sites
will never run into  that barrier), but hard-coded  limits like the 8k
row size
limit  (which  probably  dates  back to  its  earliest  days).  When I
designed
Geocrawler.com on Postgres, I had to segment large emails into 8k chunks
to work
around this  lame limitation.  Also, by  default, Postgres  is compiled
to only
support  32  connections, which  is  not enough  for  a high-traffic
web  site,
especially when you consider that  postgres delivers each page much
more slowly
than MySQL.
One other limitation may bug a lot of PHP users - Postgres has no
equivalent  to
MySQL's mysql_insertid()  function call.  That is,  if you  insert a
row into a
MySQL database, MySQL will hand you back the primary key ID for that
row.  There
is an extremely round-about way of  doing this in Postgres, but it's  a
headache
and is probably slow if used a lot.
Stability
Postgres will  run smoothly  for extended  periods of  time without
trouble. My
Postgres 6.5.3  install has  run for  90 days  without blinking  on my
tired old
PowerMac 8500, while getting about  50-100,000 pages per day. And  when
postgres
gets loaded,  it just  bogs down,  it doesn't  quit and  give up the
ghost under
stress.
The problem  with Postgres  is that  when you  do have  a problem  with
it, it's
usually really bad. Like  a fubar database file  or, more commonly, a
corrupted
index  (which  can  frequently be  dropped/rebuilt).  I  have
encountered  other
serious problems  with older  versions of  postgres (6.4.x)  where
multiple same
numbers were inserted  into a primary  key (something that  should be
impossible
under any circumstance).
There have also  been problems with  Postgres where you  can wind up
with "half
-baked" indexes, tables, etc that you cannot drop or get rid of. I have
not seen
these yet on Postgres 7, but I haven't used it enough to know. 
Conclusion
These tests pretty  much confirmed what  I already knew  - both
databases  serve
quite well  for the  vast majority  of web  sites out  there. Both  are
actually
extremely fast when compared to desktop databases like FileMaker and MS
Access.
Both are now free and supported by an active developer community.
To  choose  between  the  two  databases,  you  first  need  to
understand your
scalability limits and whether you  need the transaction support of
Postgres or
the large-text-area support in MySQL. You may need both, in which case
you  have
to wait for future stable releases of both databases.
It's interesting to note that the two databases appear to be converging
to  meet
in the middle  somewhere. While MySQL  is working on  adding transaction
support
and slowly adding  features like subselects,  Postgres is making
headway in the
performance and stability departments.
Finally,  for  the  hardest-core developers,  Postgres  could  be pretty
slick.
Foreign keys, views, subselects, and transactions  can all be pretty
cool --  if
you need them and you will make any use of them. If you don't need them
or won't
use  them,  then  you're  probably  better  off  with  MySQL  and  its
superior
performance. 
-- 
Posted via Mailgate.ORG Server - 
http://www.Mailgate.ORG

 Possibilità di gestire tabelle complesse e voluminose.
La scalabilita` di PostgreSQL non puo` essere messa in discussione.
-- 
A risentirci
\  Davide G. Borin
\\ !!Scudo anti-spam attivo!! Togliere le x e "eliminami" dall'indirizzo
\\\_____________________________________________________________________

 
non ho mai avuto problemi in tal senso neanche con mysql.
ciao

 La scalabilita` di PostgreSQL non puo` essere messa in discussione.
Ok, mi hai quasi convinto :-)
L'ultima cosa che vorrei sapere è quanto, in base alla tua esperienza
può diventare voluminoso, un database POSTGRES, prima di esere
inutilizzabile.
Ovvero, in termini di dimensioni (sia globali MB) che di volume di
record ( per tabella ).
E una volta raggiunta la "capienza massima" quali sono le prospettive?


> non ho mai avuto problemi in tal senso neanche con mysql.
Ragazzi cosa intendete esattamente per scalabilità? PErche nella mia
testa  sentendo scalabilità, si apre un mondo di server db in cluster,
ma non penso che voi vogliate dire questo.... vero?


  
si si!
il cluster con mysql viene "naturale"
un pc master e vari slave che contengono gli stessi dati x ottimizzare i 
tempi di query, ridondanza, etc.
provato.
ciao

 Le richieste quindi sono:
 
Sulla base di queste richieste escludi automaticamente MySQL. Cmq, da
parte mia, ti consiglio di buttare un occhio anche a Firebird e a SAP
DB (tutti e due opensource)
Henryx
-- 
GnuPG fingerprint = 42F9 521B 63C7 89FE 354C  89FA 4E49 3E2D 2EB6 B014

 DB (tutti e due opensource)
Ma Firebird l'ho già guardato, e non mi è smebrato molto eclatante come
db, diciamo che è appena arrivato alla versione 1. se no sbaglio, e per
un server di produzione aspetterei qualcosa di più stabile.
Di SAP francamente non ne ho sentito parlare molto bene, anzi molte
aziende che si sono spostate su sap si lamentano molto.
Diciamo che la mia domanda riguardava le prestazioni a livello di
dimensioni del DB che posso gestire.
Più che un database perfomante in termini di query, mi serve un database
ROBUSTO, che sopporti molto bene carichi di lavoro grossi, sia in
termini di query, complesse, che in termini di inserimenti.
Poi che vada piano, non mi importa, a quello ci pensa l'hardware, e al
massimo un cluster.
In pratica il punto è che non vorrei implementare una soluzione e poi
dovermi trovare dopo un anno a dover migrare sotto un altro db ( magari
closed.source) per una scelta sbagliata di software.
Migrare si, ma diciamo dopo un 2/3 anni di utilizzo, e per una
piattaforma di tipo AS400 o ORACLE, questo ovviamente se, se ne rendesse
indispenabile l'implementazione, perchè spinti da una incredibile mole
di dati.


 Alta affidabilità ( in termini di sicurezza del dato )
Dimenticavo una cosa importante a riguardo. Non ho esperienze con MySQL
a riguardo, ma con PostgreSQL mi e` successo varie volte di ritrovarmi
con crash di sistema (generalmente per mancanza di alimentazione) a DB
acceso. Non ricordo di aver mai perso dati, ma di sicuro PostgreSQL e`
(o era) abbastanza sensibile alle corruzioni sui propri file, quindi ti
consiglio in ogni caso di fare un bel pg_dumpall periodicamente (almeno
una volta al giorno). Ripeto, io non ne ho mai avuto bisogno, pero`...


 E una volta raggiunta la "capienza massima" quali sono le prospettive?
Onestamente non so se esista ancora una capienza massima... 8-)


 ma non penso che voi vogliate dire questo.... vero?
Io intendo per scalabile un server che rallenta in maniera lineare 
all'aumentare del numero di query contemporanee e delle dimensioni del 
database.


 non ho mai avuto problemi in tal senso neanche con mysql.
Ripeto, non sono aggiornatissimo, ma mi risulta che sotto forte 
pressione (cioe` con un gran numero di query contamporanee) MySQL 
tendesse a "cedere", mentre PostgreSQL semplicemente rallenta in modo 
quasi lineare.
Oltre a questo non so, infatti qui ho parlato solo di PostgreSQL.


 un server di produzione aspetterei qualcosa di più stabile.
Se dovessimo giudicare la qualita` di un software dal numero di
versione, Linux sarebbe ancora da scartare come soluzione e win
sarebbe un esmpio di stabilita` innata.
E` vero che Firebird e` ancora alla versione 1, ma questo e` un
argomento irrilevante, in quanto si basa su Interbase 6 e, fino
a oggi, le differenze tra i due sono veramente minime
