Re: [PERFORM] TCP Overhead on Local Loopback
On Tue, Apr 3, 2012 at 7:04 PM, Dave Crooke wrote: >On Tue, Apr 3, 2012 at 10:38 AM, Claudio Freire wrote: >> You perform 8 roundtrips minimum per event, so that's 375us per query. >> It doesn't look like much. That's probably Nagle and task switching >> time, I don't think you can get it much lower than that, without >> issuing less queries (ie: using the COPY method). > I may be missing something stated earlier, but surely there are options in > between 7 individual statements and resorting to COPY and temp tables. > I'm thinking of a set of precompiled queries / prepared statements along the > lines of "SELECT FOR UPDATE WHERE foo in (?, ?, ?, ?)" that handle e.g. > 500-1000 records per invocation. Or what about a stored procedure that > updates one record, performing the necessary 7 steps, and then calling that > in bulk? > I agree with the assessment that 375us per statement is pretty decent, and > that going after the communication channel (TCP vs local pipe) is chasing > pennies when there are $100 bills lying around waiting to be collected. Thanks for the suggestions. We ended up re-factoring the code: caching some of the data that we needed in order to eliminate some of the queries previously run and inserting data completion into update statements in the form of UPDATE SET ... (SELECT ...) which brought us down to only one SQL query as opposed to 7 and this brings the processing time down from 4.5ms (previously stated 3ms was not reproduced) down to ~1ms which is great for us. Many thanks for the help from all of you, Ofer -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] bad plan
Hello, I have an extremely bad plan for one of my colleague's query. Basically PostgreSQL chooses to seq scan instead of index scan. This is on: antabif=# select version(); version -- PostgreSQL 9.0.7 on amd64-portbld-freebsd8.2, compiled by GCC cc (GCC) 4.2.1 20070719 [FreeBSD], 64-bit The machines has 4GB of RAM with the following config: - shared_buffers: 512MB - effective_cache_size: 2GB - work_mem: 32MB - maintenance_work_mem: 128MB - default_statistics_target: 300 - temp_buffers: 64MB - wal_buffers: 8MB - checkpoint_segments = 15 The tables have been ANALYZE'd. I've put the EXPLAIN ANALYZE on: - http://www.pastie.org/3731956 : with default config - http://www.pastie.org/3731960 : this is with enable_seq_scan = off - http://www.pastie.org/3731962 : I tried to play on the various cost settings but it's doesn't change anything, except setting random_page_cost to 1 (which will lead to bad plans for other queries, so not a solution) - http://www.pastie.org/3732035 : with enable_hashagg and enable_hashjoin to false I'm currently out of idea why PostgreSQL still chooses a bad plan for this query ... any hint ? Thank you, Julien -- No trees were killed in the creation of this message. However, many electrons were terribly inconvenienced. <> -- 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] H800 + md1200 Performance problem
> From: Tomas Vondra > But the fluctuation, that surely is strange. What are the page cache > dirty limits, i.e. > > cat /proc/sys/vm/dirty_background_ratio > cat /proc/sys/vm/dirty_ratio > > That's probably #1 source I've seen responsible for such issues (on > machines with a lot of RAM). > +1 on that. We're running similar 32 core dell servers with H700s and 128Gb RAM. With those at the defaults (I don't recall if it's 5 and 10 respectively) you're looking at 3.2Gb of dirty pages before pdflush flushes them and 6.4Gb before the process is forced to flush its self.
[PERFORM] bad planning with 75% effective_cache_size
Hi, i've ran into a planning problem. Dedicated PostgreSQL Server: "PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit" Memory: 8GB 4CPUs The problem is reduced to the following: there are 2 tables: -product (3millions rows, 1GB) -product_parent (3000rows, 0.5MB) If effective_cache_size has a greater value (6GB), this select has a bad planning and long query time (2000ms): select distinct product_code from product p_ inner join product_parent par_ on p_.parent_id=par_.id where par_.parent_name like 'aa%' limit 2 If effective_cache_size is smaller (32MB), planning is ok and query is fast. (10ms) In the worst case (effective_cache_size=6GB) the speed depends on the value of 'limit' (in select): if it is smaller, query is slower. (12ms) Good planning: http://explain.depesz.com/s/0FD "Limit (cost=3704.00..3704.02 rows=2 width=5) (actual time=0.215..0.217 rows=1 loops=1)" " -> HashAggregate (cost=3704.00..3712.85 rows=885 width=5) (actual time=0.213..0.215 rows=1 loops=1)" "-> Nested Loop (cost=41.08..3701.79 rows=885 width=5) (actual time=0.053..0.175 rows=53 loops=1)" " -> Index Scan using telepulesbugreport_nev_idx on product_parent par_ (cost=0.00..8.27 rows=1 width=4) (actual time=0.016..0.018 rows=1 loops=1)" "Index Cond: (((parent_name)::text ~>=~ 'aa'::text) AND ((parent_name)::text ~<~ 'ab'::text))" "Filter: ((parent_name)::text ~~ 'aa%'::text)" " -> Bitmap Heap Scan on product p_ (cost=41.08..3680.59 rows=1034 width=9) (actual time=0.033..0.125 rows=53 loops=1)" "Recheck Cond: (parent_id = par_.id)" "-> Bitmap Index Scan on kapubugreport_telepules_id_idx (cost=0.00..40.82 rows=1034 width=0) (actual time=0.024..0.024 rows=53 loops=1)" " Index Cond: (parent_id = par_.id)" "Total runtime: 0.289 ms" Bad planning: http://explain.depesz.com/s/yBh "Limit (cost=0.00..854.37 rows=2 width=5) (actual time=1799.209..4344.041 rows=1 loops=1)" " -> Unique (cost=0.00..378059.84 rows=885 width=5) (actual time=1799.207..4344.038 rows=1 loops=1)" "-> Nested Loop (cost=0.00..378057.63 rows=885 width=5) (actual time=1799.204..4344.020 rows=53 loops=1)" " Join Filter: (p_.parent_id = par_.id)" " -> Index Scan using kapubugreport_irsz_telepules_id_idx on product p_ (cost=0.00..334761.59 rows=2885851 width=9) (actual time=0.015..1660.449 rows=2884172 loops=1)" " -> Materialize (cost=0.00..8.27 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=2884172)" "-> Index Scan using telepulesbugreport_nev_idx on product_parent par_ (cost=0.00..8.27 rows=1 width=4) (actual time=0.013..0.014 rows=1 loops=1)" " Index Cond: (((parent_name)::text ~>=~ 'aa'::text) AND ((parent_name)::text ~<~ 'ab'::text))" " Filter: ((parent_name)::text ~~ 'aa%'::text)" "Total runtime: 4344.083 ms" schema: CREATE TABLE product ( id serial NOT NULL, parent_id integer NOT NULL, product_code character varying COLLATE pg_catalog."C" NOT NULL, product_name character varying NOT NULL ) WITH ( OIDS=FALSE ); ALTER TABLE product OWNER TO aa; CREATE INDEX product_code_parent_id_idx ON product USING btree (product_code COLLATE pg_catalog."C" , parent_id ); CREATE INDEX product_name_idx ON product USING btree (product_name COLLATE pg_catalog."default" ); CREATE INDEX product_parent_id_idx ON product USING btree (parent_id ); CREATE INDEX product_parent_id_ocde_idx ON product USING btree (parent_id , product_code COLLATE pg_catalog."C" ); CREATE TABLE product_parent ( id serial NOT NULL, parent_name character varying NOT NULL, CONSTRAINT telepulesbugreport_pkey PRIMARY KEY (id ) ) WITH ( OIDS=FALSE ); ALTER TABLE product_parent OWNER TO aa; CREATE INDEX product_parent_name_idx ON product_parent USING btree (parent_name COLLATE pg_catalog."default" varchar_pattern_ops); I hope you can help me... :) Best Regads, Istvan
Re: [PERFORM] pg_autovacuum in PG9.x
-Original Message- From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of David Kerr Sent: Wednesday, 4 April 2012 11:37 AM To: pgsql-performance@postgresql.org Subject: [PERFORM] pg_autovacuum in PG9.x Howdy, What is/is there a replacement for pg_autovacuum in PG9.0+ ? I haven't had much luck looking for it in the docs. Thanks! Dave -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance Hi Dave, It's part of core now: http://www.postgresql.org/docs/9.1/static/routine-vacuuming.html#AUTOVACUUM -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Planner selects slow "Bitmap Heap Scan" when "Index Scan" is faster
Hi All
I have a query where the planner makes a wrong cost estimate, it looks
like it underestimates the cost of a "Bitmap Heap Scan" compared to an
"Index Scan".
This it the two plans, I have also pasted them below:
Slow (189ms): http://explain.depesz.com/s/2Wq
Fast (21ms): http://explain.depesz.com/s/ThQ
I have run "VACUUM FULL VERBOSE ANALYZE". I have configured
shared_buffers and effective_cache_size, that didn't solve my problem,
the estimates was kept the same and both queries got faster.
What can I do to fix the cost estimate?
Regards,
Kim Hansen
yield=> SELECT version();
version
---
PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by
gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit
(1 row)
yield=> explain analyze select "filtered_demands"."pol" as "c0" from
"demands"."filtered_demands" as "filtered_demands" where
("filtered_demands"."pod" = 'VELAG') group by "filtered_demands"."pol"
order by "filtered_demands"."pol" ASC NULLS LAST;
QUERY PLAN
---
Sort (cost=38564.80..38564.80 rows=2 width=6) (actual
time=188.987..189.003 rows=221 loops=1)
Sort Key: pol
Sort Method: quicksort Memory: 35kB
-> HashAggregate (cost=38564.77..38564.79 rows=2 width=6) (actual
time=188.796..188.835 rows=221 loops=1)
-> Bitmap Heap Scan on filtered_demands
(cost=566.23..38503.77 rows=24401 width=6) (actual time=6.501..182.634
rows=18588 loops=1)
Recheck Cond: (pod = 'VELAG'::text)
-> Bitmap Index Scan on filtered_demands_pod_pol_idx
(cost=0.00..560.12 rows=24401 width=0) (actual time=4.917..4.917
rows=18588 loops=1)
Index Cond: (pod = 'VELAG'::text)
Total runtime: 189.065 ms
(9 rows)
yield=> set enable_bitmapscan = false;
SET
yield=> explain analyze select "filtered_demands"."pol" as "c0" from
"demands"."filtered_demands" as "filtered_demands" where
("filtered_demands"."pod" = 'VELAG') group by "filtered_demands"."pol"
order by "filtered_demands"."pol" ASC NULLS LAST;
QUERY PLAN
--
Group (cost=0.00..76534.33 rows=2 width=6) (actual
time=0.028..20.823 rows=221 loops=1)
-> Index Scan using filtered_demands_pod_pol_idx on
filtered_demands (cost=0.00..76473.33 rows=24401 width=6) (actual
time=0.027..17.174 rows=18588 loops=1)
Index Cond: (pod = 'VELAG'::text)
Total runtime: 20.877 ms
(4 rows)
yield=>
--
Kim Rydhof Thor Hansen
Vadgårdsvej 3, 2. tv.
2860 Søborg
Phone: +45 3091 2437
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] postgresql.conf setting for max_fsm_pages
Hi All, I am new in using postgresSQL, I now support a system that been running on postgressql. Recently I found that the database are consuming the diskspace rapidly, it starting from 9GB and it now grow until 40GB within 4-5 month. I try to do a full vacuum to the database but then i get this error NOTICE: number of page slots needed (1277312) exceeds max_fsm_pages (819200) HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 1277312. VACUUM I did a vacuum verbose. postgres=# vacuum verbose; and below is the result i got. INFO: free space map contains 1045952 pages in 1896 relations DETAIL: A total of 819200 page slots are in use (including overhead). 1114192 page slots are required to track all free space. Current limits are: 819200 page slots, 2000 relations, using 5007 kB. NOTICE: number of page slots needed (1114192) exceeds max_fsm_pages (819200) HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 1114192. VACUUM As from the postgres documentation, it was advice to set it to 20K to 200K which my current setting is set to 819200 which also over 200K already, so i just wonder what is the max number that i can set for the max_fsm_pages? Is that any impact if i set the value to over 2M ? Thanks. Regards, Chio Chuan -- 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.conf setting for max_fsm_pages
On 04/04/2012 05:22 AM, ahchuan wrote: Hi All, I am new in using postgresSQL, I now support a system that been running on postgressql. Recently I found that the database are consuming the diskspace rapidly, it starting from 9GB and it now grow until 40GB within 4-5 month. I try to do a full vacuum to the database but then i get this error NOTICE: number of page slots needed (1277312) exceeds max_fsm_pages (819200) HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 1277312. VACUUM If you using max_fsm_pages, you are using the version 8.3. I recommend to you that you should update your system to major version. In PostgreSQL 9.0, for example, VACUUM FULL was rewritten and it does a better job. Try to use autovacumm = on always I did a vacuum verbose. postgres=# vacuum verbose; and below is the result i got. INFO: free space map contains 1045952 pages in 1896 relations DETAIL: A total of 819200 page slots are in use (including overhead). 1114192 page slots are required to track all free space. Current limits are: 819200 page slots, 2000 relations, using 5007 kB. NOTICE: number of page slots needed (1114192) exceeds max_fsm_pages (819200) HINT: Consider increasing the configuration parameter "max_fsm_pages" to a value over 1114192. VACUUM As from the postgres documentation, it was advice to set it to 20K to 200K which my current setting is set to 819200 which also over 200K already, so i just wonder what is the max number that i can set for the max_fsm_pages? My advice that you have to test your environment with a double value to 1114192, postgres# SET max_fsm_pages = 2228384; if you need to use 8.3 versions yet. But, again, you should upgrade your system to major version. There are a lot of performance improvements in the new versions. Is that any impact if i set the value to over 2M ? Thanks. Regards, Chio Chuan -- Marcos Luis Ortíz Valmaseda (@marcosluis2186) Data Engineer at UCI http://marcosluis2186.posterous.com 10mo. ANIVERSARIO DE LA CREACION DE LA UNIVERSIDAD DE LAS CIENCIAS INFORMATICAS... CONECTADOS AL FUTURO, CONECTADOS A LA REVOLUCION http://www.uci.cu http://www.facebook.com/universidad.uci http://www.flickr.com/photos/universidad_uci
Re: [PERFORM] bad plan
Julien Cigar wrote: > I tried to play on the various cost settings but it's doesn't > change anything, except setting random_page_cost to 1 (which will > lead to bad plans for other queries, so not a solution) Yeah, you clearly don't have the active portion of your database fully cached, so you don't want random_page_cost to go as low as seq_page_cost. Here's one suggestion to try: random_page_cost = 2 cpu_tuple_cost = 0.05 I have found that combination to work well for me when the level of caching is about where you're seeing it. I am becoming increasingly of the opinion that the default for cpu_tuple_cost should be higher than 0.01. Please let us know whether that helps. -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] postgresql.conf setting for max_fsm_pages
On Wed, Apr 4, 2012 at 3:22 AM, ahchuan wrote: > Hi All, > > > I am new in using postgresSQL, I now support a system that been > running on postgressql. Recently I found that the database are > consuming the diskspace rapidly, it starting from 9GB and it now grow > until 40GB within 4-5 month. > > I try to do a full vacuum to the database but then i get this error > > NOTICE: number of page slots needed (1277312) exceeds max_fsm_pages > (819200) > HINT: Consider increasing the configuration parameter "max_fsm_pages" > to a value over 1277312. > VACUUM I assume you're on 8.3 or earlier. since 8.3 is going into retirement soon, you'd be well served to look at upgrading. > As from the postgres documentation, it was advice to set it to 20K to > 200K which my current setting is set to 819200 which also over 200K > already, so i just wonder what is the max number that i can set for > the max_fsm_pages? The docs are just a guideline for nominal databases. > Is that any impact if i set the value to over 2M ? The fsm uses 6 bytes of memory for each entry, so 2M = 12Megabytes, I'm sure you can spare that much shared memory. I've run it at 10M or higher before on production 8.3 servers. The key is to make sure your vacuuming is aggresive enough. Even in 8.4 and above, where the fsm went away, if autovacuum isn't running or isn't aggressive enough you'll get lots of dead space and bloat. Look at the autovacuum_vacuum_cost_[delay|limit] settings. -- 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] about multiprocessingmassdata
On 5.4.2012 15:44, superman0920 wrote:
> Sure, i will post that at tomorrow.
>
> Today I install PG and MySQL at a Server. I insert 85 rows record
> to each db.
> I execute "select count(*) from poi_all_new" at two db.
> MySQL takes 0.9s
> PG takes 364s
First of all, keep the list (pgsql-performance@postgresql.org) on the
CC. You keep responding to me directly, therefore others can't respond
to your messages (and help you).
Are you sure the comparison was fair, i.e. both machines containing the
same amount of data (not number of rows, amount of data), configured
properly etc.? Have you used the same table structure (how did you
represent geometry data type in MySQL)?
For example I bet you're using MyISAM. In that case, it's comparing
apples to oranges (or maybe cats, so different it is). MyISAM does not
do any MVCC stuff (visibility checking, ...) and simply reads the number
of rows from a catalogue. PostgreSQL actually has to scan the whole
table - that's a big difference. This is probably the only place where
MySQL (with MyISAM beats PostgreSQL). But once you switch to a proper
storage manager (e.g. InnoDB) it'll have to scan the data just like
PostgreSQL - try that.
Anyway, this benchmark is rubbish because you're not going to do this
query often - use queries that actually make sense for the application.
Nevertheless, it seems there's something seriously wrong with your
machine or the environment (OS), probably I/O.
I've done a quick test - I've created the table (without the 'geometry'
column because I don't have postgis installed), filled it with one
million of rows and executed 'select count(*)'. See this:
http://pastebin.com/42cAcCqu
This is what I get:
==
test=# SELECT pg_size_pretty(pg_relation_size('test_table'));
pg_size_pretty
1302 MB
(1 row)
test=#
test=# \timing on
Timing is on.
test=#
test=# SELECT count(*) from test_table;
count
-
100
(1 row)
Time: 2026,695 ms
==
so it's running the 'count(*)' in two seconds. If I run it again, I get
this:
==
test=# SELECT count(*) from test_table;
count
-
100
(1 row)
Time: 270,020 ms
==
Yes, that's 0,27 seconds. And this is *only* my workstation - Core i5 (4
cores), 8GB of RAM, nothing special.
These results obviously depend on the data being available in page
cache. If that's not the case, PostgreSQL needs to read them from the
drive (and then it's basically i/o bound) - I can get about 250 MB/s
from my drives, so I get this:
==
test=# SELECT count(*) from test_table;
count
-
100
(1 row)
Time: 5088,739 ms
==
If you have slower drives, the dependency is about linear (half the
speed -> twice the time). So either your drives are very slow, or
there's something rotten.
I still haven's seen iostat / vmstat output ... that'd tell us much more
about the causes.
Tomas
--
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] about multiprocessingmassdata
Tomas Vondra wrote: > On 5.4.2012 15:44, superman0920 wrote: >> Today I install PG and MySQL at a Server. I insert 85 rows >> record to each db. >> I execute "select count(*) from poi_all_new" at two db. >> MySQL takes 0.9s >> PG takes 364s > Are you sure the comparison was fair, i.e. both machines > containing the same amount of data (not number of rows, amount of > data), configured properly etc.? Don't forget the "hint bits" issue -- if the count(*) was run immediately after the load (without a chance for autovacuum to get in there), all the data was re-written in place to save hint information. I remember how confusing that was for me the first time I saw it. It's very easy to get a false impression of overall PostgreSQL performance from that type of test, and it's the sort of test a lot of people will do on an ad hoc basis. -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] Planner selects slow "Bitmap Heap Scan" when "Index Scan" is faster
Kim Hansen wrote: > I have a query where the planner makes a wrong cost estimate, it > looks like it underestimates the cost of a "Bitmap Heap Scan" > compared to an "Index Scan". > What can I do to fix the cost estimate? Could you try running the query with cpu_tuple_cost = 0.05 and let us know how that goes? -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] heavly load system spec
I know this is a very general question. But if you guys had to specify system (could be one server or cluster), with sustainable transaction rate of 1.5M tps running postgresql, what configuration and hardware would you be looking for ? The transaction distribution there is 90% writes/updates and 10% reads. We're talking 64 linux, Intel/IBM system. I'm trying to see how that compares with Oracle system. 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
Re: [PERFORM] bad planning with 75% effective_cache_size
Istvan Endredy wrote: > i've ran into a planning problem. > If effective_cache_size has a greater value (6GB), this select has > a bad planning and long query time (2000ms): Could you try that configuration with one change and let us know how it goes?: set cpu_tuple_cost = '0.05'; I've seen an awful lot of queries benefit from a higher value for that setting, and I'm starting to think a change to that default is in order. -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] H800 + md1200 Performance problem
On 5.4.2012 17:17, Cesar Martin wrote: > Well, I have installed megacli on server and attach the results in file > megacli.txt. Also we have "Dell Open Manage" install in server, that can > generate a log of H800. I attach to mail with name lsi_0403. > > About dirty limits, I have default values: > vm.dirty_background_ratio = 10 > vm.dirty_ratio = 20 > > I have compared with other server and values are the same, except in > centos 5.4 database production server that have vm.dirty_ratio = 40 Do the other machines have the same amount of RAM? The point is that the values that work with less memory don't work that well with large amounts of memory (and the amount of RAM did grow a lot recently). For example a few years ago the average amount of RAM was ~8GB. In that case the vm.dirty_background_ratio = 10 => 800MB vm.dirty_ratio = 20 => 1600MB which is all peachy if you have a decent controller with a write cache. But turn that to 64GB and suddenly vm.dirty_background_ratio = 10 => 6.4GB vm.dirty_ratio = 20 => 12.8GB The problem is that there'll be a lot of data waiting (for 30 seconds by default), and then suddenly it starts writing all of them to the controller. Such systems behave just as your system - short strokes of writes interleaved with 'no activity'. Greg Smith wrote a nice howto about this - it's from 2007 but all the recommendations are still valid: http://www.westnet.com/~gsmith/content/linux-pdflush.htm TL;DR: - decrease the dirty_background_ratio/dirty_ratio (or use *_bytes) - consider decreasing the dirty_expire_centiseconds T. -- 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] H800 + md1200 Performance problem
On Thu, Apr 5, 2012 at 10:49 AM, Tomas Vondra wrote: > On 5.4.2012 17:17, Cesar Martin wrote: >> Well, I have installed megacli on server and attach the results in file >> megacli.txt. Also we have "Dell Open Manage" install in server, that can >> generate a log of H800. I attach to mail with name lsi_0403. >> >> About dirty limits, I have default values: >> vm.dirty_background_ratio = 10 >> vm.dirty_ratio = 20 >> >> I have compared with other server and values are the same, except in >> centos 5.4 database production server that have vm.dirty_ratio = 40 > > Do the other machines have the same amount of RAM? The point is that the > values that work with less memory don't work that well with large > amounts of memory (and the amount of RAM did grow a lot recently). > > For example a few years ago the average amount of RAM was ~8GB. In that > case the > > vm.dirty_background_ratio = 10 => 800MB > vm.dirty_ratio = 20 => 1600MB > > which is all peachy if you have a decent controller with a write cache. > But turn that to 64GB and suddenly > > vm.dirty_background_ratio = 10 => 6.4GB > vm.dirty_ratio = 20 => 12.8GB > > The problem is that there'll be a lot of data waiting (for 30 seconds by > default), and then suddenly it starts writing all of them to the > controller. Such systems behave just as your system - short strokes of > writes interleaved with 'no activity'. > > Greg Smith wrote a nice howto about this - it's from 2007 but all the > recommendations are still valid: > > http://www.westnet.com/~gsmith/content/linux-pdflush.htm > > TL;DR: > > - decrease the dirty_background_ratio/dirty_ratio (or use *_bytes) > > - consider decreasing the dirty_expire_centiseconds The original problem is read based performance issue though and this will not have any affect on that whatsoever (although it's still excellent advice). Also dd should bypass the o/s buffer cache. I still pretty much convinced that there is a fundamental performance issue with the raid card dell needs to explain. 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] bad plan
On Thu, Apr 5, 2012 at 2:47 PM, Julien Cigar wrote: > - http://www.pastie.org/3731956 : with default config > - http://www.pastie.org/3731960 : this is with enable_seq_scan = off It looks like the join selectivity of (context_to_context_links, ancestors) is being overestimated by almost two orders of magnitude. The optimizer thinks that there are 564 rows in the context_to_context_links table for each taxon_id, while in fact for this query the number is 9. To confirm that this, you can force the selectivity estimate to be 200x lower by adding a geo_id = geod_id where clause to the subquery. If it does help, then the next question would be why is the estimate so much off. It could be either because the stats for context_to_context_links.taxon_id are wrong or because ancestors.taxon_id(subphylum_id = 18830) is a special case. To help figuring this is out, you could run the following to queries and post the results: SELECT floor(log(num,2)) AS nmatch, COUNT(*) AS freq FROM (SELECT COUNT(*) AS num FROM context_to_context_links GROUP BY taxon_id) AS dist GROUP BY 1 ORDER BY 1; SELECT floor(log(num,2)) AS nmatch, COUNT(*) AS freq FROM (SELECT COUNT(*) AS num FROM context_to_context_links WHERE NOT geo_id IS NULL and taxon_id= ANY ( select taxon_id from rab.ancestors where ancestors.subphylum_id = 18830) GROUP BY taxon_id) AS dist GROUP BY 1 ORDER BY 1; If the second distribution has a significantly different shape then cross column statistics are necessary to get good plans. As it happens I'm working on adding this functionality to PostgreSQL and would love to hear more details about your use-case to understand if it would be solved by this work. Regards, Ants Aasma -- Cybertec Schönig & Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de -- 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] H800 + md1200 Performance problem
On 5.4.2012 20:43, Merlin Moncure wrote: > The original problem is read based performance issue though and this > will not have any affect on that whatsoever (although it's still > excellent advice). Also dd should bypass the o/s buffer cache. I > still pretty much convinced that there is a fundamental performance > issue with the raid card dell needs to explain. Well, there are two issues IMHO. 1) Read performance that's not exactly as good as one'd expect from a 12 x 15k SAS RAID10 array. Given that the 15k Cheetah drives usually give like 170 MB/s for sequential reads/writes. I'd definitely expect more than 533 MB/s when reading the data. At least something near 1GB/s (equal to 6 drives). Hmm, the dd read performance seems to grow over time - I wonder if this is the issue with adaptive read policy, as mentioned in the xbitlabs report. Cesar, can you set the read policy to a 'read ahead' megacli -LDSetProp RA -LALL -aALL or maybe 'no read-ahead' megacli -LDSetProp NORA -LALL -aALL It's worth a try, maybe it somehow conflicts with the way kernel handles read-ahead or something. I find these adaptive heuristics a bit unpredictable ... Another thing - I see the patrol reads are enabled. Can you disable that and try how that affects the performance? 2) Write performance behaviour, that's much more suspicious ... Not sure if it's related to the read performance issues. Tomas -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
