Re: [PERFORM] Do I have a hardware or a software problem?
Well, In fact I do (as you can see from my configuration). I have a similar server running with hot standby replication - and it runs two 3T HDD in a RAID1 array. So - is it still very bad if I choose to put four intel 520 disks in a RAID10 array on the other production server? Den 12/12/2012 kl. 03.47 skrev Craig Ringer : > On 12/12/2012 10:13 AM, Evgeny Shishkin wrote: >> >> Yes, i am aware of this issue. Never experienced this neither on intel 520, >> no ocz vertex 3. >> > > I wouldn't trust either of those drives. The 520 doesn't have Intel's " > Enhanced Power Loss Data Protection"; it's going to lose its buffers if it > loses power. Similarly, the Vertex 3 doesn't have any kind of power > protection. See: > > http://www.intel.com/content/www/us/en/solid-state-drives/ssd-320-series-power-loss-data-protection-brief.html > http://ark.intel.com/products/family/56572/Intel-SSD-500-Family > > http://www.ocztechnology.com/res/manuals/OCZ_SSD_Breakdown_Q2-11_1.pdf > > The only way I'd use those for a production server was if I had synchronous > replication running to another machine with trustworthy, durable storage - > and if I didn't mind some downtime to restore the corrupt DB from the replica > after power loss. > > -- > Craig Ringer http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services
[PERFORM] Read rows deleted
Hi, Anybody knows a JDBC or a multiplatform code that let read the delete rows of a table without writing of a table file? Anybody knows how to create a table using a table file? thanks
Re: [PERFORM] hash join vs nested loop join
On Tue, Dec 11, 2012 at 8:25 PM, Huan Ruan wrote: > Hello All > > While investigating switching to Postgres, we come across a query plan that > uses hash join and is a lot slower than a nested loop join. > > I don't understand why the optimiser chooses the hash join in favor of the > nested loop. What can I do to get the optimiser to make a better decision > (nested loop in this case)? I have run analyze on both tables. > > The query is, > > /* >smalltable has about 48,000 records. >bigtable has about 168,000,000 records. >invtranref is char(10) and is the primary key for both tables > */ > SELECT > * > FROM IM_Match_Table smalltable > inner join invtran bigtable on > bigtable.invtranref = smalltable.invtranref .. > " -> Index Scan using pk_invtran on public.invtran bigtable > (cost=0.00..267.03 rows=1 width=108)" This looks like the same large-index over-penalty as discussed in the recent thread "[PERFORM] Slow query: bitmap scan troubles". Back-patching the log(npages) change is starting to look like a good idea. Cheers, Jeff -- 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] Do I have a hardware or a software problem?
Den 11/12/2012 kl. 18.25 skrev Jeff Janes : > On Tue, Dec 11, 2012 at 2:04 AM, Niels Kristian Schjødt > wrote: >> Den 11/12/2012 kl. 00.58 skrev Jeff Janes : >> >>> >>> The fact that there is much more writing than reading tells me that >>> most of your indexes are in RAM. The amount of index you are rapidly >>> reading and dirtying is large enough to fit in RAM, but is not large >>> enough to fit in shared_buffers + kernel's dirty-buffer comfort level. > >> Maybe I should mention, that I never see more than max 5Gb out of my total >> 32Gb being in use on the server… Can I somehow utilize more of it? > > What tool do you use to determine that? Is that on top of the 4GB > shared_buffers, are including it? Okay I might not have made myself clear, I was talking "physical" memory utilization. Here is the stats: free -m total used free sharedbuffers cached Mem: 32075 25554 6520 0 69 22694 -/+ buffers/cache: 2791 29284 Swap: 2046595 1451 > > How big is your entire data set? Maybe all your data fits in 5GB > (believable, as all your indexes listed below sum to < 2.5GB) so there > is no need to use more. It doesn't we are a search engine for used cars, and there are quite a lot of those out there :-) However, my indexes are almost all partial indexes, which mean that they are only on cars which is still for sale, so in that sense, the indexes them selves doesn't really grow, but the tables do. > > Or maybe you have hit an bug in the 3.2 kernel. At least one of those > has been frequently discussed. > Might be true - but likely? > >>> You could really crank up shared_buffers or vm.dirty_background_ratio, >>> but doing so might cause problems with checkpoints stalling and >>> latency spikes. That would probably not be a problem during the >>> night, but could be during the day. > >> What do you have in mind here? Tweaking what parameters to what values? > > I'd set shared_buffers to 20GB (or 10GB, if that will hold all of your I had that before, Shaun suggested that I changed it to 4GB as he was talking about a strange behavior when larger than that on 12.04. But I can say, that there has not been any notable difference between having it at 4Gb and at 8Gb. > data) and see what happens. And probably increase checkpoint_timeout > and checkpoint_segments about 3x each. Also, turn on log_checkpoints > so you can see what kinds of problem those changes may be causing > there (i.e. long sync times). Preferably you do this on some kind of > pre-production or test server. > > But if your database is growing so rapidly that it soon won't fit on > 240GB, then cranking up shared_buffers won't do for long. If you can > get your tables and all of their indexes clustered together, then you > can do the updates in an order that makes IO more efficient. Maybe > partitioning would help. Can you explain a little more about this, or provide me a good link? > > >>> I don't know how big each disk is, or how big your various categories >>> of data are. Could you move everything to SSD? Could you move all >>> your actively updated indexes there? > >> With table spaces you mean? > > Yes. Or moving everything to SSD if it fits, then you don't have go > through and separate objects. > > The UPDATE you posted in a previous thread looked like the table > blocks might also be getting dirtied in a fairly random order, which > means the table blocks are in the same condition as the index blocks > so maybe singling out the indexes isn't warranted. > > Cheers, > > Jeff -- 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] Savepoints in transactions for speed?
On Thu, Nov 29, 2012 at 11:58 AM, Claudio Freire wrote: > On Thu, Nov 29, 2012 at 3:32 PM, Jeff Davis wrote: >> >> I tried a quick test with 2M tuples and 3 indexes over int8, numeric, >> and text (generated data). There was also an unindexed bytea column. >> Using my laptop, a full update of the int8 column (which is indexed, >> forcing cold updates) took less than 4 minutes. >> >> I'm sure there are other issues with real-world workloads, and I know >> that it's wasteful compared to something that can make use of HOT >> updates. But unless there is something I'm missing, it's not really >> worth the effort to batch if that is the size of the update. > > On a pre-production database I have (that is currently idle), on a > server with 4G RAM and a single SATA disk (probably similar to your > laptop in that sense more or less, possibly more TPS since the HD rpm > is 7k and your laptop probably is 5k), it's been running for half an > hour and is still running (and I don't expect it to finish today if > past experience is to be believed). So probably Jeff Davis's indexes fit in RAM (or the part that can be dirtied without causing thrashing), and yours do not. But, does batching them up help at all? I doubt it does. Cheers, Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] track_activity_query_size
Is there a performance downside to setting track_activity_query_size to a significantly larger value than the default 1024 (say 10240), given that there's plenty of memory to spare? cheers andrew -- 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] Read rows deleted
Hi, On Wed, Dec 12, 2012 at 8:26 AM, Alejandro Carrillo wrote: > Anybody knows a JDBC or a multiplatform code that let read the delete rows > of a table without writing of a table file? > Anybody knows how to create a table using a table file? I am not sure what you mean but may be one of this links will help you: - http://www.postgresql.org/docs/9.2/static/file-fdw.html - http://pgxn.org/dist/odbc_fdw/. > > thanks -- Sergey Konoplev Database and Software Architect http://www.linkedin.com/in/grayhemp Phones: USA +1 415 867 9984 Russia, Moscow +7 901 903 0499 Russia, Krasnodar +7 988 888 1979 Skype: gray-hemp Jabber: gray...@gmail.com -- 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] Read rows deleted
Hi,
Anybody knows how to create a table using a table file? It isn't a fdw, is a
file that compose the table in postgresql and get with the pg_relation_filepath
function. Ex:
select pg_relation_filepath('pg_proc');
Anybody knows a JDBC or a multiplatform code that let read the delete rows of a
table without writing of a table file?
Thanks
>
> De: Sergey Konoplev
>Para: Alejandro Carrillo
>CC: "pgsql-performance@postgresql.org"
>Enviado: Miércoles 12 de diciembre de 2012 15:13
>Asunto: Re: [PERFORM] Read rows deleted
>
>Hi,
>
>On Wed, Dec 12, 2012 at 8:26 AM, Alejandro Carrillo wrote:
>> Anybody knows a JDBC or a multiplatform code that let read the delete rows
>> of a table without writing of a table file?
>> Anybody knows how to create a table using a table file?
>
>I am not sure what you mean but may be one of this links will help you:
>
>- http://www.postgresql.org/docs/9.2/static/file-fdw.html
>- http://pgxn.org/dist/odbc_fdw/.
>
>>
>> thanks
>
>
>
>--
>Sergey Konoplev
>Database and Software Architect
>http://www.linkedin.com/in/grayhemp
>
>Phones:
>USA +1 415 867 9984
>Russia, Moscow +7 901 903 0499
>Russia, Krasnodar +7 988 888 1979
>
>Skype: gray-hemp
>Jabber: gray...@gmail.com
>
>
>
Re: [PERFORM] Read rows deleted
On 12/12/2012 03:24 PM, Alejandro Carrillo wrote:
Hi,
Anybody knows how to create a table using a table file? It isn't a
fdw, is a file that compose the table in postgresql and get with the
pg_relation_filepath function. Ex:
select pg_relation_filepath('pg_proc');
Anybody knows a JDBC or a multiplatform code that let read the delete
rows of a table without writing of a table file?
This isn't a performance related question. Please ask on the correct
list (probably pgsql-general).
cheers
andrew
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] encouraging index-only scans
A client is testing a migration from 9.1 to 9.2, and has found that a large number of queries run much faster if they use index-only scans. However, the only way he has found to get such a plan is by increasing the seq_page_cost to insanely high levels (3.5). Is there any approved way to encourage such scans that's a but less violent than this? cheers andrew -- 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] encouraging index-only scans
On 12/12/2012 04:32 PM, Tom Lane wrote: Andrew Dunstan writes: A client is testing a migration from 9.1 to 9.2, and has found that a large number of queries run much faster if they use index-only scans. However, the only way he has found to get such a plan is by increasing the seq_page_cost to insanely high levels (3.5). Is there any approved way to encourage such scans that's a but less violent than this? Is the pg_class.relallvisible estimate for the table realistic? They might need a few more VACUUM and ANALYZE cycles to get it into the neighborhood of reality, if not. That was the problem - I didn't know this hadn't been done. Keep in mind also that small values of random_page_cost necessarily decrease the apparent advantage of index-only scans. If you think 3.5 is an "insanely high" setting, I wonder whether you haven't driven those numbers too far in the other direction to compensate for something else. Right. Thanks for the help. cheers andrew -- 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] encouraging index-only scans
On 12/12/2012 05:12 PM, Andrew Dunstan wrote: On 12/12/2012 04:32 PM, Tom Lane wrote: Andrew Dunstan writes: A client is testing a migration from 9.1 to 9.2, and has found that a large number of queries run much faster if they use index-only scans. However, the only way he has found to get such a plan is by increasing the seq_page_cost to insanely high levels (3.5). Is there any approved way to encourage such scans that's a but less violent than this? Is the pg_class.relallvisible estimate for the table realistic? They might need a few more VACUUM and ANALYZE cycles to get it into the neighborhood of reality, if not. That was the problem - I didn't know this hadn't been done. Actually, the table had been analysed but not vacuumed, so this kinda begs the question what will happen to this value on pg_upgrade? Will people's queries suddenly get slower until autovacuum kicks in on the table? cheers andrew -- 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] Do I have a hardware or a software problem?
On 13/12/2012 12:22 AM, Niels Kristian Schjødt wrote: > Well, In fact I do (as you can see from my configuration). I have a > similar server running with hot standby replication - and it runs two > 3T HDD in a RAID1 array. > > So - is it still very bad if I choose to put four intel 520 disks in a > RAID10 array on the other production server? So long as you have it recording to a synchronous replia on another machine and you're fully prepared to accept the small risk that you'll have total and unrecoverable data corruption on that server, with the corresponding downtime while you rebuild it from the replica, it should be OK. Alternately, you could use PITR with a basebackup to ship WAL to another machine or a reliable HDD, so you can recover all but the last checkpoint_timeout minutes of data from the base backup + WAL. There's small window of data loss that way, but you don't need a second machine as a streaming replication follower. barman might is worth checking out as a management tool for PITR backups. If the data is fairly low-value you could even just take nightly backups and accept the risk of losing some data. -- Craig Ringer
Re: [PERFORM] hash join vs nested loop join
Huan Ruan wrote: > is a lot slower than a nested loop join. Giving actual numbers is more useful than terms like "a lot". Even better is to provide the output of EXPLAIN ANALYZZE rather than just EXPLAIN. This shows estimates against actual numbers, and give timings. For more suggestions see this page: http://wiki.postgresql.org/wiki/SlowQueryQuestions > I don't understand why the optimiser chooses the hash join in > favor of the nested loop. What can I do to get the optimiser to > make a better decision (nested loop in this case)? I have run > analyze on both tables. > Config changes are > > - shared_buffers = 6GB > - effective_cache_size = 18GB > - work_mem = 10MB > - maintenance_work_mem = 3GB As already suggested, there was a change made in 9.2 which may have over-penalized nested loops using index scans. This may be fixed in the next minor release. Also, as already suggested, you may want to reduce random_page cost, to bring it in line with the actual cost relative to seq_page_cost based on your cache hit ratio. Additionally, I just routinely set cpu_tuple_cost higher than the default of 0.01. I find that 0.03 to 0.05 better models the actual relative cost of processing a tuple. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Limit & offset effect on query plans
Hi Our scripts automatically add "LIMIT ALL" & "OFFSET 0" to every select query if no values are passed on for these parameters. I remember reading through the mailing list that it's better not to pass them if they are not needed as they add a cost to the query plan. Is this the case, or am i looking at a very minor optimization. Amitabh P.S. I haven't checked my query plans to see if there are any actual effect of these keywords as I am still working my way through reading the output of "Explain" ouput.
Re: [PERFORM] Limit & offset effect on query plans
On Thu, Dec 13, 2012 at 9:38 AM, Amitabh Kant wrote: > Hi > > Our scripts automatically add "LIMIT ALL" & "OFFSET 0" to every select query > if no values are passed on for these parameters. I remember reading through > the mailing list that it's better not to pass them if they are not needed as > they add a cost to the query plan. Is this the case, or am i looking at a > very minor optimization. > I would tend to think that is the latter. While undoubtedly limit/offset clause will add another node during query planning and execution, AFAICS the OFFSET 0 and LIMIT ALL cases are optimized to a good extent. So the overhead of having them will not be significant. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Memory issue for inheritance tables.
One parent table, having 100 child tables. In this scenario we observed that delete and update are taking more memory for preparing a plan If the system under peak i am getting out of memory issue. i.e Select on parent table is using memory - 331456 in message context Delete on parent table is using memory - 3746432 in message context Delete on single child table is using memory - 8800 in message context Select on single child table is using memory - 9328 in message context For 250 child tables Select on parent table is using memory - 810864 in message context Delete on parent table is using memory - 21273088 in message context I had seen the plans for both delete & select on parent table almost same. why this much of memory increase in message context for delete and update operation. Regards, Hari babu.
