Re: [PERFORM] partitioning materialized views

> I don't think the downstream dependencies will let that work without > rebuilding them as well. The drop fails (without a cascade), and the other > views and matviews that are built off of this all simply point to x_old. Wow, ouch. Yeah, I'd neglected to consider dependent objects. Your only "

Re: [PERFORM] partitioning materialized views

> I'm curious if I'm overlooking other possible architectures or tools that > might make this simpler to manage. One of the issues with materialized views is that they are based on views... For a concurrent update, it essentially performs a looped merge, which can be pretty ugly. That's the price

Re: [PERFORM] Postgres not using all RAM (Huge Page activated on a 96GB RAM system)

g use of set theory within a database, for example, you might be getting 100x less throughput than you could otherwise attain. If it's not proprietary in some way, or you can obfuscate it into a test case, we can probably help then. As it stands, there isn't enough to go on. --

Re: [PERFORM] Having some problems with concurrent COPY commands

ct they're not exceptionally useful anymore. :) As a side note, using INSERT instead scales almost exactly linearly. This would be useful, except that INSERT is already at least a magnitude slower than COPY. Hah. -- Shaun Thomas bonesmo...@gmail.com http://bonesmoses.org/ -- Sent via pgsq

Re: [PERFORM] Having some problems with concurrent COPY commands

t stuck, because this seems incredibly wrong. I'll keep digging. -- Shaun Thomas bonesmo...@gmail.com http://bonesmoses.org/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Having some problems with concurrent COPY commands

'/tmp/loadtest1.csv'" &>/dev/null & psql -c "COPY test_copy2 FROM '/tmp/loadtest1.csv'" &>/dev/null & psql -c "COPY test_copy3 FROM '/tmp/loadtest1.csv'" &>/dev/null & psql -c "COPY test_copy4 FROM '/tm

[PERFORM] Having some problems with concurrent COPY commands

e? I also know 9.5 underwent a lot of locking improvements, so it might not be relevant. I just haven't gotten a chance to repeat my tests with 9.5 just yet. -- Shaun Thomas bonesmo...@gmail.com

Re: [PERFORM] pgtune + configurations with 9.3

Alexey, The issue is not that 8GB is the maximum. You *can* set it higher. What I'm saying, and I'm not alone in this, is that setting it higher can actually decrease performance for various reasons. Setting it to 25% of memory on a system with 512GB of RAM for instance, would be tantamount to

Re: [PERFORM] pgtune + configurations with 9.3

Alexey, The issue is that the 1/4 memory suggestion hasn't been a recommendation in quite a while. Now that much larger amounts of RAM are readily available, tests have been finding out that more than 8GB of RAM in shared_buffers has diminishing or even worse returns. This is true for any versi

Re: [PERFORM] pgtune + configurations with 9.3

> Yeah, pgTune is pretty badly out of date. It's been on my TODO list, as > I'm sure it has been on Greg's. Yeah. And unfortunately the recommendations it gives have been spreading. Take a look at the online version: http://pgtune.leopard.in.ua/ I entered a pretty typical 92GB system, and it r

Re: [PERFORM] Turn off Hyperthreading! WAS: 60 core performance with 9.3

until 3.11+, but the worst of the behavior was solved before that. Bugs in kernel cache page aging algorithms are bad, m'kay? -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com

Re: [PERFORM] Turn off Hyperthreading! WAS: 60 core performance with 9.3

-9.2 systems encountered high CPU counts. I somehow doubt Intel would allow their HT architecture to regress so badly from Nehalem, which is almost 3-generations old at this point. This smells like something in the software stack, up to and including the Linux kernel. -- Shaun Thomas OptionsHouse

Re: [PERFORM] Window functions, partitioning, and sorting performance

n retrieve those kind of results in a few milliseconds. -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditio

Re: [PERFORM] Turn off Hyperthreading! WAS: 60 core performance with 9.3

off turbo, or forgot to disable power saving features. -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditio

