http://permalink.gmane.org/gmane.comp.db.postgresql.performance hourly 1 1901-01-01T00:00+00:00 http://gmane.org/img/gmane-25t.png http://gmane.org http://permalink.gmane.org/gmane.comp.db.postgresql.performance/36311 <pre>I'm not sure I understand your proposed solution. There is also the case to consider where the same patient can be assigned the same device multiple times. In this case, the value may be reset at each assignment (hence the line value - issued_value AS value from the original query). On Thu, May 23, 2013 at 1:01 PM, Steve Crawford &lt; scrawford&lt; at &gt;pinpointresearch.com&gt; wrote: </pre> Jonathan Morra 2013-05-23T21:57:19 http://permalink.gmane.org/gmane.comp.db.postgresql.performance/36310 <pre> If the reads are always incremented - that is the read you want is always the largest read - then something along these lines might work well and be more readable (untested code); </pre> Steve Crawford 2013-05-23T20:01:48 http://permalink.gmane.org/gmane.comp.db.postgresql.performance/36309 <pre>1. Reads is constantly inserted upon. It should never be updated or deleted. 2. I suppose I can, but that will make my insertion logic very complicated. I cannot guarantee the order of any of this data, so I might get reads at any time and also get assignments at any time (historical as well). I suppose I could do that, but I'd like to avoid it if at all possible. 3. 2 measurements can have the same date, and that is fine. The problem arises when the same device produces 2 reads at the same time and that isn't possible. 4. I agree that a lot of this is a mess, however MAX(max_read) OVER(PARTITION BY patient_id) AS latest_read seems necessary as using a group by clause forces me to group by all elements I'm selecting, which I don't want to do. On Thu, May 23, 2013 at 12:23 PM, Vladimir Sitnikov &lt; sitnikov.vladimir&lt; at &gt;gmail.com&gt; wrote: </pre> Jonathan Morra 2013-05-23T19:43:55 http://permalink.gmane.org/gmane.comp.db.postgresql.performance/36308 <pre>I'm only summing the last read for each device for each patient. Is "reads" table insert-only? Do you have updates/deletes of the "historical" rows? application) run faster? Can you have a second "reads" table that stores only up to date values? That will eliminate max-over completely, enable efficient usage in other queries, and make your queries much easier to understand by humans and computers. PS. read_datetime = max_read is prone to "what if two measurements have same date" errors. PPS. distinct MAX(max_read) OVER(PARTITION BY patient_id) AS latest_read looks like a complete mess. Why don't you just use group by? Regards, Vladimir </pre> Vladimir Sitnikov 2013-05-23T19:23:37 http://permalink.gmane.org/gmane.comp.db.postgresql.performance/36307 <pre>HAndres, I agree that I haven't proven this yet, but that doesn't make it invalid. Just unproven. I agree that performance testing is necessary ... and the kind of performance testing which generated freeze activity, which makes it harder. Hmmm. But does vacuum visit the pages anyway, in that case? Oh, that's annoying. Yeah, or come up with some way to eliminate freezing entirely. </pre> Josh Berkus 2013-05-14T04:02:04 http://permalink.gmane.org/gmane.comp.db.postgresql.performance/36306 <pre>Ultimately I'm going to deploy this to Heroku on a Linux machine (my tests have so far indicated that Heroku is MUCH slower than my machine), but I wanted to get it fast on my local machine first. I agree with your role partitioning, however, this is only a dev machine. For the sum vs. last, the idea is that each patient is issued a device and reads are recorded. The nature of the reads are that they are incremental, so if a patient never changes devices there is no need for a sum. However, patients will be changing devices, and the patient_device table records when each patient had a given device. What I want to sum up is the total value for a patient regardless of how many times they changed devices. In order to do this I have to sum up just the values of the last read for each device a patient was assigned to. This leads to the WHERE clause, WHERE read_datetime = max_read, and hence I'm only summing the last read for each device for each patient. Ultimately I want to report the values listed in th</pre> Jonathan Morra 2013-05-23T17:57:26 http://permalink.gmane.org/gmane.comp.db.postgresql.performance/36305 <pre> I'm not sure under what constraints you are operating but you will find most people on the list will recommend running live systems on Linux/Unix for a variety of reasons. To avoid future grief you should set up a user (see CREATE ROLE...) for your database that is not the cluster superuser (postgres). I assume you set up a database (see CREATE DATABASE...) for your app. The base databases (postgres, template*) should be used for administrative purposes only. I'm afraid I'm a bit confused about what you are after due to switching between "sum" and "last". It sounds like a patient is issued a device which takes a number of readings. Do you want the sum of those readings for a given patient across all devices they have been issued, the sum of readings for a specific device, the most recent reading for a specific patient regardless of which device was in use for that reading, or the sum of the most recent readings on each device issued to a specific patient? Are you looking to generate a repo</pre> Steve Crawford 2013-05-23T17:47:38 http://permalink.gmane.org/gmane.comp.db.postgresql.performance/36304 <pre></pre> fburgess< at >radiantblue.com 2013-05-23T17:21:28 http://permalink.gmane.org/gmane.comp.db.postgresql.performance/36303 <pre>I am fairly new to squeezing performance out of Postgres, but I hope this mailing list can help me. I have read the instructions found at http://wiki.postgresql.org/wiki/Slow_Query_Questions and have tried to abide by them the best that I can. I am running "PostgreSQL 9.1.7, compiled by Visual C++ build 1500, 64-bit" on an x64 Windows 7 Professional Service Pack 1 machine with 8 GB of RAM. I installed this using the downloadable installer. I am testing this using pgAdminIII but ultimately this will be deployed within a Rails application. Here are the values of some configuration parameters: shared_buffers = 1GB temp_buffers = 8MB work_mem = 10MB maintenance_work_mem = 256MB random_page_cost = 1.2 default_statistics_target = 10000 Table schema: reads-- ~250,000 rows CREATE TABLE reads ( id serial NOT NULL, device_id integer NOT NULL, value bigint NOT NULL, read_datetime timestamp without time zone NOT NULL, created_at timestamp without time zone NOT NULL, updated_at timestamp without time </pre> Jonathan Morra 2013-05-23T17:19:50 http://permalink.gmane.org/gmane.comp.db.postgresql.performance/36302 <pre> [cut] many thanks merlin for both the explanation and the good advice :) andrea </pre> Andrea Suisani 2013-05-23T13:55:08 http://permalink.gmane.org/gmane.comp.db.postgresql.performance/36301 <pre> huh -- I don't think so. wal file segments are 8kb aligned, ditto clog, etc. In XLogWrite(): /* OK to write the page(s) */ from = XLogCtl-&gt;pages + startidx * (Size) XLOG_BLCKSZ; nbytes = npages * (Size) XLOG_BLCKSZ; &lt;-- errno = 0; if (write(openLogFile, from, nbytes) != nbytes) { AFICT, that's the only way you write out xlog. One thing I would definitely advise though is to disable partial page writes if it's enabled. s3700 is algined on 8kb blocks internally -- hm. merlin </pre> Merlin Moncure 2013-05-23T13:47:13 http://permalink.gmane.org/gmane.comp.db.postgresql.performance/36300 <pre>Hello, I am testing performance of postgresql application using pgbench. I am getting spike in results(graphs) as shown in attached graph due to throughput drop at that time. pgbench itself doing checkpoint on server (where queries are running) before and after test starts. pgbench is running on separate client machine. actual queries are running on separate server machine. *Test configurations:* test duration is 5 minutes numbers clients are 120 scale is 100 query mode is prepared only select queries are used. result graph: see attachment tps.png *spike is at 12:09:14* *My Observatios:* * * In vmstat, sar , iostat, top logs i found that at the time of spike there is more iowait on pgbench-client. there is similar iowait at another timestamp present but there is no spike. So i am not getting why spike occure at *12:09:14 *only*.* If anyone find solution of this problem please reply. Also i am working to get context switches at the time of spike occurred. Please reply if any clue. -------------------</pre> Sachin D. Bhosale-Kotwal 2013-05-23T11:39:32 http://permalink.gmane.org/gmane.comp.db.postgresql.performance/36299 <pre> [cut] [cut] Sorry for interrupting but on a related note I would like to know your opinions on what the anandtech review said about 3700 poor performance on "Oracle Swingbench", quoting the relevant part that you can find here (*) &lt;quote&gt; [..] There are two components to the Swingbench test we're running here: the database itself, and the redo log. The redo log stores all changes that are made to the database, which allows the database to be reconstructed in the event of a failure. In good DB design, these two would exist on separate storage systems, but in order to increase IO we combined them both for this test. Accesses to the DB end up being 8KB and random in nature, a definite strong suit of the S3700 as we've already shown. The redo log however consists of a bunch of 1KB - 1.5KB, QD1, sequential accesses. The S3700, like many of the newer controllers we've tested, isn't optimized for low queue depth, sub-4KB, sequential workloads like this. [..] &lt;/quote&gt; Does this kind of scenario apply to post</pre> Andrea Suisani 2013-05-23T06:56:50 http://permalink.gmane.org/gmane.comp.db.postgresql.performance/36298 <pre> Explain analyze doesn't help to collect statistics. You should use Analyze &lt;table_name&gt;. Ideally optimizer should have slected the best plan, but just to check you can once try with SET enable_hashjoin=off; And see what is the plan it chooses and does it pick up index scan on larger table? Could you please output of \d SARS_ACTS and \d SARS_ACTS_RUN? With Regards, Amit Kapila. </pre> Amit Kapila 2013-05-23T06:13:44 http://permalink.gmane.org/gmane.comp.db.postgresql.performance/36297 <pre> On 05/22/2013 07:17 PM, Merlin Moncure wrote: Yeah that was my thinking. Sure it isn't an S3700 but for the money it is still faster than the comparable spindle configuration. JD </pre> Joshua D. Drake 2013-05-23T02:51:45 http://permalink.gmane.org/gmane.comp.db.postgresql.performance/36296 <pre> Having said that, there does seem to be a wear leveling counter in its SMART attributes - but, yes - I'd like to see indicators more similar the level of detail that Intel provides. Cheers Mark </pre> Mark Kirkwood 2013-05-23T02:44:18 http://permalink.gmane.org/gmane.comp.db.postgresql.performance/36295 <pre> Agreed - I was thinking the same thing! Cheers Mark </pre> Mark Kirkwood 2013-05-23T02:26:28 http://permalink.gmane.org/gmane.comp.db.postgresql.performance/36294 <pre> There are some more shots and descriptions of the internals in the excellent review at http://techreport.com/review/24666/crucial-m500-ssd-reviewed That also highlights the big problem with this drive that's kept me from buying one so far: "Unlike rivals Intel and Samsung, Crucial doesn't provide utility software with a built-in health indicator. The M500's payload of SMART attributes doesn't contain any references to flash wear or bytes written, either. Several of the SMART attributes are labeled "Vendor-specific," but you'll need to guess what they track and read the associated values using third-party software." That's a serious problem for most business use of this sort of drive. </pre> Greg Smith 2013-05-23T02:22:24 http://permalink.gmane.org/gmane.comp.db.postgresql.performance/36293 <pre>wrote: is (TBW), Wow, that seems like a pretty good deal then assuming it works and performs decently. merlin </pre> Merlin Moncure 2013-05-23T02:17:15 http://permalink.gmane.org/gmane.comp.db.postgresql.performance/36292 <pre> Make that quite a few capacitors (top right corner): http://regmedia.co.uk/2013/05/07/m500_4.jpg </pre> Mark Kirkwood 2013-05-23T02:04:32 http://permalink.gmane.org/gmane.comp.db.postgresql.performance/36291 <pre> The angle to distinguish "enterprise" hardware is moving on to error related capabilities. Soon we'll see SAS drives with the 520 byte sectors and checksumming for example. And while SATA drives have advanced a long way, they haven't caught up with SAS for failure handling. It's still far too easy for a single crazy SATA device to force crippling bus resets for example. Individual SATA ports don't expect to share things with others, while SAS chains have a much better protocol for handling things. </pre> Greg Smith 2013-05-23T01:53:31 Search the mailing list at Gmane query http://search.gmane.org/?group=$group=gmane.comp.db.postgresql.performance