Re: [PERFORM] Queries sometimes take 1000 times the normal time
We have a somewhat similar situation - we're running a fairly constant, but low priority, background load of about 70 selects and 40 inserts per second (batched into fairly large transactions), and on top of that we're trying to run time-sensitive queries for a web site (well two). I should emphasize that this is low low priority - if a query is delayed by an hour here, it doesn't matter. The web site queries will jump up one or two orders of magnitude (I have seen a normally 100ms query take in excess of 30 seconds) in duration at seemingly random points. It's not always when the transactions are committing, and it doesn't seem to be during checkpointing either. The same thing happens with WAL switched off. It appears to happen the first time the query runs after a while. If I run the same query immediately afterwards, it will take the normal amount of time. Any ideas? Cheers, Russ Garrett [EMAIL PROTECTED] wrote: Subject: [PERFORM] Queries sometimes take 1000 times the normal time Hello, We're running a set of Half-Life based game servers that lookup user privileges from a central PostgreSQL 7.3.4 database server (I recently ported the MySQL code in Adminmod to PostgreSQL to be able to do this). The data needed by the game servers are combined from several different tables, so we have some views set up to provide the data in the format needed. Currently there's only a few users in the database for testing purposes, and most of the time the user lookup's take 2-3 ms (I have syslog'ing of queries and duration turned on), but several times per hour the duration for one of the queries is 2-3 seconds (1000 times larger), while the surrounding lookups take the usual 2-3 ms. This is rather critical, as the game server software isn't asynchonous and thus waits for a reply before continuing, so when someone connects, and the user lookup happens to have one of these very long durations, the players on this server experience a major lag spike, which isn't very popular :-( All the game servers and the database server are connected to the same switch, so I don't think, that it is a network problem. So far I've been unable to locate the problem, so any suggestions are very welcome. Regards, Anders K. Pedersen ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Queries sometimes take 1000 times the normal time
The web site queries will jump up one or two orders of magnitude (I have seen a normally 100ms query take in excess of 30 seconds) in duration at seemingly random points. It's not always when the transactions are committing, and it doesn't seem to be during checkpointing either. The same thing happens with WAL switched off. It appears to happen the first time the query runs after a while. If I run the same query immediately afterwards, it will take the normal amount of time. Looks like it got flushed out of every type of cache and IO scheduler could not deliver immediately because of other loads... Yeah, I wasn't sure what (or how) Postgres caches. The db server does have 2Gb of memory, but then again the database amounts to more than 2Gb, so it's fairly possible it's getting pushed out of cache. It's also fairly possible that it's not tuned completely optimally. I wonder if FreeBSD/kernel 2.6 would perform better in such a situation... Russ ---(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
Re: [PERFORM] Update on putting WAL on ramdisk/
WAL on single drive: 7.990 rec/s WAL on 2nd IDE drive: 8.329 rec/s WAL on tmpfs: 13.172 rec/s A huge jump in performance but a bit scary having a WAL that can disappear at any time. I'm gonna workup a rsync script and do some power-off experiments to see how badly it gets mangled. Surely this is just equivalent to disabling fsync? If you put a WAL on a volatile file system, there's not a whole lot of point in having one at all. Russ Garrett[EMAIL PROTECTED] http://last.fm ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] TSearch2 vs. Apache Lucene
On 6 Dec 2005, at 16:47, Joshua Kramer wrote: Has anyone ever compared TSearch2 to Lucene, as far as performance is concerned? In our experience (small often-updated documents) Lucene leaves tsearch2 in the dust. This probably has a lot to do with our usage pattern though. For our usage it's very beneficial to have the index on a separate machine to the data, however in many cases this won't make sense. Lucene is also a lot easier to cluster than Postgres (it's simply a matter of NFS-mounting the index). Russ Garrett [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: explain analyze is your friend
