Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
Mark Rae wrote: I would say that doing the concurrency tests is probably the most important factor in comparing other databases against MySQL, as MySQL will almost always win in single-user tests. E.g. here are some performance figures from tests I have done in the past. This is with a 6GB databse on a 4CPU Itanium system running a mixture of read-only queries, but it is fairly typical of the behaviour I have seen. The Oracle figures also scaled in a similar way to postgres. Clients 1 2 3 4 6 812163264 128 --- mysql-4.1.11.00 1.41 1.34 1.16 0.93 1.03 1.01 1.00 0.94 0.86 0.80 pg-7.4.1 0.65 1.27 1.90 2.48 2.45 2.50 2.48 2.51 2.49 2.39 2.38 Would be interesting to know about the tuning of the MySQL, I guess that buffers for indexing and sort is well setup, but what about thread caching? Knowing that will once in a while you will have a connection burst you can tell mysql to cache thread so that it can save time next time it needs them. -- Robin Ericsson http://robin.vill.ha.kuddkrig.nu/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
In article [EMAIL PROTECTED], Rick Schumeyer [EMAIL PROTECTED] writes: These results are for a single process populating a table with 934k rows, and then performing some selects. I also compared the effect of creating indexes on some of the columns. I have not yet done any testing of transactions, multiple concurrent processes, etc. Bad. That's where things begin to get interesting. I did not make any changes to the default config settings. Bad. On modern hardware MySQL performs quite good with its default settings; PostgreSQL performs horribly without some tuning. I used pg 8.0.1 and mysql 5.0.2 alpha. Bad. As you noticed, MySQL 5.x is Alpha and not very stable. I'd suggest using MySQL 4.1.10 instead. I compiled pg from source, but I downloaded an binary for mysql. Good. Since MySQL is multithreaded, it's much harder to compile than PostgreSQL. The MySQL guys actually recommend using their binaries. select count(*) from data where fid=2 and rid=6; count = 100 select count(*) from data where x 5000 and x 5500; count = 35986 select count(*) from data where x 5000 and x 5020; count = 1525 Bad. These queries are exactly the sore point of PostgreSQL and MySQL/InnoDB, whereas MySQL/MyISAM really shines. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
Joshua D. Drake [EMAIL PROTECTED] writes: Also, a 32-bit machine can only hold so much RAM. If I'm correct, there are ways to address more memory than that on a 32 bit machine, but I wonder at what cost? In other words, is it a good idea to address more than 4GB on a 32 bit machine? If not, is it a reasonable choice to invest in 64 bit if you want 4GB of RAM? Or are you better off just spending the money on RAID and staying at 4GB? It entirely depends on the database but not that the 32bit limit of 4GB is per CPU. So if you have 4 CPUs you can have 16GB of ram. It's actually per-process, not per-CPU. The x86 ISA only has 32-bit address registers, so a process can only see 4GB max. The PAE extensions that came in with the PPro allow for more address bits in the page tables, so each process sees a different subset of a larger pool of physical RAM. The implication of this for PostgreSQL on x86 is that each backend has a maximum of 4GB (actually, usually more like 3 to allow for kernel address space) that must include shared buffers, server code and data, and memory used for sorting etc. On 64-bit platforms, the 4GB address space limitation disappears, and a single backend could use 20GB for a sort, if the memory was available and the administrator allowed it. However, you should be running Opterons anyway. Yup. :) -Doug ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
On Mon, Mar 14, 2005 at 06:04:01PM -0500, Chris Browne wrote: [EMAIL PROTECTED] (Mark Rae) writes: Clients 1 2 3 4 6 812163264 128 -- mysql-4.1.1 1.00 1.41 1.34 1.16 0.93 1.03 1.01 1.00 0.94 0.86 0.80 pg-7.4.1 0.65 1.27 1.90 2.48 2.45 2.50 2.48 2.51 2.49 2.39 2.38 Could you elaborate on what the measures are here? I don't quite follow what 0.8 means as compared to 2.38. The figures are generated from a representative set of ~500 queries which each client issues in a random order. The clients all connect in parallel and the elapsed time taken for them all to complete is recorded. The numbers given above are these times converted into relative throughput figures. The baseline being a single mysql client, a performance of 1.00 is equivalent to an average of 5.82 queries per second. i.e. with a single client postgres runs at 65% the speed of mysql by the time 3 clients are running, postgres is getting through the queries 1.90/1.34=1.42 times faster and once we get to 32 clients, mysql is tripping over itself and is actually running slower than if the clients ran one after another. Looking at how the database scales, i.e. all figures are relative to the speed of a single client connection. Clients 1 2 3 4 6 812163264 128 -- Theoretical 1.00 2.00 3.00 4.00 4.00 4.00 4.00 4.00 4.00 4.00 4.00 mysql-4.1.1 1.00 1.41 1.34 1.16 0.93 1.03 1.01 1.00 0.94 0.86 0.80 pg-7.4.1 1.00 1.94 2.91 3.79 3.75 3.82 3.80 3.84 3.82 3.66 3.64 The theoretical line being is how much we should expect things to scale given that this is a 4 cpu machine. -Mark ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
Le mardi 15 mars 2005 à 12:26 +, Mark Rae a écrit : Clients 1 2 3 4 6 812163264 128 -- mysql-4.1.1 1.00 1.41 1.34 1.16 0.93 1.03 1.01 1.00 0.94 0.86 0.80 pg-7.4.1 0.65 1.27 1.90 2.48 2.45 2.50 2.48 2.51 2.49 2.39 2.38 by the time 3 clients are running, postgres is getting through the queries 1.90/1.34=1.42 times faster That is very interesting!!! I have several webapps on my server each one opens several queries to the database from _each_ JSP - often more than three... So the hunch that I had all along was right: PostgreSQL is a much better back end for Tomcat/JSP than MySQL. Cheers Tony ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
On Tue, 2005-03-15 at 14:07 +0100, tony wrote: by the time 3 clients are running, postgres is getting through the queries 1.90/1.34=1.42 times faster That is very interesting!!! I have several webapps on my server each one opens several queries to the database from _each_ JSP - often more than three... So the hunch that I had all along was right: PostgreSQL is a much better back end for Tomcat/JSP than MySQL. Be careful assuming that. DB benchmarks are hard to do in a general sense. His results probably indicate a general trend, but you should test your application yourself to get a real result. His pattern of SQL queries might be very different from yours. Regards, Jeff Davis ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
On Mon, 2005-03-14 at 12:43 +, Richard Huxton wrote: Take 30 minutes to read through the article below. It covers the basics of how to manage your configuration settings. http://www.powerpostgresql.com/PerfList That's an informative article. I was hoping, however, that it would have a few details about the effects of the statistics settings on performance. Which statistics options affect the planner? Do they potentially affect autovacuum? Also, a 32-bit machine can only hold so much RAM. If I'm correct, there are ways to address more memory than that on a 32 bit machine, but I wonder at what cost? In other words, is it a good idea to address more than 4GB on a 32 bit machine? If not, is it a reasonable choice to invest in 64 bit if you want 4GB of RAM? Or are you better off just spending the money on RAID and staying at 4GB? Regards, Jeff Davis PS: A minor typo in Disk and WAL - checkpoint_segments: s/Depening/Depending/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
Also, a 32-bit machine can only hold so much RAM. If I'm correct, there are ways to address more memory than that on a 32 bit machine, but I wonder at what cost? In other words, is it a good idea to address more than 4GB on a 32 bit machine? If not, is it a reasonable choice to invest in 64 bit if you want 4GB of RAM? Or are you better off just spending the money on RAID and staying at 4GB? It entirely depends on the database but not that the 32bit limit of 4GB is per CPU. So if you have 4 CPUs you can have 16GB of ram. However, you should be running Opterons anyway. J Regards, Jeff Davis PS: A minor typo in Disk and WAL - checkpoint_segments: s/Depening/Depending/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Command Prompt, Inc., Your PostgreSQL solutions company. 503-667-4564 Custom programming, 24x7 support, managed services, and hosting Open Source Authors: plPHP, pgManage, Co-Authors: plPerlNG Reliable replication, Mammoth Replicator - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
On Tue, Mar 15, 2005 at 12:51:03PM -0800, Jeff Davis wrote: Be careful assuming that. DB benchmarks are hard to do in a general sense. His results probably indicate a general trend, but you should test your application yourself to get a real result. His pattern of SQL queries might be very different from yours. Very true. You may have noticed that I had a very low query rate of 5.8 queries per second, because some of the queries have 12 tables to join and take about 20s to run. This tends to work in postgres' favour. If you have lots have simple queries, it will be better for mysql and the break even point will be higher. Also, while on the subject of scaling. I had the opportunity to try postgres on a 16CPU Altix and couldn't get it to scale more than about 4x, whereas Oracle got up to about 12x faster I assume this is because of the NUMA architecture. I was also told that Oracle had made no special optimizations to accomodate it. My guess is that because postgres allocates all its shared buffers as a contiguous chunk, it puts all the load on one memory bank. Oracle on the other hand, seems to use lots of smaller regions which would probably be spread throughout the physical memory. Perhaps one of the developers could comment on how difficult it would be to change the shared buffer handling to use multiple segments. As I'd definitely be willing to give it a go. -Mark ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
Mark Rae wrote: On Tue, Mar 15, 2005 at 12:51:03PM -0800, Jeff Davis wrote: Be careful assuming that. DB benchmarks are hard to do in a general sense. His results probably indicate a general trend, but you should test your application yourself to get a real result. His pattern of SQL queries might be very different from yours. Very true. You may have noticed that I had a very low query rate of 5.8 queries per second, because some of the queries have 12 tables to join and take about 20s to run. This tends to work in postgres' favour. If you have lots have simple queries, it will be better for mysql and the break even point will be higher. Also, while on the subject of scaling. I had the opportunity to try postgres on a 16CPU Altix and couldn't get it to scale more than about 4x, whereas Oracle got up to about 12x faster I assume this is because of the NUMA architecture. I was also told that Oracle had made no special optimizations to accomodate it. My guess is that because postgres allocates all its shared buffers as a contiguous chunk, it puts all the load on one memory bank. Oracle on the other hand, seems to use lots of smaller regions which would probably be spread throughout the physical memory. Perhaps one of the developers could comment on how difficult it would be to change the shared buffer handling to use multiple segments. As I'd definitely be willing to give it a go. We have had some major SMP improvements in current CVS. Were you testing that or 8.0.X? -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
On Tue, Mar 15, 2005 at 06:46:50PM -0500, Bruce Momjian wrote: Mark Rae wrote: Also, while on the subject of scaling. I had the opportunity to try postgres on a 16CPU Altix and couldn't get it to scale more than about 4x, whereas Oracle got up to about 12x faster We have had some major SMP improvements in current CVS. Were you testing that or 8.0.X? It tried it with 8.0.0rc3, and had previously tried a 7.4 version -Mark ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
Mark Rae wrote: On Tue, Mar 15, 2005 at 06:46:50PM -0500, Bruce Momjian wrote: Mark Rae wrote: Also, while on the subject of scaling. I had the opportunity to try postgres on a 16CPU Altix and couldn't get it to scale more than about 4x, whereas Oracle got up to about 12x faster We have had some major SMP improvements in current CVS. Were you testing that or 8.0.X? It tried it with 8.0.0rc3, and had previously tried a 7.4 version Oh, you have to try CVS HEAD or a nightly snapshot. Tom made a major change that allows scaling in SMP environments. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
On Tue, Mar 15, 2005 at 07:00:25PM -0500, Bruce Momjian wrote: Oh, you have to try CVS HEAD or a nightly snapshot. Tom made a major change that allows scaling in SMP environments. Ok, I'll give it a try in the next couple of days when there is some free time available on the machine. -Mark ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
Mark Rae wrote: On Tue, Mar 15, 2005 at 12:51:03PM -0800, Jeff Davis wrote: Be careful assuming that. DB benchmarks are hard to do in a general sense. His results probably indicate a general trend, but you should test your application yourself to get a real result. His pattern of SQL queries might be very different from yours. Very true. You may have noticed that I had a very low query rate of 5.8 queries per second, because some of the queries have 12 tables to join and take about 20s to run. This tends to work in postgres' favour. If you have lots have simple queries, it will be better for mysql and the break even point will be higher. Also, while on the subject of scaling. I had the opportunity to try postgres on a 16CPU Altix and couldn't get it to scale more than about 4x, whereas Oracle got up to about 12x faster I assume this is because of the NUMA architecture. I was also told that Oracle had made no special optimizations to accomodate it. My guess is that because postgres allocates all its shared buffers as a contiguous chunk, it puts all the load on one memory bank. Oracle on the other hand, seems to use lots of smaller regions which would probably be spread throughout the physical memory. Perhaps one of the developers could comment on how difficult it would be to change the shared buffer handling to use multiple segments. As I'd definitely be willing to give it a go. -Mark a bit of info re mysql and speed with concurrent transactions. a community site I was working to get running a bit better was using phpnuke and mysql. ( not my site, was a contract ) with 56,000 members the site was bogged down almost to the point of timing out, this was with only 100 or so users online. another community site, with custom script using mysql backend, sperad over several servers rather than one machine, and 250,000 members. ( 4 terabytes data transfer a month minimum. ) it's often slow responding, but doesn't get close to a timeout. while these are subjective observations, they show that tuning, and structure of application will have a significant affect, more than would generally be assumed. mysql is a good application, for lower traffic applications [ local intranet with 100 users ], but I would never actually recommend using mysql on a large database.[ large query useage would be horrendously slow ] Jaqui smime.p7s Description: S/MIME Cryptographic Signature
[GENERAL] prelimiary performance comparison pgsql vs mysql
Below are some PRELIMINARY results in comparing the performance of pgsql and mysql. These results are for a single process populating a table with 934k rows, and then performing some selects. I also compared the effect of creating indexes on some of the columns. I have not yet done any testing of transactions, multiple concurrent processes, etc. I did not make any changes to the default config settings. I can do so if someone has some suggestions. My machine is a 3.0 GHz P4 with 1 GB ram, running FC 3. I used pg 8.0.1 and mysql 5.0.2 alpha. I compiled pg from source, but I downloaded an binary for mysql. If someone thinks this is significant, and can point me to a good binary for pg, I will give it a try. All timings are as reported by the db. I included the pg script below.. Finally, I don't have an axe to grind either way. I'm trying to be fair, but I am the first to admit I am not an expert in db tuning. I welcome constructive comments and advice. data and queries The data comes from some experimental data I have been working with. I load data into a table with relavant columns fid, rid, x. The combination of fid,rid is unique. x is a location, and is not unique. I loaded the data using COPY (pg) and LOAD (mysql). The queries were: select count(*) from data where fid=2 and rid=6; count = 100 select count(*) from data where x 5000 and x 5500; count = 35986 select count(*) from data where x 5000 and x 5020; count = 1525 * preliminary conclusions As suspected, MyISAM is very fast. In the tested case (only one process, only one big load and some selects) MyISAM tables are much faster than pg or InnoDB. For queries, InnoDB and pg are roughly equivalent. In some cases one or the other is a little faster, but they are mostly in the same ballpark. The one exception seems to be that pg has an edge in seq scans. pg is slower loading data when it has to create an index. Also, I found that is is critical to run vacuum analyze in pg. Running analyze in mysql did not seem to make much difference. I'm guessing that mysql builds statistics while it is loading data, and does not actually run an analyze since the table has not changed. *** preliminary results *** *** all times in seconds ** note: input table has 934500 rows. mysql 5.0.2 alpha PG 8.0.1 MyISAM InnoDB NO INDEXES Load file22.3 3.9 22.1 select count fid=?,rid=? 3.0 0.23 2.07 select count x 5000, x 5500 1.2 0.27 1.59 select count x 5000, x 5020 0.630.29 1.58 INDEXES on (fid,rid) Load file36. 13.5 30.1 vacuum analyze3.6 select count fid=?,rid=? 0.0 0.00 0.02 select count x 5000, x 5500 0.702 0.29 2.07 select count x 5000, x 5020 0.713 0.28 1.59 INDEXES on (fid,rid) and (x) Load file 202. 24. 151. vacuum analyze 11. select count fid=?,rid=? 0.002 0.00 0.02 select count x 5000, x 5500 0.9 0.06 0.75 select count x 5000, x 5020 0.048 0.01 0.01 * PG-SQL script \timing -- -- Load table, no indexes -- drop table data cascade; create table data ( fid integer, rid integer, range real, x real, y real, z real, bs real, snr real, rvelreal, cfarsmallint); COPY data (fid,rid,range,snr,bs,rvel,cfar,x,y,z) FROM '/home/rick/bed/data/data.dat'; select count(*) from data where fid=2 and rid=6; select count(*) from data where x 5000 and x 5500; select count(*) from data where x 5000 and x 5020; -- -- Load table, index on (fid,rid) -- drop table data cascade; create table data ( fid integer, rid integer, range real, x real, y real, z real, bs real, snr real, rvelreal, cfarsmallint); create index fidrid_data on data (fid,rid); COPY data (fid,rid,range,snr,bs,rvel,cfar,x,y,z) FROM '/home/rick/bed/data/data.dat'; vacuum analyze data; select count(*) from data where fid=2 and rid=6; select count(*) from data where x 5000 and x 5500; select count(*) from data where x 5000 and x 5020; -- -- Load table, index on (fid,rid) and (x) -- drop table data cascade; create table data ( fid integer, rid integer, range real, x real, y real, z real, bs
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
Hi Rick, the work you are doing is important (at least I think so). From my experience PosgreSQL performance is also very slow in case there are several LEFT JOINs and there are varchar() fields. You can see an example in archive where my problem is described (Subject: How to read query plan). There is a patch that partially solves this problem (as Tom Lane mentioned) but it is not in the latest PostgreSQL release. I will work on improving of the design of tables that I have problem with to see if there will be any significant speed improvements. Maybe you could use some of my results for some of your tests then. Miroslav Rick Schumeyer wrote: Below are some PRELIMINARY results in comparing the performance of pgsql and mysql. ... begin:vcard fn;quoted-printable:Miroslav =C5=A0ulc n;quoted-printable:=C5=A0ulc;Miroslav org:StartNet s.r.o. adr;quoted-printable;quoted-printable:;;Vrchlick=C3=A9ho 161/5;Praha 5;;150 00;=C4=8Cesk=C3=A1 republika email;internet:[EMAIL PROTECTED] title:CEO tel;work:+420 257 225 602 tel;cell:+420 603 711 413 x-mozilla-html:TRUE url:http://www.startnet.cz version:2.1 end:vcard ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
Rick Schumeyer wrote: Below are some PRELIMINARY results in comparing the performance of pgsql and mysql. These results are for a single process populating a table with 934k rows, and then performing some selects. I also compared the effect of creating indexes on some of the columns. I have not yet done any testing of transactions, multiple concurrent processes, etc. I did not make any changes to the default config settings. I can do so if someone has some suggestions. My machine is a 3.0 GHz P4 with 1 GB ram, running FC 3. Stop now. I've not looked at your test results, and frankly there is no point. As it ships, PG should run fine on a small corner of an old laptop. It will not perform well with any sort of serious workload on any sort of serious hardware. You're wasting your time if you want to get any sort of meaningful result. Take 30 minutes to read through the article below. It covers the basics of how to manage your configuration settings. http://www.powerpostgresql.com/PerfList Oh - make sure you are accounting for caching effects as well. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
On Mon, Mar 14, 2005 at 06:52:58AM -0500, Rick Schumeyer wrote: Below are some PRELIMINARY results in comparing the performance of pgsql and mysql. ... I have not yet done any testing of transactions, multiple concurrent processes, etc. I would say that doing the concurrency tests is probably the most important factor in comparing other databases against MySQL, as MySQL will almost always win in single-user tests. E.g. here are some performance figures from tests I have done in the past. This is with a 6GB databse on a 4CPU Itanium system running a mixture of read-only queries, but it is fairly typical of the behaviour I have seen. The Oracle figures also scaled in a similar way to postgres. Clients 1 2 3 4 6 812163264 128 --- mysql-4.1.11.00 1.41 1.34 1.16 0.93 1.03 1.01 1.00 0.94 0.86 0.80 pg-7.4.1 0.65 1.27 1.90 2.48 2.45 2.50 2.48 2.51 2.49 2.39 2.38 -Mark ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
That site produces some sort of php error. I don't suppose this information is available elsewhere? Stop now. I've not looked at your test results, and frankly there is no point. As it ships, PG should run fine on a small corner of an old laptop. It will not perform well with any sort of serious workload on any sort of serious hardware. You're wasting your time if you want to get any sort of meaningful result. Take 30 minutes to read through the article below. It covers the basics of how to manage your configuration settings. http://www.powerpostgresql.com/PerfList ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
Rick Schumeyer wrote: That site produces some sort of php error. Hmm - was working this morning. Perhaps some maintenance going on. I don't suppose this information is available elsewhere? Try some slightly older notes here: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
On Mon, 2005-03-14 at 05:52, Rick Schumeyer wrote: Below are some PRELIMINARY results in comparing the performance of pgsql and mysql. These results are for a single process populating a table with 934k rows, and then performing some selects. I also compared the effect of creating indexes on some of the columns. I have not yet done any testing of transactions, multiple concurrent processes, etc. I did not make any changes to the default config settings. I can do so if someone has some suggestions. My machine is a 3.0 GHz P4 with 1 GB ram, running FC 3. I used pg 8.0.1 and mysql 5.0.2 alpha. Why are all the tests here select count(*) tests? Surely your application does something more interesting than counting rows... For a more interesting test, try setting up three or four streaming writers that write information continuously into the database, and then run the select count(*) queries against both and see what happens. My guess is that the table level locking of myisam tables means the MySQL database will slow to a crawl or throw error messages, while the postgresql system will slow down somewhat but keep right on running. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
Richard Huxton dev@archonet.com writes: Rick Schumeyer wrote: Below are some PRELIMINARY results in comparing the performance of pgsql and mysql. Take 30 minutes to read through the article below. It covers the basics of how to manage your configuration settings. http://www.powerpostgresql.com/PerfList I have been fooling with the sql-bench stuff that MySQL ships with their database. Not because I take it seriously ;-) but because I thought it would be useful to understand in detail why we look so spectacularly bad on it. I'll write a more complete report when I'm done, but what's relevant to Rick's testing is that I have found that a few simple configuration adjustments make a huge difference. Specifically, I've got shared_buffers = 1 # 10x the default checkpoint_segments = 30# 10x the default work_mem = 10 # ~100x the default maintenance_work_mem = 10 # ~6x the default (The *work_mem numbers are probably excessive but I've not bothered to fine-tune them.) A stock out-of-the-box PG 8.0.1 RPM is about 10x slower overall than MySQL according to this benchmark, but these adjustments bring it to something like 2x slower. Which is at least in the ballpark. Most of the tables that this benchmark uses have about 300K not-super-wide rows, so what this says is that you need numbers in this vicinity to work on tables of that size. Bottom line is that you *must* adjust at least these settings if you want a high-performance PG server. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] prelimiary performance comparison pgsql vs mysql
[EMAIL PROTECTED] (Mark Rae) writes: On Mon, Mar 14, 2005 at 06:52:58AM -0500, Rick Schumeyer wrote: Below are some PRELIMINARY results in comparing the performance of pgsql and mysql. ... I have not yet done any testing of transactions, multiple concurrent processes, etc. I would say that doing the concurrency tests is probably the most important factor in comparing other databases against MySQL, as MySQL will almost always win in single-user tests. E.g. here are some performance figures from tests I have done in the past. This is with a 6GB databse on a 4CPU Itanium system running a mixture of read-only queries, but it is fairly typical of the behaviour I have seen. The Oracle figures also scaled in a similar way to postgres. Clients 1 2 3 4 6 812163264 128 --- mysql-4.1.11.00 1.41 1.34 1.16 0.93 1.03 1.01 1.00 0.94 0.86 0.80 pg-7.4.1 0.65 1.27 1.90 2.48 2.45 2.50 2.48 2.51 2.49 2.39 2.38 Could you elaborate on what the measures are here? I don't quite follow what 0.8 means as compared to 2.38. -- let name=cbbrowne and tld=cbbrowne.com in String.concat @ [name;tld];; http://www.ntlug.org/~cbbrowne/linuxxian.html A VAX is virtually a computer, but not quite. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
