Re: Turning off transactions completely.
От | Arsalan Zaidi |
---|---|
Тема | Re: Turning off transactions completely. |
Дата | |
Msg-id | 00c001c1980a$24b4ad00$4301a8c0@directi.com обсуждение исходный текст |
Ответ на | Re: Turning off transactions completely. ("Arsalan Zaidi" <azaidi@directi.com>) |
Ответы |
Re: Turning off transactions completely.
("Arguile" <arguile@lucentstudios.com>)
Re: Turning off transactions completely. (Martijn van Oosterhout <kleptog@svana.org>) Re: Turning off transactions completely. (Alvar Freude <alvar@agi.de>) |
Список | pgsql-general |
> I would suggest to optimize your Application! First thing I did! :-) > - cache previously inserted IDs and other things > (eg. use Perl hashes) I'm using Java/JDBC. Also, there's very little data I can cache and reuse. > > - create simpler Where clauses > Sorry, I need to be specific! Besides, I've used EXPLAIN extensively and these are the fastest possible queries (atleast I hope so) which do what I want them to do. > - look on your indexes, perhaps you can create an index on two columns? > Got them up the wazoo. Two column ones as well... Just want to know, is an index on (foo,bar) different from (bar,foo)? Does the order in which they appear in the index creation statement and in subsequent queries make a difference? > And: > use vacuum analyze after the Database is freshly build and filled > with the first ~ 100 000 rows. > Later vacuum analyze every 1 000 000 or 10 000 000 rows ... > I'm vacuuming like crazy (after the initial data COPY and then once the run ends(to prepare for the next run)) but there are two issues... 1. VACUUM is dead slow. The CPU monitor shows long stretches when there's barely any activity at all while the vacuum is going on. 2. I do vacuum analyze's to help the database figure out how to best run my queries. But it *still* doesnt use some of them... > > BTW, I've got a dual proc machine with a RAID-0 array and 1 GB of RAM, but > > pg only uses one CPU at a time. Would have been great if it had been > > multi-threaded or something. > > if you use two inserting processes, Postgres also should use two. AFAIK! :) > Ah, interesting point. I spent the last two (working) days converting my single process app into a multi threaded one, to allow me to fire off multiple queries at the same time. I was hoping this would lead to a more optimum use of system resources... Didn't work. Best case, the multi-threaded app is just as fast as the single -threaded one and in the worst case; it's much slower. I'm guessing that the various complex queries being fired at the same time are forcing the use of Swap (yes, it on another HDD entirely) ; which is slowing down the machine. --Arsalan.
В списке pgsql-general по дате отправления: