[PERFORM] how the hdd read speed is related to the query execution speed.
Hi, I have a 8 GB database, and 2 GB table. In a query i use the 2 GB table and several other tables where it takes around 90 minutes for execution. In different places, it takes drastically different time. Say everywhere i have the same, OS - Debian. Primary memory - 3 GB PostgreSQL configuration. But in one machine it takes 3.5 minutes, and in other machine 90 minutes which confuses me much. So i did a test of hard disk read speed, in the machine where it takes 90 minutes resulted in the following., dd if=/var/lib/postgresql/8.1/main/base/16385/17283 of=/dev/null bs=1M count=1024 1024+0 records in 1024+0 records out 1073741824 bytes (1.1 GB) copied, 8.32928 seconds, 129 MB/s Unfortunately, i cannot execute this same on that 3.5 minute execution machine. But i had the previous write speed test, output which is dd if=/dev/zero of=/tmp/test bs=1M count=1024 1073741824 bytes (1.1 GB) copied, 2.37701 seconds, 452 MB/s and the same write speed test in the 90 minutes machine is dd if=/dev/zero of=/tmp/test bs=1M count=1024 1024+0 records in 1024+0 records out 1073741824 bytes (1.1 GB) copied, 19.5375 seconds, 55.0 MB/s So i assume that there should be a 9 times faster execution. Because 55 MB write per second, and 450 MB write per second. But am i doing some thing silly here. Or what i can do better confirm the problem ?? can some body give me ideas on what to do for confirming what is the issue for consuming much time for the query execution ?
Re: [PERFORM] Benchmark comparing PostgreSQL, MySQL and Oracle
El Fri, 20 Feb 2009 16:54:58 -0500 Robert Haas escribió: > On Fri, Feb 20, 2009 at 4:34 PM, Jonah H. Harris > wrote: > > On Fri, Feb 20, 2009 at 3:40 PM, Merlin Moncure > > wrote: > >> > >> ISTM you are the one throwing out unsubstantiated assertions > >> without data to back it up. OP ran benchmark. showed > >> hardware/configs, and demonstrated result. He was careful to > >> hedge expectations and gave rationale for his analysis methods. > > > > As I pointed out in my last email, he makes claims about PG being > > faster than Oracle and MySQL based on his results. I've already > > pointed out significant tuning considerations, for both Postgres > > and Oracle, which his benchmark did not take into account. > > > > This group really surprises me sometimes. For such a smart group > > of people, I'm not sure why everyone seems to have a problem > > pointing out design flaws, etc. in -hackers, yet when we want to > > look good, we'll overlook blatant flaws where benchmarks are > > concerned. > > The biggest flaw in the benchmark by far has got to be that it was > done with a ramdisk, so it's really only measuring CPU consumption. > Measuring CPU consumption is interesting, but it doesn't have a lot to > do with throughput in real-life situations. The benchmark was > obviously constructed to make PG look good, since the OP even mentions > on the page that the reason he went to ramdisk was that all of the > databases, *but particularly PG*, had trouble handling all those > little writes. (I wonder how much it would help to fiddle with the > synchronous_commit settings. How do MySQL and Oracle alleviate this > problem and we can usefully imitate any of it?) > The benchmark is NOT constructed to make PostgreSQL look good, that never was my intention. All databases suffered the I/O bottleneck for their redo/xlog/binary_log files, specially PostgreSQL but closely followed by Oracle. For some reason MySQL seems to deal better with I/O contention, but still gives numbers that are less than the half it gives with tmpfs. While using the old array (StorageTek T3), I've played with synchronous_commit, wal_sync_method, commit_delay... and only setting wal_sync_method = open_datasync (which, in Solaris, completly disables I/O syncing) gave better results, for obvious reasons. Anyway, I think that in the next few months I'll be able to repeat the tests with a nice SAN, and then we'll have new numbers that will be more near to "real-world situations" (but synthetic benchmarks always are synthetic benchmarks) and also we'll be able to compare them with this ones to see how I/O contetion impacts on each database. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Benchmark comparing PostgreSQL, MySQL and Oracle
El Fri, 20 Feb 2009 14:48:06 -0500 "Jonah H. Harris" escribió: > On Fri, Feb 20, 2009 at 1:15 PM, Sergio Lopez > wrote: > > Having this said, the benchmark is not as unfair as you thought. I've > > taken care to prepare all databases to meet similar values for their > > cache, buffers and I/O configuration (to what's possible given their > > differences), and the I've left the rest as comes by default (for > > Oracle I've used the OLTP template). > > > Oracle's buffer cache is different than Postgres'. And there are > several other tuning paramaters which control how the buffer cache > and I/O between cache and disk is performed. Making them the same > size means nothing. And, as I said, you still didn't mention other > important tuning parameters in MySQL, Postgres, or Oracle. So either > you don't know about them, or you didn't bother to tune them, which > is odd if you were trying to run a truly comparative benchmark. > As I written in the article, I only tuned a few parameters and let the other out-the-box. More info: - Oracle: * AMM, sga_max_size/sga_target_size=4GB (yes, it's pretty low for a 20 GB RAM machine, but remember I needed to run the tests in another 10 GB RAM SPARC server and still need some more memory for database and redo (10 warehouses == about 1 GB of data) * db_block_size=8k (this also answers the other email) * filesystem_io=setall (which souldn't make difference, anyway) * db_writer_processes=2 (with a extremly fast tmpfs, incresing this will obviously be counterproductive) - MySQL: * innodb_buffer_pool_size=4GB * innodb_log_file_size=512MB - PostgreSQL: * effective_cache_size=4GB * shared_pool_size=512MB * fsync = on * synchronous_commit = on * wal_sync_method = fsync * checkpoint_segments = 100 * checkpoint_completion_target = 0.7 If you have some suggestions to do about this configurations, please tell me so I can put them in the next benchmark (which, hopefully, will use a nice performing SAN instead of tmpfs). > > Yes, BenchmarkSQL is NOT the perfect tool for database benchmarking > > and it is NOT a valid TPC-C test (I've made this clear in the > > article), but I've looked at its source (you assume I blindly used > > it, but actually I've even made some changes to make it work with > > Ingres for other purposes) and I find it fair enough due to the > > simplicity of the queries it executes. I found no other evident > > optimization than the "vacuum analyze" in the LoadData application. > > > Did you fix the bug in, I believe, the Order Status transaction that > can cause an endless loop? I would call giving the Postgres > optimizer correct statistics and leaving Oracle and MySQL with > defaults an optimization. > The bug was in the Delivery transaction, and yes, I fixed it. It was a simple bad locking behaviour, solved by properly using the "FOR UPDATE" clause. > > Obviously, you can optimize the queries to perform better in Oracle, > > the same way you can do with any other DB, but doing that would be > > cheating. The key here is to keep the queries as simple as possible, > > and BenchmarkSQL does this nicely. > > > BenchmarkSQL is flawed. You need to review the code more closely. > Please, could you point the bugs (or at least some of them) you're referring to? That would be very helpful for me, so I can fix them for the next benchmark. > Of course, my benchmark it's somewhat peculiar by the fact (that you > > haven't mentioned) that all databases files reside in volatile > > storage (RAM) by using tmpfs, which makes something similar (but > > not the same) as using DIRECT_IO with an extremly fast storage. > > But, again, all databases are given equal consideration. > > > You're right, it's not the same. Oracle can benefit by using real > direct I/O, not half-baked simulations which still cause > double-buffering between the linux page cache and the database buffer > cache. > _All_ databases can benefit from direct I/O, specially for their redo files. But, in this benchmark we don't have double buffering (nor read-ahead) issues, or do you expect Linux or Solaris to cache data which is already in RAM (tmpfs)? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] not in(subselect) in 8.4
On Fri, Feb 20, 2009 at 6:14 AM, marcin mank wrote: > On Fri, Feb 20, 2009 at 4:56 AM, Grzegorz Jaśkiewicz > wrote: >> Just as a question to Tom and team, > > maybe it`s time for asktom.postgresql.org? Oracle has it :) +1 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Benchmark comparing PostgreSQL, MySQL and Oracle
Robert Haas wrote: The biggest flaw in the benchmark by far has got to be that it was done with a ramdisk, so it's really only measuring CPU consumption. Measuring CPU consumption is interesting, but it doesn't have a lot to do with throughput in real-life situations. ... and memory access. Measuring these two in isolation from any (real/usual) io system is interesting but perhaps only as a curiosity - however, it would become much more interesting if we could see how the results change when a disk based filesystem is used (or even raw for the big O and innodb and filesystem for postgres...). regards Mark -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Benchmark comparing PostgreSQL, MySQL and Oracle
On Fri, Feb 20, 2009 at 2:54 PM, Robert Haas wrote: > On Fri, Feb 20, 2009 at 4:34 PM, Jonah H. Harris > wrote: >> On Fri, Feb 20, 2009 at 3:40 PM, Merlin Moncure wrote: >>> >>> ISTM you are the one throwing out unsubstantiated assertions without >>> data to back it up. OP ran benchmark. showed hardware/configs, and >>> demonstrated result. He was careful to hedge expectations and gave >>> rationale for his analysis methods. >> >> As I pointed out in my last email, he makes claims about PG being faster >> than Oracle and MySQL based on his results. I've already pointed out >> significant tuning considerations, for both Postgres and Oracle, which his >> benchmark did not take into account. >> >> This group really surprises me sometimes. For such a smart group of people, >> I'm not sure why everyone seems to have a problem pointing out design flaws, >> etc. in -hackers, yet when we want to look good, we'll overlook blatant >> flaws where benchmarks are concerned. > > The biggest flaw in the benchmark by far has got to be that it was > done with a ramdisk, so it's really only measuring CPU consumption. > Measuring CPU consumption is interesting, but it doesn't have a lot to Agreed. As soon as I saw that I pretty much threw the results out the window. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] postgreSQL performance 8.2.6 vs 8.3.3
On Fri, Feb 20, 2009 at 2:34 PM, Battle Mage wrote: > I have a server box that has 4GB of RAM, Quad core CPU AMD Opteron 200.152 > Mhz (1024 KB cache size each) with plenty of hard drive space. > > I installed both postgresql 8.2.6 and 8.3.3 on it. I've created a basic > test db and used > pgbench -i -s 1 -U test -h localhost test > to create a sample test db. > > Then, to benchmark the postgreSQLs, I executed this separately on each of > them: > pg_bench -h localhost -d test -t 2000 -c 50 -s 50 -U test > (2000 transactions per client, 50 clients, scalability factor of 50) If you're goint to test with -c50 you should initialize with -s50. -s 50 after initialization doesn't mean anything. It's the first pgbench -i -s nnn where you need to set nnn to 50 (or higher) if you're gonna test with it. > Using the above, > I get on postgreSQL 8.2.6: > Load average: Between 3.4 and 4.3 > tps = 589 (including connections establishing) > tps = 590 (excluding connections establishing) > > I get on postgreSQL 8.3.3 > Load: Between 4.5 and 5.6 > tps = 949 (including connections establishing) > tps = 951 (excluding connections establishing) Nice improvement. > The amount of tps almost doubled, which is good, but i'm worried about the > load. For my application, a load increase is bad and I'd like to keep it > just like in 8.2.6 (a load average between 3.4 and 4.3). What parameters > should I work with to decrease the resulting load average at the expense of > tps? I agree with the other poster. Why is a load increase bad? What does it mean here. I've got one load that runs smoothly with a load factor of 60 to 150 on a server, while the same server with a different load starts to bog down with load factors between 10 and 15. It's a very broad measurement. Don't try to tune to your load factor, try to tune to the real load being applied, and opimtize there. > Down below is my 8.3.3 configuration file. I removed everything that is > commented since if it's commented, it's default value. I also removed from > the sample below parameters related to logging. > > = postgresql.conf begins = > fsync = off# turns forced synchronization on or off So, I assume either your data is easily reproduceable, unimportant, or replicated in such a way that you can survive sudden power loss / kernel crash? Also, is there are reason you're running two different out of date releases of postgresql? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Benchmark comparing PostgreSQL, MySQL and Oracle
On Fri, Feb 20, 2009 at 4:34 PM, Jonah H. Harris wrote: > On Fri, Feb 20, 2009 at 3:40 PM, Merlin Moncure wrote: >> >> ISTM you are the one throwing out unsubstantiated assertions without >> data to back it up. OP ran benchmark. showed hardware/configs, and >> demonstrated result. He was careful to hedge expectations and gave >> rationale for his analysis methods. > > As I pointed out in my last email, he makes claims about PG being faster > than Oracle and MySQL based on his results. I've already pointed out > significant tuning considerations, for both Postgres and Oracle, which his > benchmark did not take into account. > > This group really surprises me sometimes. For such a smart group of people, > I'm not sure why everyone seems to have a problem pointing out design flaws, > etc. in -hackers, yet when we want to look good, we'll overlook blatant > flaws where benchmarks are concerned. The biggest flaw in the benchmark by far has got to be that it was done with a ramdisk, so it's really only measuring CPU consumption. Measuring CPU consumption is interesting, but it doesn't have a lot to do with throughput in real-life situations. The benchmark was obviously constructed to make PG look good, since the OP even mentions on the page that the reason he went to ramdisk was that all of the databases, *but particularly PG*, had trouble handling all those little writes. (I wonder how much it would help to fiddle with the synchronous_commit settings. How do MySQL and Oracle alleviate this problem and we can usefully imitate any of it?) Still, if you read his conclusions, he admits that he's just trying to show that they're in the same ballpark, and that might well be true, even with the shortcomings of the tests. Personally, I'm not as upset as you seem to be about the lack of perfect tuning. Real-world tuning is rarely perfect, either, and we don't know that his tuning was bad. We do know that whatever tuning he did was not adequately documented, and we can suspect that items mentioned were not tuned, but we really don't know that. We have plenty of evidence from these lists that fiddling with shared_buffers (either raising or even sometimes lowering it), page and tuple costs, etc. can sometimes produce dramatic performance changes. But that doesn't necessarily tell you anything about what will happen in a real life application with a more complex mix of queries where you can't optimize for the benchmark. >> If you think he's wrong, instead of picking on him why don't you run >> some tests showing alternative results and publish them...leave off >> the oracle results or use a pseudo-name or something. > > One of these days I'll get some time and post my results. I'm just pointing > out obvious flaws in this benchmark. If Sergio wants to correct them and/or > qualify them, that's cool with me. I just don't like people relying on > questionable and/or unclear data. I'd love to see more results. Even if they're not 100% complete and correct they would give us more of a notion than we have now of where more work is needed. I was interested to see that Oracle was the runaway winner for bulk data load because I did some work on that a few months back. I suspect a lot more is needed there, because the work I did would only help with create-table-as-select or copy, not retail insert, and even at that I know that the cases I did handle have room for further improvement. I am not certain which database is the fastest and suspect there is no one answer. But if we get some information that helps us figure out where we can improve, that is all to the good. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] postgreSQL performance 8.2.6 vs 8.3.3
On Fri, Feb 20, 2009 at 1:34 PM, Battle Mage wrote: > The amount of tps almost doubled, which is good, but i'm worried about the > load. For my application, a load increase is bad and I'd like to keep it > just like in 8.2.6 (a load average between 3.4 and 4.3). What parameters > should I work with to decrease the resulting load average at the expense of > tps? Why is it bad? High load can mean a number of things. The only way to reduce the load is to get the client to submit requests slower. I don't think you'll be successful in tuning the database to run slower. I think you're headed in the wrong direction. -Dave -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] postgreSQL performance 8.2.6 vs 8.3.3
On Fri, Feb 20, 2009 at 04:34:23PM -0500, Battle Mage wrote: > I have a server box that has 4GB of RAM, Quad core CPU AMD Opteron 200.152 > Mhz (1024 KB cache size each) with plenty of hard drive space. > > I installed both postgresql 8.2.6 and 8.3.3 on it. I've created a basic > test db and used > pgbench -i -s 1 -U test -h localhost test > to create a sample test db. > > Then, to benchmark the postgreSQLs, I executed this separately on each of > them: > pg_bench -h localhost -d test -t 2000 -c 50 -s 50 -U test > (2000 transactions per client, 50 clients, scalability factor of 50) > > Using the above, > I get on postgreSQL 8.2.6: > Load average: Between 3.4 and 4.3 > tps = 589 (including connections establishing) > tps = 590 (excluding connections establishing) > > I get on postgreSQL 8.3.3 > Load: Between 4.5 and 5.6 > tps = 949 (including connections establishing) > tps = 951 (excluding connections establishing) > > The amount of tps almost doubled, which is good, but i'm worried about the > load. For my application, a load increase is bad and I'd like to keep it > just like in 8.2.6 (a load average between 3.4 and 4.3). What parameters > should I work with to decrease the resulting load average at the expense of > tps? > > Down below is my 8.3.3 configuration file. I removed everything that is > commented since if it's commented, it's default value. I also removed from > the sample below parameters related to logging. Please evaluate your load on the 8.3.3 box at 590 tps. If the load is proportional to the tps than the scaled load will be: 2.8 to 3.5 for an equivalent tps. There is no free lunch but 8.3 performs much better than 8.2 and I suspect that this trend will continue. :) Cheers, Ken -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Benchmark comparing PostgreSQL, MySQL and Oracle
On Fri, Feb 20, 2009 at 3:40 PM, Merlin Moncure wrote: > ISTM you are the one throwing out unsubstantiated assertions without > data to back it up. OP ran benchmark. showed hardware/configs, and > demonstrated result. He was careful to hedge expectations and gave > rationale for his analysis methods. As I pointed out in my last email, he makes claims about PG being faster than Oracle and MySQL based on his results. I've already pointed out significant tuning considerations, for both Postgres and Oracle, which his benchmark did not take into account. This group really surprises me sometimes. For such a smart group of people, I'm not sure why everyone seems to have a problem pointing out design flaws, etc. in -hackers, yet when we want to look good, we'll overlook blatant flaws where benchmarks are concerned. > If you think he's wrong, instead of picking on him why don't you run > some tests showing alternative results and publish them...leave off > the oracle results or use a pseudo-name or something. One of these days I'll get some time and post my results. I'm just pointing out obvious flaws in this benchmark. If Sergio wants to correct them and/or qualify them, that's cool with me. I just don't like people relying on questionable and/or unclear data. -- Jonah H. Harris, Senior DBA myYearbook.com
[PERFORM] postgreSQL performance 8.2.6 vs 8.3.3
I have a server box that has 4GB of RAM, Quad core CPU AMD Opteron 200.152 Mhz (1024 KB cache size each) with plenty of hard drive space. I installed both postgresql 8.2.6 and 8.3.3 on it. I've created a basic test db and used pgbench -i -s 1 -U test -h localhost test to create a sample test db. Then, to benchmark the postgreSQLs, I executed this separately on each of them: pg_bench -h localhost -d test -t 2000 -c 50 -s 50 -U test (2000 transactions per client, 50 clients, scalability factor of 50) Using the above, I get on postgreSQL 8.2.6: Load average: Between 3.4 and 4.3 tps = 589 (including connections establishing) tps = 590 (excluding connections establishing) I get on postgreSQL 8.3.3 Load: Between 4.5 and 5.6 tps = 949 (including connections establishing) tps = 951 (excluding connections establishing) The amount of tps almost doubled, which is good, but i'm worried about the load. For my application, a load increase is bad and I'd like to keep it just like in 8.2.6 (a load average between 3.4 and 4.3). What parameters should I work with to decrease the resulting load average at the expense of tps? Down below is my 8.3.3 configuration file. I removed everything that is commented since if it's commented, it's default value. I also removed from the sample below parameters related to logging. = postgresql.conf begins = port = 5432# (change requires restart) max_connections = 180# (change requires restart) superuser_reserved_connections = 5# (change requires restart) unix_socket_directory = '/var/run/postgresql'# (change requires restart) ssl = off# (change requires restart) shared_buffers = 512MB# min 128kB or max_connections*16kB temp_buffers = 8MB# min 800kB max_prepared_transactions = 5# can be 0 or more work_mem = 16MB# min 64kB maintenance_work_mem = 512MB# min 1MB max_stack_depth = 2MB# min 100kB # - Free Space Map - max_fsm_pages = 240# min max_fsm_relations*16, 6 bytes each vacuum_cost_delay = 0# 0-1000 milliseconds vacuum_cost_page_hit = 1# 0-1 credits vacuum_cost_page_miss = 10# 0-1 credits vacuum_cost_page_dirty = 20# 0-1 credits vacuum_cost_limit = 200# 1-1 credits fsync = off# turns forced synchronization on or off #-- # QUERY TUNING #-- seq_page_cost = 1.0# measured on an arbitrary scale random_page_cost = 3.0# same scale as above effective_cache_size = 1024MB #-- # AUTOVACUUM PARAMETERS #-- autovacuum = on# Enable autovacuum subprocess? 'on' autovacuum_naptime = 1min# time between autovacuum runs autovacuum_vacuum_threshold = 500# min number of row updates before autovacuum_analyze_threshold = 250# min number of row updates before autovacuum_vacuum_scale_factor = 0.2# fraction of table size before vacuum autovacuum_analyze_scale_factor = 0.1# fraction of table size before analyze autovacuum_vacuum_cost_delay = 0# default vacuum cost delay for autovacuum_vacuum_cost_limit = 200# default vacuum cost limit for #-- # CLIENT CONNECTION DEFAULTS #-- datestyle = 'iso, mdy' timezone = UTC# actually, defaults to TZ environment lc_messages = 'en_US.UTF-8'# locale for system error message # strings lc_monetary = 'en_US.UTF-8'# locale for monetary formatting lc_numeric = 'en_US.UTF-8'# locale for number formatting lc_time = 'en_US.UTF-8'# locale for time formatting #-- # VERSION/PLATFORM COMPATIBILITY #-- escape_string_warning = off = postgresql.conf ends =
Re: [PERFORM] Benchmark comparing PostgreSQL, MySQL and Oracle
On Fri, Feb 20, 2009 at 2:48 PM, Jonah H. Harris wrote: > On Fri, Feb 20, 2009 at 1:15 PM, Sergio Lopez > wrote: >> >> On the other hand, I've neved said that what I've done is the >> Perfect-Marvelous-Definitive Benchmark, it's just a personal project, >> and I don't have an infinite amount of time to invest on it. > > When you make comments such as "As for databases, both Oracle and MySQL show > nice numbers, but it's PostgreSQL who stands in the top, giving consistent > results with each environment and workload", you should make sure that your > test is correct. Otherwise you're making statements without any real > basis-in-fact. ISTM you are the one throwing out unsubstantiated assertions without data to back it up. OP ran benchmark. showed hardware/configs, and demonstrated result. He was careful to hedge expectations and gave rationale for his analysis methods. If you think he's wrong, instead of picking on him why don't you run some tests showing alternative results and publish them...leave off the oracle results or use a pseudo-name or something. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Benchmark comparing PostgreSQL, MySQL and Oracle
On Fri, Feb 20, 2009 at 2:48 PM, Jonah H. Harris wrote: > Having this said, the benchmark is not as unfair as you thought. I've >> taken care to prepare all databases to meet similar values for their >> cache, buffers and I/O configuration (to what's possible given their >> differences), and the I've left the rest as comes by default (for >> Oracle I've used the OLTP template). > > > Oracle's buffer cache is different than Postgres'. And there are several > other tuning paramaters which control how the buffer cache and I/O between > cache and disk is performed. Making them the same size means nothing. And, > as I said, you still didn't mention other important tuning parameters in > MySQL, Postgres, or Oracle. So either you don't know about them, or you > didn't bother to tune them, which is odd if you were trying to run a truly > comparative benchmark. > Also forgot to ask, what block size did you use in Oracle? You mentioned tuning the shared pool, but you didn't specify db_cache_size or whether you were using automatic SGA tuning. Were those not tuned? -- Jonah H. Harris, Senior DBA myYearbook.com
Re: [PERFORM] Benchmark comparing PostgreSQL, MySQL and Oracle
On Fri, Feb 20, 2009 at 2:35 PM, Robert Haas wrote: > > First of all, you need to do some research on the benchmark kit itself, > > rather than blindly downloading and using one. BenchmarkSQL has > significant > > bugs in it which affect the result. I can say that authoritatively as I > > worked on/with it for quite awhile. Don't trust any result that comes > from > > BenchmarkSQL. If you fix the bugs, Oracle (out of the box in OLTP > config) > > will come out 60%. > > 60% what? Faster than PG 8.3-dev with 100 warehouses (when I last tested it). > > Oracle comes out twice as fast as PG on Linux. And, unless you're using > a > > significant number of warehouses, MySQL+InnoDB will come out better than > PG > > as well. > > I can believe that MySQL could come out faster than PG because I've > had previous experience with it being blindingly fast. Of course I've > also had experience with it having amazingly poor data integrity. That was MySQL+InnoDB. I haven't really had any integrity problems in that configuration. > I would be pretty surprised if Oracle were in general twice as fast as > PG - what are they doing that much better than what we're doing? I > could certainly imagine it being true in cases that rely on specific > features we lack (e.g. join removal)? DIO + AIO + multiple DBWR processes + large buffer cache + properly sized logs/log buffers makes a big difference. There are also several other concurrency-related tunables which contribute to it as well. -- Jonah H. Harris, Senior DBA myYearbook.com
Re: [PERFORM] Benchmark comparing PostgreSQL, MySQL and Oracle
On Fri, Feb 20, 2009 at 1:15 PM, Sergio Lopez wrote: > On the other hand, I've neved said that what I've done is the > Perfect-Marvelous-Definitive Benchmark, it's just a personal project, > and I don't have an infinite amount of time to invest on it. When you make comments such as "As for databases, both Oracle and MySQL show nice numbers, but it's PostgreSQL who stands in the top, giving consistent results with each environment and workload", you should make sure that your test is correct. Otherwise you're making statements without any real basis-in-fact. Having this said, the benchmark is not as unfair as you thought. I've > taken care to prepare all databases to meet similar values for their > cache, buffers and I/O configuration (to what's possible given their > differences), and the I've left the rest as comes by default (for > Oracle I've used the OLTP template). Oracle's buffer cache is different than Postgres'. And there are several other tuning paramaters which control how the buffer cache and I/O between cache and disk is performed. Making them the same size means nothing. And, as I said, you still didn't mention other important tuning parameters in MySQL, Postgres, or Oracle. So either you don't know about them, or you didn't bother to tune them, which is odd if you were trying to run a truly comparative benchmark. > Yes, BenchmarkSQL is NOT the perfect tool for database benchmarking and > it is NOT a valid TPC-C test (I've made this clear in the article), but > I've looked at its source (you assume I blindly used it, but actually > I've even made some changes to make it work with Ingres for other > purposes) and I find it fair enough due to the simplicity of the > queries it executes. I found no other evident optimization than the > "vacuum analyze" in the LoadData application. Did you fix the bug in, I believe, the Order Status transaction that can cause an endless loop? I would call giving the Postgres optimizer correct statistics and leaving Oracle and MySQL with defaults an optimization. > Obviously, you can optimize the queries to perform better in Oracle, > the same way you can do with any other DB, but doing that would be > cheating. The key here is to keep the queries as simple as possible, > and BenchmarkSQL does this nicely. BenchmarkSQL is flawed. You need to review the code more closely. Of course, my benchmark it's somewhat peculiar by the fact (that you > haven't mentioned) that all databases files reside in volatile storage > (RAM) by using tmpfs, which makes something similar (but not the > same) as using DIRECT_IO with an extremly fast storage. But, again, all > databases are given equal consideration. You're right, it's not the same. Oracle can benefit by using real direct I/O, not half-baked simulations which still cause double-buffering between the linux page cache and the database buffer cache. > Finally, about the license issue, (also) not trying to be rude, > forbiding people to publish benchmark of their products is simply > stupid (and it lacks for legal basis in most countries). The only reason > they do this is to scare kids and be able to make up their own results. > Of course, if you allow people to publish benchmarks there will be > some loosely done, but also there'll be others properly made (and made > by people non-related with any database vendor). Your benchmark was flawed. You made condescending statements about Oracle and MySQL based on your bad data. That's why they don't let you do it. IMHO, worse than having loosely done benchmarks is having people saying > things like "if you fix the bugs, Oracle (out of the box in OLTP > config) will come out 60%" or "Oracle comes out twice as fast as PG on > Linux" without any proof to support this words. At least, benchmarks > are refutable by using logic. Your benchmark was flawed, you didn't tune correctly, and you made statements based on bad data; refute that logic :) -- Jonah H. Harris, Senior DBA myYearbook.com
Re: [PERFORM] Benchmark comparing PostgreSQL, MySQL and Oracle
> First of all, you need to do some research on the benchmark kit itself, > rather than blindly downloading and using one. BenchmarkSQL has significant > bugs in it which affect the result. I can say that authoritatively as I > worked on/with it for quite awhile. Don't trust any result that comes from > BenchmarkSQL. If you fix the bugs, Oracle (out of the box in OLTP config) > will come out 60%. 60% what? > Oracle comes out twice as fast as PG on Linux. And, unless you're using a > significant number of warehouses, MySQL+InnoDB will come out better than PG > as well. I can believe that MySQL could come out faster than PG because I've had previous experience with it being blindingly fast. Of course I've also had experience with it having amazingly poor data integrity. I would be pretty surprised if Oracle were in general twice as fast as PG - what are they doing that much better than what we're doing? I could certainly imagine it being true in cases that rely on specific features we lack (e.g. join removal)? ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Benchmark comparing PostgreSQL, MySQL and Oracle
El Fri, 20 Feb 2009 12:39:41 -0500 "Jonah H. Harris" escribió: > On Fri, Feb 20, 2009 at 6:28 AM, Sergio Lopez > wrote: > > > Hi, > > > > I've made a benchmark comparing PostgreSQL, MySQL and Oracle under > > three environments: GNU/Linux-x86, Solaris-x86 (same machine as > > GNU/Linux) and Solaris-SPARC. I think you might find it interesting: > > > > > > http://blogs.nologin.es/slopez/archives/17-Benchmarking-Databases-I.-Volatile-Storage..html > > > Sorry Segio, > > In addition to violating your Oracle license, you need to learn a > couple things about benchmarking. > > First of all, you need to do some research on the benchmark kit > itself, rather than blindly downloading and using one. BenchmarkSQL > has significant bugs in it which affect the result. I can say that > authoritatively as I worked on/with it for quite awhile. Don't trust > any result that comes from BenchmarkSQL. If you fix the bugs, Oracle > (out of the box in OLTP config) will come out 60%. > > Oracle comes out twice as fast as PG on Linux. And, unless you're > using a significant number of warehouses, MySQL+InnoDB will come out > better than PG as well. > > Second, I didn't see anything in your Oracle settings for parallelism > and I/O tuning. Did you set them? And, based on what you presented, > you didn't set configure the SGA appropriately given the hardware > mentioned. What was your log buffer set to? > > Third, did you manually analyze the Oracle/MySQL databases, because > BenchmarkSQL will automatically analyze Postgres' tables to help the > optimizer... did you do the same for the other databases? > > Fourth, it didn't look like you tuned PG properly either. What was > shared_buffers, wal_buffers, and wal_sync_method set to? > > Fifth, did you do an out-of-the-box install of Oracle, or a custom > one? If out of the box, did you choose OLTP or General? > > There's lots of other things I could go on about in regard to > flushing all the caches prior to starting the benchmarks, filesystem > options, etc. > > Not trying to be rude, but *THIS* is why Oracle, IBM, Microsoft, et > al. don't want people running benchmarks without their permission. > When performing benchmarks, there are a lot of things to take into > consideration. If you're just performing out-of-the-box tests, then > that's fine, but you have to make sure the benchmark kit doesn't > optimize itself for any one of those databases (which it does for PG). > First, thanks for your thoughts, I found them very interesting. On the other hand, I've neved said that what I've done is the Perfect-Marvelous-Definitive Benchmark, it's just a personal project, and I don't have an infinite amount of time to invest on it. Having this said, the benchmark is not as unfair as you thought. I've taken care to prepare all databases to meet similar values for their cache, buffers and I/O configuration (to what's possible given their differences), and the I've left the rest as comes by default (for Oracle I've used the OLTP template). Yes, BenchmarkSQL is NOT the perfect tool for database benchmarking and it is NOT a valid TPC-C test (I've made this clear in the article), but I've looked at its source (you assume I blindly used it, but actually I've even made some changes to make it work with Ingres for other purposes) and I find it fair enough due to the simplicity of the queries it executes. I found no other evident optimization than the "vacuum analyze" in the LoadData application. Obviously, you can optimize the queries to perform better in Oracle, the same way you can do with any other DB, but doing that would be cheating. The key here is to keep the queries as simple as possible, and BenchmarkSQL does this nicely. Of course, my benchmark it's somewhat peculiar by the fact (that you haven't mentioned) that all databases files reside in volatile storage (RAM) by using tmpfs, which makes something similar (but not the same) as using DIRECT_IO with an extremly fast storage. But, again, all databases are given equal consideration. Finally, about the license issue, (also) not trying to be rude, forbiding people to publish benchmark of their products is simply stupid (and it lacks for legal basis in most countries). The only reason they do this is to scare kids and be able to make up their own results. Of course, if you allow people to publish benchmarks there will be some loosely done, but also there'll be others properly made (and made by people non-related with any database vendor). IMHO, worse than having loosely done benchmarks is having people saying things like "if you fix the bugs, Oracle (out of the box in OLTP config) will come out 60%" or "Oracle comes out twice as fast as PG on Linux" without any proof to support this words. At least, benchmarks are refutable by using logic. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Benchmark comparing PostgreSQL, MySQL and Oracle
On Fri, Feb 20, 2009 at 6:28 AM, Sergio Lopez wrote: > Hi, > > I've made a benchmark comparing PostgreSQL, MySQL and Oracle under three > environments: GNU/Linux-x86, Solaris-x86 (same machine as GNU/Linux) and > Solaris-SPARC. I think you might find it interesting: > > > http://blogs.nologin.es/slopez/archives/17-Benchmarking-Databases-I.-Volatile-Storage..html Sorry Segio, In addition to violating your Oracle license, you need to learn a couple things about benchmarking. First of all, you need to do some research on the benchmark kit itself, rather than blindly downloading and using one. BenchmarkSQL has significant bugs in it which affect the result. I can say that authoritatively as I worked on/with it for quite awhile. Don't trust any result that comes from BenchmarkSQL. If you fix the bugs, Oracle (out of the box in OLTP config) will come out 60%. Oracle comes out twice as fast as PG on Linux. And, unless you're using a significant number of warehouses, MySQL+InnoDB will come out better than PG as well. Second, I didn't see anything in your Oracle settings for parallelism and I/O tuning. Did you set them? And, based on what you presented, you didn't set configure the SGA appropriately given the hardware mentioned. What was your log buffer set to? Third, did you manually analyze the Oracle/MySQL databases, because BenchmarkSQL will automatically analyze Postgres' tables to help the optimizer... did you do the same for the other databases? Fourth, it didn't look like you tuned PG properly either. What was shared_buffers, wal_buffers, and wal_sync_method set to? Fifth, did you do an out-of-the-box install of Oracle, or a custom one? If out of the box, did you choose OLTP or General? There's lots of other things I could go on about in regard to flushing all the caches prior to starting the benchmarks, filesystem options, etc. Not trying to be rude, but *THIS* is why Oracle, IBM, Microsoft, et al. don't want people running benchmarks without their permission. When performing benchmarks, there are a lot of things to take into consideration. If you're just performing out-of-the-box tests, then that's fine, but you have to make sure the benchmark kit doesn't optimize itself for any one of those databases (which it does for PG). -- Jonah H. Harris, Senior DBA myYearbook.com
Re: [PERFORM] Benchmark comparing PostgreSQL, MySQL and Oracle
El Fri, 20 Feb 2009 08:36:44 -0800 Alan Hodgson escribió: > On Friday 20 February 2009, Sergio Lopez > wrote: > > Hi, > > > > I've made a benchmark comparing PostgreSQL, MySQL and Oracle under > > three environments: GNU/Linux-x86, Solaris-x86 (same machine as > > GNU/Linux) and Solaris-SPARC. I think you might find it interesting: > > > > http://blogs.nologin.es/slopez/archives/17-Benchmarking-Databases-I.-Vola > >tile-Storage..html > > How did you get permission from Oracle to publish benchmarks? > Damn, my Oracle's Evaluation License should be void now ;-). Sometimes, software licenses are somewhat funny. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Benchmark comparing PostgreSQL, MySQL and Oracle
On Friday 20 February 2009, Sergio Lopez wrote: > Hi, > > I've made a benchmark comparing PostgreSQL, MySQL and Oracle under three > environments: GNU/Linux-x86, Solaris-x86 (same machine as GNU/Linux) and > Solaris-SPARC. I think you might find it interesting: > > http://blogs.nologin.es/slopez/archives/17-Benchmarking-Databases-I.-Vola >tile-Storage..html How did you get permission from Oracle to publish benchmarks? -- Even a sixth-grader can figure out that you can’t borrow money to pay off your debt -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Benchmark comparing PostgreSQL, MySQL and Oracle
Hi, I've made a benchmark comparing PostgreSQL, MySQL and Oracle under three environments: GNU/Linux-x86, Solaris-x86 (same machine as GNU/Linux) and Solaris-SPARC. I think you might find it interesting: http://blogs.nologin.es/slopez/archives/17-Benchmarking-Databases-I.-Volatile-Storage..html -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] not in(subselect) in 8.4
On Fri, Feb 20, 2009 at 3:33 PM, Tom Lane wrote: > =?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?= writes: >> I mean query like: >> select id from foo where id not in ( select id from bar); >> into: >> select f.id from foo f left join bar b on f.id=b.id where b.id is null; > > Postgres does not do that, because they don't mean the same thing --- > the behavior for NULLs in bar.id is different. yes, the obvious assumption here is that all columns are 'not null'; > 8.4 does understand that NOT EXISTS is an antijoin, though. Yes, I noticed that it actually assumes lesser cost. -- GJ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] not in(subselect) in 8.4
=?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?= writes: > I mean query like: > select id from foo where id not in ( select id from bar); > into: > select f.id from foo f left join bar b on f.id=b.id where b.id is null; Postgres does not do that, because they don't mean the same thing --- the behavior for NULLs in bar.id is different. 8.4 does understand that NOT EXISTS is an antijoin, though. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] SSD performance
On Fri, 30 Jan 2009, Scott Carey wrote: For anyone worried about the X 25–M’s ability to withstand lots of write cycles ... Calculate how long it would take you to write 800TB to the drive at a typical rate. For most use cases that’s going to be > 5 years. For the 160GB version, it will take 2x as much data and time to wear it down. This article just came out: http://www.theregister.co.uk/2009/02/20/intel_x25emmental/ and http://www.pcper.com/article.php?aid=669 It seems that the performance of the X25-M degrades over time, as the write levelling algorithm fragments the device into little bits. Especially under database-like access patterns. Matthew -- I quite understand I'm doing algebra on the blackboard and the usual response is to throw objects... If you're going to freak out... wait until party time and invite me along -- Computer Science Lecturer -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] not in(subselect) in 8.4
> Just as a question to Tom and team, maybe it`s time for asktom.postgresql.org? Oracle has it :) -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] not in(subselect) in 8.4
On Fri, Feb 20, 2009 at 11:14 AM, marcin mank wrote: >> Just as a question to Tom and team, > > maybe it`s time for asktom.postgresql.org? Oracle has it :) hehe, on the other hand - that would make my ppl here very skilfull, the only reason I started to praise them about joins, and stuff - is because subselects were slow. (no wonder, when you check two tables against each other, and each holds few M of rows). -- GJ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] not in(subselect) in 8.4
Just as a question to Tom and team, I saw a post a bit ago, about plans for 8.4, and Tom said it is very likely that 8.4 will rewrite subselects into left joins, is it still in plans? I mean query like: select id from foo where id not in ( select id from bar); into: select f.id from foo f left join bar b on f.id=b.id where b.id is null; the latter is most often much much faster on 8.1-8.3; thanks. -- GJ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
