Обсуждение: performance issues
Hello all, We are running 7.0.3 on a RH 7.0 box w/twin 733 pentium CPUs, 512 meg. ram and a 3 drive raid 5 scsi array. The machine is dedicated. Our main table is about 700,000 rows with an associated table of about 15,000 rows. -N 128 -B 256 logging to a file Without spelling out our complete schema I'll give some basic info main table has 37 columns with 3 indexes, several foreign key constraints and one rule the associated table has about 10 columns all or which are int 4 with the exception of one text Most of our queries are simple select queries, half of which would use the indexes. Now, does this scenario sound about right or do I have a lot of work to do? At any given moment having 12 - 16 queries running (say 1 update & one insert, the rest selects), I'm dragging bad. Vacuum -z was just run. top gives me numbers around 20. Even with just a 2 or 3 queries running top gives me 2 - 3. Does this sound typical? If my information is insufficient to give a ballpark quess, perhaps you could give me some normal types of performance figures. Thank you for your feedback. -- Bill MacArthur Webmaster DHS Club
DHSC Webmaster <webmaster@dhs-club.com> writes:
> We are running 7.0.3 on a RH 7.0 box w/twin 733 pentium CPUs, 512 meg.
> ram and a 3 drive raid 5 scsi array. The machine is dedicated. Our main
> table is about 700,000 rows with an associated table of about 15,000
> rows.
> -N 128 -B 256
That's not nearly enough buffers for an installation that size. Try -B
of a few thousand.
regards, tom lane
Thank you Tom. That has seemed to help substantially. I received another sugestion to increase -B 30000 which would give 240 meg. to postgres. I plan to give that a try. I've already reset the SHMMAX val in sysctl to accomodate that size. Do you see any reason not to try this? My thought is if some is good, more must be better. :) Also I noticed in this artice: http://www.ca.postgresql.org/docs/faq-english.html#3.7 mention of another memory parameter (-S), however the elephant book refers to -S as silent mode. Could you clarify this? Tom Lane wrote: > > DHSC Webmaster <webmaster@dhs-club.com> writes: > > We are running 7.0.3 on a RH 7.0 box w/twin 733 pentium CPUs, 512 meg. > > ram and a 3 drive raid 5 scsi array. The machine is dedicated. Our main > > table is about 700,000 rows with an associated table of about 15,000 > > rows. > > -N 128 -B 256 > > That's not nearly enough buffers for an installation that size. Try -B > of a few thousand. > > regards, tom lane -- Bill MacArthur Webmaster DHS Club
DHSC Webmaster <webmaster@dhs-club.com> writes:
> Thank you Tom. That has seemed to help substantially.
> I received another sugestion to increase -B 30000 which would give 240
> meg. to postgres. I plan to give that a try. I've already reset the
> SHMMAX val in sysctl to accomodate that size. Do you see any reason not
> to try this? My thought is if some is good, more must be better. :)
Not always; there are several reasons why more can be worse:
1. It's a good idea to leave plenty of free RAM for kernel-level disk
buffering. Postgres has been tuned (to the extent that it is tuned)
to operate with a fair amount of kernel buffering underneath it.
2. If you are on a platform where the kernel thinks it can swap out
parts of shared memory (which is most platforms, these days), it's
counterproductive to make your shared mem large enough that it's not
all being used heavily. If the kernel decides to swap out some buffer
space, you have a net loss in performance, because you just wasted
disk I/O (especially if the page in the buffer is dirty --- it'll
eventually have to be read in again, then written out again, to no
purpose).
3. Also, there are some operations (such as dropping a table) which
cause linear scans of the whole buffer array, so making it too big
can slow things down, depending on your query mix.
While I have not done a lot of measurement to back this up, my rule of
thumb is that you don't want to reserve more than about a quarter of
your physical RAM for Postgres' disk buffers, even on a dedicated
machine. But feel free to experiment and see what results you get.
Please report back if you do any sort of organized tuning experiments;
AFAIK we don't have many hard numbers about these effects.
regards, tom lane
> 2. If you are on a platform where the kernel thinks it can swap out > parts of shared memory (which is most platforms, these days), it's > counterproductive to make your shared mem large enough that it's not > all being used heavily. If the kernel decides to swap out some buffer > space, you have a net loss in performance, because you just wasted > disk I/O (especially if the page in the buffer is dirty --- it'll > eventually have to be read in again, then written out again, to no > purpose). FYI, FreeBSD had the ability to disable shared memory swapping with a sysctl setting, while BSD/OS does not swap out shared memory. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Thu, 14 Jun 2001, Bruce Momjian wrote: > > 2. If you are on a platform where the kernel thinks it can swap out > > parts of shared memory (which is most platforms, these days), it's > > counterproductive to make your shared mem large enough that it's not > > all being used heavily. If the kernel decides to swap out some buffer > > space, you have a net loss in performance, because you just wasted > > disk I/O (especially if the page in the buffer is dirty --- it'll > > eventually have to be read in again, then written out again, to no > > purpose). > > FYI, FreeBSD had the ability to disable shared memory swapping with a > sysctl setting, while BSD/OS does not swap out shared memory. > > -- > Bruce Momjian | http://candle.pha.pa.us > pgman@candle.pha.pa.us | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup. | Drexel Hill, Pennsylvania 19026 > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > I was going to mention this but had to get to my database machine to find it first. The command is: sysctl -w kern.ipc.shm_use_phys=1 This is supposed to make shared memory use physical memory, but I don't think it is a an absolute rule. If you use too much memory, it will still swap I believe. - brian
when I reload my database from pg_dumpall: COPY "urlinfo" FROM stdin; ERROR: copy: line 11419, MemoryContextAlloc: invalid request size 4294967271 PQendcopy: resetting connection this table is very big. how could I resolve this?? thanks. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.stbernard.com www.ipinc.com
Jie Liang <jliang@ipinc.com> writes:
> when I reload my database from pg_dumpall:
> COPY "urlinfo" FROM stdin;
> ERROR: copy: line 11419, MemoryContextAlloc: invalid request size
> 4294967271
> PQendcopy: resetting connection
Postgres version? Table schema? Input data?
regards, tom lane
Tom,
We are fixing it now.
postgres-7.1.2
urldb=# \d urlinfo
Table "urlinfo"
Attribute | Type | Modifier
-----------+--------------------------+----------------------------------
url | text | not null
id | integer | not null
ratedby | character varying(32) |
ratedon | timestamp with time zone | default "timestamp"('now'::text)
comments | text |
list | smallint |
pidwsr | integer |
Index: urlinfo_pkey
the original schema includes another constraint which using a user defined
function to check whether url is validate or not.
we fixed input data at line 11419.
thanks.
Jie LIANG
St. Bernard Software
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
jliang@ipinc.com
www.stbernard.com
www.ipinc.com
On Sun, 17 Jun 2001, Tom Lane wrote:
> Jie Liang <jliang@ipinc.com> writes:
> > when I reload my database from pg_dumpall:
> > COPY "urlinfo" FROM stdin;
> > ERROR: copy: line 11419, MemoryContextAlloc: invalid request size
> > 4294967271
> > PQendcopy: resetting connection
>
> Postgres version? Table schema? Input data?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
Jie Liang <jliang@ipinc.com> writes:
> the original schema includes another constraint which using a user defined
> function to check whether url is validate or not.
So are you saying it was just a bug in this user-defined function?
Or is there something we need to investigate?
regards, tom lane
Tom,
Might be.
It seems that something wrong in our input data at centain line.
I don't know how it happened.
Because I have to turn our database on now, so I might check it out later.
The whole thing is :
we pg_dumpall(v7.0) from our database.
then we reload with
gunzip -c dump.gz|psql templete1 after we upgraded to postgresql-7.1.2
just that table failed, however some use defined function failed
when we use them, so we re_create every function(plpgsql,plperl)
with a script. then seems ok now.
HOWEVER, we seems to have a new problem:
I feel it slower than before, I might missed something
urldb=# \d urlinfo
Table "urlinfo"
Attribute | Type | Modifier
-----------+--------------------------+----------------------------------
url | text | not null
id | integer | not null
ratedby | character varying(32) |
ratedon | timestamp with time zone | default "timestamp"('now'::text)
comments | text |
list | smallint |
pidwsr | integer |
Indices: urlinfo_pkey,
urlinfo_ukey
urldb=# select count(*) from urlinfo;
count
---------
1298542
I vacuumed urlinfo already.
urlinfo_pkey is primary key (id), urlinfo_ukey is unique key (url)
when I:
SELECT id FROM urlinfo WHERE url='http://*.yahoo.com';
It seems taking me longer than before.
Jie LIANG
St. Bernard Software
10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873
jliang@ipinc.com
www.stbernard.com
www.ipinc.com
On Sun, 17 Jun 2001, Tom Lane wrote:
> Jie Liang <jliang@ipinc.com> writes:
> > the original schema includes another constraint which using a user defined
> > function to check whether url is validate or not.
>
> So are you saying it was just a bug in this user-defined function?
> Or is there something we need to investigate?
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
Jie Liang <jliang@ipinc.com> writes:
> I vacuumed urlinfo already.
VACUUM ANALYZE, or just VACUUM?
> urlinfo_pkey is primary key (id), urlinfo_ukey is unique key (url)
> when I:
> SELECT id FROM urlinfo WHERE url='http://*.yahoo.com';
> It seems taking me longer than before.
What does EXPLAIN say about it?
regards, tom lane
Tom, It comes back very quick now, there might some backend haven't come back before. thanks a lot. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.stbernard.com www.ipinc.com On Sun, 17 Jun 2001, Tom Lane wrote: > Jie Liang <jliang@ipinc.com> writes: > > I vacuumed urlinfo already. > > VACUUM ANALYZE, or just VACUUM? > > > urlinfo_pkey is primary key (id), urlinfo_ukey is unique key (url) > > when I: > > SELECT id FROM urlinfo WHERE url='http://*.yahoo.com'; > > It seems taking me longer than before. > > What does EXPLAIN say about it? > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
Tom, We turn on the log file by: In file 'postgresql.conf' syslog = 2 # range 0-2 syslog_facility = 'LOCAL0' syslog_ident = 'postgres' 1. It seems that postmaster writes stuff undef /db/pgsql/pg_xlog (they are not text files). 2. the log file I expected (I want log include SQL statement and ERROR) Just have NOTICE and DEBUG info. How to choose these options?? Thanks. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.stbernard.com www.ipinc.com
Tom, Since our disk is filled full so fast, Does logs under pg_xlog can be deleted and how to turn off it?? Thanks. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.stbernard.com www.ipinc.com
Tom, There are a lot of pg_sorttemp???.???, which full our disk so fast, how to enforce them in a certain size to prevent disk full. Thanks. Jie LIANG St. Bernard Software 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.stbernard.com www.ipinc.com
From: "Jie Liang" <jliang@ipinc.com> > Tom, > > We turn on the log file by: > In file 'postgresql.conf' > syslog = 2 # range 0-2 > syslog_facility = 'LOCAL0' > syslog_ident = 'postgres' > > 1. It seems that postmaster writes stuff undef /db/pgsql/pg_xlog > (they are not text files). pg_xlog stuff is WAL related (Ch 9 Administrators guide) > 2. the log file I expected (I want log include SQL statement and ERROR) > Just have NOTICE and DEBUG info. I *think* you want DEBUG_PRINT_QUERY and its friends. See Ch 3.4.2 of the Administrators Guide. HTH - Richard Huxton