Thread: perfomance question/issues
How can you tell what the performance of you DB is? I know a lot has to do with DB design.. I don't see postgres usinga lot of memory or cpu on my system.. but everything seems a little slow for what I consider a small DB.. What I have is 4 static columns that are indexed and roughly 460 that are updated. Now I have only 215 rows. If I updateall of the columns then run vacuum analyze it takes 7-10 minutes. I also have had trouble doing updates... I havetried doing them through perl and psql by doing a \i then the file name.. I have also tried using begin; and end; aroundthe file.. but it still takes 1 minute+ to update one row. any suggestions are welcome. Travis
On Mon, 21 Oct 2002, Williams, Travis L, NPONS wrote: > How can you tell what the performance of you DB is? I know a lot has to do with DB design.. I don't see postgres usinga lot of memory or cpu on my system.. but everything seems a little slow for what I consider a small DB.. > > What I have is 4 static columns that are indexed and roughly 460 that are updated. Now I have only 215 rows. If I updateall of the columns then run vacuum analyze it takes 7-10 minutes. I also have had trouble doing updates... I havetried doing them through perl and psql by doing a \i then the file name.. I have also tried using begin; and end; aroundthe file.. but it still takes 1 minute+ to update one row. any suggestions are welcome. > > Travis What OS are ya running? Just wondering. It's not Windows and cygwin, right? Take a look at the output of your system logs to make sure you aren't getting massive bus errors or running low on memory or something strange. My Postgresql boxes can import about 4 to 10 thousand rows a minute, with full PK indexes in place (dual PIII-750 with 512 Meg ram, nothing amazing.) Single drive no less. Sounds like something is definitely wrong with your box or your postgresql install. Are you running 7.2.x?
Sometimes I think I've lost my mind.. I'm runing 7.2 On a Unix machine running HPUX 11.11 512M Ram Dual 550 Mhz Dual 36G 10K Drives (One dedicated to postgres) Travis -----Original Message----- From: scott.marlowe [mailto:scott.marlowe@ihs.com] Sent: Monday, October 21, 2002 6:08 PM To: Williams, Travis L, NPONS Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] perfomance question/issues On Mon, 21 Oct 2002, Williams, Travis L, NPONS wrote: > How can you tell what the performance of you DB is? I know a lot has to do with DB design.. I don't see postgres using a lot of memory or cpu on my system.. but everything seems a little slow for what I consider a small DB.. > > What I have is 4 static columns that are indexed and roughly 460 that are updated. Now I have only 215 rows. If I update all of the columns then run vacuum analyze it takes 7-10 minutes. I also have had trouble doing updates... I have tried doing them through perl and psql by doing a \i then the file name.. I have also tried using begin; and end; around the file.. but it still takes 1 minute+ to update one row. any suggestions are welcome. > > Travis What OS are ya running? Just wondering. It's not Windows and cygwin, right? Take a look at the output of your system logs to make sure you aren't getting massive bus errors or running low on memory or something strange. My Postgresql boxes can import about 4 to 10 thousand rows a minute, with full PK indexes in place (dual PIII-750 with 512 Meg ram, nothing amazing.) Single drive no less. Sounds like something is definitely wrong with your box or your postgresql install. Are you running 7.2.x?
On the machine performance.. I see no other problems.. There is almost always no load on the processor/ram. I am not a great Unix guy.. but we migrated from a 450 intel box running solaris 8 for intel to this box about a month ago.. and so far the box has blown away the other box in all aspects.. I do have postgres running on the old box.. but I never did anything beyond the install.. maybe I'll play with it and see how it runs (just to get some comparison). It is running 7.1.3 though.. Travis -----Original Message----- From: scott.marlowe [mailto:scott.marlowe@ihs.com] Sent: Monday, October 21, 2002 6:08 PM To: Williams, Travis L, NPONS Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] perfomance question/issues On Mon, 21 Oct 2002, Williams, Travis L, NPONS wrote: > How can you tell what the performance of you DB is? I know a lot has to do with DB design.. I don't see postgres using a lot of memory or cpu on my system.. but everything seems a little slow for what I consider a small DB.. > > What I have is 4 static columns that are indexed and roughly 460 that are updated. Now I have only 215 rows. If I update all of the columns then run vacuum analyze it takes 7-10 minutes. I also have had trouble doing updates... I have tried doing them through perl and psql by doing a \i then the file name.. I have also tried using begin; and end; around the file.. but it still takes 1 minute+ to update one row. any suggestions are welcome. > > Travis What OS are ya running? Just wondering. It's not Windows and cygwin, right? Take a look at the output of your system logs to make sure you aren't getting massive bus errors or running low on memory or something strange. My Postgresql boxes can import about 4 to 10 thousand rows a minute, with full PK indexes in place (dual PIII-750 with 512 Meg ram, nothing amazing.) Single drive no less. Sounds like something is definitely wrong with your box or your postgresql install. Are you running 7.2.x?
On Mon, 21 Oct 2002, Williams, Travis L, NPONS wrote: > How can you tell what the performance of you DB is? I know a lot has > to do with DB design.. I don't see postgres using a lot of memory or > cpu on my system.. but everything seems a little slow for what I > consider a small DB.. > > What I have is 4 static columns that are indexed and roughly 460 that > are updated. Now I have only 215 rows. If I update all of the > columns then run vacuum analyze it takes 7-10 minutes. I also have > had trouble doing updates... I have tried doing them through perl and > psql by doing a \i then the file name.. I have also tried using begin; > and end; around the file.. but it still takes 1 minute+ to update one > row. any suggestions are welcome. Are you using any foreign keys to/from this table that might be doing checks on other tables?
Nope.. Travis -----Original Message----- From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com] Sent: Monday, October 21, 2002 7:07 PM To: Williams, Travis L, NPONS Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] perfomance question/issues On Mon, 21 Oct 2002, Williams, Travis L, NPONS wrote: > How can you tell what the performance of you DB is? I know a lot has > to do with DB design.. I don't see postgres using a lot of memory or > cpu on my system.. but everything seems a little slow for what I > consider a small DB.. > > What I have is 4 static columns that are indexed and roughly 460 that > are updated. Now I have only 215 rows. If I update all of the > columns then run vacuum analyze it takes 7-10 minutes. I also have > had trouble doing updates... I have tried doing them through perl and > psql by doing a \i then the file name.. I have also tried using begin; > and end; around the file.. but it still takes 1 minute+ to update one > row. any suggestions are welcome. Are you using any foreign keys to/from this table that might be doing checks on other tables?
"Williams, Travis L, NPONS" <tlw@att.com> writes:
> What I have is 4 static columns that are indexed and roughly 460 that
> are updated. Now I have only 215 rows. If I update all of the columns
> then run vacuum analyze it takes 7-10 minutes. I also have had trouble
> doing updates... I have tried doing them through perl and psql by doing
> a \i then the file name.. I have also tried using begin; and end; around
> the file.. but it still takes 1 minute+ to update one row.
That's a lot 'o columns. Resisting for the moment the temptation to
suggest that your database design needs rethinking, I wonder whether you
are running into some performance bottleneck associated with either
lots-of-columns per se, or TOAST needing to squeeze down many columns in
order to fit the rows into blocks. What datatypes are you using in this
table? Do you have any idea about the average width of the rows?
regards, tom lane