Re: [PERFORM] how to improve perf of 131MM row table?

data and WHERE clause and see if the planner still goes for the terrible plan. If it does, that would seem like an obvious planner tweak to me. -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@o

Re: [PERFORM] how to improve perf of 131MM row table?

an in that case, depending on how high random_page_cost is. -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and condi

Re: [PERFORM] how to improve perf of 131MM row table?

's still doing the sequence scan, we'll have to dig deeper. -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for te

Re: [PERFORM] how to improve perf of 131MM row table?

obvious immediately. Here's a link for your version: http://www.postgresql.org/docs/9.0/static/sql-explain.html You should still consider upgrading to the latest release of 9.0 too. -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@optionsho

Re: [PERFORM] how to improve perf of 131MM row table?

any rows, the random seeks are throwing off your performance. Of course, I can't confirm that without EXPLAIN output. -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com _

Re: [PERFORM] how to improve perf of 131MM row table?

ally match, which queries are performing badly, and so on. Please refer to this page to ask performance related questions: https://wiki.postgresql.org/wiki/Slow_Query_Questions Without much of this information, we'd only be speculating. -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd.

Re: [PERFORM] postgres files in use not staying in linux file cache

initely much happier since the upgrade, but the plural of anecdote is not data. :) -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_discl

Re: [PERFORM] postgres files in use not staying in linux file cache

-generic-lts-saucy pseudo-package to at least get onto the 3.11 instead. The 3.2 kernel is pants-on-head retarded; we've had a lot more luck with 3.8 and above. Cheers! -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604 312-676-8870 stho...@optionsho

Re: [PERFORM] Planner doesn't take indexes into account

f the table. I highly recommend reading up on cardinality and selectivity before creating more indexes. This page in the documentation does a really good job: http://www.postgresql.org/docs/9.3/static/row-estimation-examples.html -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suit

Re: [PERFORM] NFS, file system cache and shared_buffers

at(tm). It can be kinda safe-ish if mounted in sync mode with caching disabled, but I'd never use it on any of our systems. We also have this in the Wiki: http://wiki.postgresql.org/wiki/Shared_Storage -- Shaun Thomas OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 606

Re: [PERFORM] PGSQL, checkpoints, and file system syncs

shing. This is a pretty well-known issue on Linux systems with large amounts of RAM. Most VM servers fit that profile, so I'm not surprised it's hurting you. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd | Suite 400 | Chicago IL, 6060

Re: [PERFORM] query against large table not using sensible index to find very small amount of data

ving the indexes around. I can't think of many indexes in our database where I have the low cardinality value as the first column. Databases have an easier time managing many shallow buckets of values, than a few deep ones. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd | Suit

Re: [PERFORM] Slow Count-Distinct Query

in. This will pretty much never be fast, especially with 2M rows involved. I could be wrong about this, and the back-end folks might have a different answer, but I wouldn't hold my breath. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd | Suite 400 | Chicago IL, 60604 312-

Re: [PERFORM] Why shared_buffers max is 8GB?

> On most machines the limit is higher than you'd ever want to set it. I > have a set of servers with 1TB RAM and shared buffers on them is set > to 10G and even that is probably higher than it needs to be. The old > 1/4 of memory advice comes from the days when db server memory > was in the 1 to

Re: [PERFORM] Getting query plan alternatives from query planner?

n extreme cases. Optimization fences are truly forever. Unless of course they're removed. In which case, a bunch of queries that exploited them will suddenly perform a whole lot worse, causing organizations to delay upgrading PostgreSQL. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Su

Re: [PERFORM] slow query - will CLUSTER help?

> What are your thoughts on the right way to use SSDs in a RAID to > enhance postgres I/O performance? In an earlier reply, you > indicated one of a "RAID1+0 consisting of several spindles, > NVRAM-based solution (SSD or PCIe card), or a SAN" Well, it's a tiered approach. If you can identify yo

Re: [PERFORM] Recommendations for partitioning?

