Re: [PERFORM] GiST indexes and concurrency (tsearch2)
Tom Lane wrote: You might try the attached patch (which I just applied to HEAD). It cuts down the number of acquisitions of the BufMgrLock by merging adjacent bufmgr calls during a GIST index search. [...] Thanks - I applied it successfully against 8.0.0, but it didn't seem to have a noticeable effect. I'm still seeing more or less exactly 25% CPU usage by postgres processes and identical query times (measured with the Perl script I posted earlier). Regards, Marinos -- Dipl.-Ing. Marinos Yannikos, CEO Preisvergleich Internet Services AG Obere Donaustrasse 63, A-1020 Wien Tel./Fax: (+431) 5811609-52/-55 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] GiST indexes and concurrency (tsearch2)
Tom Lane wrote: I'm not completely convinced that you're seeing the same thing, but if you're seeing a whole lot of semops then it could well be. I'm seeing ~280 semops/second with spinlocks enabled and ~80k semops/second ( 4 mil. for 100 queries) with --disable-spinlocks, which increases total run time by ~20% only. In both cases, cpu usage stays around 25%, which is a bit odd. [...]You said you're testing a quad-processor machine, so it could be that you're seeing the same lock contention issues that we've been trying to figure out for the past year ... Are those issues specific to a particular platform (only x86/Linux?) or is it a problem with SMP systems in general? I guess I'll be following the current discussion on -hackers closely... Regards, Marinos ---(end of broadcast)--- TIP 3: 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] GiST indexes and concurrency (tsearch2)
Oleg Bartunov wrote:
On Thu, 3 Feb 2005, Marinos J. Yannikos wrote:
concurrent access to GiST indexes isn't possible at the moment. I [...]
there are should no problem with READ access.
OK, thanks everyone (perhaps it would make sense to clarify this in the
manual).
I'm willing to see some details: version, query, explain analyze.
8.0.0
Query while the box is idle:
explain analyze select count(*) from fr_offer o, fr_merchant m where
idxfti @@ to_tsquery('ranz mc') and eur = 70 and m.m_id=o.m_id;
Aggregate (cost=2197.48..2197.48 rows=1 width=0) (actual
time=88.052..88.054 rows=1 loops=1)
- Merge Join (cost=2157.42..2196.32 rows=461 width=0) (actual
time=88.012..88.033 rows=3 loops=1)
Merge Cond: (outer.m_id = inner.m_id)
- Index Scan using fr_merchant_pkey on fr_merchant m
(cost=0.00..29.97 rows=810 width=4) (actual time=0.041..1.233 rows=523
loops=1)
- Sort (cost=2157.42..2158.57 rows=461 width=4) (actual
time=85.779..85.783 rows=3 loops=1)
Sort Key: o.m_id
- Index Scan using idxfti_idx on fr_offer o
(cost=0.00..2137.02 rows=461 width=4) (actual time=77.957..85.754 rows=3
loops=1)
Index Cond: (idxfti @@ '\'ranz\' \'mc\''::tsquery)
Filter: (eur = 70::double precision)
Total runtime: 88.131 ms
now, while using apachebench (-c10), top says this:
Cpu0 : 15.3% us, 10.0% sy, 0.0% ni, 74.7% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu1 : 13.3% us, 11.6% sy, 0.0% ni, 75.1% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu2 : 16.9% us, 9.6% sy, 0.0% ni, 73.4% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu3 : 18.7% us, 14.0% sy, 0.0% ni, 67.0% id, 0.0% wa, 0.0% hi, 0.3% si
(this is with shared_buffers = 2000; a larger setting makes almost no
difference for overall performance: although according to top system
time goes to ~0 and user time to ~25%, the system still stays 70-75% idle)
vmstat:
r b swpd free buff cache si sobibo incs us
sy id wa
2 0 0 8654316 64908 4177136005635 279 286 5
1 94 0
2 0 0 8646188 64908 417713600 0 0 1156 2982 15
10 75 0
2 0 0 8658412 64908 417713600 0 0 1358 3098 19
11 70 0
1 0 0 8646508 64908 417713600 0 104 1145 2070 13
12 75 0
so the script's execution speed is apparently not limited by the CPUs.
The query execution times go up like this while apachebench is running
(and the system is 75% idle):
Aggregate (cost=2197.48..2197.48 rows=1 width=0) (actual
time=952.661..952.663 rows=1 loops=1)
- Merge Join (cost=2157.42..2196.32 rows=461 width=0) (actual
time=952.621..952.641 rows=3 loops=1)
Merge Cond: (outer.m_id = inner.m_id)
- Index Scan using fr_merchant_pkey on fr_merchant m
(cost=0.00..29.97 rows=810 width=4) (actual time=2.078..3.338 rows=523
loops=1)
- Sort (cost=2157.42..2158.57 rows=461 width=4) (actual
time=948.345..948.348 rows=3 loops=1)
Sort Key: o.m_id
- Index Scan using idxfti_idx on fr_offer o
(cost=0.00..2137.02 rows=461 width=4) (actual time=875.643..948.301
rows=3 loops=1)
Index Cond: (idxfti @@ '\'ranz\' \'mc\''::tsquery)
Filter: (eur = 70::double precision)
Total runtime: 952.764 ms
I can't seem to find out where the bottleneck is, but it doesn't seem to
be CPU or disk. top shows that postgres processes are frequently in
this state:
PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ WCHAN
COMMAND
6701 postgres 16 0 204m 58m 56m S 9.3 0.2 0:06.96 semtimedo
^
postmaste
Any hints are appreciated...
Regards,
Marinos
--
Dipl.-Ing. Marinos Yannikos, CEO
Preisvergleich Internet Services AG
Obere Donaustraße 63/2, A-1020 Wien
Tel./Fax: (+431) 5811609-52/-55
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] GiST indexes and concurrency (tsearch2)
Oleg Bartunov wrote:
Marinos,
what if you construct apachebench Co free script and see if
the issue still exists. There are could be many issues doesn't
connected to postgresql and tsearch2.
Yes, the problem persists - I wrote a small perl script that forks 10
chils processes and executes the same queries in parallel without any
php/apachebench involved:
--- 8 ---
#!/usr/bin/perl
use DBI;
$n=10;
$nq=100;
$sql=select count(*) from fr_offer o, fr_merchant m where idxfti @@
to_tsquery('ranz mc') and eur = 70 and m.m_id=o.m_id;;
sub reaper { my $waitedpid = wait; $running--; $SIG{CHLD} = \reaper; }
$SIG{CHLD} = \reaper;
for $i (1..$n)
{
if (fork() 0) { $running++; }
else
{
my
$dbh=DBI-connect('dbi:Pg:host=daedalus;dbname=censored','root','',{
AutoCommit = 1 }) || die !db;
for my $j (1..$nq)
{
my $sth=$dbh-prepare($sql);
$r=$sth-execute() or print STDERR $dbh-errstr();
}
exit 0;
}
}
while ($running 0)
{
sleep 1;
print Running: $running\n;
}
--- 8 ---
Result (now with shared_buffers = 2, hence less system and more user
time):
Cpu0 : 25.1% us, 0.0% sy, 0.0% ni, 74.9% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu1 : 18.3% us, 0.0% sy, 0.0% ni, 81.7% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu2 : 27.8% us, 0.3% sy, 0.0% ni, 71.9% id, 0.0% wa, 0.0% hi, 0.0% si
Cpu3 : 23.5% us, 0.3% sy, 0.0% ni, 75.9% id, 0.0% wa, 0.0% hi, 0.3% si
PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ WCHAN
COMMAND
7571 postgres 16 0 204m 62m 61m R 10.6 0.2 0:01.97 -
postmaste
7583 postgres 16 0 204m 62m 61m S 9.6 0.2 0:02.06 semtimedo
postmaste
7586 postgres 16 0 204m 62m 61m S 9.6 0.2 0:02.00 semtimedo
postmaste
7575 postgres 16 0 204m 62m 61m S 9.3 0.2 0:02.12 semtimedo
postmaste
7578 postgres 16 0 204m 62m 61m R 9.3 0.2 0:02.05 -
postmaste
i.e., virtually no difference. With 1000 queries and 10 in parallel, the
apachebench run takes 60.674 seconds and the perl script 59.392 seconds.
Regards,
Marinos
--
Dipl.-Ing. Marinos Yannikos, CEO
Preisvergleich Internet Services AG
Obere Donaustraße 63/2, A-1020 Wien
Tel./Fax: (+431) 5811609-52/-55
---(end of broadcast)---
TIP 3: 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] GiST indexes and concurrency (tsearch2)
Tom Lane schrieb: What's the platform exactly (hardware and OS)? Hardware: http://www.appro.com/product/server_1142h.asp - SCSI version, 2 x 146GB 10k rpm disks in software RAID-1 - 32GB RAM OS: Linux 2.6.10-rc3, x86_64, debian GNU/Linux distribution - CONFIG_K8_NUMA is currently turned off (no change, but now all CPUs have ~25% load, previously one was 100% busy and the others idle) - CONFIG_GART_IOMMU=y (but no change, tried both settings) [other kernel options didn't seem to be relevant for tweaking at the moment, mostly they're safe defaults] The PostgreSQL data directory is on an ext2 filesystem. Regards, Marinos -- Dipl.-Ing. Marinos Yannikos, CEO Preisvergleich Internet Services AG Obere Donaustrasse 63, A-1020 Wien Tel./Fax: (+431) 5811609-52/-55 ---(end of broadcast)--- TIP 3: 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] GiST indexes and concurrency (tsearch2)
Hi, according to http://www.postgresql.org/docs/8.0/interactive/limitations.html , concurrent access to GiST indexes isn't possible at the moment. I haven't read the thesis mentioned there, but I presume that concurrent read access is also impossible. Is there any workaround for this, esp. if the index is usually only read and not written to? It seems to be a big problem with tsearch2, when multiple clients are hammering the db (we have a quad opteron box here that stays 75% idle despite an apachebench with concurrency 10 stressing the php script that uses tsearch2, with practically no disk accesses) Regards, Marinos -- Dipl.-Ing. Marinos Yannikos, CEO Preisvergleich Internet Services AG Obere Donaustraße 63/2, A-1020 Wien Tel./Fax: (+431) 5811609-52/-55 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] optimization ideas for frequent, large(ish) updates
Jeff Trout wrote: Remember that it is going to allocate 800MB per sort. It is not you can allocate up to 800MB, so if you need 1 meg, use one meg. Some queries may end up having a few sort steps. I didn't know that it always allocates the full amount of memory specificed in the configuration (e.g. the annotated configuration guide says: Note that for a complex query, several sorts might be running in parallel, and each one _will be allowed to use_ as much memory as this value specifies before it starts to put data into temporary files.). The individual postgres processes don't look like they're using the full amount either (but that could be because the memory isn't written to). In terms of sort mem it is best to set a system default to a nice good value for most queries. and then in your reporting queries or other ones set sort_mem for that session (set sort_mem = 80) then only that session will use the looney sort_mem Queries from the web front-end use up to ~130MB sort memory (according to pgsql_tmp), so I set this to 150MB - thanks. It would be interesting to know if your machine is swapping. It's not being monitored closely (other than with the occasional top), but it's highly unlikely: Mem: 12441864k total, 10860648k used, 1581216k free,84552k buffers Swap: 4008176k total, 2828k used, 4005348k free, 9762628k cached (that's a typical situation - the 2828k used are probably some rarely used processes that have lower priority than the cache ...) Regards, Marinos ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] optimization ideas for frequent, large(ish) updates
Josh Berkus wrote: 800MB for sort mem? Are you sure you typed that correctly? You must be counting on not having a lot of concurrent queries. It sure will speed up index updating, though! 800MB is correct, yes... There are usually only 10-30 postgres processes active (imagine 5-10 people working on the web front-end while cron jobs access the db occasionally). Very few queries can use such large amounts of memory for sorting, but they do exist. I think you might do well to experiment with using the checkpoint_delay and checkpoint_sibilings settings in order to get more efficient batch processing of updates while selects are going on. [commit_*?] I thought that could improve only concurrent transactions... What have you set max_fsm_relations and max_fsm_pages to? The latter should be very high for you, like 10,000,000 good guess ;-) the former is set to 10,000 (I'm not sure how useful this is for those temporary tables) For that matter, what *version* of PostgreSQL are you running? 7.4.1 Also, make sure that your tables get vaccuumed regularly. There is a noticeable difference between a properly vacuumed db (nightly vacuum full) and a non-vacuumed one and people will start complaining immediately if something goes wrong there... Well, a battery-backed RAID controller with a fast cache would certainly help. http://www.lsilogic.com/products/ultra320_scsi_megaraid_storage_adapters/320x4128t.html (RAID-5 with 9 15k rpm drives; at a hindsight, perhaps we should have tried a 0+1) You'll also be glad to know that a *lot* of the improvements in the upcoming PostgreSQL 7.5 are aimed at giving better peformance on large, high-activity databases like yours. That's good to hear... Regards, Marinos ---(end of broadcast)--- TIP 3: 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] (partial?) indexes, LIKE and NULL
Hi, with the following table: Table public.foo Column | Type | Modifiers +--+--- t | text | Indexes: a btree (t) Shouldn't queries that use ... where t like '%something%' benefit from a when t is NULL in almost all cases, since the query planner could use a to access the few non-NULL rows quickly? It doesn't seem to work right now. (I assume that it would make no difference if the index a was partial, excluding NULLs) Regards, -mjy ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] why do optimizer parameters have to be set manually?
Tom Lane wrote: No, they are not that easy to determine. In particular I think the idea of automatically feeding back error measurements is hopeless, because you cannot tell which parameters are wrong. Isn't it just a matter of solving an equation system with n variables (n being the number of parameters), where each equation stands for the calculation of the run time of a particular query? I.e. something like this for a sequential scan over 1000 rows with e.g. 2 operators used per iteration that took 2 seconds (simplified so that the costs are actual timings and not relative costs to a base value): 1000 * sequential_scan_cost + 1000 * 2 * cpu_operator_cost = 2.0 seconds With a sufficient number of equations (not just n, since not all query plans use all the parameters) this system can be solved for the particular query mix that was used. E.g. with a second sequential scan over 2000 rows with 1 operator per iteration that took 3 seconds you can derive: sequential_scan_cost = 1ms cpu_operator_cost = 0.5ms This could probably be implemented with very little overhead compared to the actual run times of the queries. Regard, Marinos ---(end of broadcast)--- TIP 3: 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] why do optimizer parameters have to be set manually?
Hi, it seems to me that the optimizer parameters (like random_page_cost etc.) could easily be calculated and adjusted dynamically be the DB backend based on the planner's cost estimates and actual run times for different queries. Perhaps the developers could comment on that? I'm not sure how the parameters are used internally (apart from whatever EXPLAIN shows), but if cpu_operator_cost is the same for all operators, this should probably also be adjusted for individual operators (I suppose that is not as costly as ~*). As far as the static configuration is concerned, I'd be interested in other users' parameters and hardware configurations. Here's ours (for a write-intensive db that also performs many queries with regular expression matching): effective_cache_size = 100 # typically 8KB each #random_page_cost = 0.2 # units are one sequential page fetch cost random_page_cost = 3# units are one sequential page fetch cost #cpu_tuple_cost = 0.01 # (same) cpu_index_tuple_cost = 0.01 # (same) 0.1 #cpu_operator_cost = 0.0025 # (same) cpu_operator_cost = 0.025 # (same) other options: shared_buffers = 24 # 2*max_connections, min 16, typically 8KB each max_fsm_relations = 1 # min 10, fsm is free space map, ~40 bytes max_fsm_pages = 1000# min 1000, fsm is free space map, ~6 bytes #max_locks_per_transaction = 20 # min 10 wal_buffers = 128 # min 4, typically 8KB each sort_mem = 80 # min 64, size in KB vacuum_mem = 10 # min 1024, size in KB checkpoint_segments = 80# in logfile segments, min 1, 16MB each checkpoint_timeout = 300# range 30-3600, in seconds commit_delay = 10 # range 0-10, in microseconds commit_siblings = 5 # range 1-1000 12GB RAM, dual 2,80GHz Xeon, 6x 10K rpm disks in a RAID-5, Linux 2.4.23 with HT enabled. Regards, Marinos ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
