Thread: More Prelimiary DBT-2 Test Results with PostgreSQL 7.3.4
http://developer.osdl.org/markw/74/ I had a couple of hiccups doubling the database size, but I have results with proper linux kernel profile data now. The increase in database size has decreased the overall performance, as expected... I haven't had the opportunity to try different database parameters, such as effective cache sizes, WAL buffers, etc, yet. I'm trying to track down why I'm attempting to insert duplicate keys into one of the tables, which will hopefully be a simple driver error. But will get back with more data when I can. -- Mark Wong - - markw@osdl.org Open Source Development Lab Inc - A non-profit corporation 12725 SW Millikan Way - Suite 400 - Beaverton, OR 97005 (503) 626-2455 x 32 (office) (503) 626-2436 (fax)
markw@osdl.org writes: > http://developer.osdl.org/markw/74/ Are those response times in the right unit? 7-10s? Are these mostly full table scans and big batch updates? Personally, I'm more interested in seeing OLTP-oriented benchmarks testing quick index based transactions in the 20-200ms range, not big i/o-bound batch transactions. There's certainly a need for both, but I think the former are much more important to micro-optimize, at least for my needs. -- greg
On 22 Sep, Greg Stark wrote: > > markw@osdl.org writes: > >> http://developer.osdl.org/markw/74/ > > Are those response times in the right unit? 7-10s? Yeah, the database really isn't tuned at all. I've gotten some suggestions off the list that I will be trying. I'll report them as I complete them. > Are these mostly full table scans and big batch updates? The plans (http://developer.osdl.org/markw/74/db/plan0.out) don't show any table scans. They appears to be mostly index scans. There aren't any batch updates. > Personally, I'm more interested in seeing OLTP-oriented benchmarks testing > quick index based transactions in the 20-200ms range, not big i/o-bound batch > transactions. There's certainly a need for both, but I think the former are > much more important to micro-optimize, at least for my needs. Right, and when this thing is tuned better, I expect the transactions to be down in the 20-200ms range. I do have about 70 drives for the database, so it really shouldn't be i/o bound at with the size of the database I'm using now, which is about 20 or 30 GB. Mark
markw@osdl.org writes: > On 22 Sep, Greg Stark wrote: > > Are those response times in the right unit? 7-10s? > > The plans (http://developer.osdl.org/markw/74/db/plan0.out) don't show > any table scans. They appears to be mostly index scans. There aren't > any batch updates. Would it be easy to generate this file doing "explain analyze" instead? It would give me a better idea what you're doing since I don't know much about the data. Just looking at the plans I don't see how any of these queries could possibly take 7s. There are two that could possibly take a few hundred milliseconds, the others I would expect to be under 100ms. Obviously this is all just a gut reaction, not having looked at the data, and it would depend on the hardware. But none of the queries look like they should measuring reaction times in seconds. > Right, and when this thing is tuned better, I expect the transactions to > be down in the 20-200ms range. I do have about 70 drives for the > database, so it really shouldn't be i/o bound at with the size of the > database I'm using now, which is about 20 or 30 GB. 70 drives for 20-30G? That's pretty extreme. -- greg
On 24 Sep, Greg Stark wrote: > markw@osdl.org writes: > >> On 22 Sep, Greg Stark wrote: >> > Are those response times in the right unit? 7-10s? >> >> The plans (http://developer.osdl.org/markw/74/db/plan0.out) don't show >> any table scans. They appears to be mostly index scans. There aren't >> any batch updates. > > Would it be easy to generate this file doing "explain analyze" instead? It > would give me a better idea what you're doing since I don't know much about > the data. No problem: http://developer.osdl.org/markw/misc/plana.out > Just looking at the plans I don't see how any of these queries could possibly > take 7s. There are two that could possibly take a few hundred milliseconds, > the others I would expect to be under 100ms. Obviously this is all just a gut > reaction, not having looked at the data, and it would depend on the hardware. > But none of the queries look like they should measuring reaction times in > seconds. I have a 4-way 1.5Ghz Xeon with 256KB cache and hyper-threading enabled and 4GB of memory. Although the disk controllers are reducing my physical memory by about 500MB. I can provide more details if you like. >> Right, and when this thing is tuned better, I expect the transactions to >> be down in the 20-200ms range. I do have about 70 drives for the >> database, so it really shouldn't be i/o bound at with the size of the >> database I'm using now, which is about 20 or 30 GB. > > 70 drives for 20-30G? That's pretty extreme. Yeah, we're starting small but we'll increase the database size as we move along. Mark
markw@osdl.org writes: > On 24 Sep, Greg Stark wrote: > > markw@osdl.org writes: > > > >> On 22 Sep, Greg Stark wrote: > >> > Are those response times in the right unit? 7-10s? > > No problem: http://developer.osdl.org/markw/misc/plana.out Ok, I guess I misunderstood you. These queries are taking 0.5ms - 300ms except for the last aggregate query which takes just over 1s. -- greg
> > >> > Are those response times in the right unit? 7-10s? > > > > No problem: http://developer.osdl.org/markw/misc/plana.out > > Ok, I guess I misunderstood you. These queries are taking 0.5ms - 300ms except > for the last aggregate query which takes just over 1s. Yes, but because this is a benchmark he probably has many clients running in parallel. It would be interesting to see the 'explain analyze' from a session that runs concurrently with the benchmark. Andreas
On 25 Sep, Zeugswetter Andreas SB SD wrote: > >> > >> > Are those response times in the right unit? 7-10s? >> > >> > No problem: http://developer.osdl.org/markw/misc/plana.out >> >> Ok, I guess I misunderstood you. These queries are taking 0.5ms - 300ms except >> for the last aggregate query which takes just over 1s. > > Yes, but because this is a benchmark he probably has many clients running > in parallel. > > It would be interesting to see the 'explain analyze' from a session that runs > concurrently with the benchmark. Funny you should mention that, because I generated that 'explain analyze' while I was running the workload. :) Mark
On 24 Sep, Greg Stark wrote: > Ok, I guess I misunderstood you. These queries are taking 0.5ms - 300ms except > for the last aggregate query which takes just over 1s. http://developer.osdl.org/markw/dbt2-pgsql/120/ I have more data where I got the response times for each transaction much lower by increasing shared_buffers and wal_buffers. I wanted to let everyone know in case someone wanted to take another look. I'm working on increasing the database size to drive the system harder. Mark