Re: [PERFORM] Postgress is taking lot of CPU on our embedded hardware.
Greetings, On Sat, Jan 28, 2012 at 12:51 PM, Jayashankar K B wrote: > Hi, > > I downloaded the source code and cross compiled it into a relocatable package > and copied it to the device. > LTIB was the cross-compile tool chain that was used. Controller is coldfire > MCF54418 CPU. > Here is the configure options I used. Ok, no floating point, and just ~250MHz... small. Anyway, lets not talk about hardware options, because you already have it. About kernel, I'm not sure if on this arch you have the option, but did you enable "PREEMPT" kernel config option? (on menuconfig: "Preemptible Kernel (Low-Latency Desktop)") Or, is that a RT kernel? With such a small CPU, almost any DB engine you put there will be CPU-hungry, but if your CPU usage is under 95%, you know you still have some CPU to spare, on the other hand, if you are 100% CPU, you have to evaluate required response time, and set priorities accordingly.. However, I have found that, even with processes with nice level 19 using 100% CPU, other nice level 0 processes will slow-down unless I set PREEMPT option to on kernel compile options (other issue are IO wait times, at least on my application that uses CF can get quite high). Sincerely, Ildefonso Camargo -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Having I/O problems in simple virtualized environment
Hi list, I am running PostgreSQL 8.1 (CentOS 5.7) on a VM on a single XCP (Xenserver) host. This is a HP server with 8GB, Dual Quad Core, and 2 SATA in RAID-1. The problem is: it's running very slow compared to running it on bare metal, and the VM is starving for I/O bandwidht, so other processes (slow to a crawl. This does not happen on bare metal. I had to replace the server with a bare-metal one, I could not troubleshoot in production. Also it was hard to emulte the workload for that VM in a test environment, so I concentrated on PostgreSQLand why it apparently generated so much I/O. Before I start I should confess having only spotty experience with Xen and PostgreSQL performance testing. I setup a test Xen server created a CentOS5.7 VM with out-of-the-box PostgreSQL and ran: pgbench -i pgbench ; time pgbench -t 10 pgbench This ran for 3:28. Then I replaced the SATA HD with an SSD disk, and reran the test. It ran for 2:46. This seemed strange as I expected the run to finish much faster. I reran the first test on the SATA, and looked at CPU and I/O use. The CPU was not used too much in both the VM (30%) and in dom0 (10%). The I/O use was not much as well, around 8MB/sec in the VM. (Couldn't use iotop in dom0, because of missing kernel support in XCP 1.1). It reran the second test on SSD, and experienced almost the same CPU, and I/O load. (I now probably need to run the same test on bare metal, but didn't get to that yet, all this already ruined my weekend.) Now I came this far, can anybody give me some pointers? Why doesn't pgbench saturate either the CPU or the I/O? Why does using SSD only change the performance this much? Thanks, Ron -- 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] Having I/O problems in simple virtualized environment
On Sun, Jan 29, 2012 at 7:48 PM, Ron Arts wrote: > Hi list, > > I am running PostgreSQL 8.1 (CentOS 5.7) on a VM on a single XCP (Xenserver) > host. > This is a HP server with 8GB, Dual Quad Core, and 2 SATA in RAID-1. > > The problem is: it's running very slow compared to running it on bare metal, > and > the VM is starving for I/O bandwidht, so other processes (slow to a crawl. > This does not happen on bare metal. My experience with xen and postgres, which we use for testing upgrades before doing them on production servers, never in production per-se, is that I/O is very costly on CPU cycles because of the necessary talk between domU and dom0. It's is worthwhile to pin at least one core for exclusive use of the dom0, or at least only let low-load VMs use that core. That frees up cycles on the dom0, which is the one handling all I/O. You'll still have lousy I/O. But it will suck a little less. -- 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] pl/pgsql functions outperforming sql ones?
Pavel, are you saying that the code of the stored function is actually being added to the SQL query, instead of a call to it? For example, I have seen this: SELECT myVar FROM myTable WHERE myVar > 0 AND myFunc(myVar) And seen the SQL body of myVar appended to the outer query: ... Filter: SELECT CASE WHERE myVar < 10 THEN true ELSE false END Is this what we are talking about? Two questions: 1) Is this also done when the function is called as a SELECT column; e.g. would: SELECT myFunc(myVar) AS result - become: SELECT ( SELECT CASE WHERE myVar < 10 THEN true ELSE false END ) AS result? 2) Does that not bypass the benefits of IMMUTABLE? -Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Pavel Stehule Sent: January 28, 2012 1:38 AM To: Carlo Stonebanks Cc: Merlin Moncure; pgsql-performance@postgresql.org Subject: Re: [PERFORM] pl/pgsql functions outperforming sql ones? 2012/1/27 Carlo Stonebanks : > Yes, I did test it - i.e. I ran the functions on their own as I had always > noticed a minor difference between EXPLAIN ANALYZE results and direct query > calls. > > Interesting, so sql functions DON'T cache plans? Will plan-caching be of any > benefit to SQL that makes no reference to any tables? The SQL is emulating > the straight non-set-oriented procedural logic of the original plpgsql. > It is not necessary usually - simple SQL functions are merged to outer query - there are e few cases where this optimization cannot be processed and then there are performance lost. For example this optimization is not possible (sometimes) when some parameter is volatile Regards Pavel Stehule -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance -- 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] Having I/O problems in simple virtualized environment
On Sun, Jan 29, 2012 at 6:18 PM, Ron Arts wrote: > Hi list, > > I am running PostgreSQL 8.1 (CentOS 5.7) on a VM on a single XCP (Xenserver) > host. > This is a HP server with 8GB, Dual Quad Core, and 2 SATA in RAID-1. > > The problem is: it's running very slow compared to running it on bare metal, > and > the VM is starving for I/O bandwidht, so other processes (slow to a crawl. > This does not happen on bare metal. > > I had to replace the server with a bare-metal one, I could not troubleshoot > in production. > Also it was hard to emulte the workload for that VM in a test environment, so > I > concentrated on PostgreSQLand why it apparently generated so much I/O. > > Before I start I should confess having only spotty experience with Xen and > PostgreSQL > performance testing. > > I setup a test Xen server created a CentOS5.7 VM with out-of-the-box > PostgreSQL and ran: > pgbench -i pgbench ; time pgbench -t 10 pgbench > This ran for 3:28. Then I replaced the SATA HD with an SSD disk, and reran > the test. > It ran for 2:46. This seemed strange as I expected the run to finish much > faster. > > I reran the first test on the SATA, and looked at CPU and I/O use. The CPU > was not used > too much in both the VM (30%) and in dom0 (10%). The I/O use was not much as > well, > around 8MB/sec in the VM. (Couldn't use iotop in dom0, because of missing > kernel support > in XCP 1.1). > > It reran the second test on SSD, and experienced almost the same CPU, and I/O > load. > > (I now probably need to run the same test on bare metal, but didn't get to > that yet, > all this already ruined my weekend.) > > Now I came this far, can anybody give me some pointers? Why doesn't pgbench > saturate > either the CPU or the I/O? Why does using SSD only change the performance > this much? Ok, one point: Which IO scheduler are you using? (on dom0 and on the VM). -- 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] Having I/O problems in simple virtualized environment
Op 30-01-12 02:52, Jose Ildefonso Camargo Tolosa schreef: > On Sun, Jan 29, 2012 at 6:18 PM, Ron Arts wrote: >> Hi list, >> >> I am running PostgreSQL 8.1 (CentOS 5.7) on a VM on a single XCP (Xenserver) >> host. >> This is a HP server with 8GB, Dual Quad Core, and 2 SATA in RAID-1. >> >> The problem is: it's running very slow compared to running it on bare metal, >> and >> the VM is starving for I/O bandwidht, so other processes (slow to a crawl. >> This does not happen on bare metal. >> >> I had to replace the server with a bare-metal one, I could not troubleshoot >> in production. >> Also it was hard to emulte the workload for that VM in a test environment, >> so I >> concentrated on PostgreSQLand why it apparently generated so much I/O. >> >> Before I start I should confess having only spotty experience with Xen and >> PostgreSQL >> performance testing. >> >> I setup a test Xen server created a CentOS5.7 VM with out-of-the-box >> PostgreSQL and ran: >> pgbench -i pgbench ; time pgbench -t 10 pgbench >> This ran for 3:28. Then I replaced the SATA HD with an SSD disk, and reran >> the test. >> It ran for 2:46. This seemed strange as I expected the run to finish much >> faster. >> >> I reran the first test on the SATA, and looked at CPU and I/O use. The CPU >> was not used >> too much in both the VM (30%) and in dom0 (10%). The I/O use was not much as >> well, >> around 8MB/sec in the VM. (Couldn't use iotop in dom0, because of missing >> kernel support >> in XCP 1.1). >> >> It reran the second test on SSD, and experienced almost the same CPU, and >> I/O load. >> >> (I now probably need to run the same test on bare metal, but didn't get to >> that yet, >> all this already ruined my weekend.) >> >> Now I came this far, can anybody give me some pointers? Why doesn't pgbench >> saturate >> either the CPU or the I/O? Why does using SSD only change the performance >> this much? > > Ok, one point: Which IO scheduler are you using? (on dom0 and on the VM). Ok, first dom0: For the SSD (hda): # cat /sys/block/sda/queue/scheduler [noop] anticipatory deadline cfq For the SATA: # cat /sys/block/sdb/queue/scheduler noop anticipatory deadline [cfq] Then in the VM: # cat /sys/block/xvda/queue/scheduler [noop] anticipatory deadline cfq Ron -- 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] pl/pgsql functions outperforming sql ones?
Hello 2012/1/30 Carlo Stonebanks : > Pavel, are you saying that the code of the stored function is actually being > added to the SQL query, instead of a call to it? For example, I have seen > this: > > SELECT myVar > FROM myTable > WHERE myVar > 0 AND myFunc(myVar) > > And seen the SQL body of myVar appended to the outer query: > > ... Filter: SELECT CASE WHERE myVar < 10 THEN true ELSE false END > > Is this what we are talking about? Two questions: yes - it is SQL function "inlining" > > 1) Is this also done when the function is called as a SELECT column; > e.g. would: > SELECT myFunc(myVar) AS result > - become: > SELECT ( > SELECT CASE WHERE myVar < 10 THEN true ELSE false END > ) AS result? > yes CREATE OR REPLACE FUNCTION public.fx(integer, integer) RETURNS integer LANGUAGE sql AS $function$ select coalesce($1, $2) $function$ postgres=# explain verbose select fx(random()::int, random()::int); QUERY PLAN -- Result (cost=0.00..0.02 rows=1 width=0) Output: COALESCE((random())::integer, (random())::integer) (2 rows) > 2) Does that not bypass the benefits of IMMUTABLE? > no - optimizator works with expanded query - usually is preferred style a writing SQL functions without flags, because optimizer can work with definition of SQL function and can set well flags. SQL function is not black box for optimizer like plpgsql does. And SQL optimizer chooses a inlining or some other optimizations. Sometimes explicit flags are necessary, but usually not for scalar SQL functions. postgres=# create or replace function public.fxs(int) postgres-# returns setof int as $$ postgres$# select * from generate_series(1,$1) postgres$# $$ language sql; CREATE FUNCTION postgres=# explain verbose select * from fxs(10); QUERY PLAN --- Function Scan on public.fxs (cost=0.25..10.25 rows=1000 width=4) Output: fxs Function Call: fxs(10) (3 rows) postgres=# create or replace function public.fxs(int) returns setof int as $$ select * from generate_series(1,$1) $$ language sql IMMUTABLE; CREATE FUNCTION postgres=# explain verbose select * from fxs(10); QUERY PLAN --- Function Scan on pg_catalog.generate_series (cost=0.00..10.00 rows=1000 width=4) Output: generate_series.generate_series Function Call: generate_series(1, 10) -- inlined query (3 rows) Regards Pavel Stehule > > > -Original Message- > From: pgsql-performance-ow...@postgresql.org > [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Pavel Stehule > Sent: January 28, 2012 1:38 AM > To: Carlo Stonebanks > Cc: Merlin Moncure; pgsql-performance@postgresql.org > Subject: Re: [PERFORM] pl/pgsql functions outperforming sql ones? > > 2012/1/27 Carlo Stonebanks : >> Yes, I did test it - i.e. I ran the functions on their own as I had > always >> noticed a minor difference between EXPLAIN ANALYZE results and direct > query >> calls. >> >> Interesting, so sql functions DON'T cache plans? Will plan-caching be of > any >> benefit to SQL that makes no reference to any tables? The SQL is emulating >> the straight non-set-oriented procedural logic of the original plpgsql. >> > > It is not necessary usually - simple SQL functions are merged to outer > query - there are e few cases where this optimization cannot be > processed and then there are performance lost. > > For example this optimization is not possible (sometimes) when some > parameter is volatile > > Regards > > Pavel Stehule > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
