[PERFORM] Intermittent slowdowns, connection delays
Hi All, We have a Postgres 7.4.1 server running on FreeBSD 5.2. Hardware is a Dual Xeon 2.6 (HT enabled), 2 GB Memory, 3Ware SATA RAID-5 w/ 4 7200 RPM Seagate disks and gigabit Intel Server Ethernet. The server is dedicated to serving data to our web-based CMS. We have a few web servers load balanced, and we do around 1M page impressions per day. Our website is highly personalized, and we've optimized it to limit the number of queries, but we still see between 2 and 3 SELECT's (with JOIN's) and 1 UPDATE per page load, selectively more - a fair volume. The single UPDATE per page load is updating a timestamp in a small table (about 150,000 rows) with only 1 index (on the 1 field that needs to be matched). We're seeing some intermittent spikes in query time as actual connection time. I.e., during these seemingly random spikes, our debug output looks like this (times from start of HTTP request): SQL CONNECTION CREATING 'gf' 0.0015 - ESTABLISHING CONNECTION 1.7113 - CONNECTION OK SQL QUERY ID 1 COST 0.8155 ROWS 1 SQL QUERY ID 2 COST 0.5607 ROWS 14 .. etc.. (all queries taking more time than normal, see below) Refresh the page 2 seconds later, and we'll get: SQL CONNECTION CREATING 'gf' 0.0017 - ESTABLISHING CONNECTION 0.0086 - CONNECTION OK SQL QUERY ID 1 COST 0.0128 ROWS 1 SQL QUERY ID 2 COST 0.0033 ROWS 14 .. etc.. (with same queries) Indeed, during these types, it takes a moment for "psql" to connect on the command line (from the same machine using a local file socket), so it's not a network issue or a web-server issue. During these spurts, there's nothing too out of the ordinary in vmstat, systat or top. These programs show that we're not using much CPU (usually 60-80% idle), and disks usage is virtually nil. I've attached 60 seconds of "vmstat 5". Memory usage looks like this (constantly): Mem: 110M Active, 1470M Inact, 206M Wired, 61M Cache, 112M Buf, 26M Free I've cleaned up and tested query after query, and nothing is a "hog". On an idle server, every query will execute in < 0.05 sec. Perhaps some of you veterans have ideas? Thanks, Jason Coene Gotfrag eSports 585-598-6621 Phone 585-598-6633 Fax [EMAIL PROTECTED] http://www.gotfrag.com d01.gotfrag.com> vmstat 5 procs memory pagedisks faults cpu r b w avmfre flt re pi po fr sr tw0 fd0 in sy cs us sy id 0 9 5 335952 103108 625 0 0 0 319 4 0 0 5840 437 3 5 92 0 4 5 350772 90140 24534 0 0 0 2533 0 8 0 14480 45969 8 22 71 0 0 0 321016 112884 10603 0 0 0 2840 0 3 0 20300 26562 6 12 82 0 0 0 341428 99548 10823 0 0 0 1014 0 4 0 6870 4891 4 5 91 0 0 0 352064 91748 13041 0 0 0 1979 0 6 0 7430 4950 6 6 88 0 0 0 346236 96024 7562 0 0 0 2070 0 2 0 7360 2057 4 3 93 0 1 0 366876 82184 10081 0 0 0 1502 0 50 0 8280 2607 5 5 90 0 0 0 321600 112344 9724 0 0 0 3984 0 1 0 8850 3440 5 5 90 2 0 0 321200 112716 24244 0 0 0 2571 0 8 0 7940 33756 8 17 75 0 0 1 329016 107352 16676 0 0 0 2834 0 10 0 9220 44430 9 20 71 0 0 0 328620 107328 13862 0 0 0 1713 0 2 0 6160 8500 4 7 90 0 0 0 317376 114780 3798 0 0 0 1321 0 0 0 5140 1137 2 2 97 0 5 0 334724 102396 12999 0 0 0 1106 0 39 0 6720 24891 5 13 82 0 3 3 336904 102068 12886 0 0 0 2527 0 29 0 8790 18817 6 10 84 2 0 0 324008 110416 14625 0 0 0 2378 0 4 0 7450 28433 7 14 79 0 0 4 333692 104400 15440 0 0 0 1154 0 7 0 6450 31156 4 16 80 4 12 0 352328 91884 19349 0 0 0 1095 0 5 0 6230 46283 9 21 70 5 5 0 345796 95412 15790 0 0 0 1896 0 2 0 7270 50062 10 20 70 4 1 0 331440 105316 16178 0 0 0 2909 0 5 0 17280 48194 9 20 71 0 0 0 326664 108364 11869 0 0 0 1533 0 61 0 6400 11855 5 9 85 0 0 2 322980 110452 5970 0 0 0 1520 0 0 0 5940 1614 3 3 95 0 10 6 343108 97884 17571 0 0 0 1409 0 14 0 6430 33528 6 18 76 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Intermittent slowdowns, connection delays
Hi Paul, Thanks for the valuable feedback. I suspect you're correct about the serialization in some capacity, but the actual cause is eluding me. Basically, every time a registered user checks a page, the site has to authenticate them (with a query against a table with > 200,000 records). It doesn't update this table, however - it updates another table with "user stats" information (last click, last ip, etc). >From what I've seen, there doesn't seem to be any serious locking issues. It does make sense when a number of users whose information isn't in cache, it could take a bit longer - but AFAIK this shouldn't prevent other simultaneous queries. What else could cause such serialization? If I look at open locks (this is a view, info from pg tables): relname| mode | numlocks --+--+-- users| AccessShareLock |4 userstats| AccessShareLock |4 pg_statistic | AccessShareLock |2 users_ix_id | AccessShareLock |2 countries| AccessShareLock |2 comments | AccessShareLock |2 countries_ix_id | AccessShareLock |2 userstats_ix_id | AccessShareLock |2 comments_ix_parentid | AccessShareLock |2 users| RowExclusiveLock |1 filequeue_ix_id | AccessShareLock |1 pg_class | AccessShareLock |1 vopenlocks | AccessShareLock |1 pg_locks | AccessShareLock |1 userstats| RowExclusiveLock |1 filequeue| AccessShareLock |1 pg_class_oid_index | AccessShareLock |1 Also of note, executing a random "in the blue" query on our "users" table returns results very fast. While there's no doubt that caching may help, returning a row that is definitely not cached is very fast: < 0.05 sec. Top tells me that the system isn't using much memory - almost always under 100MB (of the 2GB we have). Is there a way to increase the amount of physical RAM that PG uses? It seems there's a lot of room there. Postgresql.conf has: shared_buffers = 16384 sort_mem = 8192 vacuum_mem = 8192 Also, would queries becoming serialized effect connection delays? I think there's still something else at large here... I've attached a vmstat output, while running dd. The RAID array is tw0. It does show the tw0 device getting significantly more work, numbers not seen during normal operation. Thanks, Jason Coene Gotfrag eSports 585-598-6621 Phone 585-598-6633 Fax [EMAIL PROTECTED] http://www.gotfrag.com -Original Message- From: Paul Tuckfield [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 11, 2004 7:50 PM To: Jason Coene Subject: Re: [PERFORM] Intermittent slowdowns, connection delays The things you point out suggest a heavy dependence on good cache performance (typical of OLTP mind you) Do not be fooled if a query runs in 2 seconds then the second run takes < .01 secons: the first run put it in cache the second got all cache hits :) But beyond that, in an OLTP system, and typical website backing database, "cache is king". And serialization is the devil So look for reasons why your cache performance might deteriorate during peak, (like large historical tables that users pull up dozens of scattered rows from, flooding cache) or why you may be serializing somewhere inside postgres (ex. if every page hit re-logs in, then theres probably serialization trying to spawn what must be 40 processes/sec assuming your 11hit/sec avg peaks at about 40/sec) Also: I am really surprised you see zero IO in the vmstat you sent, but I'm unfamiliar with BSD version of vmstat. AFAIR, Solaris shows cached filesystem reads as "page faults" which is rather confusing. Since you have 1500 page faults per second, yet no paging (bi bo) does thins mean the 1500 page faults are filesystem IO that pg is doing? do an objective test on an idle system by dd'ing a large file in and watching what vmstat does. On May 11, 2004, at 3:10 PM, Jason Coene wrote: > Hi All, > > We have a Postgres 7.4.1 server running on FreeBSD 5.2. Hardware is a > Dual > Xeon 2.6 (HT enabled), 2 GB Memory, 3Ware SATA RAID-5 w/ 4 7200 RPM > Seagate > disks and gigabit Intel Server Ethernet. The server is dedicated to > serving > data to our web-based CMS. > > We have a few web servers load balanced, and we do around 1M page > impressions per day. Our website is highly personalized, and we've > optimized it to limit the number of queries, but we still see between > 2 and > 3 SELECT's (with JOIN's) and 1 UPDATE per page load, selectively more > - a > fair volume. > > The single UPDATE per page l
[PERFORM] Hardware upgrade for a high-traffic database
Hi All, We're currently running Postgres 7.4.1 on FreeBSD 5.2, a Dual Xeon 2.4, 2GB ECC, 3Ware Serial ATA RAID 5 w/ 4 disks (SLOW!!). Our database is about 20GB on disk, we have some quite large tables - 2M rows with TEXT fields in a sample table, accessed constantly. We average about 4,000 - 5,000 queries per second - all from web traffic. As you can imagine, we're quite disk limited and checkpoints can be killer. Additionally, we see queries and connections getting serialized due to queries that take a long time (5 sec or so) while waiting on disk access. No fun at all. We've tweaked everything long and hard, and at the end of the day, the disk is killing us. We're looking to upgrade our server - or rather, replace it as it has no upgrade path to SCSI. I'm considering going Opteron (though right now we don't need more CPU time), and am looking for suggestions on what an optimal RAID configuration may look like (disks, controller, cache setting). We're in the market to buy right now - any good vendor suggestions? I'd appreciate any input, thanks! Jason ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Hardware upgrade for a high-traffic database
Hi Rod, Actually, we're already using a substantial caching system in code for nearly all pages delivered - we've exhausted that option. Our system uses a login/session table for about 1/8 of our page views (those visitors who are logged in), and has tracking features. While I'd love to scrap them and give the database server a vacation, it's a requirement for us. You're correct about the query caching (stored in memory) being used - most of our queries are run once and then come from memory (or, based on speed of consecutive executions, that seems to be the case). Once a user hits a page for the first time in an hour or so, it seems to cache their session query. The issue that I think we're seeing is that the performance on the 3Ware RAID is quite bad, watching FreeBSD systat will show it at "100% busy" at around "3.5 MB/s". When it needs to seek across a table (for, say, an aggregate function - typically a COUNT()), it slows the entire server down while working on the disk. Additionally, VACUUM's make the server practically useless. We have indexes on everything that's used in queries, and the planner is using them. The server has 2GB of physical memory, however it's only uses between 130MB and 200MB of it. Postgres is the only application running on the server. Our pertinent settings look like this: max_connections = 512 shared_buffers = 2 sort_mem = 2000 vacuum_mem = 2 effective_cache_size = 30 fsync = false wal_sync_method = fsync wal_buffers = 32 checkpoint_segments = 2 checkpoint_timeout = 30 commit_delay = 1 Typically, we don't use anywhere near the 512 connections - however there are peak hours where we come close, and other times that we eclipse it and run out (should some connections become serialized due to a slowdown). It's not something that we can comfortably lower. The non-standard checkpoint settings have helped making it less likely that a large (in disk time) query will conflict with a checkpoint write. I'm a programmer - definitely not a DBA by any stretch - though I am forced into the role. From reading this list, it seems to me that our settings are reasonable given our usage, and that a disk upgrade is likely in order. I'd love to hear any suggestions. Thanks, Jason -Original Message- From: Rod Taylor [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 10, 2004 7:07 PM To: Jason Coene Cc: Postgresql Performance Subject: Re: [PERFORM] Hardware upgrade for a high-traffic database > Our database is about 20GB on disk, we have some quite large tables - 2M > rows with TEXT fields in a sample table, accessed constantly. We average > about 4,000 - 5,000 queries per second - all from web traffic. As you can 99% is reads? and probably the same data over and over again? You might want to think about a small code change to cache sections of page output in memory for the most commonly generated pages (there are usually 3 or 4 that account for 25% to 50% of web traffic -- starting pages). The fact you're getting 5k queries/second off IDE drives tells me most of the active data is in memory -- so your actual working data set is probably quite small (less than 10% of the 20GB). If the above is all true (mostly reads, smallish dataset, etc.) and the database is not growing very quickly, you might want to look into RAM and RAM bandwidth over disk. An Opteron with 8GB ram using the same old IDE drives. Get a mobo with a SCSI raid controller in it, so the disk component can be upgraded in the future (when necessary). ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Hardware upgrade for a high-traffic database
Thanks for all the feedback. To clear it up, we are definitely not CPU bound at the moment. Any slowdown seems to be disk dependant, or from to serialization due to a long query (due to disk). We do have a lot of INSERT/UPDATE calls, specifically on tables that track user sessions, then of course things like comments, etc (where we'll see 10-30 INSERT's per second, with TEXT field, and hundreds of reads per second). Additionally, our system does use a lot of aggregate functions. I'll look into materialized views, it sounds like it may be worth implementing. One question I do have though - you specifically mentioned NOW() as something to watch out for, in that it's mutable. We typically use COUNT() as a subselect to retrieve the number of associated rows to the current query. Additionally, we use NOW a lot, primarily to detect the status of a date, i.e.: SELECT id FROM subscriptions WHERE userid = 1 AND timeend > NOW(); Is there a better way to do this? I was under the impression that NOW() was pretty harmless, just to return a current timestamp. Based on feedback, I'm looking at a minor upgrade of our RAID controller to a 3ware 9000 series (SATA with cache, battery backup optional), and re-configuring it for RAID 10. It's a damn cheap upgrade at around $350 and an hour of downtime, so I figure that it's worth it for us to give it a shot. Thanks, Jason -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Merlin Moncure Sent: Wednesday, August 11, 2004 1:04 PM To: [EMAIL PROTECTED] Cc: Postgresql Performance; [EMAIL PROTECTED] Subject: Re: [PERFORM] Hardware upgrade for a high-traffic database > On Wed, 2004-08-11 at 17:51, Merlin Moncure wrote: > > > One thing you might consider is materialized views. Your aggregate > > functions are killing you...try to avoid using them (except min/max on > > an index). Just watch out for mutable functions like now(). > > > > http://www.varlena.com/varlena/GeneralBits/Tidbits/matviews.html > > > > An application specific approach is to use triggers to keep the data you > > need in as close to query form as possible...you can reap enormous > > savings particularly if your queries involve 3 or more tables or have > > large aggregate scans. > > I thought materialized views support in pgsql was experimental as yet. > Are the pg mat-view code upto production servers? Also, do you have to > delete mat-views before you dump the db or does dump automatically not > dump the mat-views data? I think you are thinking about 100% 'true' materialized views. In that case the answer is no, the server does not have them. The GeneralBits article describes how to emulate them through pl/sql triggers. I just bumped into the article yesterday and was very impressed by it...I have to admin though Note: I have never tried the method, but it should work. I cc'd the author who perhaps might chime in and tell you more about them. Materialized views can give performance savings so good that the tpc people had to ban them from benchmarks because they skewed results...:) In postgres, they can help a lot with aggregates...there are many gotchas tho, for example keeping a count() up to date can get kind of tricky. If you can get them to work, the filesystem cache efficiency will rocket upwards...YMMV. Getting back on topic, I missed the original post where the author stated his problems were i/o related, not cpu (contrary to my speculation). I wonder what his insert/update load is? Merlin ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] Hardware upgrade for a high-traffic database
> > Right. The point is: is your i/o bottle neck on the read side or the > write side. With 10-30 inserts/sec and fsync off, it's definitely on > the read side. What's interesting is that such a low insert load is > causing i/o storm problems. How does your app run with fsync on? > > With read-bound i/o problems, might want to consider upgrading memory > first to get better cache efficiency. You may want to consider Opteron > for > 4GB allocations (yummy!). > > The good news is that read problems are usually solvable by being > clever, whereas write problems require hardware. > The difference with fsync being off makes seems to be that it allows the server to write in groups instead of scattering our INSERT/UPDATE calls all over - it helps keep things going. When a checkpoint occurs, reads slow down there. Normal reads are usually quite fast, aside from some reads. A good example, a comments table where users submit TEXT data. A common query is to find the last 5 comments a user has submitted. The scan, while using an index, takes a considerable amount of time (> 0.5 sec is about as good as it gets). Again, it's using an index on the single WHERE clause (userid = int). The field that's used to ORDER BY (timestamp) is also indexed. I'm wondering why our PG server is using so little memory... The system has 2GB of memory, though only around 200MB of it are used. Is there a PG setting to force more memory usage towards the cache? Additionally, we use FreeBSD. I've heard that Linux may manage that memory better, any truth there? Sorry if I'm grabbing at straws here :) > > One question I do have though - you specifically mentioned NOW() as > > something to watch out for, in that it's mutable. We typically use > > This is specifically with regards to materialized views. Mutable > functions cause problems because when they are pushed unto the view, > they are refreshed...something to watch out for. > > The trick with MVs is to increase your filesystem cache efficiency. The > big picture is to keep frequently read data in a single place to make > better benefit of cache. Aggregates naturally read multiple rows to > return a single row's worth of data so you want to target them first. > This all comes at a cost of update I/O time and some application > complexity. > > > as a subselect to retrieve the number of associated rows to the > current > > query. Additionally, we use NOW a lot, primarily to detect the status > of > > a > > date, i.e.: > > Might want to check if your application middleware (php?) exposes > PQntuples()...this is a zero cost way to get the same information. > Thanks, I'll look into it. We use C and PHP. > > Based on feedback, I'm looking at a minor upgrade of our RAID > controller > > to > > a 3ware 9000 series (SATA with cache, battery backup optional), and > > re-configuring it for RAID 10. It's a damn cheap upgrade at around > $350 > > and > > an hour of downtime, so I figure that it's worth it for us to give it > a > > shot. > > p.s. you can also increase cache efficiency by reducing database size, > for example use int2/int4 vs. numerics. > I've gone through and optimized data types as much as possible. I'll see what else we can do w/o causing downtime once PG 8 is ready to go and we can change data types on the fly. Thanks, Jason ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Hardware upgrade for a high-traffic database
> -Original Message-
> From: Rod Taylor [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, August 11, 2004 5:46 PM
> To: Jason Coene
> Cc: 'Merlin Moncure'; Postgresql Performance
> Subject: Re: [PERFORM] Hardware upgrade for a high-traffic database
>
> > I'm wondering why our PG server is using so little memory... The system
> has
> > 2GB of memory, though only around 200MB of it are used. Is there a PG
>
> This is the second time you've said this. Surely you're not implying
> there is 1.8GB Free Memory -- rather than 1.8GB in Buffers or Cache.
Hi Rod,
I was looking at top and vmstat - which always show under 300MB "Active".
We may hit 400MB at peak. Everything I see (though this isn't my area of
expertise) points to most of the memory simply being unused. Results below,
am I missing something?
Jason
>
> Send output of the below:
>
> sysctl vm
d01> sysctl vm
vm.vmtotal:
System wide totals computed every five seconds: (values in kilobytes)
===
Processes: (RUNQ: 1 Disk Wait: 0 Page Wait: 0 Sleep: 149)
Virtual Memory: (Total: 2101614K, Active 440212K)
Real Memory:(Total: 2023532K Active 327032K)
Shared Virtual Memory: (Total: 14356K Active: 3788K)
Shared Real Memory: (Total: 4236K Active: 2456K)
Free Memory Pages: 88824K
vm.loadavg: { 0.46 0.41 0.42 }
vm.v_free_min: 3312
vm.v_free_target: 13997
vm.v_free_reserved: 749
vm.v_inactive_target: 20995
vm.v_cache_min: 13997
vm.v_cache_max: 27994
vm.v_pageout_free_min: 34
vm.pageout_algorithm: 0
vm.swap_enabled: 1
vm.swap_async_max: 4
vm.dmmax: 32
vm.nswapdev: 1
vm.swap_idle_threshold1: 2
vm.swap_idle_threshold2: 10
vm.v_free_severe: 2030
vm.stats.sys.v_swtch: 627853362
vm.stats.sys.v_trap: 3622664114
vm.stats.sys.v_syscall: 1638589210
vm.stats.sys.v_intr: 3250875036
vm.stats.sys.v_soft: 1930666043
vm.stats.vm.v_vm_faults: 3197534554
vm.stats.vm.v_cow_faults: 2999625102
vm.stats.vm.v_cow_optim: 10093309
vm.stats.vm.v_zfod: 3603956919
vm.stats.vm.v_ozfod: 3104475907
vm.stats.vm.v_swapin: 3353
vm.stats.vm.v_swapout: 3382
vm.stats.vm.v_swappgsin: 3792
vm.stats.vm.v_swappgsout: 7213
vm.stats.vm.v_vnodein: 14675
vm.stats.vm.v_vnodeout: 140671
vm.stats.vm.v_vnodepgsin: 24330
vm.stats.vm.v_vnodepgsout: 245840
vm.stats.vm.v_intrans: 3643
vm.stats.vm.v_reactivated: 35038
vm.stats.vm.v_pdwakeups: 26984
vm.stats.vm.v_pdpages: 335769007
vm.stats.vm.v_dfree: 8
vm.stats.vm.v_pfree: 1507856856
vm.stats.vm.v_tfree: 430723755
vm.stats.vm.v_page_size: 4096
vm.stats.vm.v_page_count: 512831
vm.stats.vm.v_free_reserved: 749
vm.stats.vm.v_free_target: 13997
vm.stats.vm.v_free_min: 3312
vm.stats.vm.v_free_count: 968
vm.stats.vm.v_wire_count: 62039
vm.stats.vm.v_active_count: 44233
vm.stats.vm.v_inactive_target: 20995
vm.stats.vm.v_inactive_count: 343621
vm.stats.vm.v_cache_count: 21237
vm.stats.vm.v_cache_min: 13997
vm.stats.vm.v_cache_max: 27994
vm.stats.vm.v_pageout_free_min: 34
vm.stats.vm.v_interrupt_free_min: 2
vm.stats.vm.v_forks: 45205536
vm.stats.vm.v_vforks: 74315
vm.stats.vm.v_rforks: 0
vm.stats.vm.v_kthreads: 2416
vm.stats.vm.v_forkpages: 1464383994
vm.stats.vm.v_vforkpages: 4259727
vm.stats.vm.v_rforkpages: 0
vm.stats.vm.v_kthreadpages: 0
vm.stats.misc.zero_page_count: 709
vm.stats.misc.cnt_prezero: -972664922
vm.max_proc_mmap: 34952
vm.msync_flush_flags: 3
vm.idlezero_enable: 1
vm.idlezero_maxrun: 16
vm.max_launder: 32
vm.pageout_stats_max: 13997
vm.pageout_full_stats_interval: 20
vm.pageout_stats_interval: 5
vm.pageout_stats_free_max: 5
vm.swap_idle_enabled: 0
vm.defer_swapspace_pageouts: 0
vm.disable_swapspace_pageouts: 0
vm.pageout_lock_miss: 0
vm.zone:
ITEMSIZE LIMIT USEDFREE REQUESTS
FFS2 dinode: 256,0, 30156, 4389, 20093512
FFS1 dinode: 128,0, 0, 0,0
FFS inode: 140,0, 30156, 4340, 20093512
SWAPMETA:276, 121576, 16,264,44599
ripcb: 180,32780, 0,132, 289
hostcache:88,15390, 6,309, 741
syncache:104,15390, 0,418, 44592418
tcptw:56, 6603, 3, 1204, 224900
tcpcb: 368,32769,136, 4264, 44594153
inpcb: 180,32780,139, 4437, 44594153
udpcb: 180,32780, 10,144,85953
unpcb: 140,32788, 6,246, 143982
socket: 240,32768,152, 4248, 44824378
KNOTE:64,0, 0,434, 7561
PIPE:172,0, 8,222, 352848
NFSNODE: 460,0, 1596, 92, 2419
NFSMOUNT:424,0, 1, 17,1
DIRHASH:1024,0,238, 86, 287
L VFS Cache: 291,0,165,160,11956
S VFS Cache: 68,0, 38283, 3430, 3795133
NAMEI: 1024,0,
Re: [PERFORM] Hardware upgrade for a high-traffic database
> > Hi Rod, > > > > I was looking at top and vmstat - which always show under 300MB > "Active". > > We may hit 400MB at peak. Everything I see (though this isn't my area > of > > expertise) points to most of the memory simply being unused. Results > below, > > am I missing something? > > This looks fine. The memory is not unused (only 5MB is actually empty) > but is being used for disk cache. > > Active is memory used by programs and would need to be swapped if this > space was needed. > > Inactive is memory that is generally dirty. Disk cache is often here. In > your case, you likely write to the same pages you're reading from -- > which is why this number is so big. It also explains why a checkpoint is > a killer; a large chunk of this memory set needs to be pushed to disk. > > Cache is memory used generally for disk cache that is not dirty. It's > been read from the disk and could be cleared immediately if necessary. > > Wired is memory that cannot be swapped. In your case, Shared Memory is > probably Wired (this is good). There is another sysctl to check and set > whether it is wired or swappable. > > > > Interesting (if dry) read: > http://www.freebsd.org/doc/en_US.ISO8859-1/articles/vm-design/index.html > Ah, thanks - I didn't know that Inactive was still being used. I'm glad to know that at least the OS is using up the free memory for disk cache. Shared memory is Wired, set via sysctl. Thanks for the info! It sounds like adding more memory would help cache more data - I'll look into the upgrade. Jason ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Hardware upgrade for a high-traffic database
> You mean you are doing > SELECT ... WHERE userid = 42 ORDER BY timestamp DESC LIMIT 5; > and hoping that separate indexes on userid and timestamp will get the > job done? They won't. There are only two possible plans for this, > neither very good: select all of user 42's posts and sort them, or > scan timewise backwards through *all* posts looking for the last 5 from > user 42. Wow! I did try the method you state below (including the WHERE restricted column in the sort by, and creating a two-column index), and it did execute much faster (even on odd userid's to avoid cached results as much as possible). We have a lot of: SELECT whatever FROM ourtable WHERE field1 = X AND field2 = Y AND field3 = Z ORDER BY id DESC LIMIT 5 With indexes: ourtable(id) ourtable(field1, field2, field3) Is it standard procedure with postgres to include any fields listed in WHERE in the ORDER BY, and create a single index for only the ORDER BY fields (in order of appearance, of course)? > > If you do this enough to justify a specialized index, I would suggest a > two-column index on (userid, timestamp). You will also need to tweak > the query, because the planner is not quite smart enough to deduce that > such an index is applicable to the given sort order: > SELECT ... WHERE userid = 42 ORDER BY userid DESC, timestamp DESC > LIMIT 5; > This should generate an index-scan-backwards plan that will execute nigh > instantaneously, because it will only fetch the rows you really want. > > You might or might not be able to drop the separate indexes on userid > and timestamp, depending on what other queries you might have that need > them. But you should be paying attention to what plans you are really > getting (see EXPLAIN) rather than just assuming that some indexes chosen > at random will do what you need. > > regards, tom lane > We do many varied queries on nearly every table - our data is highly relational, and we have a lot of indexes. I thought the planner would pick up the right index via constraints and not require them in ORDER BY... EXPLAIN ANALYZE says that the indexes are being used, ala: gf=# EXPLAIN ANALYZE SELECT id FROM comments WHERE userid = 51 ORDER BY timestamp DESC LIMIT 5; QUERY PLAN --- Limit (cost=1608.43..1608.45 rows=5 width=8) (actual time=0.292..0.317 rows=5 loops=1) -> Sort (cost=1608.43..1609.45 rows=407 width=8) (actual time=0.287..0.295 rows=5 loops=1) Sort Key: "timestamp" -> Index Scan using comments_ix_userid on comments (cost=0.00..1590.79 rows=407 width=8) (actual time=0.031..0.190 rows=35 loops=1) Index Cond: (userid = 51) Total runtime: 0.375 ms (6 rows) Is this the wrong procedure? Your suggested syntax seems much more efficient, but I don't quite understand exactly why, as PG is using our existing indexes... gf=# EXPLAIN ANALYZE SELECT id FROM comments WHERE userid = 51 ORDER BY userid DESC, timestamp DESC LIMIT 5; QUERY PLAN Limit (cost=0.00..19.90 rows=5 width=12) (actual time=0.040..0.076 rows=5 loops=1) -> Index Scan Backward using comments_ix_userid_timestamp on comments (cost=0.00..1620.25 rows=407 width=12) (actual time=0.035..0.054 rows=5 loops=1) Index Cond: (userid = 51) Total runtime: 0.134 ms (4 rows) Note: This was done after adding an index on comments (userid, timestamp) Regards, Jason ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] indexes make other queries slow!
> My guess is that all the queries that involves the columns that are > being indexed need to > be rewritten to use the newly created indexes to avoid the performance > issues. The reason > is that REINDEX does not help either. Does it make sense? > Qing, Generally, adding new indexes blindly will hurt performance, not help it. More indexes mean more work during INSERT/UPDATE. That could easily be hampering your performance if you have a high INSERT/UPDATE volume. Run your queries through EXPLAIN ANALYZE to make sure they're using the right indexes. Take a look at the pg_stat_user_indexes table to see what indexes are simply not being used. Jason ---(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] Caching of Queries
I'm not an expert, but I've been hunting down a killer performance problem for a while now. It seems this may be the cause. At peak load, our database slows to a trickle. The CPU and disk utilization are normal - 20-30% used CPU and disk performance good. All of our "postgres" processes end up in the "semwai" state - seemingly waiting on other queries to complete. If the system isn't taxed in CPU or disk, I have a good feeling that this may be the cause. I didn't know that planning queries could create such a gridlock, but based on Mr Pink's explanation, it sounds like a very real possibility. We're running on SELECT's, and the number of locks on our "high traffic" tables grows to the hundreds. If it's not the SELECT locking (and we don't get that many INSERT/UPDATE on these tables), could the planner be doing it? At peak load (~ 1000 queries/sec on highest traffic table, all very similar), the serialized queries pile up and essentially create a DoS on our service - requiring a restart of the PG daemon. Upon stop & start, it's back to normal. I've looked at PREPARE, but apparently it only lasts per-session - that's worthless in our case (web based service, one connection per data-requiring connection). Does this sound plausible? Is there an alternative way to do this that I don't know about? Additionally, in our case, I personally don't see any downside to caching and using the same query plan when the only thing substituted are variables. In fact, I'd imagine it would help performance significantly in high-volume web applications. Thanks, Jason > -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-performance- > [EMAIL PROTECTED] On Behalf Of Mr Pink > Sent: Thursday, September 23, 2004 11:29 AM > To: Scott Kirkwood; [EMAIL PROTECTED] > Subject: Re: [PERFORM] Caching of Queries > > Not knowing anything about the internals of pg, I don't know how this > relates, but in theory, > query plan caching is not just about saving time re-planning queries, it's > about scalability. > Optimizing queries requires shared locks on the database metadata, which, > as I understand it > causes contention and serialization, which kills scalability. > > I read this thread from last to first, and I'm not sure if I missed > something, but if pg isnt > caching plans, then I would say plan caching should be a top priority for > future enhancements. It > needn't be complex either: if the SQL string is the same, and none of the > tables involved in the > query have changed (in structure), then re-use the cached plan. Basically, > DDL and updated > statistics would have to invalidate plans for affected tables. > > Preferably, it should work equally for prepared statements and those not > pre-prepared. If you're > not using prepare (and bind variables) though, your plan caching down the > drain anyway... > > I don't think that re-optimizing based on values of bind variables is > needed. It seems like it > could actually be counter-productive and difficult to asses it's impact. > > That's the way I see it anyway. > > :) > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [PERFORM] Caching of Queries
Hi Tom, Easily recreated with Apache benchmark, "ab -n 3 -c 3000 http://webserver ". This runs 1 query per page, everything else is cached on webserver. The lone query: SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0 ORDER BY nuked DESC, lastpost DESC LIMIT 8 Limit (cost=0.00..1.99 rows=8 width=39) (actual time=27.865..28.027 rows=8 loops=1) -> Index Scan Backward using threads_ix_nuked_lastpost on threads (cost=0.0 0..16824.36 rows=67511 width=39) (actual time=27.856..27.989 rows=8 loops=1) Filter: (nuked = 0) Total runtime: 28.175 ms I'm not sure how I go about getting the stack traceback you need. Any info on this? Results of "ps" below. System is dual xeon 2.6, 2gb ram, hardware raid 10 running FreeBSD 5.2.1. Jason last pid: 96094; load averages: 0.22, 0.35, 0.38 up 19+20:50:37 13:10:45 161 processes: 2 running, 151 sleeping, 8 lock CPU states: 12.2% user, 0.0% nice, 16.9% system, 1.6% interrupt, 69.4% idle Mem: 120M Active, 1544M Inact, 194M Wired, 62M Cache, 112M Buf, 2996K Free Swap: 4096M Total, 4096M Free PID USERNAME PRI NICE SIZERES STATE C TIME WCPUCPU COMMAND 50557 pgsql 980 95276K 4860K select 0 24:00 0.59% 0.59% postgres 95969 pgsql 40 96048K 34272K sbwait 0 0:00 2.10% 0.29% postgres 95977 pgsql -40 96048K 29620K semwai 2 0:00 1.40% 0.20% postgres 96017 pgsql 40 96048K 34280K sbwait 0 0:00 2.05% 0.20% postgres 95976 pgsql -40 96048K 30564K semwai 3 0:00 1.05% 0.15% postgres 95970 pgsql -40 96048K 24404K semwai 1 0:00 1.05% 0.15% postgres 95972 pgsql -40 96048K 21060K semwai 1 0:00 1.05% 0.15% postgres 96053 pgsql -40 96048K 24140K semwai 3 0:00 1.54% 0.15% postgres 96024 pgsql -40 96048K 22192K semwai 3 0:00 1.54% 0.15% postgres 95985 pgsql -40 96048K 15208K semwai 3 0:00 1.54% 0.15% postgres 96033 pgsql 980 95992K 7812K *Giant 2 0:00 1.54% 0.15% postgres 95973 pgsql -40 96048K 30936K semwai 3 0:00 0.70% 0.10% postgres 95966 pgsql 40 96048K 34272K sbwait 0 0:00 0.70% 0.10% postgres 95983 pgsql 40 96048K 34272K sbwait 2 0:00 1.03% 0.10% postgres 95962 pgsql 40 96048K 34268K sbwait 2 0:00 0.70% 0.10% postgres 95968 pgsql -40 96048K 26232K semwai 2 0:00 0.70% 0.10% postgres 95959 pgsql 40 96048K 34268K sbwait 2 0:00 0.70% 0.10% postgres > -Original Message- > From: Tom Lane [mailto:[EMAIL PROTECTED] > Sent: Thursday, September 23, 2004 1:06 PM > To: Jason Coene > Cc: 'Mr Pink'; 'Scott Kirkwood'; [EMAIL PROTECTED] > Subject: Re: [PERFORM] Caching of Queries > > "Jason Coene" <[EMAIL PROTECTED]> writes: > > All of our "postgres" processes end up in the "semwai" state - seemingly > > waiting on other queries to complete. If the system isn't taxed in CPU > or > > disk, I have a good feeling that this may be the cause. > > Whatever that is, I'll bet lunch that it's got 0 to do with caching > query plans. Can you get stack tracebacks from some of the stuck > processes? What do they show in "ps"? > > regards, tom lane ---(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] Caching of Queries
Hi All, It does sound like we should be pooling connections somehow. I'll be looking at implementing that shortly. I'd really like to understand what the actual problem is, though. Sorry, I meant 30,000 with 300 connections - not 3,000. The 300 connections / second is realistic, if not underestimated. As is the nature of our site (realtime information about online gaming), there's a huge fan base and as a big upset happens, we'll do 50,000 page views in a span of 3-5 minutes. I get the same results with: ab -n 1 -c 150 http://www.gotfrag.com/portal/news/ I've attached results from the above test, showing open locks, top output, and vmstat 5. Tom, I've run the test described in: http://archives.postgresql.org/pgsql-performance/2004-04/msg00280.php Results attached in mptest.txt. The box did experience the same problems as we've seen before. I ran it under a separate database (test), and it still caused our other queries to slow significantly from our production database (gf) - semwait again. It does look like the "cs" column under CPU (which I'd assume is Context Swap) does bump up significantly (10x or better) during both my ab test, and the test you suggested in that archived message. Reading the first thread you pointed out (2004-04/msg00249.php), Josh Berkus was questioning the ServerWorks chipsets. We're running on the Intel E7501 Chipset (MSI board). Our CPU's are 2.66 GHz with 533MHz FSB, Hyperthreading enabled. Unfortunately, I don't have physical access to the machine to turn HT off. Thanks, Jason > -Original Message- > From: Gaetano Mendola [mailto:[EMAIL PROTECTED] > Sent: Thursday, September 23, 2004 1:41 PM > To: Jason Coene > Subject: Re: Caching of Queries > > Jason Coene wrote: > > Hi Tom, > > > > Easily recreated with Apache benchmark, "ab -n 3 -c 3000 > > http://webserver ". This runs 1 query per page, everything else is > cached > > on webserver. > > That test require 3 access with 3000 connections that is not a normal > load. Describe us your HW. > > 3000 connections means a very huge load, may you provide also the result > of > "vmstat 5" my webserver trash already with -c 120 ! > > how many connection your postgres can manage ? > > You have to consider to use a connection pool with that ammount of > connections. > > > Regards > Gaetano Mendola gf=# SELECT r.relname, l."mode", count(*) AS numlocks FROM pg_locks l, pg_class r WHERE r.oid = l.relation GROUP BY r.relname, l."mode" ORDER BY count(*) DESC; relname | mode | numlocks --+-+-- threads | AccessShareLock | 63 threads_ix_nuked_lastpost| AccessShareLock | 47 threads_ix_nuked_gameid_lastpost | AccessShareLock |7 pg_class | AccessShareLock |5 pg_opclass_am_name_nsp_index | AccessShareLock |3 pg_opclass | AccessShareLock |3 pg_class_oid_index | AccessShareLock |3 pg_type | AccessShareLock |2 pg_statistic | AccessShareLock |2 pg_attribute | AccessShareLock |2 pg_amop_opc_strategy_index | AccessShareLock |2 pg_attrdef | AccessShareLock |2 pg_trigger_tgrelid_tgname_index | AccessShareLock |2 pg_trigger | AccessShareLock |2 users| AccessShareLock |2 pg_statistic_relid_att_index | AccessShareLock |2 pg_type_oid_index| AccessShareLock |2 pg_amop | AccessShareLock |2 pg_attribute_relid_attnum_index | AccessShareLock |2 comments | AccessShareLock |2 pg_shadow| AccessShareLock |2 acls | AccessShareLock |1 pg_index_indexrelid_index| AccessShareLock |1 pg_attrdef_adrelid_adnum_index | AccessShareLock |1 surveyresults_ix_userid | AccessShareLock |1 pg_cast | AccessShareLock |1 pg_shadow_usesysid_index | AccessShareLock |1 pg_index | AccessShareLock |1 games| AccessShareLock |1 usersessions | AccessShareLock |1 surveyoptions| AccessShareLock |1 countries| AccessShareLock |1 surveyresults| AccessShareLock |1 vopenlocks | AccessShareLock |
Re: [PERFORM] Caching of Queries
Update: I just tried running the same test (ab with 150 concurrent connections) while connecting to postgres through 35 persistent connections (PHP library), and had roughly the same type of results. This should eliminate the "new connection" overhead. I've attached top and vmstat. I let it run until it had completed 800 requests. Unless I'm missing something, there's more than the "new connection" IO load here. Jason > -----Original Message- > From: Jason Coene [mailto:[EMAIL PROTECTED] > Sent: Thursday, September 23, 2004 3:08 PM > To: [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: RE: Caching of Queries > > Hi All, > > It does sound like we should be pooling connections somehow. I'll be > looking at implementing that shortly. I'd really like to understand what > the actual problem is, though. > > Sorry, I meant 30,000 with 300 connections - not 3,000. The 300 > connections > / second is realistic, if not underestimated. As is the nature of our > site > (realtime information about online gaming), there's a huge fan base and as > a > big upset happens, we'll do 50,000 page views in a span of 3-5 minutes. > > I get the same results with: > > ab -n 1 -c 150 http://www.gotfrag.com/portal/news/ > > I've attached results from the above test, showing open locks, top output, > and vmstat 5. > > Tom, I've run the test described in: > > http://archives.postgresql.org/pgsql-performance/2004-04/msg00280.php > > Results attached in mptest.txt. The box did experience the same problems > as > we've seen before. I ran it under a separate database (test), and it > still > caused our other queries to slow significantly from our production > database > (gf) - semwait again. > > It does look like the "cs" column under CPU (which I'd assume is Context > Swap) does bump up significantly (10x or better) during both my ab test, > and > the test you suggested in that archived message. > > Reading the first thread you pointed out (2004-04/msg00249.php), Josh > Berkus > was questioning the ServerWorks chipsets. We're running on the Intel > E7501 > Chipset (MSI board). Our CPU's are 2.66 GHz with 533MHz FSB, > Hyperthreading > enabled. Unfortunately, I don't have physical access to the machine to > turn > HT off. > > > Thanks, > > Jason > > > > > -Original Message- > > From: Gaetano Mendola [mailto:[EMAIL PROTECTED] > > Sent: Thursday, September 23, 2004 1:41 PM > > To: Jason Coene > > Subject: Re: Caching of Queries > > > > Jason Coene wrote: > > > Hi Tom, > > > > > > Easily recreated with Apache benchmark, "ab -n 3 -c 3000 > > > http://webserver ". This runs 1 query per page, everything else is > > cached > > > on webserver. > > > > That test require 3 access with 3000 connections that is not a > normal > > load. Describe us your HW. > > > > 3000 connections means a very huge load, may you provide also the result > > of > > "vmstat 5" my webserver trash already with -c 120 ! > > > > how many connection your postgres can manage ? > > > > You have to consider to use a connection pool with that ammount of > > connections. > > > > > > Regards > > Gaetano Mendola last pid: 48239; load averages: 5.83, 2.43, 1.50 up 19+22:59:04 15:19:12 127 processes: 16 running, 111 sleeping CPU states: 17.7% user, 0.0% nice, 20.0% system, 1.0% interrupt, 61.3% idle Mem: 125M Active, 1456M Inact, 193M Wired, 96M Cache, 112M Buf, 54M Free Swap: 4096M Total, 4096M Free PID USERNAME PRI NICE SIZERES STATE C TIME WCPUCPU COMMAND 48190 pgsql -40 97408K 86416K semwai 1 0:01 3.35% 1.32% postgres 47761 pgsql -40 96816K 56708K semwai 2 0:01 0.90% 0.88% postgres 47765 pgsql 40 96816K 56708K sbwait 3 0:01 0.90% 0.88% postgres 47754 pgsql -40 96816K 56708K semwai 2 0:01 0.85% 0.83% postgres 47763 pgsql -40 96816K 56708K semwai 0 0:01 0.85% 0.83% postgres 47741 pgsql 40 96816K 56708K sbwait 3 0:01 0.75% 0.73% postgres 47674 pgsql -40 96264K 38992K semwai 1 0:01 0.74% 0.73% postgres 47753 pgsql -40 96816K 56708K semwai 1 0:00 0.65% 0.63% postgres 48204 pgsql -40 96856K 46752K semwai 0 0:00 2.15% 0.63% postgres 47698 pgsql 40 96240K 37792K sbwait 3 0:01 0.59% 0.59% postgres 47757 pgsql -40 96816K 56708K semwai 3 0:01 0.60% 0.59% postgres 47740 pgsql 40 96240K 37768K sbwait 0 0:01 0.55% 0.54% postgres 4
Re: [PERFORM] Caching of Queries (now with pgpool)
Hi Josh,
I just tried using pgpool to pool the connections, and ran:
ab -n 1000 -c 50 http://wstg.int/portal/news/
I ran some previous queries to get pgpool to pre-establish all the
connections, and ab ran for a few minutes (with one query per page, eek!).
It was still exhibiting the same problems as before. While so many new
connections at once can surely make the problem worse (and pgpool will
surely help there), shouldn't this prove that it's not the only issue?
We're running FreeBSD 5.2.1
I've attached open locks, running queries, query plans, top output and
vmstat 5 output for while ab was running, from start to finish.
Any ideas?
Jason
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-performance-
> [EMAIL PROTECTED] On Behalf Of Josh Berkus
> Sent: Thursday, September 23, 2004 8:06 PM
> To: Jason Coene; [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: Re: [PERFORM] Caching of Queries
>
> Jason,
>
> > Sorry, I meant 30,000 with 300 connections - not 3,000. The 300
> > connections
> > / second is realistic, if not underestimated. As is the nature of
> > our site
> > (realtime information about online gaming), there's a huge fan base
> > and as a
> > big upset happens, we'll do 50,000 page views in a span of 3-5
> > minutes.
>
> First, your posts show no evidences of the CS storm bug.
>
> Second, 300 *new* connections a second is a lot. Each new connection
> requires a significant amount of both database and OS overhead. This
> is why all the other web developers use a connection pool.
>
> In fact, I wouldn't be surprised if your lockups are on the OS level,
> even; I don't recall that you cited what OS you're using, but I can
> imagine locking up Linux 2.4 trying to spawn 300 new processes a
> second.
>
> --Josh
>
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
OPEN LOCKS:
gf=# SELECT r.relname, l."mode", count(*) AS numlocks
FROM pg_locks l, pg_class r
WHERE r.oid = l.relation
GROUP BY r.relname, l."mode"
ORDER BY count(*) DESC;
relname | mode | numlocks
--+
threads | AccessShareLock | 43
threads_ix_nuked_lastpost| AccessShareLock | 35
threads_ix_nuked_gameid_lastpost | AccessShareLock |7
pg_attribute | AccessShareLock |1
v_locks | AccessShareLock |1
pg_class | AccessShareLock |1
usersessions | AccessShareLock |1
countries| AccessShareLock |1
users| AccessShareLock |1
userstats_ix_id | AccessShareLock |1
pg_statistic_relid_att_index | AccessShareLock |1
pg_attribute_relid_attnum_index | AccessShareLock |1
userstats| AccessShareLock |1
demos| AccessShareLock |1
pg_cast_source_target_index | AccessShareLock |1
pg_locks | AccessShareLock |1
users_ix_id | AccessShareLock |1
buddies | AccessShareLock |1
buddies_ix_userid| AccessShareLock |1
pg_cast | AccessShareLock |1
pg_statistic | AccessShareLock |1
(21 rows)
RUNNING QUERIES (AND HOW LONG FOR):
gf=# SELECT pg_stat_activity.usename, round(date_part('epoch'::text, now() -
pg_stat_activity.query_start)) AS duration, pg_stat_activity.current_query
FROM pg_stat_activity
ORDER BY round(date_part('epoch'::text, now() - pg_stat_activity.query_start)) DESC;
usename | duration |
current_query
-+--+---
--
gf |4 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0
AND gameid = 1 ORDER BY nuked DESC, gameid DESC, lastpost DESC LIMIT 8
gf |3 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0
ORDER BY nuked DESC, lastpost DESC LIMIT 8
gf |3 | SELECT id, gameid, forumid, subject FROM threads WHERE nuked = 0
ORDER BY nuked DESC, lastpost DESC LIMIT 8
gf |3 | SELECT id, gameid, forumid, subject FROM threads WHERE
