RES: [PERFORM] Temporary table
Ok, it works. Thanks Franklin -Mensagem original- De: Larry Rosenman [mailto:[EMAIL PROTECTED] Enviada em: sexta-feira, 23 de junho de 2006 19:08 Para: 'Franklin Haut'; pgsql-performance@postgresql.org Assunto: RE: [PERFORM] Temporary table Franklin Haut wrote: > Hello, > > I´m have some problems with a temporary table, i need create a table, > insert some values, make a select and at end of transaction the table > must droped, but after i created a table there not more exist, is this > normal ? > > How to reproduce : > > > CREATE TEMP TABLE cademp ( > codemp INTEGER, > codfil INTEGER, > nomemp varchar(50) > ) ON COMMIT DROP; > > INSERT INTO cademp (codemp, codfil, nomemp) values (1,1,'TESTE'); > INSERT INTO cademp (codemp, codfil, nomemp) values (1,2,'TESTE1'); > > Select * from cademp; > > > > In this case, the table cademp doesn´t exist at the first insert, in > the same transaction. > It is NOT the same transaction. By default, each STATEMENT is it's own transaction. Stick a BEGIN; before the create table, and a commit; after the select. Larry Rosenman > > > > Tks, > > Franklin > > > ---(end of > broadcast)--- TIP 6: explain analyze is your > friend -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 512-248-2683 E-Mail: ler@lerctr.org US Mail: 430 Valona Loop, Round Rock, TX 78681-3893 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] Temporary table
Hello, I´m have some problems with a temporary table, i need create a table, insert some values, make a select and at end of transaction the table must droped, but after i created a table there not more exist, is this normal ? How to reproduce : CREATE TEMP TABLE cademp ( codemp INTEGER, codfil INTEGER, nomemp varchar(50) ) ON COMMIT DROP; INSERT INTO cademp (codemp, codfil, nomemp) values (1,1,'TESTE'); INSERT INTO cademp (codemp, codfil, nomemp) values (1,2,'TESTE1'); Select * from cademp; In this case, the table cademp doesn´t exist at the first insert, in the same transaction. Tks, Franklin ---(end of broadcast)--- TIP 6: explain analyze is your friend
ENC: RES: [PERFORM] pg_dump slow - Solution
Hi, Finally i found the problem of slow backup/restore, i´m only instaled de Windows 2000 Service Pack 4... :) Thanks to all Franklin -Mensagem original- De: Richard Huxton [mailto:[EMAIL PROTECTED] Enviada em: quarta-feira, 30 de novembro de 2005 14:28 Para: Franklin Haut Cc: 'Ron'; pgsql-performance@postgresql.org Assunto: Re: RES: [PERFORM] pg_dump slow Franklin Haut wrote: > Hi, > > Yes, my problem is that the pg_dump takes 40 secs to complete under > WinXP and 50 minutes under W2K! The same database, the same hardware!, > only diferrent Operational Systems. > > The hardware is: >Pentium4 HT 3.2 GHz >1024 Mb Memory >HD 120Gb SATA There have been reports of very slow network performance on Win2k systems with the default configuration. You'll have to check the archives for details I'm afraid. This might apply to you. If you're happy that doesn't affect you then I'd look at the disk system - perhaps XP has newer drivers than Win2k. What do the MS performance-charts show is happening? Specifically, CPU and disk I/O. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] pg_dump slow
I Maked a new install on machine this night, and the same results, on console localhost Windows 2000 Server Version 5.00.2195 PG Version 8.1 Franklin >Franlin: are you making pg_dump from local or remote box and is this a >clean install? Try fresh patched win2k install and see what happens. He claimed this was local, not network. It is certainly an intriguing possibility that W2K and WinXP handle bytea differently. I'm not competent to comment on that however. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
RES: [PERFORM] pg_dump slow
Complementing... The test was maked at the same machine ( localhost ) at Command-Prompt, no client´s connected, no concurrent processes only PostgreSQL running. In windows XP, exists much access to the processor (+- 70%) and HD (I see HD Led allways on), while in the W2K almost without activity of processor (3%)and little access to the HardDisk (most time of the led HD is off). Look, the database has 81 Tables, one of these, has 2 fields ( one integer and another ByteA ), these table as 5.150 Records. I´m Dumpped only this table and the file size is 7Mb (41% of total (17MB is the total)) was very slow Then I Maked Backup of the others tables was fast! So i´m conclused that pg_dump and pg_restore is very slow when manipulates ByteA type on W2K!, is this possible ? Franklin -Mensagem original- De: Merlin Moncure [mailto:[EMAIL PROTECTED] Enviada em: quarta-feira, 30 de novembro de 2005 13:57 Para: Ron Cc: pgsql-performance@postgresql.org; Franklin Haut Assunto: RE: [PERFORM] pg_dump slow > At 08:35 AM 11/30/2005, Franklin Haut wrote: > >Hi > > > >i´m using PostgreSQL on windows 2000, the pg_dump take around 50 > >minutes to do backup of 200Mb data ( with no compression, and 15Mb > >with compression), > > Compression is reducing the data to 15/200= 3/40= 7.5% of original > size? > > >but in windows XP does not pass of 40 seconds... :( > > You mean that 40 secs in pg_dump under Win XP > crashes, and therefore you have a WinXP problem? > > Or do you mean that pg_dump takes 40 secs to > complete under WinXP and 50 minutes under W2K and > therefore you have a W2K problem? I think he is saying the time to dump does not take more than 40 seconds, but I'm not sure. > In fact, either 15MB/40secs= 375KBps or > 200MB/40secs= 5MBps is _slow_, so there's a problem under either > platform! 5 mb/sec dump output from psql is not terrible or even bad, depending on hardware. > >not pass of 3%. > Assuming Win XP completes the dump, the first thing to do is *don't > use W2K* XP is not a server platform. Next level up is 2003 server. Many organizations still have 2k deployed. About half of my servers still run it. Anyways, the 2k/xp issue does not explain why there is a performance problem. > M$ has stopped supporting it in anything but absolutely minimum > fashion anyway. > _If_ you are going to use an M$ OS you should be using WinXP. (You > want to pay licensing fees for your OS, but you are using free DB SW? > Huh? If you are trying to save $$$, use Open Source SW like Linux > or *BSD. pg will perform better under it, and it's cheaper!) I would like to see some benchmarks supporting those claims. No comment on licensing issue, but there are many other factors in considering server platform than licensing costs. That said, there were several win32 specific pg performance issues that were rolled up into the 8.1 release. So for win32 you definitely want to be running 8.1. > Assuming that for some reason you can't/won't > migrate to a non-M$ OS, the next problem is the > slow HD IO you are getting under WinXP. Problem is almost certainly not related to disk unless there is a imminent disk failure. Could be TCP/IP issue (are you running pg_dump from remote box?), or possibly a network driver issue or some other weird software issue. Can you determine if disk is running normally with respect to other applications? Is this a fresh win2k install? A LSP, virus scanner, backup software, or some other garbage can really ruin your day. Merlin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
RES: [PERFORM] pg_dump slow
Hi, Yes, my problem is that the pg_dump takes 40 secs to complete under WinXP and 50 minutes under W2K! The same database, the same hardware!, only diferrent Operational Systems. The hardware is: Pentium4 HT 3.2 GHz 1024 Mb Memory HD 120Gb SATA Im has make again the test, and then real size of database is 174Mb (avaliable on pg_admin, properties) and the file size of pg_dump is 18Mb ( with command line pg_dump -i -F c -b -v -f "C:\temp\BackupTest.bkp" NameOfDatabase ). The time was equal in 40 seconds on XP and 50 minutes on W2K, using PG 8.1 Unhappyly for some reasons I cannot use other platforms, I need use PG on Windows, and must be W2K. Is strange to have a so great difference in the time of execution of dump, therefore the data are the same ones and the archive is being correctly generated in both OS. Franklin -Mensagem original- De: Ron [mailto:[EMAIL PROTECTED] Enviada em: quarta-feira, 30 de novembro de 2005 10:57 Para: Franklin Haut; pgsql-performance@postgresql.org Assunto: Re: [PERFORM] pg_dump slow At 08:35 AM 11/30/2005, Franklin Haut wrote: >Hi > >i´m using PostgreSQL on windows 2000, the pg_dump take around 50 >minutes to do backup of 200Mb data ( with no compression, and 15Mb with >compression), Compression is reducing the data to 15/200= 3/40= 7.5% of original size? >but in windows XP does not pass of 40 seconds... :( You mean that 40 secs in pg_dump under Win XP crashes, and therefore you have a WinXP problem? Or do you mean that pg_dump takes 40 secs to complete under WinXP and 50 minutes under W2K and therefore you have a W2K problem? In fact, either 15MB/40secs= 375KBps or 200MB/40secs= 5MBps is _slow_, so there's a problem under either platform! >This happens with 8.1 and version 8.0, somebody >passed for the same situation? > >It will be that a configuration in the priorities of the exists >processes ? in Windows XP the processing of schemes goes 70% and >constant accesses to the HardDisk, while that in windows 2000 it does >not pass of 3%. Assuming Win XP completes the dump, the first thing to do is *don't use W2K* M$ has stopped supporting it in anything but absolutely minimum fashion anyway. _If_ you are going to use an M$ OS you should be using WinXP. (You want to pay licensing fees for your OS, but you are using free DB SW? Huh? If you are trying to save $$$, use Open Source SW like Linux or *BSD. pg will perform better under it, and it's cheaper!) Assuming that for some reason you can't/won't migrate to a non-M$ OS, the next problem is the slow HD IO you are getting under WinXP. What is the HW involved here? Particularly the HD subsystem and the IO bus(es) it is plugged into? For some perspective, Raw HD average IO rates for even reasonably modern 7200rpm HD's is in the ~50MBps per HD range. Top of the line 15Krpm SCSI and FC HD's have raw average IO rates of just under 80MBps per HD as of this post. Given that most DB's are not on 1 HD (if you DB _is_ on only 1 HD, change that ASAP before you lose data...), for anything other than a 2 HD RAID 1 set I'd expect raw HD average IO rates to be at least 100MBps. If you are getting >= 100MBps of average HD IO, you should be getting > 5MBps during pg_dump, and certainly > 375MBps! Ron ---(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
[PERFORM] pg_dump slow
Hi i´m using PostgreSQL on windows 2000, the pg_dump take around 50 minutes to do backup of 200Mb data ( with no compression, and 15Mb with compression), but in windows XP does not pass of 40 seconds... :( This happens with 8.1 and version 8.0, somebody passed for the same situation? It will be that a configuration in the priorities of the exists processes ? in Windows XP the processing of schemes goes 70% and constant accesses to the HardDisk, while that in windows 2000 it does not pass of 3%. thanks Franklin ---(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