he results of the insert. WITH saved AS ( INSERT INTO dst SELECT * FROM src WHERE ... RETURNING * ) DELETE FROM src WHERE ...; I'll admit yours is cleaner, though. :) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@o

Re: [PERFORM] slow query - will CLUSTER help?

table middle ground with 240GB. Of course, don't forget to buy modules in multiples of four, otherwise you're not taking advantage of all the CPU's memory channels. :) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 606

Re: [PERFORM] slow query - will CLUSTER help?

d to a separate set of disks for a workload of this volume. The amount of writes here will constantly degrade read IO and further increase fetch times. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho

Re: [PERFORM] Regarding Hardware Tuning

couple versions. There are also a ton of considerations regarding new Linux kernel settings. Greg, go tell Packt they need to pay you to write the second edition. ;) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@o

Re: [PERFORM] Unexpected pgbench result

only scans. So theoretically, you might actually see some benefit there. If it were me and I had spindles available, I would just increase the overall size of the pool. It's a lot easier than managing multiple tablespaces. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chica

Re: [PERFORM] slow query - will CLUSTER help?

users have that setup. You're much more likely to get meaningful feedback if you follow the herd. :) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.pea

Re: [PERFORM] Optimizing a query

nt records are? Add an index to the createdAt column: CREATE INDEX idx_log_createdat ON "Log" (createdAt DESC); Using that, it should get the ten most recent Log records almost immediately, including associated article content. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd.

Re: [PERFORM] Unexpected pgbench result

solution. Your '-s 2000' test was probably running mostly from memory, while the '-s 4000' did not. What you're seeing is the speed your records are being supplied from disk, plus whatever cache effects are there when records are read before they are flushed in favor of m

Re: [PERFORM] autovacuum and dead tuples

mind that the dead tuples are still in the table, but reusable. The free_space column and free_percent is a better description of table bloat from data turnover cleaned up by autovacuum. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676

Re: [PERFORM] PostgreSQL settings for running on an SSD drive

h more controller chips can (purportedly) push upwards of 6GB/s, which is a tad faster than the 800MB/s (measured) of our ancient gen-1 cards. Too many variables. -_- -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __

Re: [PERFORM] Partitions not Working as Expected

of this will probably pan out, but I need to see the limits of how badly I can abuse the database. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/e

Re: [PERFORM] Partitions not Working as Expected

ld tables has a low cost, but it's not zero. With about a dozen of them, query times increase from 0.130ms to 0.280ms for my test case. Not a lot in the long run, but in a OLTP system, it can be fairly noticeable. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 5

Re: [PERFORM] Partitions not Working as Expected

telling devs to use current_date instead of ORM injections, then. Hopefully we can track down and tweak the affected queries on the tables we're partitioning without too much work and QA. Thanks, Tom! -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 31

Re: [PERFORM] Partitions not Working as Expected

pg_proc SET provolatile = 'i' WHERE proname = 'date_in'; -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for te

Re: [PERFORM] Partitions not Working as Expected

apparently detrimental to query execution. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

[PERFORM] Partitions not Working as Expected

ong, and I can't seem to come up with a workaround other than, "Ok devs, hard code dates into all of your queries from now on." -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com

Re: [PERFORM] PostgreSQL settings for running on an SSD drive

, and we were getting some really bad query plans unless we *strongly* suggested RPC was cheap. I was afraid I'd have to go lower, but 1 seemed to do the trick. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshous

Re: [PERFORM] PostgreSQL settings for running on an SSD drive

ce * 2 years, 3 months in operation * 1.29PB written * 1.75PB read The load on our system right now is 3.7 on a 24 CPU box while serving 4100 TPS after active trading hours. The FusionIO drive is basically the only reason we can do all of that without a lot of excessive contortions. -- Shaun Thomas

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

that would run into performance problems with a (relatively inexpensive) setup like this. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_discl

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

read queue, which suggests a firmware issue. Often this is because the device was optimized for sequential reads and post lower IOPS than is theoretically possible so they can advertise higher numbers alongside consumer-grade disks. They're Greg's disks though. :) -- Shaun Thomas

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

