[GENERAL] managing kernel
My sysadmin has changed the values for shmall to 256MB (268435456) and shmmax to 1GB (1073741824) and I wonder if these values seem ok to you. We are working on a linux system (2.4 kernel) and have 2 GB RAM at our disposal. I have this large database running (15 million records). Are there any other parameters I should/could tweak for better performance? Best Regards Dino __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] managing kernel
OK, I have RTFM:-) SHMALL Total amount of shared memory available (bytes or pages) if bytes, same as SHMMAX; if pages, ceil(SHMMAX/PAGE_SIZE) These values should be the same. I've changed that now. Still my second question is valid though. Are there any other values I should consider changing in order to boost performance. I'm using postgresql 7.4. Brgds Dino --- Dino Vliet [EMAIL PROTECTED] wrote: My sysadmin has changed the values for shmall to 256MB (268435456) and shmmax to 1GB (1073741824) and I wonder if these values seem ok to you. We are working on a linux system (2.4 kernel) and have 2 GB RAM at our disposal. I have this large database running (15 million records). Are there any other parameters I should/could tweak for better performance? Best Regards Dino __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 8: explain analyze is your friend __ Do you Yahoo!? Plan great trips with Yahoo! Travel: Now over 17,000 guides! http://travel.yahoo.com/p-travelguide ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Help! Access is Denied Installation on WinXP rolled back
Hi All! I googled for a solution to my problem and then searched the PostgreSQL site for solutions and since neither produced an answer I thought I'd see if anyone else out there knows how to resolve my dilemna... Any assistance is appreciated! Essentially what happens is that I just attempt to install PostgreSQL 8.0.2 with the default options and it nearly completes the installation but then on the step where it initializes (or whatever it's called) the database it crashes and rolls back the install. When I check the log file it tells me to check before it deletes it in the rollback it says repeatedly something about access is denied. Below is the actual install log (or a small portion of it, near the end where the error occurs). Any ideas? Regards, Abe p.s. this is a repost from my initial post on the novice list... which I've become aware is relatively inactive. MSI (s) (C0:74) [11:03:22:531]: Source for file 'user_lockstxt' is compressed InstallFiles: File: README.user_locks, Directory: C:\Program Files\PostgreSQL\8.0\doc\contrib\, Size: 2723 MSI (s) (C0:74) [11:03:22:531]: Note: 1: 2318 2: C:\Program Files\PostgreSQL\8.0\doc\contrib\README.user_locks MSI (s) (C0:74) [11:03:22:531]: Executing op: FileCopy(SourceName=readm~1.vac|README.vacuumlo,SourceCabKey=vacuumlotxt,DestName=README.vacuumlo,Attributes=0,FileSize=1449,PerTick=32768,,VerifyMedia=1,CheckCRC=0,,,InstallMode=58982400,HashOptions=0,HashPart1=-1578222883,HashPart2=1022240769,HashPart3=1742458498,HashPart4=808312946,,) MSI (s) (C0:74) [11:03:22:531]: File: C:\Program Files\PostgreSQL\8.0\doc\contrib\README.vacuumlo;To be installed; Won't patch;No existing file MSI (s) (C0:74) [11:03:22:531]: Source for file 'vacuumlotxt' is compressed InstallFiles: File: README.vacuumlo, Directory: C:\Program Files\PostgreSQL\8.0\doc\contrib\, Size: 1449 MSI (s) (C0:74) [11:03:22:531]: Note: 1: 2318 2: C:\Program Files\PostgreSQL\8.0\doc\contrib\README.vacuumlo MSI (s) (C0:74) [11:03:22:531]: Executing op: CacheSizeFlush(,) MSI (s) (C0:74) [11:03:22:531]: Executing op: InstallProtectedFiles(AllowUI=1) MSI (s) (C0:74) [11:03:22:531]: Executing op: ActionStart(Name=SetPermissions,Description=Setting filesystem permissions...,) Action 11:03:22: SetPermissions. Setting filesystem permissions... MSI (s) (C0:74) [11:03:22:531]: Executing op: CustomActionSchedule(Action=SetPermissions,ActionType=1025,Source=BinaryData,[EMAIL PROTECTED],CustomActionData=1033;C:\Program Files\PostgreSQL\8.0\;C:\Program Files\PostgreSQL\8.0\data\;MYTHOS;postgres;u35351364) MSI (s) (C0:14) [11:03:22:546]: Invoking remote custom action. DLL: C:\WINDOWS\Installer\MSI100.tmp, Entrypoint: [EMAIL PROTECTED] MSI (s) (C0:74) [11:03:23:375]: Executing op: ActionStart(Name=RunInitdb,Description=Initializing database cluster (this may take a minute or two)...,) Action 11:03:23: RunInitdb. Initializing database cluster (this may take a minute or two)... MSI (s) (C0:74) [11:03:23:390]: Executing op: CustomActionSchedule(Action=RunInitdb,ActionType=1025,Source=BinaryData,[EMAIL PROTECTED],CustomActionData=1033;C:\Program Files\PostgreSQL\8.0\;C:\Program Files\PostgreSQL\8.0\data\;5432;1;C;SQL_ASCII;postgres;u35351364;MYTHOS;postgres;u35351364) MSI (s) (C0:14) [11:03:23:390]: Invoking remote custom action. DLL: C:\WINDOWS\Installer\MSI101.tmp, Entrypoint: [EMAIL PROTECTED] MSI (c) (04:34) [11:03:27:031]: Note: 1: 2205 2: 3: Error MSI (c) (04:34) [11:03:27:031]: Note: 1: 2228 2: 3: Error 4: SELECT `Message` FROM `Error` WHERE `Error` = 2867 DEBUG: Error 2867: The error dialog property is not set The installer has encountered an unexpected error installing this package. This may indicate a problem with this package. The error code is 2867. The arguments are: , , MSI (c) (04:34) [11:03:27:031]: Font created. Charset: Req=0, Ret=0, Font: Req=MS Shell Dlg, Ret=MS Shell Dlg Failed to run initdb: 1! Please see the logfile in 'C:\Program Files\PostgreSQL\8.0\tmp\initdb.log'. Note! You must read/copy this logfile before you click OK, or it will be automatically removed. MSI (s) (C0!3C) [11:03:32:093]: Note: 1: 2205 2: 3: Error MSI (s) (C0!3C) [11:03:32:093]: Note: 1: 2228 2: 3: Error 4: SELECT `Message` FROM `Error` WHERE `Error` = 1709 MSI (s) (C0!3C) [11:03:32:093]: Product: PostgreSQL 8.0 -- Failed to run initdb: 1! Please see the logfile in 'C:\Program Files\PostgreSQL\8.0\tmp\initdb.log'. Note! You must read/copy this logfile before you click OK, or it will be automatically removed. Action ended 11:03:32: InstallFinalize. Return value 3. MSI (s) (C0:74) [11:03:32:109]: User policy value 'DisableRollback' is 0 MSI (s) (C0:74) [11:03:32:109]: Machine policy value 'DisableRollback' is 0 MSI (s) (C0:74) [11:03:32:531]: Executing op:
Re: [GENERAL] SQL Question
Julian Scarfe wrote: From: Alex [EMAIL PROTECTED] - How can i select only the newest record for each ProdId ? 100| 2005-04-01 200| 2005-04-01 DISTINCT ON was made for this and on the similar tables I have performs rather more efficiently than using a subquery. select distinct on (ProdId) ProdId , LastUpdate from produpdate order by ProdId , LastUpdate desc; - How can i select to retrieve the last 2 dates in record 100| 2005-04-01 | 2005-03-01 200| 2005-04-01 | 2005-03-01 To get the previous one, my first thought is something like: select distinct on (ProdId) ProdId , LastUpdate from produpdate p1 where LastUpdate ( select max(LastUpdate ) from produpdate p2 where p2.ProdId = p1.ProdId ) order by ProdId , LastUpdate desc ; but there may be a much more efficient way of getting the nth result in general. Julian Scarfe ---(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 Thanks for the help. will give it a try. Alex ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Help! Access is Denied Installation on WinXP rolled
log file it tells me to check before it deletes it in the rollback it says repeatedly something about access is denied. Below is the actual install log (or a small portion of it, near the end where the error occurs). Any ideas? Regards, Abe p.s. this is a repost from my initial post on the novice list... which I've become aware is relatively inactive. Did you check the initdb.log that was referenced in your Windows error message? I do not know this to be your issue but I am guess that you tried to install PostgreSQL with Administrator (or as an Administrator) rights. PostgreSQL must be installed as a non Administrative user on Windows. 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: [GENERAL] managing kernel
You'll probably want to take a look at this performance tuning reference: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html and this annotated configuration file: http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC Strategic Open Source: Open Your i http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 On Apr 17, 2005, at 4:57 AM, Dino Vliet wrote: OK, I have RTFM:-) SHMALL Total amount of shared memory available (bytes or pages) if bytes, same as SHMMAX; if pages, ceil(SHMMAX/PAGE_SIZE) These values should be the same. I've changed that now. Still my second question is valid though. Are there any other values I should consider changing in order to boost performance. I'm using postgresql 7.4. Brgds Dino ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Could not read block during vacuum
My company's product is developing a product using postgres 7.4.6 on a Linux 2.6.10 or 11 kernel, (don't have the exact version handy). We do vacuum verbose analyze daily. Two days after the database was created and loaded with a small amount of data (megabytes), the postgres logs had this line: 2005-04-16 00:00:53 ERROR: could not read block 52 of relation idx_ef_hash: Input/output error idx_ef_hash is an index. I believe that vacuuming is to blame based on vacuum output: 04/16 00:00:14.388 03136277 Starting daily vacuum 04/16 00:00:53.192 03136280 process failed with exit code 1. 04/16 00:00:53.193 03136281 stderr: INFO: vacuuming information_schema.sql_features 04/16 00:00:53.193 03136282 stderr: INFO: sql_features: found 0 removable, 360 nonremovable row versions in 5 pages 04/16 00:00:53.193 03136283 stderr: DETAIL: 0 dead row versions cannot be removed yet. 04/16 00:00:53.193 03136284 stderr: There were 0 unused item pointers. 04/16 00:00:53.193 03136285 stderr: 0 pages are entirely empty. 04/16 00:00:53.193 03136286 stderr: CPU 0.00s/0.00u sec elapsed 0.00 sec. 04/16 00:00:53.193 03136287 stderr: INFO: vacuuming pg_toast.pg_toast_17070 ... (The first two lines are from my application, the rest is stderr from the vacuum process.) Googling didn't turn up useful information on this problem. Can anyone tell me whether this error indicates a disk problem, OS problem, known postgres bug, or something else? We've only seen this occur once. Jack Orenstein This message was sent using IMP, the Internet Messaging Program. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Could not read block during vacuum
[EMAIL PROTECTED] writes: 2005-04-16 00:00:53 ERROR: could not read block 52 of relation idx_ef_hash: Input/output error Googling didn't turn up useful information on this problem. Can anyone tell me whether this error indicates a disk problem, OS problem, known postgres bug, or something else? We've only seen this occur once. Hardware problem ... probably the disk, but could be disk interface. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Exception handling: Oracle's SQLERRM keyword option?
Hi, I have installed PostgreSQL 8.0.1 on Solaris 9. I am porting my database from Oracle 9i to PostgreSQL. I am using PL/pgSQL language. In Oracle we can get errormessagefrom SQLERRM keyword and insert messages into log table. How can I get errormessage/error codein PostgreSQL after an EXCEPTION or RAISE EXCEPTION occurs in EXCEPTION block?? Pls help me or send me some example. Thanks Dinesh Pandey
Re: [GENERAL] [PATCHES] Exception handling: Oracle's SQLERRM keyword option?
Dinesh Pandey [EMAIL PROTECTED] writes: Pls help me or send me some example. Please do not post requests for help on pgsql-patches ... there is no Postgres list on which they are further off-topic. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
