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

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

hin 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. -- Shaun Thomas bonesmo...@gmail.com h

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

, 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

t 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 pgsql-pe

[PERFORM] Having some problems with concurrent COPY commands

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] Having some problems with concurrent COPY commands

ot; &>/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 '/tmp/loadtest1.csv'" &>/dev/null & -- S

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

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

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

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

+, 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 __ See

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

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 conditions related to this email

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] Turn off Hyperthreading! WAS: 60 core performance with 9.3

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 conditions related to this email

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

/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...@optionshouse.com __ See http://www.peak6

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

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 terms and conditions related to this email -- Sent via pgsql

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

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 conditions

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

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...@optionshouse.com

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

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. | Suite 800 | Chicago IL, 60604 312

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

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 __ See http://www.peak6.com

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

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...@optionshouse.com __ See

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

of 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. | Suite 800

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

). 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, 60604 312-676-8870

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

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, 60604 312-676-8870 stho...@optionshouse.com __ See

Re: [PERFORM] Slow Count-Distinct Query

. 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-676-8870 stho

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 16GB

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

. 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. | Suite 500 | Chicago IL

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 your

Re: [PERFORM] Unexpected pgbench result

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 | Chicago IL, 60604 312-676

Re: [PERFORM] Regarding Hardware Tuning

. 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...@optionshouse.com

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

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...@optionshouse.com

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

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, 60604 312-676-8870 stho...@optionshouse.com

Re: [PERFORM] Recommendations for partitioning?

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...@optionshouse.com

Re: [PERFORM] Unexpected pgbench result

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 more recent data. -- Shaun Thomas OptionsHouse | 141 W

Re: [PERFORM] Optimizing a query

, including associated article content. -- 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

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

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.peak6.com/email_disclaimer/ for terms and conditions related

Re: [PERFORM] autovacuum and dead tuples

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-8870 stho

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

) 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 __ See http://www.peak6.com/email_disclaimer/ for terms and conditions

[PERFORM] Partitions not Working as Expected

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 __ See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email

Re: [PERFORM] Partitions not Working as Expected

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 -- Sent

Re: [PERFORM] Partitions not Working as Expected

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 terms and conditions related to this email

Re: [PERFORM] Partitions not Working as Expected

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 312-676-8870 stho...@optionshouse.com __ See http://www.peak6.com/email_disclaimer/ for terms

Re: [PERFORM] Partitions not Working as Expected

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 500 | Chicago IL, 60604 312-676-8870 stho

Re: [PERFORM] Partitions not Working as Expected

, 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/email_disclaimer/ for terms

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

* 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 OptionsHouse | 141 W. Jackson Blvd

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...@optionshouse.com

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

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 OptionsHouse | 141 W. Jackson Blvd

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

, 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 OptionsHouse | 141 W

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

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_disclaimer/ for terms

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

by a 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

[PERFORM] Thinking About Correlated Columns (again)

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 this email -- Sent via pgsql-performance

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

this 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

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

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 | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-676-8870 stho

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

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 W. Jackson

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

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-8870 stho

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

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 it. :) -- Shaun Thomas

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

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 OptionsHouse | 141 W

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

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 related

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

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 __ See http://www.peak6.com/email_disclaimer/ for terms

[PERFORM] Two Necessary Kernel Tweaks for Linux Systems

. 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 __ See http://www.peak6.com/email_disclaimer/ for terms

[PERFORM] sched_migration_cost for high-connection counts

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://www.peak6.com

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

performance and inflated CPU usage are quite another. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com 100 __ See http://www.peak6.com/email_disclaimer/ for terms and conditions

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

. 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 __ See http://www.peak6

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

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/email_disclaimer

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

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://www.peak6.com/email_disclaimer/ for terms

Re: [PERFORM] Optimize update query

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 conditions related

Re: [PERFORM] Optimize update query

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...@optionshouse.com __ See http://www.peak6

Re: [PERFORM] Optimize update query

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 Thomas OptionsHouse

Re: [PERFORM] shared_buffers on ubuntu precise

, 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 __ See

Re: [PERFORM] shared_buffers on ubuntu precise

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 via pgsql

Re: [PERFORM] Optimize update query

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 hardware decisions. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500

Re: [PERFORM] Optimize update query

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 your write load, that will make a huge difference. -- Shaun Thomas OptionsHouse | 141 W. Jackson

Re: [PERFORM] Optimize update query

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 __ See http

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

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 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com

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

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-8534 stho...@optionshouse.com

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

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 __ See http://www.peak6.com

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

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-8534 stho...@optionshouse.com

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

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...@optionshouse.com

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

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-8534 stho...@optionshouse.com __ See

Re: [PERFORM] Setting Statistics on Functional Indexes

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 __ See http://www.peak6.com/email_disclaimer/ for terms

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

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 W. Jackson Blvd

Re: [PERFORM] Request for help with slow query

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 recent patch level. -- Shaun Thomas OptionsHouse | 141 W

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

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_disclaimer/ for terms and conditions

Re: [PERFORM] Setting Statistics on Functional Indexes

EDB server is doing the same thing on much beefier hardware and correspondingly increased settings, which is what prompted me to test it in plain PG. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com

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

. 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 stho...@optionshouse.com

[PERFORM] Setting Statistics on Functional Indexes

so ridiculously optimistic with functional indexes, even in the case of much higher selectivity as reported by pg_stats on the named columns? Thanks! -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com

Re: [PERFORM] Setting Statistics on Functional Indexes

) = '2012-10-24 00:00:00'::timestamp without time zone) Filter: (((col1)::text = 'S:96'::text) AND (col2 = 657::numeric)) Total runtime: 77.091 ms All I have to say about that is: wat. -- 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 WITHOUT TIME

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

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.com/email_disclaimer/ for terms and conditions

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

: 24449 48036 So, I've got 14GB of RAM that the OS is just refusing to use for disk or page cache. Does anyone know what might cause that? Our uname -sir, for reference: Linux 3.2.0-31-generic x86_64 -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444

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

-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 __ See

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

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. Jackson Blvd. | Suite

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

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 theory. -- Shaun Thomas

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

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 conditions related to this email

[PERFORM] Tablespace-derived stats?

! -- 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 via pgsql-performance mailing list

Re: [PERFORM] Tablespace-derived stats?

On 10/19/2012 09:51 AM, Tom Lane wrote: We've had tablespace-specific settings for those for some time. Ah, my apologies. I didn't see any in the CREATE TABLESPACE page, and didn't think to check ALTER TABLESPACE. I withdraw my question. :) -- Shaun Thomas OptionsHouse | 141 W. Jackson

Re: [PERFORM] Tablespace-derived stats?

forgetting Postgres prefers a CREATE + ALTER style than overloading every CREATE with all ALTER options. Though in my opinion that just adds extra unnecessary steps. -- Shaun Thomas OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604 312-444-8534 stho...@optionshouse.com

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

upgrade, we've kept using our home-grown backup system. :( -- 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

  1   2   3   >