ZIL would compare. It's still disk-based, but the transparent SSD layer acting as a gigantic passive read and write cache intrigue me. It seems like it would also make a good middle-ground concerning cost vs. performance. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 5

Re: [PERFORM] Reliability with RAID 10 SSD and Streaming Replication

ut those situations are few and far between in a heavy transactional setting. Having used NVRAM/SSDs, I could never go back so long as the budget allows us to procure them. A data warehouse? Maybe spindles still have a place there. Heavy transactional system? Not a chance. -- Shaun Thomas

Re: [PERFORM] Thinking About Correlated Columns (again)

factor of 1000, which is the actual problem. The other reason I tried the combined index was that there was some noise a while back about having the planner check for possible column correlations on composite indexes. Clearly that ended up not being the case, but it was worth examination. -- Shaun

Re: [PERFORM] Thinking About Correlated Columns (again)

with functional indexes. It would definitely be nice to override the stats with known correlations when possible. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http

[PERFORM] Thinking About Correlated Columns (again)

but never any consensus. Anyone care to comment? -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to th

Re: [PERFORM] Changing ORDER BY column slows query dramatically

27;t complete enough, so it thinks there are few matches. If that doesn't work and you want a quick, but ugly fix for this, you can create the following index: CREATE INDEX event_20130406_id_desc_tstamp_utc_idx ON event_20130406 (id DESC, tstamp_utc); -- Shaun Thomas OptionsHouse |

Re: [PERFORM] Postgresql.conf file from like 7.x to 9.2

Settings that are new, and could assist in setting up streaming or backups: wal_level max_wal_senders People are getting more and more vocal about increasing cpu_tuple_cost, as the default is apparently too low in practice. Everything else? Salt to taste. -- Shaun Thomas OptionsHouse | 141

Re: [PERFORM] [OT] linux 3.10 kernel will improve ipc,sysv semaphore scalability

autogrouping and increasing sched_migration_cost. If the completely fair scheduler has less locking contention with this patch-set, those tweaks may not even be necessary. I need to see if I can find a system to test on. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604

Re: [PERFORM] Slow query when used in a view

ms to work without major adverse effects. Apparently the tiny table really will be tiny in actual use, so impact should be minimal. I just really don't like using subselects that way. :) Thanks, Tom! -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676

[PERFORM] Slow query when used in a view

sure what's going on here. I sense an optimization fence, but I can't see where. Thanks in advance! -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho...@optionshouse.com __ See http:/

Re: [PERFORM] Two Necessary Kernel Tweaks for Linux Systems

ly ones who ran into this, too. You settled on a much higher setting than we did, but the end result was the same. I wonder how prevalent this will become as more servers are switched over to newer kernels in the next couple of years. Hopefully more people start complaining so they fix

Re: [PERFORM] Two Necessary Kernel Tweaks for Linux Systems

y prefer PostgreSQL's approach... -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email --

Re: [PERFORM] Two Necessary Kernel Tweaks for Linux Systems

squat about internal kernel mechanics. Anyone who actually *isn't* talking out of his ass is free to interject. :) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ S

Re: [PERFORM] Two Necessary Kernel Tweaks for Linux Systems

itself or combined with Linux terms is tremendously unhelpful. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions

Re: [PERFORM] Two Necessary Kernel Tweaks for Linux Systems

s with the Completely Fair Scheduler, as opposed to the O(1) scheduler. For all intents and purposes, this means 3.0 kernels and above. With a 2.6 kernel, you're fine. Effectively these changes fix what is basically a performance regression compared to older kernels. -- Shaun Thomas Optio

[PERFORM] Two Necessary Kernel Tweaks for Linux Systems

scheduler settings should be modified under different usage scenarios. I just figured I'd share, since we found this info so beneficial. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com ___

[PERFORM] sched_migration_cost for high-connection counts

