Re: [PERFORM] indexes ignored when querying the master table
* Thomas Hägi: > how can i get postgres to use the indexes when querying the master > table? I believe that this is a new feature in PostgreSQL 9.1 ("Allow inheritance table queries to return meaningfully-sorted results"). -- Florian Weimer BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- 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] indexes ignored when querying the master table
On 05/06/2011 05:13 PM, Thomas Hägi wrote: the query "SELECT * FROM data.logs ORDER BY re_timestamp DESC LIMIT 100" does use seq scans on all tables instead of using the existing indexes which takes ages. when issuing the the same query to one of the child tables directly ("SELECT * FROM data.logs_2011 ORDER BY re_timestamp DESC LIMIT 100") the index is used as expected and the data returned quickly. Let's see, cut and paste http://archives.postgresql.org/message-id/4db8ce7d.8030...@2ndquadrant.com and: This is probably the limitation that's fixed in PostgreSQL 9.1 by this commit (following a few others leading up to it): http://archives.postgresql.org/pgsql-committers/2010-11/msg00028.php There was a good example showing what didn't work as expected before (along with an earlier patch that didn't everything the larger 9.1 improvement does) at http://archives.postgresql.org/pgsql-hackers/2009-07/msg01115.php ; "ORDER BY x DESC LIMIT 1" returns the same things as MAX(x). It's a pretty serious issue with the partitioning in earlier versions. I know of multiple people, myself included, who have been compelled to apply this change to an earlier version of PostgreSQL to make larger partitioned databases work correctly. The other option is to manually decompose the queries into ones that target each of the child tables individually, then combine the results, which is no fun either. (Am thinking about a documentation backpatch pointing out this limitation) -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] wildcard makes seq scan on prod db but not in test
Dear list, I have a table with a few million rows and this index: CREATE INDEX bond_item_common_x7 ON bond_item_common ((lower(original_filename))); There are about 2M rows on bonddump and 4M rows on bond90. bonddump is on a 8MB RAM machine, bond90 is on a 72MB RAM machine. The table is analyzed properly both places. I'm an index hint zealot, but aware of our different stances in the matter. :) Dropping the wildcard for the like, both databases uses the index. Is there a way to convince Postgres to try not to do full table scan as much? This is just one of several examples when it happily spends lots of time sequentially going thru tables. Thanks, Marcus psql (9.0.4) Type "help" for help. bonddump=# explain analyze select pic2.objectid bonddump-# from bond_item_common pic2 bonddump-# where bonddump-# lower(pic2.original_filename) like 'this is a test%' ; QUERY PLAN --- Index Scan using bond_item_common_x7 on bond_item_common pic2 (cost=0.01..8.69 rows=208 width=4) (actual time=26.415..26.415 rows=0 loops=1) Index Cond: ((lower((original_filename)::text) >= 'this is a test'::text) AND (lower((original_filename)::text) < 'this is a tesu'::text)) Filter: (lower((original_filename)::text) ~~ 'this is a test%'::text) Total runtime: 26.519 ms (4 rows) psql (9.0.4) bond90=> explain analyze select pic2.objectid bond90-> from bond_item_common pic2 bond90-> where bond90-> lower(pic2.original_filename) like 'this is a test%' ; QUERY PLAN -- Seq Scan on bond_item_common pic2 (cost=0.00..839226.81 rows=475 width=4) (actual time=10599.401..10599.401 rows=0 loops=1) Filter: (lower((original_filename)::text) ~~ 'this is a test%'::text) Total runtime: 10599.425 ms (3 rows) -- 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] wildcard makes seq scan on prod db but not in test
Marcus Engene writes: > There are about 2M rows on bonddump and 4M rows on bond90. > bonddump is on a 8MB RAM machine, bond90 is on a 72MB RAM machine. > The table is analyzed properly both places. I'll bet one database was initialized in C locale and the other not. 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] wildcard makes seq scan on prod db but not in test
Marcus Engene wrote: > I have a table with a few million rows and this index: > CREATE INDEX bond_item_common_x7 ON bond_item_common > ((lower(original_filename))); > Dropping the wildcard for the like, both databases uses the index. > > Is there a way to convince Postgres to try not to do full table > scan as much? That could be a difference is collations. What do you get from the query on this page for each database?: http://wiki.postgresql.org/wiki/Server_Configuration -Kevin -- 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] wildcard makes seq scan on prod db but not in test
On 5/9/11 8:57 , Kevin Grittner wrote: That could be a difference is collations. What do you get from the query on this page for each database?: http://wiki.postgresql.org/wiki/Server_Configuration -Kevin There's indeed a different collation. Why is this affecting? Can i force a column to be ascii? The (fast) test server: version | PostgreSQL 9.0.4 on x86_64-apple-darwin10.7.0, compiled by GCC i686-apple-darwin10-gcc-4.2.1 (GCC) 4.2.1 (Apple Inc. build 5664), 64-bit effective_cache_size | 512MB lc_collate | C lc_ctype | UTF-8 maintenance_work_mem | 128MB max_connections | 100 max_stack_depth | 2MB port | 5435 server_encoding | UTF8 shared_buffers | 512MB temp_buffers | 8192 TimeZone | Europe/Zurich wal_buffers | 1MB work_mem | 128MB (14 rows) The (slow) production server: version | PostgreSQL 9.0.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (Debian 4.3.2-1.1) 4.3.2, 64-bit checkpoint_completion_target | 0.9 checkpoint_segments | 64 effective_cache_size | 48GB lc_collate | en_US.UTF-8 lc_ctype | en_US.UTF-8 listen_addresses | localhost,10.0.0.3,74.50.57.76 maintenance_work_mem | 1GB max_connections | 600 max_stack_depth | 2MB port | 5435 server_encoding | UTF8 shared_buffers | 8GB temp_buffers | 32768 TimeZone | UTC work_mem | 128MB (16 rows) Thanks, Marcus -- 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] wildcard makes seq scan on prod db but not in test
Marcus Engene wrote: > On 5/9/11 8:57 , Kevin Grittner wrote: >> >> That could be a difference is collations. What do you get from >> the query on this page for each database?: >> >> http://wiki.postgresql.org/wiki/Server_Configuration > There's indeed a different collation. Why is this affecting? If the index isn't sorted in an order which leaves the rows you are requesting near one another, it's not very useful for the query. Try this query on both: create temp table order_example (val text); insert into order_example values ('a z'),('ab'),('123'),(' 456'); select * from order_example order by val; > Can i force a column to be ascii? You don't need to do that; you can specify an opclass for the index to tell it that you don't want to order by the normal collation, but rather in a way which will allow the index to be useful for pattern matching: http://www.postgresql.org/docs/9.0/interactive/indexes-opclass.html > The (fast) test server: > effective_cache_size | 512MB > lc_collate | C > lc_ctype | UTF-8 > maintenance_work_mem | 128MB > max_connections | 100 > server_encoding | UTF8 > shared_buffers | 512MB > temp_buffers | 8192 > TimeZone | Europe/Zurich > wal_buffers | 1MB > The (slow) production server: > checkpoint_completion_target | 0.9 > checkpoint_segments | 64 > effective_cache_size | 48GB > lc_collate | en_US.UTF-8 > lc_ctype | en_US.UTF-8 > listen_addresses | localhost,10.0.0.3,74.50.57.76 > maintenance_work_mem | 1GB > max_connections | 600 > server_encoding | UTF8 > shared_buffers | 8GB > temp_buffers | 32768 > TimeZone | UTC As you've discovered, with that many differences, performance tests on one machine may have very little to do with actual performance on the other. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Benchmarking a large server
I've got a fun problem. My employer just purchased some new db servers that are very large. The specs on them are: 4 Intel X7550 CPU's (32 physical cores, HT turned off) 1 TB Ram 1.3 TB Fusion IO (2 1.3 TB Fusion IO Duo cards in a raid 10) 3TB Sas Array (48 15K 146GB spindles) The issue we are running into is how do we benchmark this server, specifically, how do we get valid benchmarks for the Fusion IO card? Normally to eliminate the cache effect, you run iozone and other benchmark suites at 2x the ram. However, we can't do that due to 2TB > 1.3TB. So, does anyone have any suggestions/experiences in benchmarking storage when the storage is smaller then 2x memory? Thanks, Chris
[PERFORM] good performance benchmark
I'm looking for a good ready-to-run broad spectrum (tests cpu bound, i/o bound, various cases, various sizes) benchmark. I tried dbt5 and got it compiled after some effort but it immediately fails upon running so I punted. Anybody have any ideas where I could look? 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] Benchmarking a large server
On Mon, May 9, 2011 at 3:32 PM, Chris Hoover wrote: > I've got a fun problem. > My employer just purchased some new db servers that are very large. The > specs on them are: > 4 Intel X7550 CPU's (32 physical cores, HT turned off) > 1 TB Ram > 1.3 TB Fusion IO (2 1.3 TB Fusion IO Duo cards in a raid 10) > 3TB Sas Array (48 15K 146GB spindles) my GOODNESS! :-D. I mean, just, wow. > The issue we are running into is how do we benchmark this server, > specifically, how do we get valid benchmarks for the Fusion IO card? > Normally to eliminate the cache effect, you run iozone and other benchmark > suites at 2x the ram. However, we can't do that due to 2TB > 1.3TB. > So, does anyone have any suggestions/experiences in benchmarking storage > when the storage is smaller then 2x memory? hm, if it was me, I'd write a small C program that just jumped directly on the device around and did random writes assuming it wasn't formatted. For sequential read, just flush caches and dd the device to /dev/null. Probably someone will suggest better tools though. 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] Benchmarking a large server
hm, if it was me, I'd write a small C program that just jumped directly on the device around and did random writes assuming it wasn't formatted. For sequential read, just flush caches and dd the device to /dev/null. Probably someone will suggest better tools though. I have a program I wrote years ago for a purpose like this. One of the things it can do is write to the filesystem at the same time as dirtying pages in a large shared or non-shared memory region. The idea was to emulate the behavior of a database reasonably accurately. Something like bonnie++ would probably be a good starting point these days though. -- 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] Benchmarking a large server
On May 9, 2011, at 1:32 PM, Chris Hoover wrote: > 1.3 TB Fusion IO (2 1.3 TB Fusion IO Duo cards in a raid 10) Be careful here. What if the entire card hiccups, instead of just a device on it? (We've had that happen to us before.) Depending on how you've done your raid 10, either all your parity is gone or your data is. -- 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] Benchmarking a large server
On 05/09/2011 03:32 PM, Chris Hoover wrote: So, does anyone have any suggestions/experiences in benchmarking storage when the storage is smaller then 2x memory? We had a similar problem when benching our FusionIO setup. What I did was write a script that cleared out the Linux system cache before every iteration of our pgbench tests. You can do that easily with: echo 3 > /proc/sys/vm/drop_caches Executed as root. Then we ran short (10, 20, 30, 40 clients, 10,000 transactions each) pgbench tests, resetting the cache and the DB after every iteration. It was all automated in a script, so it wasn't too much work. We got (roughly) a 15x speed improvement over a 6x15k RPM RAID-10 setup on the same server, with no other changes. This was definitely corroborated after deployment, when our frequent periods of 100% disk IO utilization vanished and were replaced by occasional 20-30% spikes. Even that's an unfair comparison in favor of the RAID, because we added DRBD to the mix because you can't share a PCI card between two servers. If you do have two 1.3TB Duo cards in a 4x640GB RAID-10, you should get even better read times than we did. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email_disclaimer.php for terms and conditions related to this email -- 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] Benchmarking a large server
On Mon, May 9, 2011 at 3:59 PM, David Boreham wrote: > >> hm, if it was me, I'd write a small C program that just jumped >> directly on the device around and did random writes assuming it wasn't >> formatted. For sequential read, just flush caches and dd the device >> to /dev/null. Probably someone will suggest better tools though. > > I have a program I wrote years ago for a purpose like this. One of the > things it can > do is write to the filesystem at the same time as dirtying pages in a large > shared > or non-shared memory region. The idea was to emulate the behavior of a > database > reasonably accurately. Something like bonnie++ would probably be a good > starting > point these days though. The problem with bonnie++ is that the results aren't valid, especially the read tests. I think it refuses to even run unless you set special switches. 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] Benchmarking a large server
On 5/9/2011 3:11 PM, Merlin Moncure wrote: The problem with bonnie++ is that the results aren't valid, especially the read tests. I think it refuses to even run unless you set special switches. I only care about writes ;) But definitely, be careful with the tools. I tend to prefer small programs written in house myself, and of course simply running your application under a synthesized load. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Postgres refusing to use >1 core
I have a multi-threaded app. It uses ~22 threads to query Postgres. Postgres won't use more than 1 CPU core. The 22-threaded app only has 3% CPU utilization because it's mostly waiting on Postgres. Here's the details: The app has a "main" thread that reads table A's 11,000,000 rows, one at a time. The main thread spawns a new thread for each row in table A's data. This new thread: 1. Opens a connection to the DB. 2. Does some calculations on the data, including 1 to 102 SELECTs on table B. 3. With an INSERT query, writes a new row to table C. 4. Closes the connection. 5. Thread dies. Its data is garbage collected eventually. Physical/software details: - Core i7 processor--4 physical cores, but OS sees 8 cores via hyper-threading - 7200 RPM 500 GB HDD - About 1/2 total RAM is free during app execution - Windows 7 x64 - Postgres 9.0.4 32-bit (32-bit required for PostGIS) - App is C# w/ .NET 4.0. PLINQ dispatches threads. Npgsql is Postgres connection tool. At first, the app pounds all 8 cores. But it quickly tapers off, and only 1 core that's busy. The other 7 cores are barely doing a thing. Postgres has 9 open processes. 1 process was slamming that 1 busy core. The other 8 Postgres processes were alive but idle. Each thread creates its own connection. It's not concurrently shared with the main thread or any other threads. I haven't disabled connection pooling; when a thread closes a connection, it's technically releasing it into a pool for later threads to use. Disk utilization is low. The HDD light is off much more than it is on, and a review of total HDD activity put it between 0% and 10% of total capacity. The HDD busy indicator LED would regularly flicker every 0.1 to 0.3 seconds. The app runs 2 different queries on table B. The 1st query is run once, the 2nd query can be run up to 101 times. Table C has redundant indexes: every column referenced in the SQL WHERE clauses for both queries are indexed separately and jointly. E.g., if query X references columns Y and Z, there are 3 indexes: 1. An index for Y 2. An index for Z 3. An index for Y and Z Table C is simple. It has four columns: two integers, a varchar(18), and a boolean. It has no indexes. A primary key on the varchar(18) column is its only constraint. A generalized version of my INSERT command for table C is: *INSERT INTO raw.C VALUES (:L, :M, :N, :P)* I am using parameters to fill in the 4 values. I have verified table C manually, and correct data is being stored in it. Several Google searches suggest Postgres should use multiple cores automatically. I've consulted with Npgsql's developer, and he didn't see how Npgsql itself could force Postgres to one core. (See http://pgfoundry.org/pipermail/npgsql-devel/2011-May/001123.html.) What can I do to improve this? Could I be inadvertently limiting Postgres to one core? Aren Cambre
Re: [PERFORM] wildcard makes seq scan on prod db but not in test
On 5/9/11 9:59 , Kevin Grittner wrote: You don't need to do that; you can specify an opclass for the index to tell it that you don't want to order by the normal collation, but rather in a way which will allow the index to be useful for pattern matching: http://www.postgresql.org/docs/9.0/interactive/indexes-opclass.html -Kevin Hi, Thanks for the explanation. Works brilliantly! Best regards, Marcus For future googlers: http://www.postgresonline.com/journal/archives/78-Why-is-my-index-not-being-used.html drop index bond_item_common_x7; CREATE INDEX bond_item_common_x7 ON bond_item_common USING btree(lower(original_filename) varchar_pattern_ops); bond90=> explain analyze select pic2.objectid from bond_item_common pic2 where lower(pic2.original_filename) like 'this is a test%' ; QUERY PLAN --... Bitmap Heap Scan on bond_item_common pic2 (cost=705.84..82746.05 rows=23870 width=4) (actual time=0.015..0.015 rows=0 loops=1) Filter: (lower((original_filename)::text) ~~ 'this is a test%'::text) -> Bitmap Index Scan on bond_item_common_x7 (cost=0.00..699.87 rows=23870 width=0) (actual time=0.014..0.014 rows=0 loops=1) Index Cond: ((lower((original_filename)::text) ~>=~ 'this is a test'::text) AND (lower((original_filename)::text) ~<~ 'this is a tesu'::text)) Total runtime: 0.033 ms -- 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] Postgres refusing to use >1 core
On Mon, May 9, 2011 at 4:23 PM, Aren Cambre wrote: > I have a multi-threaded app. It uses ~22 threads to query Postgres. > Postgres won't use more than 1 CPU core. The 22-threaded app only has 3% CPU > utilization because it's mostly waiting on Postgres. > Here's the details: > The app has a "main" thread that reads table A's 11,000,000 rows, one at a > time. The main thread spawns a new thread for each row in table A's data. > This new thread: > > Opens a connection to the DB. > Does some calculations on the data, including 1 to 102 SELECTs on table B. > With an INSERT query, writes a new row to table C. > Closes the connection. > Thread dies. Its data is garbage collected eventually. > > Physical/software details: > > Core i7 processor--4 physical cores, but OS sees 8 cores via hyper-threading > 7200 RPM 500 GB HDD > About 1/2 total RAM is free during app execution > Windows 7 x64 > Postgres 9.0.4 32-bit (32-bit required for PostGIS) > App is C# w/ .NET 4.0. PLINQ dispatches threads. Npgsql is Postgres > connection tool. > > At first, the app pounds all 8 cores. But it quickly tapers off, and only 1 > core that's busy. The other 7 cores are barely doing a thing. > Postgres has 9 open processes. 1 process was slamming that 1 busy core. The > other 8 Postgres processes were alive but idle. > Each thread creates its own connection. It's not concurrently shared with > the main thread or any other threads. I haven't disabled connection pooling; > when a thread closes a connection, it's technically releasing it into a pool > for later threads to use. > Disk utilization is low. The HDD light is off much more than it is on, and a > review of total HDD activity put it between 0% and 10% of total capacity. > The HDD busy indicator LED would regularly flicker every 0.1 to 0.3 seconds. > The app runs 2 different queries on table B. The 1st query is run once, the > 2nd query can be run up to 101 times. Table C has redundant indexes: every > column referenced in the SQL WHERE clauses for both queries are indexed > separately and jointly. E.g., if query X references columns Y and Z, there > are 3 indexes: > > An index for Y > An index for Z > An index for Y and Z > > Table C is simple. It has four columns: two integers, a varchar(18), and a > boolean. It has no indexes. A primary key on the varchar(18) column is its > only constraint. > A generalized version of my INSERT command for table C is: > INSERT INTO raw.C VALUES (:L, :M, :N, :P) > I am using parameters to fill in the 4 values. > I have verified table C manually, and correct data is being stored in it. > Several Google searches suggest Postgres should use multiple cores > automatically. I've consulted with Npgsql's developer, and he didn't see how > Npgsql itself could force Postgres to one core. > (See http://pgfoundry.org/pipermail/npgsql-devel/2011-May/001123.html.) > What can I do to improve this? Could I be inadvertently limiting Postgres to > one core? Are you sure you are really using > 1 connection? While your test is running, log onto postgres with psql and grab the output of pg_stat_activity a few times. What do you see? 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] Postgres refusing to use >1 core
Aren Cambre wrote: > Postgres won't use more than 1 CPU core. One *connection* to PostgreSQL won't directly use more than one core. As Merlin suggests, perhaps you're really only running one query at a time? The other possibility is that you're somehow acquiring locks which cause one process to block others. >- Core i7 processor--4 physical cores, but OS sees 8 cores >via hyper-threading Most benchmarks I've seen comparing hyper-threading show that PostgreSQL performs better if you don't try to convince it that one core is actually two different cores. With HT on, you tend to see context switching storms, and performance suffers. > At first, the app pounds all 8 cores. You really shouldn't let the marketers get to you like that. You have four cores, not eight. The most important information for finding your bottleneck is probably going to be in pg_stat_activity and pg_locks. -Kevin -- 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] Postgres refusing to use >1 core
> > Are you sure you are really using > 1 connection? While your test is > running, log onto postgres with psql and grab the output of > pg_stat_activity a few times. What do you see? > Thanks. If a connection corresponds to a process, then this suggests I am using 1 connection for my main thread, and all the threads it spawns are sharing another connection. Aren
Re: [PERFORM] Postgres refusing to use >1 core
> > > Postgres won't use more than 1 CPU core. > > One *connection* to PostgreSQL won't directly use more than one > core. As Merlin suggests, perhaps you're really only running one > query at a time? The other possibility is that you're somehow > acquiring locks which cause one process to block others. > The "one connection" theory appears correct per prior email, if correctly understood what I was reading. I guess I need to head back over to the Npgsql folks and see what I am doing wrong? > >- Core i7 processor--4 physical cores, but OS sees 8 cores > >via hyper-threading > > Most benchmarks I've seen comparing hyper-threading show that > PostgreSQL performs better if you don't try to convince it that one > core is actually two different cores. With HT on, you tend to see > context switching storms, and performance suffers. > > > At first, the app pounds all 8 cores. > > You really shouldn't let the marketers get to you like that. You > have four cores, not eight. > I agree. :-) Just trying to express things as my OS sees and reports on them. Aren
Re: [PERFORM] Postgres refusing to use >1 core
On Mon, May 9, 2011 at 4:50 PM, Aren Cambre wrote: >> Are you sure you are really using > 1 connection? While your test is >> running, log onto postgres with psql and grab the output of >> pg_stat_activity a few times. What do you see? > > Thanks. If a connection corresponds to a process, then this suggests I am > using 1 connection for my main thread, and all the threads it spawns are > sharing another connection. Yes. However I can tell you with absolute certainly that postgres will distribute work across cores. Actually the o/s does it -- each unique connection spawns a single threaded process on the backend. As long as your o/s of choice is supports using more than once process at once, your work will distribute. So, given that, your problem is: *) your code is actually using only one connection *) you have contention on the server side (say, a transaction outstanding that it blocking everyone) *) you have contention on the client side -- a lock in your code or inside npgsql *) your measuring is not correct. so follow the advice above. we need to see pg_stat_activity, and/or pg_locks while your test is running (especially take note of pg_lock records with granted=f) 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] Postgres refusing to use >1 core
Aren Cambre wrote: >>>- Core i7 processor--4 physical cores, but OS sees 8 cores >>>via hyper-threading >> >> Most benchmarks I've seen comparing hyper-threading show that >> PostgreSQL performs better if you don't try to convince it that >> one core is actually two different cores. With HT on, you tend >> to see context switching storms, and performance suffers. >> >> > At first, the app pounds all 8 cores. >> >> You really shouldn't let the marketers get to you like that. You >> have four cores, not eight. >> > > I agree. :-) Just trying to express things as my OS sees and > reports on them. Your OS won't *see* eight processors if you turn of HT. :-) I'm going to pursue this digression just a little further, because it probably will be biting you sooner or later. We make sure to configure the BIOS on our database servers to turn off hyperthreading. It really can make a big difference in performance. -Kevin -- 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] Benchmarking a large server
On 05/09/2011 04:32 PM, Chris Hoover wrote: So, does anyone have any suggestions/experiences in benchmarking storage when the storage is smaller then 2x memory? If you do the Linux trick to drop its caches already mentioned, you can start a database test with zero information in memory. In that situation, whether or not everything could fit in RAM doesn't matter as much; you're starting with none of it in there. In that case, you can benchmark things without having twice as much disk space. You just have to recognize that the test become less useful the longer you run it, and measure the results accordingly. A test starting from that state will start out showing you random I/O speed on the device, slowing moving toward in-memory cached speeds as the benchmark runs for a while. You really need to capture the latency data for every transaction and graph it over time to make any sense of it. If you look at "Using and Abusing pgbench" at http://projects.2ndquadrant.com/talks , starting on P33 I have several slides showing such a test, done with pgbench and pgbench-tools. I added a quick hack to pgbench-tools around then to make it easier to run this specific type of test, but to my knowledge no one else has ever used it. (I've had talks about PostgreSQL in my yard that were better attended than that session, for which I blame Jonah Harris for doing a great talk in the room next door concurrent with it.) -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- 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] Postgres refusing to use >1 core
On 05/09/2011 05:59 PM, Kevin Grittner wrote: I'm going to pursue this digression just a little further, because it probably will be biting you sooner or later. We make sure to configure the BIOS on our database servers to turn off hyperthreading. It really can make a big difference in performance. You're using connection pooling quite aggressively though. The sort of people who do actually benefit from hyperthreading are the ones who don't, where there's lots of CPU time being burnt up in overhead you don't see, and that even a virtual HT processor can help handle. I'm not a big fan of the current hyperthreading implementation, but it's not nearly as bad as the older ones, and there are situations where it is useful. I am unsurprised you don't ever see them on your workload though, you're well tweaked enough to probably be memory or disk limited much of the time. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- 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] good performance benchmark
On 5/9/11 1:41 PM, Merlin Moncure wrote: > I'm looking for a good ready-to-run broad spectrum (tests cpu bound, > i/o bound, various cases, various sizes) benchmark. I tried dbt5 and > got it compiled after some effort but it immediately fails upon > running so I punted. Anybody have any ideas where I could look? I don't know any real benchmark currently that isn't fairly involved to set up. As in, week-long debugging session. I wish it were different, but to date nobody is available to put in the kind of work required to have credible benchmarks which are relatively portable. DBT2 is a bit more stable than DBT5, though, so you might have a better time with it. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] Postgres 9.0.4 + Hot Standby + FusionIO Drive + Performance => Query failed ERROR: catalog is missing 1 attribute(s) for relid 172226
On 5/5/11 12:47 AM, Sethu Prasad wrote: > I did the hot standby configured earlier and at that time I started > using(querying) the standby database. > > May be something missed on the archive command. Most likely, yes. PostgreSQL shouldn't start up under such circumstances, but apparently you fooled it. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] Benchmarking a large server
2011/5/9 Chris Hoover : > I've got a fun problem. > My employer just purchased some new db servers that are very large. The > specs on them are: > 4 Intel X7550 CPU's (32 physical cores, HT turned off) > 1 TB Ram > 1.3 TB Fusion IO (2 1.3 TB Fusion IO Duo cards in a raid 10) > 3TB Sas Array (48 15K 146GB spindles) > The issue we are running into is how do we benchmark this server, > specifically, how do we get valid benchmarks for the Fusion IO card? > Normally to eliminate the cache effect, you run iozone and other benchmark > suites at 2x the ram. However, we can't do that due to 2TB > 1.3TB. > So, does anyone have any suggestions/experiences in benchmarking storage > when the storage is smaller then 2x memory? You can reduce the memory size on server boot. If you use linux, you can add a 'mem=512G' to your boot time parameters. (maybe it supports only K or M, so 512*1024...) > Thanks, > Chris -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support -- 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] Benchmarking a large server
2011/5/9 Chris Hoover: I've got a fun problem. My employer just purchased some new db servers that are very large. The specs on them are: 4 Intel X7550 CPU's (32 physical cores, HT turned off) 1 TB Ram 1.3 TB Fusion IO (2 1.3 TB Fusion IO Duo cards in a raid 10) 3TB Sas Array (48 15K 146GB spindles) The issue we are running into is how do we benchmark this server, specifically, how do we get valid benchmarks for the Fusion IO card? Normally to eliminate the cache effect, you run iozone and other benchmark suites at 2x the ram. However, we can't do that due to 2TB> 1.3TB. So, does anyone have any suggestions/experiences in benchmarking storage when the storage is smaller then 2x memory? Maybe this is a dumb question, but why do you care? If you have 1TB RAM and just a little more actual disk space, it seems like your database will always be cached in memory anyway. If you "eliminate the cach effect," won't the benchmark actually give you the wrong real-life results? Craig -- 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] Benchmarking a large server
On 5/9/2011 6:32 PM, Craig James wrote: Maybe this is a dumb question, but why do you care? If you have 1TB RAM and just a little more actual disk space, it seems like your database will always be cached in memory anyway. If you "eliminate the cach effect," won't the benchmark actually give you the wrong real-life results? The time it takes to populate the cache from a cold start might be important. Also, if it were me, I'd be wanting to check for weird performance behavior at this memory scale. I've seen cases in the past where the VM subsystem went bananas because the designers and testers of its algorithms never considered the physical memory size we deployed. How many times was the kernel tested with this much memory, for example ? (never??) -- 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] Benchmarking a large server
Craig James wrote: Maybe this is a dumb question, but why do you care? If you have 1TB RAM and just a little more actual disk space, it seems like your database will always be cached in memory anyway. If you "eliminate the cach effect," won't the benchmark actually give you the wrong real-life results? If you'd just spent what two FusionIO drives cost, you'd want to make damn sure they worked as expected too. Also, if you look carefully, there is more disk space than this on the server, just not on the SSDs. It's possible this setup could end up with most of RAM filled with data that's stored on the regular drives. In that case the random performance of the busy SSD would be critical. It would likely take a very bad set of disk layout choices for that to happen, but I could see heavy sequential scans of tables in a data warehouse pushing in that direction. Isolating out the SSD performance without using the larger capacity of the regular drives on the server is an excellent idea here, it's just tricky to do. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- 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] Benchmarking a large server
On Mon, 9 May 2011, David Boreham wrote: On 5/9/2011 6:32 PM, Craig James wrote: Maybe this is a dumb question, but why do you care? If you have 1TB RAM and just a little more actual disk space, it seems like your database will always be cached in memory anyway. If you "eliminate the cach effect," won't the benchmark actually give you the wrong real-life results? The time it takes to populate the cache from a cold start might be important. you may also have other processes that will be contending with the disk buffers for memory (for that matter, postgres may use a significant amount of that memory as it's producing it's results) David Lang Also, if it were me, I'd be wanting to check for weird performance behavior at this memory scale. I've seen cases in the past where the VM subsystem went bananas because the designers and testers of its algorithms never considered the physical memory size we deployed. How many times was the kernel tested with this much memory, for example ? (never??) -- 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] Postgres refusing to use >1 core
> > so follow the advice above. we need to see pg_stat_activity, and/or > pg_locks while your test is running (especially take note of pg_lock > records with granted=f) Attached. The database is named de. The process with procpid 3728 has the SQL query for my "main" thread--the one that reads the 12,000,000 rows one by one. procpid 6272 was handling the queries from the ~22 threads, although at the time this was taken, it was idle. But if I monitor it, I can see the queries of tables B and C going through it. I am not clear what to read into pg_locks except that the "main" thread (3728's query) sure has a lot of locks! But all 3728 is doing is reading rows from table A, nothing else. Aren de=# SELECT * FROM pg_locks; locktype| database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted ---+--+--+--+---++---+-+---+--++--+--+- relation |18801 |20333 | | || | | | | 5/9| 3728 | AccessShareLock | t relation |18801 |10985 | | || | | | | 7/27 | 6492 | AccessShareLock | t virtualxid| | | | | 7/27 | | | | | 7/27 | 6492 | ExclusiveLock| t relation |18801 |20013 | | || | | | | 5/9| 3728 | AccessShareLock | t relation |18801 |20329 | | || | | | | 5/9| 3728 | AccessShareLock | t relation |18801 |20391 | | || | | | | 5/9| 3728 | AccessShareLock | t relation |18801 |20007 | | || | | | | 5/9| 3728 | AccessShareLock | t relation |18801 |20001 | | || | | | | 5/9| 3728 | AccessShareLock | t relation |18801 |20390 | | || | | | | 5/9| 3728 | AccessShareLock | t relation |18801 |20339 | | || | | | | 5/9| 3728 | AccessShareLock | t virtualxid| | | | | 5/9| | | | | 5/9| 3728 | ExclusiveLock| t relation |18801 |20317 | | || | | | | 5/9| 3728 | AccessShareLock | t relation |18801 |20019 | | || | | | | 5/9| 3728 | AccessShareLock | t relation |18801 |20398 | | || | | | | 5/9| 3728 | AccessShareLock | t relation |18801 |20389 | | || | | | | 5/9| 3728 | AccessShareLock | t virtualxid| | | | | 6/601815 | | | | | 6/601815 | 6272 | ExclusiveLock| t relation |18801 |20025 | | || | | | | 5/9| 3728 | AccessShareLock | t relation |18801 |20337 | | || | | | | 5/9| 3728 | AccessShareLock | t relation |18801 |19983 | | || | | | | 5/9| 3728 | AccessShareLock | t relation |18801 |19977 | | || | | | | 5/9| 3728 | AccessShareLock | t relation |18801 |19995 | | || | | | | 5/9| 3728 | AccessShareLock | t relation |18801 |19971 | | || | | | | 5/9| 3728 | AccessShareLock | t relation |18801 |20031 | | || | | | | 5/9| 3728 | AccessShareLock | t relation |18801 |20393 | | || | | | | 5/9| 3728 | AccessShareLock | t relation |18801 |20125 |
Re: [PERFORM] Postgres refusing to use >1 core
> > Your OS won't *see* eight processors if you turn of HT. :-) > > I'm going to pursue this digression just a little further, because > it probably will be biting you sooner or later. We make sure to > configure the BIOS on our database servers to turn off > hyperthreading. It really can make a big difference in performance. > OK, OK, I need to admit that this is a Core i7 720QM on an HP Envy 14 laptop. :-) There is no BIOS option to disable HT. I am a doctoral student (but married with kids, about 5-10 years over traditional doctorate student age) and am trying to speed up some of my data analysis with parallelism. Right now the current operation,if run in series, takes 30 hours and only stresses one of the 8 (fake) cores. I'd rather see something that maximizes CPU use, provided that it doesn't overwhelm I/O. Aren
Re: [PERFORM] Postgres refusing to use >1 core
On Mon, May 9, 2011 at 8:15 PM, Aren Cambre wrote: >> Your OS won't *see* eight processors if you turn of HT. :-) >> I'm going to pursue this digression just a little further, because >> it probably will be biting you sooner or later. We make sure to >> configure the BIOS on our database servers to turn off >> hyperthreading. It really can make a big difference in performance. > > OK, OK, I need to admit that this is a Core i7 720QM on an HP Envy 14 > laptop. :-) There is no BIOS option to disable HT. > I am a doctoral student (but married with kids, about 5-10 years over > traditional doctorate student age) and am trying to speed up some of my data > analysis with parallelism. Right now the current operation,if run in series, > takes 30 hours and only stresses one of the 8 (fake) cores. I'd rather see > something that maximizes CPU use, provided that it doesn't overwhelm I/O. The easiest way to use more cores is to just partition the data you want to work on into 4 or more chunks and launch that many multi-threaded processes at once. -- 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] Postgres refusing to use >1 core
On Mon, May 9, 2011 at 10:15 PM, Aren Cambre wrote: >> Your OS won't *see* eight processors if you turn of HT. :-) >> I'm going to pursue this digression just a little further, because >> it probably will be biting you sooner or later. We make sure to >> configure the BIOS on our database servers to turn off >> hyperthreading. It really can make a big difference in performance. > > OK, OK, I need to admit that this is a Core i7 720QM on an HP Envy 14 > laptop. :-) There is no BIOS option to disable HT. > I am a doctoral student (but married with kids, about 5-10 years over > traditional doctorate student age) and am trying to speed up some of my data > analysis with parallelism. Right now the current operation,if run in series, > takes 30 hours and only stresses one of the 8 (fake) cores. I'd rather see > something that maximizes CPU use, provided that it doesn't overwhelm I/O. > Aren how are you reading through the table? if you are using OFFSET, you owe me a steak dinner. 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] Postgres refusing to use >1 core
> > how are you reading through the table? if you are using OFFSET, you > owe me a steak dinner. > > Nope. :-) Below is my exact code for the main thread. The C# PLINQ statement is highlighted. Let me know if I can help to explain this. NpgsqlConnection arrestsConnection = new NpgsqlConnection (Properties.Settings.Default.dbConnectionString); arrestsConnection.Open(); /// First clear out the geocoding table NpgsqlCommand geocodingTableClear = new NpgsqlCommand("TRUNCATE raw.\"TxDPS geocoding\"", arrestsConnection); geocodingTableClear.ExecuteNonQuery(); NpgsqlDataReader arrests = new NpgsqlCommand("SELECT * FROM \"raw\".\"TxDPS all arrests\"", arrestsConnection).ExecuteReader(); /// Based on the pattern defined at /// http://social.msdn.microsoft.com/Forums/en-US/parallelextensions/thread/2f5ce226-c500-4899-a923-99285ace42ae . foreach(IDataRecord arrest in from row in arrests.AsParallel().Cast () select row) { Geocoder geocodeThis = new Geocoder(arrest); geocodeThis.Geocode(); } arrestsConnection.Close(); Aren
Re: [PERFORM] Benchmarking a large server
> How many times was the kernel tested with this much memory, for example > ? (never??) This is actually *extremely* relevant. Take a look at /proc/sys/vm/dirty_ratio and /proc/sys/vm/dirty_background_ratio if you have an older Linux system, or /proc/sys/vm/dirty_bytes, and /proc/sys/vm/dirty_background_bytes with a newer one. On older systems for instance, those are set to 40 and 20 respectively (recent kernels cut these in half). That's significant because ratio is the *percentage* of memory that can remain dirty before causing async, and background_ratio tells it when it should start writing in the background to avoid hitting that higher and much more disruptive number. This is another source of IO that can be completely independent of the checkpoint spikes that long plagued PostgreSQL versions prior to 8.3. With that much memory (1TB!), that's over 100GB of dirty memory before it starts writing that out to disk even with the newer conservative settings. We had to tweak and test for days to find good settings for these, and our servers only have 96GB of RAM. You also have to consider, as fast as the FusionIO drives are, they're still NVRAM, which has write-amplification issues. How fast do you think it can commit 100GB of dirty memory to disk? Even with a background setting of 1%, that's 10GB on your system. That means you'd need to use a very new kernel so you can utilize the dirty_bytes and dirty_background_bytes settings so you can force those settings into more sane levels to avoid unpredictable several-minute long asyncs. I'm not sure how much testing Linux sees on massive hardware like that, but that's just one hidden danger of not properly benchmarking the server and just thinking 1TB of memory and caching the entire dataset is only an improvement. -- Shaun Thomas Peak6 | 141 W. Jackson Blvd. | Suite 800 | Chicago, IL 60604 312-676-8870 stho...@peak6.com __ See http://www.peak6.com/email_disclaimer.php for terms and conditions related to this email -- 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] Postgres refusing to use >1 core
On 10/05/11 10:40, Aren Cambre wrote: > how are you reading through the table? if you are using OFFSET, you > owe me a steak dinner. > > > Nope. :-) > > Below is my exact code for the main thread. The C# PLINQ statement is > highlighted. Let me know if I can help to explain this. Looking at that code, I can't help but wonder why you're not doing it server side in batches. In general, it's really inefficient to use this pattern: rows = runquery("select * from something"); for (item in rows) { // do something with item } Adding implicit parallelism within the loop won't help you much if client-side CPU use isn't your limitation. If each computation done on "item" is very expensive in client-side CPU this pattern makes sense, but otherwise should be avoided in favour of grabbing big chunks of rows and processing them all at once in batch SQL statements that let the database plan I/O efficiently. Even if you're going to rely on client-side looping - say, because of complex or library-based computation that must be done for each record - you must ensure that EACH THREAD HAS ITS OWN CONNECTION, whether that's a new connection established manually or one grabbed from an appropriate pool. Your code below shows no evidence of that at all; you're probably sharing one connection between all the threads, achieving no real parallelism whatsoever. Try limiting your parallel invocation to 4 threads (since that's number of cores you have) and making sure each has its own connection. In your case, that probably means having a new Geocoder instance grab a connection from a pool that contains at least 5 connections (one per Geocoder, plus the main connection). It also looks - though I don't know C# and npgsql so I can't be sure - like you're passing some kind of query result object to the Geocoder. Avoid that, because they might be using the connection to progressively read data behind the scenes in which case you might land up having locking issues, accidentally serializing your parallel work on the single main connection, etc. Instead, retrieve the contents of the IDataRecord (whatever that is) and pass that to the new Geocoder instance, so the new Geocoder has *absolutely* *no* *link* to the arrestsConnection and cannot possibly depend on it accidentally. Even better, use a server-side work queue implementation like pgq, and have each worker use its private connection to ask the server for the next record to process when it's done with the previous one, so you don't need a co-ordinating queue thread in your client side at all. You can also optionally make your client workers independent processes rather than threads that way, which simplifies debugging and resource management. -- Craig Ringer -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] partition query on multiple cores
I have 8-core server, I wanted to ask whether a query can be divided for multiple processors or cores, if it could be what to do in postgresql Thanks
Re: [PERFORM] Benchmarking a large server
On 05/09/2011 11:13 PM, Shaun Thomas wrote: Take a look at /proc/sys/vm/dirty_ratio and /proc/sys/vm/dirty_background_ratio if you have an older Linux system, or /proc/sys/vm/dirty_bytes, and /proc/sys/vm/dirty_background_bytes with a newer one. On older systems for instance, those are set to 40 and 20 respectively (recent kernels cut these in half). 1/4 actually; 10% and 5% starting in kernel 2.6.22. The main sources of this on otherwise new servers I see are RedHat Linux RHEL5 systems running 2.6.18. But as you say, even the lower defaults of the newer kernels can be way too much on a system with lots of RAM. The main downside I've seen of addressing this by using a kernel with dirty_bytes and dirty_background_bytes is that VACUUM can slow down considerably. It really relies on the filesystem having a lot of write cache to perform well. In many cases people are happy with VACUUM throttling if it means nasty I/O spikes go away, but the trade-offs here are still painful at times. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books -- 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] Benchmarking a large server
On 2011-05-09 22:32, Chris Hoover wrote: The issue we are running into is how do we benchmark this server, specifically, how do we get valid benchmarks for the Fusion IO card? Normally to eliminate the cache effect, you run iozone and other benchmark suites at 2x the ram. However, we can't do that due to 2TB > 1.3TB. So, does anyone have any suggestions/experiences in benchmarking storage when the storage is smaller then 2x memory? Oracle's Orion test tool has a configurable cache size parameter - it's a separate download and specifically written to benchmark database oltp and olap like io patterns, see http://www.oracle.com/technetwork/topics/index-089595.html -- Yeb Havinga http://www.mgrid.net/ Mastering Medical Data -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance