a RAID setup make the disk faster? Because top rarely shows the
CPUs above 50%, I suspect maybe the disk is the bottleneck.
I'm thrilled to be able to use Postgres instead of a commercial database and
I'm looking forward to putting this into production. Any help with the
above qu
> Don't log your monitoring info directly into the database, log
> straight to one
> or more text-files and sync them every few seconds. Rotate the
> files once a
> minute (or whatever seems suitable). Then have a separate process
> that reads
> "old" files and processes them into the database.
>
>
re, but I'm sure there are some users who don't make
this effort and end up switching to another database, which is bad for
Postgres' image. Anyway, I hope my summary can help others who may find
this email in the archives.
Regards,
Michael
> -Original Message-
>
> > configure it properly and trial & error. I do think the
> documentation could
> > be enhanced a bit here, but I'm sure there are some users who don't make
>
> Do you have any specific thoughts about documentation? Areas of
> confusion? Was it difficult to find the information in question, or
> [ This has been written offline yesterday. Now I see that most of it
> has already been covered. I send it anyway ... ]
Still great advice with slightly different explanations, very useful.
> |INFO: --Relation public.jdo_sequencex--
> |INFO: Pages 28: Changed 1, Empty 0; Tup 1: Vac 5124, Ke
rows)
As you can see, it's doing a sort on ms.datex. I created an index on the
monitorstatusx (ms) table for the datex, but it doesn't use it. Is it
possible to create an index to prevent this sort?
Thanks,
Michael
Michael Mattox
[EMAIL PROTECTED] / http://www.advweb.com/michael
Sorry, I neglected to say the version, yes I'm using Postgres 7.3.2 on
Linux.
Here's the output of explain analyze. The query typically takes 0-4 seconds
depending on the time frame. It's run very frequently especially to process
the nightly reports.
veriguard=# explain analyze select ms.averag
> Are jdoidx and monitorx integers?
Yes both are integers:
-- Table: public.monitorstatusx
CREATE TABLE public.monitorstatusx (
averageconnecttimex numeric(65535, 65532),
averagedurationx numeric(65535, 65532),
datex timestamp,
idx varchar(255),
jdoclassx varchar(255),
jdoidx int8 NOT
> Oh, and using tables in your where clause that aren't in the from clause
> is non-portable and often hides bugs:
>
> from monitorstatusx ms
> , monitorstatusitemx msi
> where monitorx.idx = 'M-TEST_1444-TEST_00_10560561260561463219352'
>
> Are you sure you sure you don't have any duplicated
> Finally, decided to do an ad-hoc adjustment. Try this, or (wild guess) try
> to completely eliminate the WHERE part by subselects on ms and monitorx.
>
> This may be faster, slower, or even give different results, based
> on whether
> I guessed the 1:N relationships right or not.
It's much slowe
With a slight correction (you had m & mx so I changed them to be all mx, I
hope this is what you intended) this query works. It's exactly the same
speed, but it doesn't give me the warnings I was getting:
NOTICE: Adding missing FROM-clause entry for table "monitorx"
NOTICE: Adding missing FROM-
I have my shared buffers at 8192 and my effective cache at 64000 (which is
500 megs). Depends a lot on how much RAM you have. I have 1.5 gigs and
I've been asking my boss for another 512megs for over a month now. I have
no idea if my buffers are too high/low.
Michael
> -Original Message---
> yes, I'd say start with about 25% of RAM, then adjust from there. If 25%
> takes you over your SHMMAX then start at your SHMMAX.
You're the first person I've seen to suggest that many buffers. I've read
that too many can slow down performance. I have 1.5 gigs of RAM on my
server but I'm also r
s an equivalent of effective_cache_size in that
> > version. Upgrading would be far easier than performance tuning
> 7.0. since
> >
> > the query planner was much simpler (i.e. more prone to make bad
> decisions)
> >
> > in 7.0.
> >
> > On Tue, 1 Jul 2
> Try this:
Rod, you improved my query last week (thank you very much) but I'm not sure
why but my performance is getting worse. I think I know what happened, when
I did my load testing I created data that all had the same date, so sorting
on the date was very fast. But now I've been running the
> My postgresql.conf is attached in case I have it configured incorrectly.
Forgot my postgres.conf..
postgresql.conf
Description: Binary data
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subs
> Shared buffers is probably too high. How much memory in this machine?
> Is there anything else running aside from PostgreSQL? What does top say
> about cached / buffered data (number)
I was using the 25% of RAM guideline posted recently. The machine has
1.5gig but it also has a couple other j
> I'd be tempted to bump it up to 2.0 or 2.5 since data is on a single
> disk (sequential scans *will* be faster than an index scan), but you
> would need to run a benchmark on your disk to see if that is right.
I just set it to 2.5. What kind of benchmark can I run?
> Every monitor is updated e
I don't have much to add because I'm pretty new to Postgres and have been
soliciting advice here recently, but I totally agree with everything you
said. I don't mind if it's in the postgres.conf file or in a faq that is
easy to find, I just would like it to be in one place. A good example of
the
> I recently took a system from MySQL to Postgres. Same HW, SW, same data.
> The major operations where moderately complex queries (joins on 8 tables).
>
> The results we got was that Postgres was fully 3 times slower than MySql.
> We were on this list a fair bit looking for answers and tried all
> I'm actually leaving this list but I can answer this question.
> Our results
> were with a single user and we were running Inodb. We were running on
> RedHat 8.0 / 9.0 with vanilla linux settings.
That's funny, you make a statement that Postgres was 3 times slower than
MySQL and then you prompt
> This appears to be a "yes" answer to my question above. Out of the
> box, PostgreSQL is set up to be able to run on a 1992-vintage SGI
> Indy with 8 M of RAM (ok, I may be exaggerating, but only by a bit);
> it is not tuned for performance. Running without even tweaking the
> shared buffers is
> Are you willing to say that the PostgreSQL database system should only be
> used by DBAs? I believe that Postgres is such a good and useful tool that
> anyone should be able to start using it with little or no barrier
> to entry.
This is a good point. After reading previous responses I was sta
23 matches
Mail list logo
