Re: [PERFORM] db size
Hi Now, is the bloat in the tables (which tables ?) or in the indexes (which indexes ?), or in the toast tables perhaps, or in the system catalogs or all of the above ? Or perhaps there is a long-forgotten process that got zombified while holding a huge temp table ? (not very likely, but who knows). Use pg_relation_size() and its friends to get an idea of the size of stuff. Can anybody give me some advice on the above? I'm not sure where to start looking or how to start looking -- Adrian Moisey Systems Administrator | CareerJunction | Your Future Starts Here. Web: www.careerjunction.co.za | Email: [EMAIL PROTECTED] Phone: +27 21 686 6820 | Mobile: +27 82 858 7830 | Fax: +27 21 686 6842 -- 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] shared_buffers performance
Greg Smith wrote: On Mon, 14 Apr 2008, Gaetano Mendola wrote: I'm using postgres 8.2.3 on Red Hat compiled with GCC 3.4.6. 8.2.3 has a performance bug that impacts how accurate pgbench results are; you really should be using a later version. http://img84.imageshack.us/my.php?image=totalid7.png as you can see using 64MB as value for shared_buffers I'm obtaining better results. I'm assuming you've read my scaling article at http://www.westnet.com/~gsmith/content/postgresql/pgbench-scaling.htm since you're using the graph template I suggest there. Yes I was basically inspired from that page, my true goal is not to study the effect of shared_buffers (this was a side effect) but to study the performance lose using DRBD on our server. I'm producing similar graph using pgperf without -S, I will post them as soon they are ready. Regards Gaetano Mendola -- 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] shared_buffers performance
Greg Smith wrote: On Mon, 14 Apr 2008, Gaetano Mendola wrote: I'm using postgres 8.2.3 on Red Hat compiled with GCC 3.4.6. 8.2.3 has a performance bug that impacts how accurate pgbench results are; you really should be using a later version. Thank you, I will give it a shot and performe some tests to see if they change a lot, in case I will repeat the entire benchmarks. Regards Gaetano Mendola -- 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] Performance increase with elevator=deadline
* Jeff: Using 4 of these with a dataset of about 30GB across a few files (Machine has 8GB mem) I went from around 100 io/sec to 330 changing to noop. Quite an improvement. If you have a decent controller CFQ is not what you want. I tried deadline as well and it was a touch slower. The controller is a 3ware 9550sx with 4 disks in a raid10. I'll be trying this out on the big array later today. I found it suprising this info wasn't more widespread (the use of CFQ on a good controller). 3ware might be a bit special because the controller has got very deep queues on its own, so many assumptions of the kernel I/O schedulers do not seem to apply. Toying with the kernel/controller queue depths might help, but I haven't done real benchmarks to see if it's actually a difference. A few days ago, I experienced this: On a machine with a 3ware controller, a simple getxattr call on a file in an uncontended directory took several minutes because a PostgreSQL dump process was running in the background (and some other activity of a legacy database which caused frequent fdatasync calls). Clearly, this is unacceptable, and I've since switched to the deadline scheduler, too. So far, this particular behavior hasn't occurred again. -- Florian Weimer[EMAIL PROTECTED] 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] shared_buffers performance
Gaetano Mendola wrote: Hi all, I started to do some performance tests (using pgbench) in order to estimate the DRBD impact on our servers, my plan was to perform some benchmarks without DRBD in order to compare the same benchmark with DRBD. I didn't perform yet the benchmark with DRBD and I'm already facing something I can not explain (I performed at the moment only reads test). I'm using postgres 8.2.3 on Red Hat compiled with GCC 3.4.6. I'm using pgbench with scaling factor with a range [1:500], my server has 4 cores so I'm trying with 16 client and 4000 transaction per client: pgbench -t 4000 -c 16 -S db_perf. I did 3 session using 3 different values of shared_buffers: 64MB, 256MB, 512MB and my server has 2GB. The following graph reports the results: http://img84.imageshack.us/my.php?image=totalid7.png as you can see using 64MB as value for shared_buffers I'm obtaining better results. Is this something expected or I'm looking in the wrong direction? I'm going to perform same tests without using the -S option in pgbench but being a time expensive operation I would like to ear your opinion first. I have complete today the other benchmarks using pgbench in write mode as well, and the following graph resumes the results: http://img440.imageshack.us/my.php?image=totalwbn0.png what I can say here the trend is the opposite seen on the read only mode as increasing the shared_buffers increases the TPS. I still didn't upgrade to 8.2.7 as suggested by Greg Smith because I would like to compare the results obtained till now with the new one (simulations running while I write) using postgres on a DRBD partition; sure as soon the current tests terminate I will upgrade postgres. If you have any suggestions on what you would like to see/know, just let me know. Regards Gaetano Mendola -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Oddly slow queries
Hi everyone, I have some serious performance problems on a database where some queries take up to 100 (or even more) times longer occasionally. The database itself consists of one bigger table (around 3.5GB in size and around 2 mio rows, 4-5 additional indexes) and some really small tables. The queries in question (select's) occasionally take up to 5 mins even if they take ~2-3 sec under normal conditions, there are no sequencial scans done in those queries. There are not many users connected (around 3, maybe) to this database usually since it's still in a testing phase. I tried to hunt down the problem by playing around with resource usage cfg options but it didn't really made a difference. The processes of such queries show up in 'uninterruptible sleep' state more or less for the whole time afaict. When I strace(1) such a process I see tons of _llseek()'s and and quite some read()'s. iostat(1) shows an utilization of close to 100% with iowait of 25-50% (4 CPU's). I assume that the server underequipped in terms of RAM. But even if the the queries need to read data from the disk it seems odd to me that the variance of the time spend is so enormously big. Is this normal or am I correct with my assumtion that there's something wrong? Has anyone an idea what else I could do to find out what's the cause of my problem? The server: Linux 2.6.15.6 PostgreSQL 8.1.8 4x Xeon CPU's 1.5 GB Ram 3x SCSI HD's (probably on a RAID-5 config, not quite sure though) Regards, Tom -- 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] Performance increase with elevator=deadline
On Tue, 15 Apr 2008, Florian Weimer wrote: * Jeff: Using 4 of these with a dataset of about 30GB across a few files (Machine has 8GB mem) I went from around 100 io/sec to 330 changing to noop. Quite an improvement. If you have a decent controller CFQ is not what you want. I tried deadline as well and it was a touch slower. The controller is a 3ware 9550sx with 4 disks in a raid10. I'll be trying this out on the big array later today. I found it suprising this info wasn't more widespread (the use of CFQ on a good controller). 3ware might be a bit special because the controller has got very deep queues on its own, so many assumptions of the kernel I/O schedulers do not seem to apply. Toying with the kernel/controller queue depths might help, but I haven't done real benchmarks to see if it's actually a difference. A few days ago, I experienced this: On a machine with a 3ware controller, a simple getxattr call on a file in an uncontended directory took several minutes because a PostgreSQL dump process was running in the background (and some other activity of a legacy database which caused frequent fdatasync calls). Clearly, this is unacceptable, and I've since switched to the deadline scheduler, too. So far, this particular behavior hasn't occurred again. one other thing to watch out for. up until very recent kernels (2.6.23 or 2.6.24) it was possible for one very busy block device to starve other block devices. they added isolation of queues for different block devices, but I've seen reports that the isolation can end up throttling high performance devices as a result. I haven't had time to really dig into this, but there are tuning knobs available to adjust the que space available to different devices and the reports are significantly better activity on a tuned system. David Lang -- 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] Oddly slow queries
The queries in question (select's) occasionally take up to 5 mins even if they take ~2-3 sec under normal conditions, there are no sequencial scans done in those queries. There are not many users connected (around 3, maybe) to this database usually since it's still in a testing phase. I tried to hunt down the problem by playing around with resource usage cfg options but it didn't really made a difference. Could that be caused by a CHECKPOINT ? -- 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] Oddly slow queries
On 16.04.2008, at 01:24, PFC wrote: The queries in question (select's) occasionally take up to 5 mins even if they take ~2-3 sec under normal conditions, there are no sequencial scans done in those queries. There are not many users connected (around 3, maybe) to this database usually since it's still in a testing phase. I tried to hunt down the problem by playing around with resource usage cfg options but it didn't really made a difference. Could that be caused by a CHECKPOINT ? actually there are a few log (around 12 per day) entries concerning checkpoints: LOG: checkpoints are occurring too frequently (10 seconds apart) HINT: Consider increasing the configuration parameter checkpoint_segments. But wouldn't that only affect write performance? The main problems I'm concerned about affect SELECT queries. Regards, Tom PS: WAL settings are all set to defaults. -- 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] Performance increase with elevator=deadline
Gregory Stark wrote: After some time of trial and error we found that changing the I/O scheduling algorithm to deadline improved I/O performance by a factor 4 (!) for this specific load test. What was the algorithm before? The default algorithm, CFQ I think it is. Yours, Laurenz Albe -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
