[PERFORM] Deleting Records
Hi! I'm experiencing a very slow deletion of records. Which I thin is not right. I have a Dual Xeon Server with 6gig Memory. I am only deleting about 22,000 records but it took me more than 1 hour to finish this. What could possibly I do so that I can make this fast? Here is the code inside my function: FOR temp_rec IN SELECT * FROM item_qc_doer LOOP DELETE FROM qc_session WHERE item_id = temp_rec.item_id; DELETE FROM item_qc_doer WHERE item_id = temp_rec.item_id; END LOOP; Item_qc_oder table contains 22,000 records. I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] Used Memory
HI! I am having a confusion to the memory handling of postgreSQL. Here is the Scenario. I rebooted my Server which is a PostgreSQL 8.0 Running on Redhat 9, which is a Dual Xeon Server and 6 gig of memory. Of course there is not much memory still used since it is just restarted. But after a number of access to the tables the memory is being used and it is not being free up. Actually after this access to the database and the server is just idle The memory is still used up. I am monitoring this using the “free” command which gives me about 5.5 gig of used memory and the rest free. Is there something that I should do to minimize and free up the used memory? Thanks You. I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html
Re: [PERFORM] Used Memory
But as long as the memory is in the cache my database became much slower. What could probably be the cause of this? But When I restarted the database is back to normal processing. -Original Message- From: Jens-Wolfhard Schicke [mailto:[EMAIL PROTECTED] Sent: Friday, October 21, 2005 7:23 AM To: Christian Paul B. Cosinas; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Used Memory --On Freitag, Oktober 21, 2005 03:40:47 + "Christian Paul B. Cosinas" <[EMAIL PROTECTED]> wrote: > I am having a confusion to the memory handling of postgreSQL. > I rebooted my Server which is a PostgreSQL 8.0 Running on Redhat 9, > which is a Dual Xeon Server and 6 gig of memory. > > Of course there is not much memory still used since it is just restarted. > > But after a number of access to the tables the memory is being used > and it is not being free up. Actually after this access to the > database and the server is just idle > > The memory is still used up. I am monitoring this using the "free" > command which gives me about 5.5 gig of used memory and the rest free. I suppose you looked at the top row of the free output? Because there the disk-cache is counted as "used"... Have a look at the second row where buffers are counted as free, which they more or less are. > Is there something that I should do to minimize and free up the used > memory? No, the buffers make your database faster because they reduce direct disk access > I choose Polesoft Lockspam to fight spam, and you? > http://www.polesoft.com/refer.html I don't :) Mit freundlichem Gruß, Jens Schicke -- Jens Schicke [EMAIL PROTECTED] asco GmbH http://www.asco.de Mittelweg 7 Tel 0531/3906-127 38106 BraunschweigFax 0531/3906-400 I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Used Memory
Also Does Creating Temporary table in a function and not dropping them affects the performance of the database? -Original Message- From: Jens-Wolfhard Schicke [mailto:[EMAIL PROTECTED] Sent: Friday, October 21, 2005 7:23 AM To: Christian Paul B. Cosinas; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Used Memory --On Freitag, Oktober 21, 2005 03:40:47 + "Christian Paul B. Cosinas" <[EMAIL PROTECTED]> wrote: > I am having a confusion to the memory handling of postgreSQL. > I rebooted my Server which is a PostgreSQL 8.0 Running on Redhat 9, > which is a Dual Xeon Server and 6 gig of memory. > > Of course there is not much memory still used since it is just restarted. > > But after a number of access to the tables the memory is being used > and it is not being free up. Actually after this access to the > database and the server is just idle > > The memory is still used up. I am monitoring this using the "free" > command which gives me about 5.5 gig of used memory and the rest free. I suppose you looked at the top row of the free output? Because there the disk-cache is counted as "used"... Have a look at the second row where buffers are counted as free, which they more or less are. > Is there something that I should do to minimize and free up the used > memory? No, the buffers make your database faster because they reduce direct disk access > I choose Polesoft Lockspam to fight spam, and you? > http://www.polesoft.com/refer.html I don't :) Mit freundlichem Gruß, Jens Schicke -- Jens Schicke [EMAIL PROTECTED] asco GmbH http://www.asco.de Mittelweg 7 Tel 0531/3906-127 38106 BraunschweigFax 0531/3906-400 I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Used Memory
total used free shared buffers cached Mem: 6192460 6137424 55036 0 85952 5828844 -/+ buffers/cache: 222628 5969832 Swap: 2096472 0 2096472 Here is the result of “free” command” I am talking about. What does this result mean? I just noticed that as long as the free memory in the first row (which is 55036 as of now) became low, the slower is the response of the database server. I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html
Re: [PERFORM] Used Memory
Hi To all those who replied. Thank You. I monitor my database server a while ago and found out that memory is used extensively when I am fetching records from the database. I use the command "fetch all" in my VB Code and put it in a recordset.Also in this command the CPU utilization is used extensively. Is there something wrong with my code or is it just the way postgresql is behaving which I cannot do something about it? I just monitor one workstation connecting to the database server and it is already eating up about 20 % of the CPU of database server. Which I think will not be applicable to our system since we have a target of 25 PC connecting to the database server most of the time. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Craig A. James Sent: Monday, October 24, 2005 9:47 PM To: Scott Marlowe Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Used Memory Scott Marlowe wrote: >>What's needed is a way for the application developer to explicitely >>say, "This object is frequenly used, and I want it kept in memory." > > There's an interesting conversation happening on the linux kernel > hackers mailing list right about now that applies: > > http://www.gossamer-threads.com/lists/linux/kernel/580789 Thanks for the pointer. If you're a participant in that mailing list, maybe you could forward this comment... A fundamental flaw in the kernel, which goes WAY back to early UNIX implementations, is that the nice(1) setting of a program only applies to CPU usage, not to other resources. In this case, the file-system cache has no priority, so even if I set postmaster's nice(1) value to a very high priority, any pissant process with the lowest priority possible can come along with a "cat some-big-file >/dev/null" and trash my cached file-system pages. It's essentially a denial-of-service mechanism that's built in to the kernel. The kernel group's discussion on the heuristics of how and when to toss stale cache pages should have a strong nice(1) component to it. A process with a low priority should not be allowed to toss memory from a higher-priority process unless there is no other source of memory. Getting back to Postgres, the same points that the linux kernel group are discussing apply to Postgres. There is simply no way to devise a heuristic that comes even close to what the app developer can tell you. A mechanism that allowed an application to say, "Keep this table in memory" is the only way. App developers should be advised to use it sparingly, because most of the time the system is pretty good at memory management, and such a mechanism hobbles the system's ability to manage. But when it's needed, there is no substitute. Craig ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Used Memory
Hi mark I have so many functions, more than 100 functions in the database :) And I am dealing about 3 million of records in one database. And about 100 databases :) -Original Message- From: Mark Kirkwood [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 25, 2005 3:07 AM To: Christian Paul B. Cosinas Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Used Memory Christian Paul B. Cosinas wrote: > Hi To all those who replied. Thank You. > > I monitor my database server a while ago and found out that memory is > used extensively when I am fetching records from the database. I use > the command "fetch all" in my VB Code and put it in a recordset.Also > in this command the CPU utilization is used extensively. > > Is there something wrong with my code or is it just the way postgresql > is behaving which I cannot do something about it? > > I just monitor one workstation connecting to the database server and > it is already eating up about 20 % of the CPU of database server. > > Which I think will not be applicable to our system since we have a > target of > 25 PC connecting to the database server most of the time. > Could you post the query and the output of EXPLAIN ANALYZE? In addition, have you run ANALYZE on all the tables in that database ? (sorry, have to ask :-) ). cheers Mark I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] Temporary Table
Does Creating Temporary table in a function and NOT dropping them affects the performance of the database? I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html
FW: [PERFORM] Used Memory
Here are the configuration of our database server: port = 5432 max_connections = 300 superuser_reserved_connections = 10 authentication_timeout = 60 shared_buffers = 48000 sort_mem = 32168 sync = false Do you think this is enough? Or can you recommend a better configuration for my server? The server is also running PHP and Apache but wer'e not using it extensively. For development purpose only. The database slow down is occurring most of the time (when the memory free is low) I don't think it has something to do with vacuum. We only have a full server vacuum once a day. I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 1: 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] Temporary Table
I am creating a temporary table in every function that I execute. Which I think is bout 100,000 temporary tables a day. What is the command for vacuuming these 3 tables? Also I read about the auto vacuum of postgresql. How can I execute this auto vacuum or the settings in the configuration? -Original Message- From: Alvaro Nunes Melo [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 25, 2005 10:58 AM To: Christian Paul B. Cosinas Subject: Re: [PERFORM] Temporary Table Hi Christian, Christian Paul B. Cosinas wrote: > Does Creating Temporary table in a function and NOT dropping them > affects the performance of the database? > I believe it will depend on how many temporary tables you will create in a daily basis. We had a performance problem caused by them, and by not monitoring properly the database size. The pg_attribite, pg_class and pg_depend tables grow a lot. When I found out that this was the problem I saw some messages in the list archieve, and now the overall performance is great. What I do is daily run VACUUM FULL and REINDEX in this three tables. Alvaro I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Configuration Suggestion
Hi! Here is the Specifications of My Server. I would really appreciate the best configuration of postgresql.conf for my sevrer. I have tried so many value in the parameters but It seems that I cannot get the speed I want. OS: Redhat Linux CPU: Dual Xeon Memory: 6 gigabyte PostgreSQL Version 8.0 Most of my queries are having Order by Clause, and group by clause. Creation of temporary table. The biggest rows is about 3-5 million which I query almost every 5 seconds. I'm just wondering is it normal to have this result in my memory usage: total used free sharedbuffers cached Mem: 61924606172488 19972 0 399045890824 -/+ buffers/cache: 2417605950700 Swap: 2096472 02096472 What does this mean? I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Used Memory
It affect my application since the database server starts to slow down. Hence a very slow in return of functions. Any more ideas about this everyone? Please…. From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Alex Turner Sent: Friday, October 21, 2005 3:42 PM To: Jon Brisbin Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Used Memory [snip] to the second processor in my dual Xeon eServer) has got me to the point that the perpetually high memory usage doesn't affect my application server. I'm curious - how does the high memory usage affect your application server? Alex I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html
FW: [PERFORM] Used Memory
Here are the configuration of our database server: port = 5432 max_connections = 300 superuser_reserved_connections = 10 authentication_timeout = 60 shared_buffers = 48000 sort_mem = 32168 sync = false Do you think this is enough? Or can you recommend a better configuration for my server? The server is also running PHP and Apache but wer'e not using it extensively. For development purpose only. The database slow down is occurring most of the time (when the memory free is low) I don't think it has something to do with vacuum. We only have a full server vacuum once a day. -Original Message- From: Mark Kirkwood [mailto:[EMAIL PROTECTED] Sent: Monday, October 24, 2005 3:14 AM To: Christian Paul B. Cosinas Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Used Memory > > > I just noticed that as long as the free memory in the first row (which > is 55036 as of now) became low, the slower is the response of the > database server. > Also, how about posting your postgresql.conf (or just the non-default parameters) to this list? Some other stuff that could be relevant: - Is the machine just a database server, or does it run (say) Apache + Php? - When the slowdown is noticed, does this coincide with certain activities - e.g, backup , daily maintenance, data load(!) etc. regards Mark > > I choose Polesoft Lockspam to fight spam, and you? > http://www.polesoft.com/refer.html Nope, not me either. I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] Temporary Table
Does Creating Temporary table in a function and NOT dropping them affects the performance of the database? I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html
Re: [PERFORM] Temporary Table
In what directory in my linux server will I find these 3 tables? -Original Message- From: Alvaro Nunes Melo [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 26, 2005 10:49 AM To: Christian Paul B. Cosinas Subject: Re: [PERFORM] Temporary Table Christian Paul B. Cosinas wrote: >I am creating a temporary table in every function that I execute. >Which I think is bout 100,000 temporary tables a day. > > I think that a lot. ;) >What is the command for vacuuming these 3 tables? > > VACUUM FULL pg_class; VACUUM FULL pg_attribute; VACUUM FULL pg_depend; I'm using this ones. Before using them, take a look in the size that this tables are using in your HD, and compare to what you get after running this commands. I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Temporary Table
I try to run this command in my linux server. VACUUM FULL pg_class; VACUUM FULL pg_attribute; VACUUM FULL pg_depend; But it give me the following error: -bash: VACUUM: command not found I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Temporary Table
I see. But How Can I put this in the Cron of my Linux Server? I really don't have an idea :) What I want to do is to loop around all the databases in my server and execute the vacuum of these 3 tables in each tables. -Original Message- From: Joshua D. Drake [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 08, 2005 2:11 AM To: Christian Paul B. Cosinas Cc: 'Alvaro Nunes Melo'; pgsql-performance@postgresql.org Subject: Re: [PERFORM] Temporary Table Christian Paul B. Cosinas wrote: > I try to run this command in my linux server. > VACUUM FULL pg_class; > VACUUM FULL pg_attribute; > VACUUM FULL pg_depend; > > But it give me the following error: > -bash: VACUUM: command not found That needs to be run from psql ... > > > > > > I choose Polesoft Lockspam to fight spam, and you? > http://www.polesoft.com/refer.html > > > ---(end of > broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org I choose Polesoft Lockspam to fight spam, and you? http://www.polesoft.com/refer.html ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
