Re: [PERFORM] RAID controllers etc... was: PostgreSQL data on a NAS device ?
So I guess the PERC4/Di RAID controller is pretty good. It seems that RedHat9 supports it out-of-the-box (driver 1.18f), but I gather from the sites mentioned before that upgrading this driver to 1.18i would be better... ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[PERFORM] Processors vs Memory
If I have a fixed amount of money to spend as a general rule is it better to buy one processor and lots of memory or two processors and less memory for a system which is transactional based (in this case it's handling reservations). I realise the answer will be a generalised one but all the performance bits I've read seem to talk about adjusting memory allocation. The client has received the general advice from their hardware supplier that 2 Xeon processors and less memory is better but for postgresql I'm thinking they might be better off with a single processor and loads of memory. The OS is Red Hat Linux. How long is a piece of string I guess but all comments welcome! TAI Hilary Hilary Forbes - DMR Computer Limited: http://www.dmr.co.uk/ Direct line: 01689 889950 Switchboard: (44) 1689 86 Fax: (44) 1689 860330 E-mail: [EMAIL PROTECTED] ** ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Processors vs Memory
Hilary Forbes wrote: If I have a fixed amount of money to spend as a general rule >is it better to buy one processor and lots of memory or two >processors and less memory for a system which is transactional >based (in this case it's handling reservations). I realise the >answer will be a generalised one but all the performance bits >I've read seem to talk about adjusting memory allocation. >The client has received the general advice from their hardware >supplier that 2 Xeon processors and less memory is better but >for postgresql I'm thinking they might be better off with a single >processor and loads of memory. The OS is Red Hat Linux. Well it depends. If your projected database size is say 2 gigs, then you should buy 2Gigsof RAM and spend rest of the money on processor. But if your database size(max of currrent and projected) is 100GB, obviously you can not buy 100GB of memory that cheaply. So you should look for fast storage. The order of priority is IO, memory and CPU. If database is just big enough to fit in a gig or two, you should get RAM first. Processor is hardly ever a concern w.r.t database unless you are doing a lot in database business logic. HTH Shridhar ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] Postgresql performance
Hi Currently we are running Postgresql v7.3.2 on Redhat Linux OS v9.0. We have Windows2000 client machines inserting records into the Postgresql tables via ODBC. After a few weeks of usage, when we do a \d at the sql prompt, there was a duplicate object name, ie it can be a duplicate row of index or table. When we do a \d table_name, it will show a duplication of column names inside the table. It doesnt affect the insertion/updating of the tables, but when we do a pg_dump -Da -t > /exp/.sql, it will not do a proper backup/dump. Do we need to apply any patches or maintenace? Please be informed that NEC Singapore Pte Ltd is now known as NEC Solutions Asia Pacific Pte Ltd. Our address and contact numbers remain. Email: [EMAIL PROTECTED] http://www.nec.com.sg/ap Thank you, REgards. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Postgresql performance
[EMAIL PROTECTED] writes: > Currently we are running Postgresql v7.3.2 on Redhat Linux OS v9.0. We have > Windows2000 client machines inserting records into the Postgresql tables > via ODBC. > After a few weeks of usage, when we do a \d at the sql prompt, there was a > duplicate object name, ie it can be a duplicate row of index or table. > When we do a \d table_name, it will show a duplication of column names > inside the table. Are you sure you are using 7.3 psql? This sounds like something that could happen with a pre-7.3 (not schema aware) psql, if there are multiple occurrences of the same table name in different schemas. > It doesnt affect the insertion/updating of the tables, but when we do a > pg_dump -Da -t > /exp/.sql, it will not > do a proper backup/dump. I'd wonder about whether you have the right pg_dump, too. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[PERFORM] slow select
I'm using pg 7.3.4 to do a select involving a join on 2 tables.
The query is taking 15 secs which seems extreme to me considering
the indices that exist on the two tables. EXPLAIN ANALYZE shows
that the indices aren't being used. I've done VACUUM ANALYZE on the
db with no change in results. Shouldn't the indices be used?
Below is what I believe to be the relevant information. I haven't
included the definitions of the tables involved in the foreign
key definititions because I don't think they matter.
Any help will be greatly appreciated.
CREATE TABLE shotpoint (
shot_line_num FLOAT4, \
shotpoint FLOAT4,
x FLOAT4,
y FLOAT4,
template_id INT4,
num_chans INT4)
CREATE TABLE shot_record (
shot_line_num FLOAT4,
shotpoint FLOAT4,
index INT2,
dev INT4,
dev_offset INT8,
bin INT4,
shot_time INT8,
record_length INT4,
nav_x FLOAT4,
nav_y FLOAT4,
num_rus INT4,
status INT4 DEFAULT 0,
reel_num INT4,
file_num INT4,
nav_status INT2,
nav_shot_line FLOAT4,
nav_shotpoint FLOAT4,
nav_depth FLOAT4,
sample_skew INT4,
trace_count INT4,
PRIMARY KEY (shot_line_num, shotpoint, index))
ALTER TABLE shotpoint ADD CONSTRAINT shot_line_fk
FOREIGN KEY (shot_line_num)
REFERENCES shot_line(shot_line_num)
CREATE UNIQUE INDEX shotpoint_idx
ON shotpoint(shot_line_num, shotpoint)
ALTER TABLE shot_record ADD CONSTRAINT shot_record_shotpoint_index_fk
FOREIGN KEY (shot_line_num, shotpoint)
REFERENCES shotpoint(shot_line_num, shotpoint)
EXPLAIN ANALYZE SELECT r.shot_line_num, r.shotpoint, index,
shot_time,
record_length, dev,
dev_offset, num_rus, bin, template_id, trace_count
FROM shot_record r, shotpoint p
WHERE p.shot_line_num = r.shot_line_num
AND p.shotpoint = r.shotpoint;
Merge Join (cost=49902.60..52412.21 rows=100221 width=58) (actual
time=12814.28..15000.65 rows=100425 loops=1)
Merge Cond: (("outer".shot_line_num = "inner".shot_line_num) AND ("outer".shotpoint
= "inner".shotpoint))
-> Sort (cost=13460.90..13711.97 rows=100425 width=46) (actual
time=3856.94..4157.01 rows=100425 loops=1)
Sort Key: r.shot_line_num, r.shotpoint
-> Seq Scan on shot_record r (cost=0.00..2663.25 rows=100425 width=46)
(actual time=18.00..1089.00 rows=100425 loops=1)
-> Sort (cost=36441.70..37166.96 rows=290106 width=12) (actual
time=8957.19..9224.09 rows=100749 loops=1)
Sort Key: p.shot_line_num, p.shotpoint
-> Seq Scan on shotpoint p (cost=0.00..5035.06 rows=290106 width=12)
(actual time=7.55..2440.06 rows=290106 loops=1)
Total runtime: 15212.05 msec
***
Medora Schauer
Sr. Software Engineer
Fairfield Industries
14100 Southwest Freeway
Suite 600
Sugar Land, Tx 77478-3469
USA
[EMAIL PROTECTED]
phone: 281-275-7664
fax: 281-275-7551
***
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
[PERFORM] poor cpu utilization on dual cpu box
Folks, Im hoping someone can give me some pointers to resolving an issue with postgres and its ability to utilize multiple CPUs effectively. The issue is that no matter how much query load we throw at our server it seems almost impossible to get it to utilize more than 50% cpu on a dual-cpu box. For a single connection we can use all of one CPU, but multiple connections fail to increase the overall utilization (although they do cause it to spread across CPUs). The platform is a dual CPU 2.8Ghz P4 Xeon Intel box (hyperthreading disabled) running a fairly standard Redhat 9 distribution. We are using postgres on this platform with a moderate sized data set (some hundreds of megs of data). The tests perform no updates and simply hit the server with a single large complex query via a multithreaded java/jdbc client. To avoid network distortion we run the client on the localhost (its cpu load is minimal). We are running with shared buffers large enough to hold the entire database and sort memory of 64m, should easily be enough to prevent sorting to disk. At this point Ive tried everything I can think of to diagnose this - checking the pg_locks table indicates that even under heavy load there are no ungranted locks, so it would appear not to be a locking issue. Vmstat/iostat show no excessive figures for network or io waits. The only outlandish figure is that context switches which spike up to 250,000/sec (seems large). By all indications, postgres is waiting internally as if it is somehow singlethreaded. However the documentation clearly indicates this should not be so. Can anyone give me some pointers as to why postgres would be doing this? Is postgres really multi-process capable or are the processes ultimately waiting on each other to run queries or access shared memory? On a second note, has anyone got some tips on how to profile postgres in this kind of situation? I have tried using gprof, but because postgres spawns its processes dynamically I always end up profiling the postmaster (not very useful). Thanking in advance for any help! Cheers, Simon. Ps. posted this to general, but then realised this is a better forum - sorry for the cross. Do you Yahoo!? The New Yahoo! Shopping - with improved product search
Re: [PERFORM] RAID controllers etc... was: PostgreSQL data on a
Heya On Wed, 2003-10-22 at 01:13, Alexander Priem wrote: > So I guess the PERC4/Di RAID controller is pretty good. It seems that > RedHat9 supports it out-of-the-box (driver 1.18f), but I gather from the > sites mentioned before that upgrading this driver to 1.18i would be > better... Actually upgrading to the Megaraid_2 driver would be even better. There are a -ton- of performance enhancements with it. Depending on your performance needs and testing capabilities, I would highly recommend trying it out. Will signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Postgresql performance
NEC, > After a few weeks of usage, when we do a \d at the sql prompt, there was a > duplicate object name, ie it can be a duplicate row of index or table. > When we do a \d table_name, it will show a duplication of column names > inside the table. I think the version of PSQL and pg_dump which you are using do not match the back-end database version. Correct this. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] poor cpu utilization on dual cpu box
Simon, > The issue is that no matter how much query load we throw at our server it > seems almost impossible to get it to utilize more than 50% cpu on a > dual-cpu box. For a single connection we can use all of one CPU, but > multiple connections fail to increase the overall utilization (although > they do cause it to spread across CPUs). This is perfectly normal. It's a rare x86 machine (read fiber channel) where you don't saturate the I/O or the RAM *long* before you saturate the CPU. Transactional databases are an I/O intensive operation, not a CPU-intensive one. > We are running with shared buffers large enough to hold the > entire database Which is bad. This is not what shared buffers are for. See: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] slow select
Medora, > I'm using pg 7.3.4 to do a select involving a join on 2 tables. > The query is taking 15 secs which seems extreme to me considering > the indices that exist on the two tables. EXPLAIN ANALYZE shows > that the indices aren't being used. I've done VACUUM ANALYZE on the > db with no change in results. Shouldn't the indices be used? No. You're selecting 100,000 records. For such a large record dump, a seq scan is usually faster. If you don't believe me, try setting enable_seqscan=false and see how long the query takes. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Low Insert/Update Performance
Rhaoni, > Total runtime: 3.56 msec > (4 rows) Well, from that figure it's not the query that's holding you up. You said that the system bogs down when you're doing a whole series of these updates, or just one? If the former, then I'm afraid that it's your disk that's to blame ... large numbers of rapid-fire updates simply won't be fast on a single IDE disk. Try getting a second disk and moving the transaction log to it. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Processors vs Memory
On Wed, 22 Oct 2003, Hilary Forbes wrote: > If I have a fixed amount of money to spend as a general rule is it > better to buy one processor and lots of memory or two processors and > less memory for a system which is transactional based (in this case > it's handling reservations). I realise the answer will be a generalised > one but all the performance bits I've read seem to talk about adjusting > memory allocation. The client has received the general advice from > their hardware supplier that 2 Xeon processors and less memory is better > but for postgresql I'm thinking they might be better off with a single > processor and loads of memory. The OS is Red Hat Linux. My opinion is that two CPUs is optimal because it allows the OS to operate in parallel to the database. After the second CPU, the only advantage is if you are doing a lot of parallel access. Go for fast I/O first, a RAID1+0 setup is optimal for smaller numbers of drives (works on 4 or 6 drives nicely) and RAID5 is optimal for a larger number of drives (works well on 10 or more drives). Always use hardware RAID with battery backed cache for a heavily updated database. For a reports database software RAID is quite acceptable. There's a limit to how much memory you can throw at the problem if you're on 32 bit hardware, and that limit is about 2 to 4 gig. While you can install more, it usually makes little or no difference. Lastly, don't forget to tune your database and server once you have it up and running: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Low Insert/Update Performance
Rhaoni, >First of all , thank's for your atention and fast answer. The system > really bogs down when I'm doing a whole series of these updates. That would be consistent with a single-disk problem. > Take a > look at my postgresql.conf I'm afraid of putting some parameters wrong ( > too high or too low ). And sorry if it sounds stupid but how can I move the > transaction log to this second disk ? 1) Install the 2nd disk. 2) With PostgreSQL shut down, copy the PGDATA/pg_xlog directory to the 2nd disk. 3) delete the old pg_xlog directory 4) Symlink or Mount the new pg_xlog directory under PGDATA as PGDATA/pg_xlog. 5) Restart Postgres. What I am interested in is your original assertion that this ran faster on Oracle. Was Oracle installed on this particular machine, or a different one? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] poor cpu utilization on dual cpu box
Josh Berkus <[EMAIL PROTECTED]> writes: >> We are running with shared buffers large enough to hold the >> entire database > Which is bad. This is not what shared buffers are for. See: > http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html In fact, that may be the cause of the performance issue. The high context-swap rate suggests heavy contention for shared-memory data structures. The first explanation that occurs to me is that too much time is being spent managing the buffer hashtable, causing that to become a serialization bottleneck. Try setting shared_buffers to 1 or so and see if it gets better. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] poor cpu utilization on dual cpu box
The suggestion that we are saturating the memory bus makes a lot of sense. We originally started with a low setting for shared buffers and resized it to fit all our tables (since we have memory to burn). That improved stand alone performance but not concurrent performance - this would explain that phenomenon somewhat. Will investigate further down this track. Thanks to everyone who responded! Cheers, Simon. Josh Berkus <[EMAIL PROTECTED]> wrote:Simon, > The issue is that no matter how much query load we throw at our server it > seems almost impossible to get it to utilize more than 50% cpu on a > dual-cpu box. For a single connection we can use all of one CPU, but > multiple connections fail to increase the overall utilization (although > they do cause it to spread across CPUs). This is perfectly normal. It's a rare x86 machine (read fiber channel) where you don't saturate the I/O or the RAM *long* before you saturate the CPU. Transactional databases are an I/O intensive operation, not a CPU-intensive one. > We are running with shared buffers large enough to hold the > entire database Which is bad. This is not what shared buffers are for. See: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] vacuum locking
> "RN" == Rob Nagler <[EMAIL PROTECTED]> writes: RN> Vendor: DELL Model: PERCRAID Mirror Rev: V1.0 RN> Type: Direct-AccessANSI SCSI revision: 02 AMI or Adaptec based? If AMI, make sure it has write-back cache enabled (and you have battery backup!), and disable the 'readahead' feature if you can. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] vacuum locking
> "RN" == Rob Nagler <[EMAIL PROTECTED]> writes: RN> This solution doesn't really fix the fact that VACUUM consumes the RN> disk while it is running. I want to avoid the erratic performance on RN> my web server when VACUUM is running. What's the disk utilization proir to running vacuum? If it is hovering around 95% or more of capacity, of course you're gonna overwhelm it. This ain't Star Trek -- the engines can't run at 110%, Cap'n! -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] slow select
>
> Medora,
>
> > I'm using pg 7.3.4 to do a select involving a join on 2 tables.
> > The query is taking 15 secs which seems extreme to me considering
> > the indices that exist on the two tables. EXPLAIN ANALYZE shows
> > that the indices aren't being used. I've done VACUUM ANALYZE on the
> > db with no change in results. Shouldn't the indices be used?
>
> No. You're selecting 100,000 records. For such a large
> record dump, a seq
> scan is usually faster.
>
> If you don't believe me, try setting enable_seqscan=false and
> see how long the
> query takes.
I did as you suggested (set enable_seqscan = false) and the query now takes 6 sec (vs
15 secs before) :
Merge Join (cost=0.00..287726.10 rows=100221 width=58) (actual time=61.60..5975.63
rows=100425 loops=1)
Merge Cond: (("outer".shot_line_num = "inner".shot_line_num) AND ("outer".shotpoint
= "inner".shotpoint))
-> Index Scan using hsot_record_idx on shot_record r (cost=0.00..123080.11
rows=100425 width=46) (actual time=24.15..2710.31 rows=100425 loops=1)
-> Index Scan using shotpoint_idx on shotpoint p (cost=0.00..467924.54
rows=290106 width=12) (actual time=37.38..1379.64 rows=100749 loops=1)
Total runtime: 6086.32 msec
So why did were the indices not used before when they yield a better plan?
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [PERFORM] slow select
Medora, > So why did were the indices not used before when they yield a better plan? Your .conf settings, most likely. I'd lower your random_page_cost and raise your effective_cache_size. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] slow select
Josh, > > So why did were the indices not used before when they yield > a better plan? > > Your .conf settings, most likely. I'd lower your > random_page_cost and raise > your effective_cache_size. Increasing effective_cache_size to 1 did it. The query now takes 4 secs. I left random_page_cost at the default value of 4. I thought, mistakenly apparently, that our database was relatively itty bitty and so haven't messed with the .conf file. Guess I better take a look at all the settings (I know where the docs are). Thanks for your help, Medora *** Medora Schauer Sr. Software Engineer Fairfield Industries *** ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Tuning for mid-size server
On Tue, 2003-10-21 at 14:27, Christopher Browne wrote:
> In the last exciting episode, [EMAIL PROTECTED] (Josh Berkus) wrote:
> > So what is the ceiling on 32-bit processors for RAM? Most of the
> > 64-bit vendors are pushing Athalon64 and G5 as "breaking the 4GB
> > barrier", and even I can do the math on 2^32. All these 64-bit
> > vendors, then, are talking about the limit on ram *per application*
> > and not per machine?
>
> I have been seeing ia-32 servers with 8GB of RAM; it looks as though
> there are ways of having them support ("physically, in theory, if you
> could get a suitable motherboard") as much as 64GB.
>
> But that certainly doesn't get you past 2^32 bytes per process, and
> possibly not past 2^31 bytes/process.
>
> >From Linux kernel help:
>
> CONFIG_NOHIGHMEM:
>
> Linux can use up to 64 Gigabytes of physical memory on x86
> systems. However, the address space of 32-bit x86 processors is
> only 4 Gigabytes large. That means that, if you have a large
> amount of physical memory, not all of it can be "permanently
> mapped" by the kernel. The physical memory that's not permanently
> mapped is called "high memory".
>
> And that leaves open the question of how much shared memory you can
> address. That presumably has to fit into the 4GB, and if your
> PostgreSQL processes had (by some fluke) 4GB of shared memory, there
> wouldn't be any "local" memory for sort memory and the likes.
>
> Add to that the consideration that there are reports of Linux "falling
> over" when you get to right around 2GB/4GB. I ran a torture test a
> while back that _looked_ like it was running into that; I can't verify
> that, unfortunately.
Well thank goodness that Linux & Postgres work so well on Alpha
and long-mode AMD64.
--
-
Ron Johnson, Jr. [EMAIL PROTECTED]
Jefferson, LA USA
"Fear the Penguin!!"
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] slow select
Medora, > Increasing effective_cache_size to 1 did it. That would be 78MB RAM. If you have more than that available, you can increase it further. Ideally, it should be about 2/3 to 3/4 of available RAM. >The query now > takes 4 secs. I left random_page_cost at the default value of 4. > I thought, mistakenly apparently, that our database was relatively > itty bitty and so haven't messed with the .conf file. Actually, for a itty bitty database on a fast machine, you definitely want to lower random_page_cost. It's a large database that would make you cautious about this. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Low Insert/Update Performance
Hi List;
Here follow the update query, explain analyze of it , my postgresql.conf and
my db configuration. This is my first PostgreSQL DB so I would like to know if
its performance is normal !
If there is some postgresql.conf's parameter that you think will optmize the
database just tell me !!!
QUERY:
update ftnfco00 set
empfil = 0,
data_entrega = NULL,
situacao_nf = 'N',
cod_fiscal = 6101,
base_calc_icm_trib = '264.1'::float8,
nf_emitida = 'S',
tipo_cad_clicre ='C',
cod_cliente = '55380'::float8,
cod_repres = 8,
cod_tipo_cliente = 1,
estado_cliente = 'PR',
pais_cliente = 978,
classif_cliente = '',
cod_suframa = ' ',
ordem_compra = ' ',
banco_cobranca = 0,
situacao_comissao = '0',
perc_comissao = '6'::float8,
emitir_bloqueto = 'N',
cod_tipo_venda = 0,
prazo_pgto_01 = 68,
prazo_pgto_02 = 0,
prazo_pgto_03 = 0,
prazo_pgto_04 = 0,
prazo_pgto_05 = 0,
prazo_pgto_06 = 0,
prazo_pgto_07 = 0,
prazo_pgto_08 = 0,
prazo_pgto_09 = 0,
prazo_pgto_desc_duplic = 0,
perc_desc_duplic = '0'::float8,
qtde_fisica = '5'::float8,
vlr_liquido = '264.1'::float8,
vlr_ipi = '0'::float8,
vlr_compl_nf = 0,
vlr_frete = '26.4'::float8,
vlr_acresc_fin_emp = 0,
vlr_acresc_fin_tab = 0,
vlr_dolar_vcto_dupl = 1,
vlr_dolar_dia_fatur = 1,
vlr_icm = '31.69'::float8,
vlr_ipi_consignacao = 0,
perc_juro_dia = '0.15'::float8,
cod_texto_padrao = 19,
cod_transp = 571,
cod_transp_redesp = 0,
placa_transp = '',
peso_liquido = '5.832'::float8,
peso_bruto = '6.522'::float8,
qtde_volumes = 5,
proxima_nf = '0'::float8,
lista_preco = '03RS',
lista_preco_basico = ' ',
atu_guia_embarque = 'N',
vlr_pis_cofins = 0,
qtde_duzias = 5,
obs_nf = 'ORDEM DE COMPRA 40851583',
margem_comercial = 0,
margem_operac = 0
where
emp = 909 and
fil = 101 and
nota_fiscal = '57798'::float8 and
serie = 'UNICA' and
data_emissao = cast('2003-01-03 00:00:00'::timestamp as timestamp)
EXPLAIN ANALYZE:
QUERY
PLAN $
-$
Index Scan using ftnfco06 on ftnfco00 (cost=0.00..20.20 rows=1 width=535)
(actual time=1.14..1.27 rows=1 loops=1)
Index Cond: ((emp = 909::numeric) AND (fil = 101::numeric) AND (data_emissao
= '2003-01-03 00:00:00'::timestamp without ti$
Filter: (((nota_fiscal)::double precision = 57798::double precision) AND
(serie = 'UNICA'::character varying))
Total runtime: 3.56 msec
(4 rows)
postgresql.conf:
# Connection Parameters
#
tcpip_socket = true
#ssl = false
max_connections = 10
#superuser_reserved_connections = 2
port = 5432
#hostname_lookup = false
#show_source_port = false
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
#virtual_host = ''
#krb_server_keyfile = ''
#
# Shared Memory Size
#
shared_buffers = 1 # min max_connections*2 or 16, 8KB each
max_fsm_relations = 2000# min 10, fsm is free space map, ~40 bytes
max_fsm_pages = 2 # min 1000, fsm is free space map, ~6 bytes
#max_locks_per_transaction = 64 # min 10
#wal_buffers = # min 4, typically 8KB each
#
# Non-shared Memory Sizes
#
sort_mem = 8000 # min 64, size in KB
vacuum_mem = 16192 # min 1024, size in KB
#
# Write-ahead log (WAL)
#
checkpoint_segments = 9 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#
#commit_delay = 0 # range 0-10, in microseconds
#commit_siblings = 5# range 1-1000
#
fsync = false
#wal_sync_method = fsync# the default varies across platforms:
# # fsync, fdatasync, open_sync, or open_datasync
#wal_debug = 0 # range 0-16
#
# Optimizer Parameters
#
enable_seqscan = false
enable_indexscan = true
enable_tidscan = true
enable_sort = true
enable_nestloop = true
enable_mergejoin = true
enable_hashjoin = true
effective_cache_size = 16000# typically 8KB each
#random_page_cost = 4 # units are one sequential page fetch cost
#cpu_tuple_cost = 0.01 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
#cpu_operator_cost = 0.0025 # (same)
default_statistics_target = 1000# range 1-1000
#
# GEQO Optimizer Parameters
#
#geqo = true
#geqo_selection_bias = 2.0 # range 1.5-2.0
#geqo_threshold = 11
#geqo_pool_size = 0 # default based on tables in statement,
# range 128-1024
#geqo_effort = 1
#geqo_generations = 0
#geqo_random_seed = -1 # auto-compute seed
#
# Message display
#
#server_min_messages = notice # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error, log, fatal,
Re: [PERFORM] vacuum locking
Vivek Khera writes: > AMI or Adaptec based? Adaptec, I think. AIC-7899 LVD SCSI is what dmidecode says, and Red Hat/Adaptec aacraid driver, Aug 18 2003 is what comes up when it boots. I haven't be able to use the aac utilities with this driver, however, so it's hard to interrogate the device. > If AMI, make sure it has write-back cache enabled (and you have > battery backup!), and disable the 'readahead' feature if you can. I can't do this so easily. It's at a colo, and it's production. I doubt this has anything to do with this problem, anyway. We're talking about hundreds of megabytes of data. > What's the disk utilization proir to running vacuum? If it is > hovering around 95% or more of capacity, of course you're gonna > overwhelm it. Here's the vmstat 5 at a random time: procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs us sy id 0 0 0 272372 38416 78220 375048 0 3 2 00 0 2 2 0 0 0 0 272372 3 78320 375660 0 034 274 382 284 5 1 94 0 1 0 272372 23012 78372 375924 0 025 558 445 488 8 2 90 1 0 0 272368 22744 78472 376192 0 6 125 594 364 664 9 3 88 And here's it during vacuum: procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs us sy id 1 2 1 277292 9620 72028 409664 46 32 4934 4812 1697 966 8 4 88 0 3 0 277272 9588 72096 412964 61 0 7303 2478 1391 976 3 3 94 2 2 0 277336 9644 72136 393264 1326 32 2827 2954 1693 1519 8 3 89 The pages are growing proportionately with the number of tuples, btw. Here's a vacuum snippet from a few days ago after a clean import, running every 15 minutes: INFO: Removed 2192 tuples in 275 pages. CPU 0.06s/0.01u sec elapsed 0.91 sec. INFO: Pages 24458: Changed 260, Empty 0; Tup 1029223: Vac 2192, Keep 3876, UnUsed 26. Total CPU 2.91s/2.22u sec elapsed 65.74 sec. And here's the latest today, running every 2 hours: INFO: Removed 28740 tuples in 1548 pages. CPU 0.08s/0.06u sec elapsed 3.73 sec. INFO: Pages 27277: Changed 367, Empty 0; Tup 1114178: Vac 28740, Keep 1502, UnUsed 10631. Total CPU 4.78s/4.09u sec elapsed 258.10 sec. The big tables/indexes are taking longer, but it's a big CPU/elapsed time savings to vacuum every two hours vs every 15 minutes. There's still the problem that when vacuum is running interactive performance drops dramatically. A query that takes a couple of seconds to run when the db isn't being vacuumed will take minutes when vacuum is running. It's tough for me to correlate exactly, but I suspect that while postgres is vacuuming an index or table, nothing else runs. In between relations, other stuff gets to run, and then vacuum hogs all the resources again. This could be for disk reasons or simply because postgres locks the index or table while it is being vacuumed. Either way, the behavior is unacceptable. Users shouldn't have to wait minutes while the database picks up after itself. The concept of vacuuming seems to be problematic. I'm not sure why the database simply can't garbage collect incrementally. AGC is very tricky, especially AGC that involves gigabytes of data on disk. Incremental garbage collection seems to be what other databases do, and it's been my experience that other databases don't have the type of unpredictable behavior I'm seeing with Postgres. I'd rather the database be a little bit slower on average than have to figure out the best time to inconvenience my users. Since my customer already has Oracle, we'll be running tests in the coming month(s :-) with Oracle to see how it performs under the same load and hardware. I'll keep this group posted. Rob ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] vacuum locking
Rob Nagler <[EMAIL PROTECTED]> writes: > Here's the vmstat 5 at a random time: >procs memoryswap io system cpu > r b w swpd free buff cache si sobibo incs us sy id > 0 0 0 272372 38416 78220 375048 0 3 2 00 0 2 2 0 > 0 0 0 272372 3 78320 375660 0 034 274 382 284 5 1 94 > 0 1 0 272372 23012 78372 375924 0 025 558 445 488 8 2 90 > 1 0 0 272368 22744 78472 376192 0 6 125 594 364 664 9 3 88 > And here's it during vacuum: >procs memoryswap io system cpu > r b w swpd free buff cache si sobibo incs us sy id > 1 2 1 277292 9620 72028 409664 46 32 4934 4812 1697 966 8 4 88 > 0 3 0 277272 9588 72096 412964 61 0 7303 2478 1391 976 3 3 94 > 2 2 0 277336 9644 72136 393264 1326 32 2827 2954 1693 1519 8 3 89 The increased I/O activity is certainly to be expected, but what I find striking here is that you've got substantial swap activity in the second trace. What is causing that? Not VACUUM I don't think. It doesn't have any huge memory demand. But swapping out processes could account for the perceived slowdown in interactive response. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Postgresql performance
Hi The Postgresql package came from the Redhat v9.0 CDROM. I have checked the version using psql --version and it showed v7.3.2 How to check the pg_dump version? Thank you, REgards. Josh Berkus <[EMAIL PROTECTED]To: [EMAIL PROTECTED], [EMAIL PROTECTED] m> cc: Subject: Re: [PERFORM] Postgresql performance 23/10/2003 12:06 AM NEC, > After a few weeks of usage, when we do a \d at the sql prompt, there was a > duplicate object name, ie it can be a duplicate row of index or table. > When we do a \d table_name, it will show a duplication of column names > inside the table. I think the version of PSQL and pg_dump which you are using do not match the back-end database version. Correct this. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [PERFORM] Postgresql performance
Hi The Postgresql package came from the Redhat v9.0 CDROM. I have checked the version using psql --version and it showed v7.3.2 The duplication of table names is in the same schema. How to check the pg_dump version? Thank you, REgards. Tom Lane <[EMAIL PROTECTED]To: [EMAIL PROTECTED] s> cc: [EMAIL PROTECTED] Subject: Re: [PERFORM] Postgresql performance 22/10/2003 10:03 PM [EMAIL PROTECTED] writes: > Currently we are running Postgresql v7.3.2 on Redhat Linux OS v9.0. We have > Windows2000 client machines inserting records into the Postgresql tables > via ODBC. > After a few weeks of usage, when we do a \d at the sql prompt, there was a > duplicate object name, ie it can be a duplicate row of index or table. > When we do a \d table_name, it will show a duplication of column names > inside the table. Are you sure you are using 7.3 psql? This sounds like something that could happen with a pre-7.3 (not schema aware) psql, if there are multiple occurrences of the same table name in different schemas. > It doesnt affect the insertion/updating of the tables, but when we do a > pg_dump -Da -t > /exp/.sql, it will not > do a proper backup/dump. I'd wonder about whether you have the right pg_dump, too. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] vacuum locking
Am Donnerstag, 23. Oktober 2003 01:32 schrieb Rob Nagler: > The concept of vacuuming seems to be problematic. I'm not sure why > the database simply can't garbage collect incrementally. AGC is very > tricky, especially AGC that involves gigabytes of data on disk. > Incremental garbage collection seems to be what other databases do, > and it's been my experience that other databases don't have the type > of unpredictable behavior I'm seeing with Postgres. I'd rather the > database be a little bit slower on average than have to figure out the > best time to inconvenience my users. I think oracle does not do garbage collect, it overwrites the tuples directly and stores the old tuples in undo buffers. Since most transactions are commits, this is a big win. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