y this, because frankly, I find it difficult to believe. If legit, high-connection systems would benefit greatly. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http:

Re: [PERFORM] Ubuntu 12.04 / 3.2 Kernel Bad for PostgreSQL Performance

x27;m still curious why only recent 3.2's cause it, but 3.4 don't. That's mighty odd. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/e

Re: [PERFORM] Ubuntu 12.04 / 3.2 Kernel Bad for PostgreSQL Performance

4 levels, but that's expected. I haven't checked 3.0, but other threads I've read suggest it had less problems. Sorry if I wasn't clear. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __

[PERFORM] Ubuntu 12.04 / 3.2 Kernel Bad for PostgreSQL Performance

3.4, but still happens. If anyone has tested against 3.6 or 3.7, I'd love to hear your input. Inconsistent load reports are one thing... strangled performance and inflated CPU usage are quite another. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444

Re: [PERFORM] shared_buffers on ubuntu precise

own tests prove beneficial. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email -- Sent

Re: [PERFORM] shared_buffers on ubuntu precise

er eclectic and a little wonky, or that nailing down load calculations went awry since the NOHZ stuff started, or both. At this point, I wish we'd stayed on CentOS. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __

Re: [PERFORM] Optimize update query

read IO by reducing shared_buffers to 4GB. Without as many reads, your writes should be much less disruptive. You'll need to restart PG to adopt that change. But I encourage you to keep iostat running in a terminal window so you can watch it for a while. It's very revealing. -- Shaun

Re: [PERFORM] Optimize update query

er. You should also use sar. Just a plain: sar 1 100 Will give you a lot of info on what the CPU is doing. You want that %iowait column to be as low as possible. Keep us updated. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@opti

Re: [PERFORM] Optimize update query

7;s some other write stream happening we're not privy to. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and conditi

Re: [PERFORM] Optimize update query

