Re: [PERFORM] Mount database on RAM disk?
If I could get and deploy some SSD (Solid State Disk) devices that would make this sort of thing *actually safe,* I'd expect that to be a pretty fabulous improvement, at least for write-heavy database activity. Not nearly as much as you would expect. For the price of the SSD and a SCSI controller capable of keeping up to the SSD along with your regular storage with enough throughput to keep up to structure IO, you can purchase a pretty good mid-range SAN which will be just as capable and much more versatile. -- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] plain inserts and deletes very slow
* Klint Gore [EMAIL PROTECTED] wrote: snip Turn on statement logging. I've seen delphi interfaces do extra queries on system tables to find some structure information. I'm already using statement logging of all queries taking longer than 200ms. It seems that only the INSERT takes such a time. The client is in fact written in delphi, and it sometimes seems to do strange things. For example we had the effect, that some new fields in some table were regularily NULL'ed. None of the triggers and rules inside the DB could do that (since there's no dynamic query stuff) and the delphi application is the only one writing directly to this table. cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Select performance vs. mssql
* Bruno Wolff III [EMAIL PROTECTED] wrote: snip This gets brought up a lot. The problem is that the index doesn't include information about whether the current transaction can see the referenced row. Putting this information in the index will add significant overhead to every update and the opinion of the developers is that this would be a net loss overall. wouldn't it work well to make this feature optionally for each index ? There could be some flag on the index (ie set at create time) which tells postgres whether to store mvcc information. cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Need help to decide Mysql vs Postgres
* PFC [EMAIL PROTECTED] wrote: snip For Python it's the reverse : the MySQL driver is slow and dumb, and the postgres driver (psycopg 2) is super fast, handles all quoting, and knows about type conversions, it will automatically convert a Python List into a postgres Array and do the right thing with quoting, and it works both ways (ie you select a TEXT[] you get a list of strings all parsed for you). It knows about all the postgres types (yes even numeric = python Decimal) and you can even add your own types. That's really cool, plus the developer is a friendly guy. Is there anything similar for java ? cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Need help to decide Mysql vs Postgres
On Fri, 2005-07-08 at 16:43 +0200, Enrico Weigelt wrote: * PFC [EMAIL PROTECTED] wrote: snip For Python it's the reverse : the MySQL driver is slow and dumb, and the postgres driver (psycopg 2) is super fast, handles all quoting, and knows about type conversions, it will automatically convert a Python List into a postgres Array and do the right thing with quoting, and it works both ways (ie you select a TEXT[] you get a list of strings all parsed for you). It knows about all the postgres types (yes even numeric = python Decimal) and you can even add your own types. That's really cool, plus the developer is a friendly guy. Is there anything similar for java ? The postgres JDBC driver is very good-- refer to pgsql-jdbc mailing list or look at jdbc.postgresql.org. I've had only limited experience with the mysql jdbc driver, but it seemed servicable enough, if you can live with their licensing and feature set. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Need help to decide Mysql vs Postgres
Linux(Debian) + Java + PostgreSQL = Fastest 2005/7/8, Mark Lewis [EMAIL PROTECTED]: On Fri, 2005-07-08 at 16:43 +0200, Enrico Weigelt wrote: * PFC [EMAIL PROTECTED] wrote: snip For Python it's the reverse : the MySQL driver is slow and dumb, and the postgres driver (psycopg 2) is super fast, handles all quoting, and knows about type conversions, it will automatically convert a Python List into a postgres Array and do the right thing with quoting, and it works both ways (ie you select a TEXT[] you get a list of strings all parsed for you). It knows about all the postgres types (yes even numeric = python Decimal) and you can even add your own types. That's really cool, plus the developer is a friendly guy. Is there anything similar for java ? The postgres JDBC driver is very good-- refer to pgsql-jdbc mailing list or look at jdbc.postgresql.org. I've had only limited experience with the mysql jdbc driver, but it seemed servicable enough, if you can live with their licensing and feature set. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Atte Moises Alberto Lindo Gutarra Consultor y Desarrollador Java / Open Source TUMI Solutions SAC Tel: +51.13481104 Cel: +51.197366260 MSN : [EMAIL PROTECTED] ---(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] cost-based vacuum
I am beginning to look at Postgres 8, and am particularly interested in cost-based vacuum/analyze. I'm hoping someone can shed some light on the behavior I am seeing. Suppose there are three threads: writer_thread every 1/15 second do BEGIN TRANSACTION COPY table1 FROM stdin ... COPY tableN FROM stdin perform several UPDATEs, DELETEs and INSERTs COMMIT reader_thread every 1/15 second do BEGIN TRANSACTION SELECT FROM table1 ... ... SELECT FROM tableN ... COMMIT analyze_thread every 5 minutes do ANALYZE table1 ... ANALYZE tableN Now, Postgres 8.0.3 out-of-the-box (all default configs) on a particular piece of hardware runs the Postgres connection for writer_thread at about 15% CPU (meaningless, I know, but for comparison) and runs the Postgres connection for reader_thread at about 30% CPU. Latency for reader_thread seeing updates from writer_thread is well under 1/15s. Impact of analyze_thread is negligible. If I make the single configuration change of setting vacuum_cost_delay=1000, each iteration in analyze_thread takes much longer, of course. But what I also see is that the CPU usage of the connections for writer_thread and reader_thread spike up to well over 80% each (this is a dualie) and latency drops to 8-10s, during the ANALYZEs. I don't understand why this would be. I don't think there are any lock issues, and I don't see any obvious I/O issues. Am I missing something? Is there any way to get some insight into what those connections are doing? Thanks, --Ian ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Why the planner is not using the INDEX .
* David Gagnon [EMAIL PROTECTED] wrote: FOR inventoryTransaction IN SELECT DISTINCT IRNUM, IRAENUM, IRSTATUT, IRSENS, IRSOURCE, IRDATE, IRQTE FROM IR WHERE IRNUM = ANY (requestIds) and IRYPNUM = companyId LOOP hmm. you probably could create the query dynamically and then execute it. BTW: why isn't IN not usable with arrays ? cu -- - Enrico Weigelt== metux IT service phone: +49 36207 519931 www: http://www.metux.de/ fax: +49 36207 519932 email: [EMAIL PROTECTED] - Realtime Forex/Stock Exchange trading powered by postgresSQL :)) http://www.fxignal.net/ - ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] cost-based vacuum
Ian Westmacott [EMAIL PROTECTED] writes: If I make the single configuration change of setting vacuum_cost_delay=1000, each iteration in analyze_thread takes much longer, of course. But what I also see is that the CPU usage of the connections for writer_thread and reader_thread spike up to well over 80% each (this is a dualie) and latency drops to 8-10s, during the ANALYZEs. [ scratches head... ] That doesn't make any sense at all. I don't understand why this would be. I don't think there are any lock issues, and I don't see any obvious I/O issues. Am I missing something? Is there any way to get some insight into what those connections are doing? Profiling maybe? Can you put together a self-contained test case that replicates this behavior, so other people could look? regards, tom lane ---(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] Mount database on RAM disk?
Stuart, I'm putting together a road map on how our systems can scale as our load increases. As part of this, I need to look into setting up some fast read only mirrors of our database. We should have more than enough RAM to fit everything into memory. I would like to find out if I could expect better performance by mounting the database from a RAM disk, or if I would be better off keeping that RAM free and increasing the effective_cache_size appropriately. If you're accessing a dedicated, read-only system with a database small enough to fit in RAM, it'll all be cached there anyway, at least on Linux and BSD. You won't be gaining anything by creating a ramdisk. ditto windows. Files cached in memory are slower than reading straight from memory but not nearly enough to justify reserving memory for your use. In other words, your O/S is a machine with years and years of engineering designed best how to dole memory out to caching and various processes. Why second guess it? Merlin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Select performance vs. mssql
Enrico Weigelt wrote: Bruno Wolff III wrote: This gets brought up a lot. The problem is that the index doesn't include information about whether the current transaction can see the referenced row. Putting this information in the index will add significant overhead to every update and the opinion of the developers is that this would be a net loss overall. wouldn't it work well to make this feature optionally for each index ? There could be some flag on the index (ie set at create time) which tells postgres whether to store mvcc information. There is no reason to assume it can't work. There is little reason to assume that it will be the best solution in many circumstances. There is a big reason why people are sceptical: there is no patch. The issue has been debated and beaten to death. People have formed their opinions and are unlikely to change their position. If you want to convince people, your best bet is to submit a patch and have OSDL measure the performance improvement. Jochem ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] How to revoke a password
I created a user with a password. That newly created user now have tables and indexes. I want to ALTER that user to exclude the password. How is this accomplished without dropping and recreating the users? Larry Bailey Sr. Oracle DBA First American Real Estate Solution (714) 701-3347 [EMAIL PROTECTED] ** This message contains confidential information intended only for the use of the addressee(s) named above and may contain information that is legally privileged. If you are not the addressee, or the person responsible for delivering it to the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictly prohibited. If you have received this message by mistake, please immediately notify us by replying to the message and delete the original message immediately thereafter. Thank you. FADLD Tag ** ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] How to revoke a password
Bailey, Larry wrote: I created a user with a password. That newly created user now have tables and indexes. I want to ALTER that user to exclude the password. How is this accomplished without dropping and recreating the users? Never tried to go backwards before but: alter user foo with encrypted password ''; But as I look at pg_shadow there is still a hash... You could do: update pg_shadow set passwd = '' where usename = 'foo'; Sincerely, Joshua D. Drake Larry Bailey Sr. Oracle DBA First American Real Estate Solution (714) 701-3347 [EMAIL PROTECTED] ** This message contains confidential information intended only for the use of the addressee(s) named above and may contain information that is legally privileged. If you are not the addressee, or the person responsible for delivering it to the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictly prohibited. If you have received this message by mistake, please immediately notify us by replying to the message and delete the original message immediately thereafter. Thank you. FADLD Tag ** ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Your PostgreSQL solutions provider, Command Prompt, Inc. 24x7 support - 1.800.492.2240, programming, and consulting Home of PostgreSQL Replicator, plPHP, plPerlNG and pgPHPToolkit http://www.commandprompt.com / http://www.postgresql.org ---(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] How to revoke a password
On Fri, Jul 08, 2005 at 05:09:48PM -0700, Joshua D. Drake wrote: Bailey, Larry wrote: I created a user with a password. That newly created user now have tables and indexes. I want to ALTER that user to exclude the password. How is this accomplished without dropping and recreating the users? Never tried to go backwards before but: alter user foo with encrypted password ''; I think you use NULL as password to ALTER USER. -- Alvaro Herrera (alvherre[a]alvh.no-ip.org) Y eso te lo doy firmado con mis lágrimas (Fiebre del Loco) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] How to revoke a password
Bailey, Larry wrote: Thanks but it is still prompting for a password. Does your pg_hba.conf require a password? Sincerely, Joshua D. Drake Larry Bailey Sr. Oracle DBA First American Real Estate Solution (714) 701-3347 [EMAIL PROTECTED] -Original Message- From: Joshua D. Drake [mailto:[EMAIL PROTECTED] Sent: Friday, July 08, 2005 5:10 PM To: Bailey, Larry Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] How to revoke a password Bailey, Larry wrote: I created a user with a password. That newly created user now have tables and indexes. I want to ALTER that user to exclude the password. How is this accomplished without dropping and recreating the users? Never tried to go backwards before but: alter user foo with encrypted password ''; But as I look at pg_shadow there is still a hash... You could do: update pg_shadow set passwd = '' where usename = 'foo'; Sincerely, Joshua D. Drake Larry Bailey Sr. Oracle DBA First American Real Estate Solution (714) 701-3347 [EMAIL PROTECTED] ** This message contains confidential information intended only for the use of the addressee(s) named above and may contain information that is legally privileged. If you are not the addressee, or the person responsible for delivering it to the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictly prohibited. If you have received this message by mistake, please immediately notify us by replying to the message and delete the original message immediately thereafter. Thank you. FADLD Tag ** ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Your PostgreSQL solutions provider, Command Prompt, Inc. 24x7 support - 1.800.492.2240, programming, and consulting Home of PostgreSQL Replicator, plPHP, plPerlNG and pgPHPToolkit http://www.commandprompt.com / http://www.postgresql.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Your PostgreSQL solutions provider, Command Prompt, Inc. 24x7 support - 1.800.492.2240, programming, and consulting Home of PostgreSQL Replicator, plPHP, plPerlNG and pgPHPToolkit http://www.commandprompt.com / http://www.postgresql.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] How to revoke a password
On Fri, Jul 08, 2005 at 05:16:27PM -0700, Bailey, Larry wrote: Thanks but it is still prompting for a password. Let's back up a bit: what problem are you trying to solve? Do you want the user to be able to log in without entering a password? If so then see Client Authentication in the documentation: http://www.postgresql.org/docs/8.0/static/client-authentication.html If you're trying to do something else then please elaborate, as it's not clear what you mean by I want to ALTER that user to exclude the password. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
