On 14/08/07, Gregory Stark <stark@xxxxxxxxxxxxxxxx> wrote: > "Phoenix Kiula" <phoenix.kiula@xxxxxxxxx> writes: > > > Though other threads I have learned that multiple inserts or updates > > can be sped up with: > > > > [QUOTE] > > - BEGIN TRANSACTION; > > - INSERT OR UPDATE queries, ideally PREPAREd; > > - COMMIT; > > [/QUOTE] > > > > QUESTION1: > > Is this it? Or am I missing something in terms of execution? We use > > Perl on our site but may be gradually switching to PHP at some point. > > Will the above plan of execution be ok? > > A transaction is a bunch of queries which you want to all get committed or > aborted together. The expensive step in Postgres is the COMMIT. Postgres does > an fsync which forces the data to go to disk (if you're using good hardware, > and don't have fsync=off). That takes from 5-15ms depending on how much data > and how fast your drives are. If I am reading this right, does this mean it is probably better to leave fsync as "fsync=off" on production machines? Also, is COMMIT automatic for my queries? In some minor testing I did (not scientific I did at all) some queries through Perl did not update the database at all. I had "fsync=off" in my conf file, and there was no COMMIT etc in my SQL, just plain SQL. So I am a bit confused. What's a good starting point? > Grouping more work into a single transaction makes the delay for the fsync at > COMMIT time less of a problem. Agree. That's what I am trying to do. Include as many UPDATEs etc into the same TRANSACTION block, but my worry is when I read about autocommit and how it is enabled by default in postgresql 8. Specifying explicit BEGIN and COMMIT blocks should only commit when I want the DB to, or will each UPDATE in the middle of this block get executed? Sorry if this is a naive question. I am reading up as much as I can. > Also having more connections (but not too many, > more than a few per processor is probably not helping, more than 100 and it's > probably slowing you down) also means it's less important since another > process can do some of its work while you're waiting for the fsync. So, again, in the conf file, is this what you recommend: fsync=off max_connections=100 ? > Yes, it's a pain. Running vacuum frequently will be necessary. You may also > have to raise your fsm settings to allow Postgres to remember more free space > between vacuums. Thank you for your comments about autovacuum. I have these FSM and memory type settings settings in my conf (picked off the internet :)) -- max_fsm_relations = 1500 max_fsm_pages = 80000 shared_buffers = 21000 effective_cache_size = 21000 sort_mem = 16348 work_mem = 16348 vacuum_mem = 16348 temp_buffers = 4096 authentication_timeout = 10s ssl = off Do these sound right? > Incidentally, most data integrity checks are handled with CHECK constraints > and FOREIGN KEY constraints rather than manual triggers. They're both easier > and cheaper. The problem with simple CHECK constraints is that they can only reference the primary key in another table. What if I want more than one column to be the same as the referenced table, but do not want to have a compound primary key in the referenced table? From reading and re-reading the manual, I dont think FKs allow for this. Only primary key references are supported. > Sorry, this is one of the main deficiencies in Postgres. You will probably > have to convert your data to utf8 across the board and hopefully you'll find a > collation which satisfies all your needs. I am happy to do this, but I don't see an ALTER DATABASE command. I would really like not to have to execute the CREATE DATABASE command again! Can I make the entire DB utf8 using some command now? Have not been able to find it. ALl manual and google stuff seems to point to the CREATE DB command only. Many thanks! ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
