{"id":1497,"date":"2018-01-19T19:22:54","date_gmt":"2018-01-19T19:22:54","guid":{"rendered":"http:\/\/goofy-trucks.flywheelsites.com\/mysql-and-postgresql-compared-page-2\/"},"modified":"2018-01-19T19:24:50","modified_gmt":"2018-01-19T19:24:50","slug":"mysql-and-postgresql-compared-page-2","status":"publish","type":"post","link":"https:\/\/phpbuilder.com\/mysql-and-postgresql-compared-page-2\/","title":{"rendered":"MySQL and PostgreSQL Compared Page 2"},"content":{"rendered":"
\n
\n
By Tim Perdue<\/div>\n
on July 30, 2000<\/div>\n<\/p><\/div>\n
\n

Methodology<\/h2>\n
\nTo try to make this as realistic as possible, I took an actual page from a
\nwebsite and made it portable across both MySQL and Postgres. This basically
\nmeant replacing all mysql_query() calls with pg_exec(). This page involves
\na lot of selects and joins, as probably most pages on a typical website do.<\/div>\n
\nOnce the test page was up and debugged, I then ran “ab”, the “Apache Benchmarking”
\nutility, from my workstation across my 100-mbit LAN to the quad-xeon machine. To
\nget an idea of scalability under load, I varied the “concurrent connections”
\non ab from 10-120, while leaving the number of page views steady at 1000.<\/div>\n
\nTo more closely simulate real-world use, I set up a random-number generator in
\nthe script that inserts a row into the database on 10% of the page views. My
\nown numbers on PHPBuilder show that about 10% of all pages in the discussion
\nforums are for posting new messages.<\/div>\n
\nFurther, as mentioned above, I used real data from a production database. You
\ncan’t get a whole lot more realistic than this scenario.<\/div>\n

The Numbers<\/h2>\n
\n[Raw Test Results]<\/a><\/div>\n
\nThe most interesting thing about my test results was to see how much of a load
\nPostgres could withstand before giving any errors. In fact, Postgres seemed to
\nscale 3 times higher than MySQL before giving any errors at all. MySQL
\nbegins collapsing at about 40-50 concurrent connections, whereas Postgres
\nhandily scaled to 120 before balking. My guess is, that Postgres could have gone
\nfar past 120 connections with enough memory and CPU.<\/div>\n
\nOn the surface, this can appear to be a huge win for Postgres, but if you
\nlook at the results in more detail, you’ll see that Postgres took up to 2-3
\ntimes longer to generate each page, so it needs to scale 2-3 times higher
\njust to break even with MySQL. So in terms of max numbers of pages generated
\nconcurrently without giving errors, it’s pretty much a dead heat between
\nthe two databases. In terms of generating one page at a time, MySQL does it
\nup to 2-3 times faster.<\/div>\n
\nAnother interesting point was that MySQL crumbles faster in the “10%
\ninsert” test described above. Research reveals that MySQL locks the entire
\ntable when an insert occurs, while Postgres has a pretty nifty “better than
\nrow-level locking” feature. This difference quickly causes MySQL to pile up concurrent
\nconnections and thus collapse. The same is true if you are doing a large select
\nout of a database while another process is inserting into that table. Postgres
\nis completely unfazed, while MySQL piles up connections until it falls apart
\nlike a house of cards.<\/div>\n
\nFor those of you wondering about persistent connections in PHP, they don’t
\nappear to benefit MySQL that much, whereas they are a clear boon for Postgres.
\nIn fact, Postgres benchmarked as much as 30% faster just by using persistent
\nconnections. That tells me that Postgres has a tremendous amount of overhead in
\nits connection-opening and authentication process. Some of this may be the
\nfault of Linux and its relatively lame process scheduler. Still, MySQL on the
\nsame box beat it handily no matter how you look at it.<\/div>\n<\/div>\n

<\/p>\n

\n
\u00ab Previous Page<\/a><\/div>\n
1<\/a> <\/div>\n
| <\/div>\n
2<\/div>\n
| <\/div>\n
3<\/a> <\/div>\n
| <\/div>\n
4<\/a> <\/div>\n
| <\/div>\n
5<\/a> <\/div>\n
Next Page \u00bb<\/a><\/div>\n<\/div><\/div>\n","protected":false},"excerpt":{"rendered":"

By Tim Perdue on July 30, 2000 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… <\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-1497","post","type-post","status-publish","format-standard","hentry","category-tutorials"],"_links":{"self":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1497","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/comments?post=1497"}],"version-history":[{"count":1,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1497\/revisions"}],"predecessor-version":[{"id":3341,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/posts\/1497\/revisions\/3341"}],"wp:attachment":[{"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/media?parent=1497"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/categories?post=1497"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/phpbuilder.com\/wp-json\/wp\/v2\/tags?post=1497"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}