t The rest of this is fine, except that you probably should have added: sudo chown -R postgres:postgres /ssd/pg_xlog/* -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://

Re: [PERFORM] Optimize update query

. Increase that to a few hundred, and my TPS drops down to 30k. Simply having the clients there kills performance. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http

Re: [PERFORM] Optimize update query

e performance of the master does it? Only if you're using synchronous replication. From what I saw in the config, that isn't the case. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __

Re: [PERFORM] Optimize update query

the kernel decides to write out dirty memory. That can make checkpoints take minutes to commit in some cases, which basically stops all write traffic to your database entirely. That should get you going, anyway. You still need more/better disks so you can move your pg_xlog directory. With you

Re: [PERFORM] Optimize update query

spec three years ago, and unsuited to database use simply due to the 2-disk RAID. Is the only way out of this really a SSD disk? No. There are many, many steps you can and should take before going this route. You need to know the problem you're solving before making potentially expensive

Re: [PERFORM] Hints (was Poor performance using CTE)

mp tables because I know that's how they work. But I'd be more than willing to modify my syntax one way or the other to adopt non-materialized CTEs, provided there's some way to get the current behavior. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chica

Re: [PERFORM] HT on or off for E5-26xx ?

ur X5675's perform about 40-50% better with HT enabled. Not the 2x you might expect by doubling the amount of "processors", but it definitely didn't make things worse. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444

Re: [PERFORM] Seq scan on big table, episode 2

using HASH indexes, though. They're not WAL logged, so they can't be replicated, and they're also not crash safe. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __

Re: [PERFORM] How to keep queries low latency as concurrency increases

erved in their own private pool or something. I mean... a pool is basically a proxy server. I don't have 12 individual proxy servers for 12 webservers. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@option

Re: [PERFORM] Seq scan on 10million record table.. why?

same. So it would be very helpful to also see the actual CREATE TABLE, and CREATE INDEX statements for those tables. We can't help you with this limited information. Sorry. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-85

Re: [PERFORM] Request for help with slow query

ilar with 8.2. Even 8.3 is on the way to retirement. You're *six* major versions behind the main release. At the very least, you need to upgrade PostgreSQL from 8.1.17 to 8.1.23. You're still on a version of PG that's almost 7-years old, but at least you'd have the most rec

Re: [PERFORM] Tons of free RAM. Can't make it go away.

ws a heavy preference for freeing memory from proc 0. It seems to only do it on this node, so we're going to switch nodes soon and see if the problem reappears. We may have to perform a node hardware audit if this persists. Thanks for your input, though. :) -- Shaun Thomas OptionsHouse | 141

Re: [PERFORM] Setting Statistics on Functional Indexes

performance. I mean, I get it. I just wonder if this particular tweak isn't more of a regression than initially thought. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ S

Re: [PERFORM] Prepared statements slow in 9.2 still (bad query plan)

That really bit us in the ass until EnterpriseDB sent us a patch. From browsing the threads, didn't someone say a similar problem existed in PG core? -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444

Re: [PERFORM] Setting Statistics on Functional Indexes

g else. I was trying to produce a clean-room to showcase this. But I'm seeing it everywhere I test, even with sane settings. Our EDB server is doing the same thing on much beefier hardware and correspondingly increased settings, which is what prompted me to test it in plai

[PERFORM] PSA: New Kernels and intel_idle cpuidle Driver!

eed. It took us days to figure this out, so I figured I'd share. Thanks, everyone! -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_discla

Re: [PERFORM] How to upgrade from 9.1 to 9.2 with replication?

ish, really. Or is there some embedded magic in streaming replication that requires start/stop backup? I've never had problems starting slaves built from an rsync before. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534

Re: [PERFORM] Setting Statistics on Functional Indexes

On 10/24/2012 02:31 PM, Shaun Thomas wrote: The main flaw with my example is that it's random. But I swear I'm not making it up! :) And then I find a way to make it non-random. Hooray: CREATE TABLE date_test ( id SERIAL, col1 varchar, col2 numeric, action_date TIMESTAMP WI

Re: [PERFORM] Setting Statistics on Functional Indexes

nc on date_test (cost=0.00..9.37 rows=1 width=23) (actual time=77.046..77.046 rows=0 loops=1) Index Cond: (date_trunc('day'::text, action_date) >= '2012-10-24 00:00:00'::timestamp without time zone) Filter: (((col1)::text = 'S:96'::text) AND (col2 =

[PERFORM] Setting Statistics on Functional Indexes

erriding problem seems to be two-fold: 1. Is there any way to specifically set stats on a functional index? 2. Why is the planner so ridiculously optimistic with functional indexes, even in the case of much higher selectivity as reported by pg_stats on the named columns? Thanks!

Re: [PERFORM] Tons of free RAM. Can't make it go away.

n case someone else encounters this. Yes, even if that answer is "reboot." :) Thanks again! -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.c

Re: [PERFORM] Tons of free RAM. Can't make it go away.

after the market closes to see if the unallocated chunk shrinks. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms and con

Re: [PERFORM] Tons of free RAM. Can't make it go away.

y segments directly to any large block. With MySQL, it's not uncommon to dedicate over 50% of RAM to the MySQL process itself, but I don't often see PG systems with more than 8GB in shared_buffers. All the rest should be available for random allocation in general. At least, in theor

Re: [PERFORM] Tons of free RAM. Can't make it go away.

2 4 6 8 10 12 14 16 18 20 22 node 0 size: 36853 MB node 0 free: 13816 MB node 1 cpus: 1 3 5 7 9 11 13 15 17 19 21 23 node 1 size: 36863 MB node 1 free: 751 MB node distances: node 0 1 0: 10 20 1: 20 10 Looks like CPU 0 is hoarding memory. :( -- Shaun Thomas OptionsHouse | 141 W. J

Re: [PERFORM] Tons of free RAM. Can't make it go away.

t, anywhere from 20-60% disk utilization that's mostly reads. It's pretty aggressively keeping that 14GB free, and it's driving me nuts. :) -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com

  1   2   3   >