agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedperformance
16+ messages / 13 participants
[nested] [flat]
* performance
@ 2000-04-05 04:51 Joe Conway <jconway2@home.com>
2000-04-05 06:28 ` Re: performance Chris Albertson <chrisja@jps.net>
0 siblings, 1 reply; 16+ messages in thread
From: Joe Conway @ 2000-04-05 04:51 UTC (permalink / raw)
To: pgsql-admin
Hello,
I'm currently working with a development database, PostgreSQL 6.5.2 on RedHat 6.1 Linux. There is one fairly large table (currently ~ 1.3 million rows) which will continue to grow at about 500k rows per week (I'm considering various options to periodically archive or reduce the collected data). Is there anything I can do to cache some or all of this table in memory in order to speed queries against it? The physical file is about 130 MB. The server is a dual Pentium Pro 200 with 512 MB of RAM.
Any suggestions would be appreciated.
Joe Conway
p.s. I tried to search the archives, but it did not return any results with even the simplest of searches.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: performance
2000-04-05 04:51 performance Joe Conway <jconway2@home.com>
@ 2000-04-05 06:28 ` Chris Albertson <chrisja@jps.net>
0 siblings, 0 replies; 16+ messages in thread
From: Chris Albertson @ 2000-04-05 06:28 UTC (permalink / raw)
To: Joe Conway <jconway2@home.com>; +Cc: pgsql-admin
I'm working with some large tables too. Around 10x your size and due to become
maybe 20x larger. The good news is that Linux will use all the "extra" RAM it
has for a disk cache. You don't have to do anything. Postgresql has it's
own cache too. Use the "-B" option to make this buffer cache large. I use
-B10000. Also use the "-F" option to turn off the fsync and buy an UPS for
the computer. The biggest performance boost I got is when I discoved that the
COPY command is an order of magnitude faster then INSERT. Experiment with
indexies to speed querries. Experiment usually there are several ways to
write a query. One way may be faster.
The "top" display is a big help while tunning your system. Your goal is
to get the CPU(s) to near 100% utilization. If there is much idle CPU time
it means you are I/O bound and could use more RAM or a bigger -B value.
Joe Conway wrote:
>
> Hello,
>
> I'm currently working with a development database, PostgreSQL 6.5.2 on RedHat 6.1 Linux. There is one fairly large table (currently ~ 1.3 million rows) which will continue to grow at about 500k rows per week (I'm considering various options to periodically archive or reduce the collected data). Is there anything I can do to cache some or all of this table in memory in order to speed queries against it? The physical file is about 130 MB. The server is a dual Pentium Pro 200 with 512 MB of RAM.
>
> Any suggestions would be appreciated.
>
> Joe Conway
>
> p.s. I tried to search the archives, but it did not return any results with even the simplest of searches.
--
--Chris Albertson home: chrisja@jps.net
Redondo Beach, California work: calbertson@logicon.com
^ permalink raw reply [nested|flat] 16+ messages in thread
* Performance
@ 2001-02-05 09:59 Johan Segernäs <johan.segernas@foretagsuniversitetet.se>
2001-02-05 10:21 ` Re: Performance Karel Zak <zakkr@zf.jcu.cz>
0 siblings, 1 reply; 16+ messages in thread
From: Johan Segernäs @ 2001-02-05 09:59 UTC (permalink / raw)
To: pgsql-admin
And running postgresql on a P133 with 64 meggs of RAM using debian woody.
How many users will that box be able to serve?
I'm running apache/php on another machine and the database will have
questions and answers for some exams in it. Nothing hard, just text.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Performance
2001-02-05 09:59 Performance Johan Segernäs <johan.segernas@foretagsuniversitetet.se>
@ 2001-02-05 10:21 ` Karel Zak <zakkr@zf.jcu.cz>
0 siblings, 0 replies; 16+ messages in thread
From: Karel Zak @ 2001-02-05 10:21 UTC (permalink / raw)
To: Johan Segernäs <johan.segernas@foretagsuniversitetet.se>; +Cc: pgsql-admin
On Mon, 5 Feb 2001, [iso-8859-1] Johan Segernäs wrote:
> And running postgresql on a P133 with 64 meggs of RAM using debian woody.
> How many users will that box be able to serve?
>
> I'm running apache/php on another machine and the database will have
> questions and answers for some exams in it. Nothing hard, just text.
Depends on:
- RAM
- I/O operations (do you run UPDATE, INSERT very often?)
- fsync() setting (-F or 7.1)
- size of DB
- size of data transfered between client-server
- number of used tables in typical query
- and more and more.. :-)
..cca you need for each connected client 2Mb + x RAM (where 'x'
depend on processed data). Not is problem create table and write query
that spend all your memory.
Karel
^ permalink raw reply [nested|flat] 16+ messages in thread
* RE: Performance
@ 2001-02-05 11:21 =?iso-8859-2?Q?Johan_Segern=E4s?= <johan.segernas@foretagsuniversitetet.se>
0 siblings, 0 replies; 16+ messages in thread
From: Johan Segernäs @ 2001-02-05 11:21 UTC (permalink / raw)
To: 'Karel Zak' <zakkr@zf.jcu.cz>; +Cc: pgsql-admin
> On Mon, 5 Feb 2001, [iso-8859-1] Johan Segernäs wrote:
> > And running postgresql on a P133 with 64 meggs of RAM using
> debian woody.
> > How many users will that box be able to serve?
> >
> > I'm running apache/php on another machine and the database
> will have
> > questions and answers for some exams in it. Nothing hard, just text.
>
> Depends on:
>
> - RAM
64 MB EDO
> - I/O operations (do you run UPDATE, INSERT very often?)
No, very seldom. It'll be for serving questions and check for the right
answer to the question.
We'll only do INSERT/UPDATE when adding users, adding new questions and so
on.
> - fsync() setting (-F or 7.1)
?
> - size of DB
> - size of data transfered between client-server
> - number of used tables in typical query
As I said, it's gonna be for an exam-system with around 150 questions around
100 chars long.
With answers for each and every one.
No big amount of data.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Performance
@ 2002-01-18 10:00 Martins Zarins <mark@vestnesis.lv>
2002-01-18 17:22 ` Re: Performance Jeremy Buchmann <jeremy@wellsgaming.com>
2002-01-21 08:04 ` Re: Performance Martins Zarins <mark@vestnesis.lv>
0 siblings, 2 replies; 16+ messages in thread
From: Martins Zarins @ 2002-01-18 10:00 UTC (permalink / raw)
To: pgsql-admin
Hello all!
I need to setup high performance DB server. Some time ago I red
there about processor cache influence on query execution
performance.
A question:
What system would perform better?
lh6000 with two xeon 7000Mhz 2MB cache
or
with four xeon 7000Mhz 1MB cache
Mark
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Performance
2002-01-18 10:00 Performance Martins Zarins <mark@vestnesis.lv>
@ 2002-01-18 17:22 ` Jeremy Buchmann <jeremy@wellsgaming.com>
1 sibling, 0 replies; 16+ messages in thread
From: Jeremy Buchmann @ 2002-01-18 17:22 UTC (permalink / raw)
To: mark@vestnesis.lv; +Cc: pgsql-admin
On Friday, January 18, 2002, at 02:00 AM, Martins Zarins wrote:
> Hello all!
>
> I need to setup high performance DB server. Some time ago I red
> there about processor cache influence on query execution
> performance.
> A question:
> What system would perform better?
> lh6000 with two xeon 7000Mhz 2MB cache
> or
> with four xeon 7000Mhz 1MB cache
It's more than just processor cache, it's your whole I/O subsystem.
How fast are your drives? How fast is the drive controller? How much
cache is on each drive? How much cache is on the drive controller?
Are you going to use a RAID? If so, what type? Do you have enough
memory for the size of the database and type of queries you're going to
run?
As far as processor cache goes, your goal is to avoid cache misses...so
it
depends on how many connections you're expecting, what those connections
will be doing, etc. The best advice is to run your own benchmarks and
find
out for yourself.
--Jeremy
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Performance
2002-01-18 10:00 Performance Martins Zarins <mark@vestnesis.lv>
@ 2002-01-21 08:04 ` Martins Zarins <mark@vestnesis.lv>
2002-01-21 13:33 ` Re: Performance Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 1 reply; 16+ messages in thread
From: Martins Zarins @ 2002-01-21 08:04 UTC (permalink / raw)
To: pgsql-admin
On 18 Jan 2002, at 9:22, Jeremy Buchmann wrote:
>
> It's more than just processor cache, it's your whole I/O subsystem.
> How fast are your drives? How fast is the drive controller? How much
> cache is on each drive? How much cache is on the drive controller?
> Are you going to use a RAID? If so, what type? Do you have enough
> memory for the size of the database and type of queries you're going
> to run?
Is there any good doc about this on net?
(About disc cache, raid cache processor cache and queries - how
they influence each other?)
Mark
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Performance
2002-01-18 10:00 Performance Martins Zarins <mark@vestnesis.lv>
2002-01-21 08:04 ` Re: Performance Martins Zarins <mark@vestnesis.lv>
@ 2002-01-21 13:33 ` Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 0 replies; 16+ messages in thread
From: Bruce Momjian @ 2002-01-21 13:33 UTC (permalink / raw)
To: mark@vestnesis.lv; +Cc: pgsql-admin
See techdocs performance article:
http://techdocs.postgresql.org
Martins Zarins wrote:
> On 18 Jan 2002, at 9:22, Jeremy Buchmann wrote:
> >
> > It's more than just processor cache, it's your whole I/O subsystem.
> > How fast are your drives? How fast is the drive controller? How much
> > cache is on each drive? How much cache is on the drive controller?
> > Are you going to use a RAID? If so, what type? Do you have enough
> > memory for the size of the database and type of queries you're going
> > to run?
> Is there any good doc about this on net?
> (About disc cache, raid cache processor cache and queries - how
> they influence each other?)
>
> Mark
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
--
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
^ permalink raw reply [nested|flat] 16+ messages in thread
* Performance.
@ 2003-05-16 08:40 Hargobind Singh <hgs@acplexports.com>
2003-05-16 12:43 ` Re: Performance. matt <matt@ymogen.net>
0 siblings, 1 reply; 16+ messages in thread
From: Hargobind Singh @ 2003-05-16 08:40 UTC (permalink / raw)
To: pgsql-admin
I have a linux box P4 1.7, 512 RAM, 40GB HDD with 1.2 GB swap partition,
total 20GB free on the hard disk. RedHat 8.0 with GNome installed, and
Postgres 7.2 installed.
The performance of Postgres is very very poor. I have this table with 19
fields, a primary key, and i have indexed it as well. A single select query
on that filters a single field takes 9 seconds to execute. THe table has
1,40,000 records.
Any place where i can find OPTIMIZATION of PostGres ??
Thanx..
Hargobind Singh
--------------------------
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Performance.
2003-05-16 08:40 Performance. Hargobind Singh <hgs@acplexports.com>
@ 2003-05-16 12:43 ` matt <matt@ymogen.net>
0 siblings, 0 replies; 16+ messages in thread
From: matt @ 2003-05-16 12:43 UTC (permalink / raw)
To: Hargobind Singh <hgs@acplexports.com>; +Cc: pgsql-admin
Postgres comes by default tuned to run on a mobile phone (or maybe a
Palm Pilot), not a database server, so you need to increase the amount
of shared memory, semaphores &c available on your system.
Look for 'kernel parameters' in the docs when they come back up.
*Minimally* you want to add the following lines at the top of
/etc/init.d/postgresql:
echo 67108864 > /proc/sys/kernel/shmall
echo 67108864 > /proc/sys/kernel/shmmax
echo "250 32000 32 500" > /proc/sys/kernel/sem
And set
shared_buffers = 4096 # min max_connections*2 or 16, 8KB each
In /var/lib/pgsql/data/postgresql.conf
then do /etc/init.d/postgresql restart
That would give you 32MB of shared buffers for postgres to use, which is
what I have for dev boxen. For live servers it all depends on the size
of your DB and the amount of RAM available. As with any DB you really
need enough shared memory to keep pretty much all the active DB tables
in, otherwise you'll be hitting the disk all the time. My production DB
server has 512MB of shared mem, and I'll be upping that soon...
On Fri, 2003-05-16 at 09:40, Hargobind Singh wrote:
> I have a linux box P4 1.7, 512 RAM, 40GB HDD with 1.2 GB swap partition,
> total 20GB free on the hard disk. RedHat 8.0 with GNome installed, and
> Postgres 7.2 installed.
>
> The performance of Postgres is very very poor. I have this table with 19
> fields, a primary key, and i have indexed it as well. A single select query
> on that filters a single field takes 9 seconds to execute. THe table has
> 1,40,000 records.
>
> Any place where i can find OPTIMIZATION of PostGres ??
>
> Thanx..
>
> Hargobind Singh
> --------------------------
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Performance
@ 2024-12-16 01:22 Anex Hul <anexsql2014@gmail.com>
2024-12-16 02:14 ` Re: Performance Ron Johnson <ronljohnsonjr@gmail.com>
2024-12-16 04:22 ` Re: Performance Rui DeSousa <rui.desousa@icloud.com>
0 siblings, 2 replies; 16+ messages in thread
From: Anex Hul @ 2024-12-16 01:22 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello everyone,
Testing 100 million records data import from Azure blob storage to Azure
postgresql. I did run the test 5 times and the time it took keep increasing
for each run.
Is there know justification for this linear increment of the time it took
for same size of data?
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Performance
2024-12-16 01:22 Performance Anex Hul <anexsql2014@gmail.com>
@ 2024-12-16 02:14 ` Ron Johnson <ronljohnsonjr@gmail.com>
1 sibling, 0 replies; 16+ messages in thread
From: Ron Johnson @ 2024-12-16 02:14 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Sun, Dec 15, 2024 at 8:22 PM Anex Hul <anexsql2014@gmail.com> wrote:
> Hello everyone,
>
> Testing 100 million records data import from Azure blob storage to Azure
> postgresql. I did run the test 5 times and the time it took keep increasing
> for each run.
> Is there know justification for this linear increment of the time it took
> for same size of data?
>
1. What version of PG is it? ("SELECT VERSION();" should tell you.)
2. Are you truncating the table after each test run, or deleting all
records, or appending?
3. Is the blob data stored in BYTEA column data, or are you using the
(discouraged) "Large Objects"?
4. How are you loading the blob data?
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Performance
2024-12-16 01:22 Performance Anex Hul <anexsql2014@gmail.com>
@ 2024-12-16 04:22 ` Rui DeSousa <rui.desousa@icloud.com>
2024-12-16 14:05 ` Re: Performance Anex Hul <anexsql2014@gmail.com>
1 sibling, 1 reply; 16+ messages in thread
From: Rui DeSousa @ 2024-12-16 04:22 UTC (permalink / raw)
To: Anex Hul <anexsql2014@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> On Dec 15, 2024, at 8:22 PM, Anex Hul <anexsql2014@gmail.com> wrote:
>
> Hello everyone,
>
> Testing 100 million records data import from Azure blob storage to Azure postgresql. I did run the test 5 times and the time it took keep increasing for each run.
> Is there know justification for this linear increment of the time it took for same size of data?
Check you I/O quotas; you might have hit quota limits and being throttled.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Performance
2024-12-16 01:22 Performance Anex Hul <anexsql2014@gmail.com>
2024-12-16 04:22 ` Re: Performance Rui DeSousa <rui.desousa@icloud.com>
@ 2024-12-16 14:05 ` Anex Hul <anexsql2014@gmail.com>
2024-12-16 14:20 ` Re: Performance Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 16+ messages in thread
From: Anex Hul @ 2024-12-16 14:05 UTC (permalink / raw)
To: Rui DeSousa <rui.desousa@icloud.com>; +Cc: pgsql-admin@lists.postgresql.org
Thank you all for your response.
Show quoted text
1. What version of PG is it? ("SELECT VERSION();" should tell you.)
PG Version 16
2. Are you truncating the table after each test run, or deleting all
records, or appending?
created new schema for each run.
3. Is the blob data stored in BYTEA column data, or are you using the
(discouraged) "Large Objects"?
Blob storage
4. How are you loading the blob data?
used the Import data using a COPY statement, followed this doc
https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-use-pg-azure-storage?tabs=...
On Sun, Dec 15, 2024, 10:22 PM Rui DeSousa <rui.desousa@icloud.com> wrote:
>
>
> > On Dec 15, 2024, at 8:22 PM, Anex Hul <anexsql2014@gmail.com> wrote:
> >
> > Hello everyone,
> >
> > Testing 100 million records data import from Azure blob storage to Azure
> postgresql. I did run the test 5 times and the time it took keep increasing
> for each run.
> > Is there know justification for this linear increment of the time it
> took for same size of data?
>
> Check you I/O quotas; you might have hit quota limits and being throttled.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Performance
2024-12-16 01:22 Performance Anex Hul <anexsql2014@gmail.com>
2024-12-16 04:22 ` Re: Performance Rui DeSousa <rui.desousa@icloud.com>
2024-12-16 14:05 ` Re: Performance Anex Hul <anexsql2014@gmail.com>
@ 2024-12-16 14:20 ` Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 16+ messages in thread
From: Ron Johnson @ 2024-12-16 14:20 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
On Mon, Dec 16, 2024 at 9:05 AM Anex Hul <anexsql2014@gmail.com> wrote:
[snip]
> 2. Are you truncating the table after each test run, or deleting all
> records, or appending?
>
> created new schema for each run.
>
> 3. Is the blob data stored in BYTEA column data, or are you using the
> (discouraged) "Large Objects"?
>
> Blob storage
>
Postgresql does not know what "Blob storage" means.
> 4. How are you loading the blob data?
>
> used the Import data using a COPY statement, followed this doc
>
>
> https://learn.microsoft.com/en-us/azure/postgresql/flexible-server/how-to-use-pg-azure-storage?tabs=...
>
If you're using a Microsoft extension, then you'd better ask Microsoft.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2024-12-16 14:20 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-04-05 04:51 performance Joe Conway <jconway2@home.com>
2000-04-05 06:28 ` Chris Albertson <chrisja@jps.net>
2001-02-05 09:59 Performance Johan Segernäs <johan.segernas@foretagsuniversitetet.se>
2001-02-05 10:21 ` Re: Performance Karel Zak <zakkr@zf.jcu.cz>
2001-02-05 11:21 RE: Performance =?iso-8859-2?Q?Johan_Segern=E4s?= <johan.segernas@foretagsuniversitetet.se>
2002-01-18 10:00 Performance Martins Zarins <mark@vestnesis.lv>
2002-01-18 17:22 ` Re: Performance Jeremy Buchmann <jeremy@wellsgaming.com>
2002-01-21 08:04 ` Re: Performance Martins Zarins <mark@vestnesis.lv>
2002-01-21 13:33 ` Re: Performance Bruce Momjian <pgman@candle.pha.pa.us>
2003-05-16 08:40 Performance. Hargobind Singh <hgs@acplexports.com>
2003-05-16 12:43 ` Re: Performance. matt <matt@ymogen.net>
2024-12-16 01:22 Performance Anex Hul <anexsql2014@gmail.com>
2024-12-16 02:14 ` Re: Performance Ron Johnson <ronljohnsonjr@gmail.com>
2024-12-16 04:22 ` Re: Performance Rui DeSousa <rui.desousa@icloud.com>
2024-12-16 14:05 ` Re: Performance Anex Hul <anexsql2014@gmail.com>
2024-12-16 14:20 ` Re: Performance Ron Johnson <ronljohnsonjr@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox