Jira (PDB-1472) [Performance] Improvements for paged results
Title: Message Title Ryan Senior updated an issue PuppetDB / PDB-1472 [Performance] Improvements for paged results Change By: Ryan Senior Sprint: PuppetDB 2015-06-17 Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1472) [Performance] Improvements for paged results
Title: Message Title Wyatt Alt updated an issue PuppetDB / PDB-1472 [Performance] Improvements for paged results Change By: Wyatt Alt Sprint: PuppetDB 2015-06- 03 17 Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1472) [Performance] Improvements for paged results
Title: Message Title Wyatt Alt updated an issue PuppetDB / PDB-1472 [Performance] Improvements for paged results Change By: Wyatt Alt Sprint: PuppetDB 2015-06-03 Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1472) [Performance] Improvements for paged results
Title: Message Title Ryan Senior updated an issue PuppetDB / PDB-1472 [Performance] Improvements for paged results Change By: Ryan Senior Sprint: PuppetDB 2015-06-03 Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1472) [Performance] Improvements for paged results
Title: Message Title Steve Barlow updated an issue PuppetDB / PDB-1472 [Performance] Improvements for paged results Change By: Steve Barlow Sprint: PuppetDB 2015- 05 06 - 20 03 Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1472) [Performance] Improvements for paged results
Title: Message Title Kenneth Barber updated an issue PuppetDB / PDB-1472 [Performance] Improvements for paged results Change By: Kenneth Barber Sprint: PuppetDB 2015-05-20 Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1472) [Performance] Improvements for paged results
Title: Message Title Karel Brezina commented on PDB-1472 Re: [Performance] Improvements for paged results Thanks Wyatt Alt for taking your time. I'm glad that you found the strange behavior even on your machine. Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1472) [Performance] Improvements for paged results
Title: Message Title Wyatt Alt commented on PDB-1472 Re: [Performance] Improvements for paged results I'm on a 2013 macbook pro with 16gb ram and 4 hyperthreaded cores. 10.10.1. I can reproduce your plan by bumping the offset to 1.3M. Somewhere between 1.2 and 1.3 seems to be the break point. I'm just using the default postgres.conf with some logging changes, but I attached it in case it's of interest. I tried to see if I could fix it by running pgtune and found that using pgtune's recommendations (substantially more memory allocated) caused me to get your slower plan at 1M, which was surprising. I've confirmed that your query is blazing fast with the additional index. We'll need to put some thought into this. Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1472) [Performance] Improvements for paged results
Title: Message Title Wyatt Alt updated an issue PuppetDB / PDB-1472 [Performance] Improvements for paged results Change By: Wyatt Alt Attachment: wyatt_postgresql.conf Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1472) [Performance] Improvements for paged results
Title: Message Title Karel Brezina commented on PDB-1472 Re: [Performance] Improvements for paged results Wyatt Alt That's weird. I'm also on PGS 9.4.0. I did run vacuum analyze. Current query: https://gist.github.com/kbrezina/7e702f65a926b2254998 Proposed query: https://gist.github.com/kbrezina/e5af5313761dc96de4cf My observations: totally different query plans for the current query the same query plan for the proposed query the query plan for the proposed query doesn't use the end_time index -> proposed query with added '(end_time, id)' index: https://gist.github.com/kbrezina/47341a9aeb25420c when I decrease offset to 100,000 then my query plan for the current query is the same as yours: https://gist.github.com/kbrezina/9344f8dfd3e7539b4ee6 It seems that your PGS is installed on a more powerful machine. Could that matter? My results are from VM Ubuntu 14.04 with 4 GB RAM and 2 CPUs. However, similar results are from my workstation OSX 10.10.3 with 16 GB RAM and 4 CPUs. Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegr
Jira (PDB-1472) [Performance] Improvements for paged results
Title: Message Title Wyatt Alt commented on PDB-1472 Re: [Performance] Improvements for paged results Karel Brezina I can't reproduce this at all. Here are the plans and timings for the two queries, run on my laptop with your data: Current query: https://gist.github.com/wkalt/6caefe1b80451dbe5707 Proposed query: https://gist.github.com/wkalt/2c2c6330b8fa19f01cee What version of postgres are you on (I'm on 9.4)? Also, do you get the same results after running a vacuum analyze? Wyatt Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1472) [Performance] Improvements for paged results
Title: Message Title Karel Brezina commented on PDB-1472 Re: [Performance] Improvements for paged results PG dump of my DB is here. To load the data to "puppetdb" database use: gunzip -c puppetdb.gz | psql puppetdb Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d) -- You received this message because you are subscribed to the Google Groups "Puppet Bugs" group. To unsubscribe from this group and stop receiving emails from it, send an email to puppet-bugs+unsubscr...@googlegroups.com. To post to this group, send email to puppet-bugs@googlegroups.com. Visit this group at http://groups.google.com/group/puppet-bugs. For more options, visit https://groups.google.com/d/optout.
Jira (PDB-1472) [Performance] Improvements for paged results
Title: Message Title Karel Brezina commented on PDB-1472 Re: [Performance] Improvements for paged results For paged reports PuppetDB uses the following query: SELECT paged_results.* FROM ( SELECT reports.certname AS certname, reports.configuration_version AS configuration_version, reports.end_time AS end_time, environments.name AS environment, reports.hash AS hash, reports.metrics AS metrics, reports.receive_time AS receive_time, report_statuses.status AS status FROM reports LEFT JOIN environments ON environments.id = reports.environment_id LEFT JOIN report_statuses ON reports.status_id = report_statuses.id) paged_results
Jira (PDB-1472) [Performance] Improvements for paged results
Title: Message Title Karel Brezina created an issue PuppetDB / PDB-1472 [Performance] Improvements for paged results Issue Type: Improvement Assignee: Unassigned Created: 2015/05/04 5:33 AM Priority: Normal Reporter: Karel Brezina The original PuppetDB query for paged results is suboptimal with huge offsets. Add Comment This message was sent by Atlassian JIRA (v6.3.15#6346-sha1:dbc023d)
