Re: [PERFORM] Postgresql works too slow
Try enabliing your checkpoint_segments. In my example, our database restore took 75mins. After enabling checkpoints_segments to 20, we cut it down to less than 30 minutes. Increasing maintenance_work_mem might help too ... or several other settings ... with no information about exactly *what* is slow, it's hard to say. Try turning fsync = false for the duration of your reload. Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Postgresql works too slow
Steve Poe <[EMAIL PROTECTED]> writes: > Try enabliing your checkpoint_segments. In my example, our database > restore took 75mins. After enabling checkpoints_segments to 20, we cut > it down to less than 30 minutes. Increasing maintenance_work_mem might help too ... or several other settings ... with no information about exactly *what* is slow, it's hard to say. 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 works too slow
Nurlan, Try enabliing your checkpoint_segments. In my example, our database restore took 75mins. After enabling checkpoints_segments to 20, we cut it down to less than 30 minutes. Is your pg_xlog on a seperate disc..or at least a partition? This will help too. A checkpoints_segments of 20, if memory serves correctly, will occupy around 800-900M of disc space in pg_xlog. Steve Poe Nurlan Mukhanov (AL/EKZ) wrote: Hello. I'm trying to restore my database from dump in several parrallel processes, but restore process works too slow. Number of rows about 100 000 000, RAM: 8192M CPU: Ultra Sparc 3 Number of CPU: 4 OS: SunOS sun 5.8 RDBMS: PostgreSQL 8.0 prstat info PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP 14231 postgres 41M 37M sleep 580 0:00.01 0.2% postgres/1 14136 postgres 41M 37M sleep 580 0:00.03 0.2% postgres/1 14211 postgres 41M 37M sleep 580 0:00.01 0.2% postgres/1 14270 postgres 41M 37M sleep 580 0:00.00 0.2% postgres/1 13767 postgres 41M 37M sleep 580 0:00.18 0.2% postgres/1 13684 postgres 41M 36M sleep 580 0:00.14 0.2% postgres/1 NPROC USERNAME SIZE RSS MEMORY TIME CPU 74 root 272M 191M 2.3% 0:26.29 24% 124 postgres 1520M 1306M16% 0:03.05 5.0% How to encrease postgresql speed? Why postgres took only 5.0% of CPU time? Nurlan Mukhanov ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Postgresql works too slow
Hello. I'm trying to restore my database from dump in several parrallel processes, but restore process works too slow. Number of rows about 100 000 000, RAM: 8192M CPU: Ultra Sparc 3 Number of CPU: 4 OS: SunOS sun 5.8 RDBMS: PostgreSQL 8.0 prstat info PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP 14231 postgres 41M 37M sleep 580 0:00.01 0.2% postgres/1 14136 postgres 41M 37M sleep 580 0:00.03 0.2% postgres/1 14211 postgres 41M 37M sleep 580 0:00.01 0.2% postgres/1 14270 postgres 41M 37M sleep 580 0:00.00 0.2% postgres/1 13767 postgres 41M 37M sleep 580 0:00.18 0.2% postgres/1 13684 postgres 41M 36M sleep 580 0:00.14 0.2% postgres/1 NPROC USERNAME SIZE RSS MEMORY TIME CPU 74 root 272M 191M 2.3% 0:26.29 24% 124 postgres 1520M 1306M16% 0:03.05 5.0% How to encrease postgresql speed? Why postgres took only 5.0% of CPU time? Nurlan Mukhanov ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] plperl vs plpgsql
After a long battle with technology, [EMAIL PROTECTED] (Alex), an earthling,
wrote:
> Christopher Browne wrote:
>>After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (Alex) belched
>>out:
>>>i am thinking about swiching to plperl as it seems to me much more
>>>flexible and easier to create functions.
>>>
>>>what is the recommended PL for postgres? or which one is most widely
>>>used / most popular?
>>>is there a performance difference between plpgsql and plperl ?
>>>
>>>
>>
>>If what you're trying to do is "munge text," pl/perl will be a whole
>>lot more suitable than pl/pgsql because it has a rich set of text
>>mungeing tools and string functions which pl/pgsql lacks.
>>
>>If you intend to do a lot of work involving reading unmunged tuples
>>from this table and that, pl/pgsql provides a much more natural
>>syntax, and will probably be a bit faster as the query processor may
>>even be able to expand some of the actions, rather than needing to
>>treat Perl code as an "opaque blob."
>>
>>I would definitely be inclined to use the more natural language for
>>the given task...
> Is there a performance difference between the two?
> which of the PL is most widely used. One problem i have with the
> plpgsql is that the quoting is really a pain.
You seem to be inclined to play the mistaken game of "Which language
is the fastest?" which encourages myopic use of bad benchmarks.
In 8.0, quoting in pl/pgsql is less of a pain, as you can use $$ as
the begin/end indicators.
Performance will always depend on what you're doing.
- If you doing heavy amounts of "text munging," Perl has highly
optimized library routines that you're likely to be taking
advantage of which will likely be way faster than any pl/pgsql
equivalent.
- If you are writing "set operations," operating on table data,
the fact that pl/pgsql won't need to 'context switch' between
language mode and 'accessing data from the database' mode will
probably make it a bit quicker than pl/Perl.
- If you need some sort of "ultimate fastness," then you might look to
writing in a language that compiles to assembler so that your loops
will run as quick and tight as possible, which would encourage
writing stored procedures in C. Alas, this is _way_ harder to debug
and deploy, and errors could pretty readily destroy your database
instance if they were sufficiently awful.
pl/pgsql is almost certainly the most widely used procedural language,
if you're into "popularity contests."
I would be very much inclined to start with whichever language makes
it the easiest to write and maintain the algorithms you plan to write.
I would only move to another language if the initial choice proved to
_systematically_ be a conspicuous bottleneck.
--
output = ("cbbrowne" "@" "gmail.com")
http://linuxdatabases.info/info/linuxdistributions.html
"One of the most dangerous things in the universe is an ignorant
people with real grievances. That is nowhere near as dangerous,
however, as an informed and intelligent society with grievances. The
damage that vengeful intelligence can wreak, you cannot even imagine."
-- Miles Teg, Heretics of Dune
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[PERFORM] refcurosr vs. setof
Hello, I need to write several PL/pgSQL functions all returning a "result set" wich can be obtained by a single SELECT statement. For now the functions are called by a Java application. Both REFCURSOR and SETOF serve my purpose, but I was wondering if there is a perfonance difference between the two. The result set can become quite large. I hope not to ask this question the 1001 time, though I couldn't find anything on the net.. Any hints are welcome. Regards Rüdiger ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] plperl vs plpgsql
On Sun, 17 Apr 2005, Alex wrote: Is there a performance difference between the two? Hello, It depends on what you are using it for. My experience is that for some reason plPGSQL is faster when looping but other than that they should be very similar. which of the PL is most widely used. One problem i have with the plpgsql is that the quoting is really a pain. plpgsql but I believe that will change in a short period of time. Sincerely, Joshua D. Drake -- Command Prompt, Inc., Your PostgreSQL solutions company. 503-667-4564 Custom programming, 24x7 support, managed services, and hosting Open Source Authors: plPHP, pgManage, Co-Authors: plPerlNG Reliable replication, Mammoth Replicator - http://www.commandprompt.com/ ---(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] plperl vs plpgsql
On 2005-04-17 14:56, Alex wrote: > Is there a performance difference between the two? As Christopher already pointed out, it depends on what you want to do. If you're doing some complex string processing, it will be easier (and in some cases) faster to do in plperl, if you're mainly dealing with sets, plpgsql will be better suited. > which of the PL is most widely used. plpgsql. > One problem i have with the plpgsql > is that the quoting is really a pain. In current versions of PostgreSQL you can use $$ quoting, which should make your life easier: http://www.postgresql.org/docs/8.0/static/plpgsql-structure.html http://www.postgresql.org/docs/8.0/static/plperl.html HTH, stefan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] plperl vs plpgsql
Is there a performance difference between the two? which of the PL is most widely used. One problem i have with the plpgsql is that the quoting is really a pain. Christopher Browne wrote: After takin a swig o' Arrakan spice grog, [EMAIL PROTECTED] (Alex) belched out: i am thinking about swiching to plperl as it seems to me much more flexible and easier to create functions. what is the recommended PL for postgres? or which one is most widely used / most popular? is there a performance difference between plpgsql and plperl ? If what you're trying to do is "munge text," pl/perl will be a whole lot more suitable than pl/pgsql because it has a rich set of text mungeing tools and string functions which pl/pgsql lacks. If you intend to do a lot of work involving reading unmunged tuples from this table and that, pl/pgsql provides a much more natural syntax, and will probably be a bit faster as the query processor may even be able to expand some of the actions, rather than needing to treat Perl code as an "opaque blob." I would definitely be inclined to use the more natural language for the given task... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] immutable functions vs. join for lookups ?
On 4/17/05, Enrico Weigelt <[EMAIL PROTECTED]> wrote: > * Tom Lane <[EMAIL PROTECTED]> wrote: > > Enrico Weigelt <[EMAIL PROTECTED]> writes: > > > c) CREATE FUNCTION id2username(oid) RETURNS text > > > LANGUAGE 'SQL' IMMUTABLE AS ' > > > SELECT username AS RESULT FROM users WHERE uid = $1'; > > > > This is simply dangerous. The function is *NOT* immutable (it is > > stable though). When ... not if ... your application breaks because > > you got the wrong answers, you'll get no sympathy from anyone. > > In my case it is immutable. The username never changes. > Even if your data never changes it *can* change so the function should be at most stable not immutable. regards, Jaime Casanova ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
