Re: [GENERAL] PostgreSQL on tablet grade SSD ?
On 10/31/2014 7:31 AM, Merlin Moncure wrote: Can anyone share any experiences with running PostgreSQL on a tablet ? (Surface Pro 3, ASUS Transformer) The SSD in a modern tablet is considerably faster than a hard drive in a high-end server from the era when PG was originally developed so from a performance perspective there's no problem (provided your expectations of overall performance are realistic). The Surface 3 Pro in particular has a pretty high performance SSD. Other tablets may use lower performing devices so YMMV. I have PG installed on my Surface 3 for development purposes fwiw. As others have said, watch out for durability issues with SSDs that are not explicitly specified for server/enterprise use, although in a tablet where there is a local battery backup power source these concerns may be not too relevant. Probably not wise to run a bank on a tablet though... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Async IO HTTP server frontend for PostgreSQL
Hi Dmitriy, are you able to say a little about what's driving your quest for async http-to-pg ? I'm curious as to the motivations, and whether they match up with some of my own reasons for wanting to use low-thread-count solutions. Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SSD Drives
On 4/4/2014 5:29 PM, Lists wrote: So, spend the money and get the enterprise class SSDs. They have come down considerably in price over the last year or so. Although on paper the Intel Enterprise SSDs tend to trail the performance numbers of the leading consumer drives, they have wear characteristics that mean you can trust them as much as you can any other drive for years, and they still leave spinning rust far, far behind. Another issue to bear in mind is that SSD performance may not be consistent over time. This is because the software on the drive that manages where data lives in the NAND chips has to perform operations similar to garbage collection. Drive performance may slowly decrease over the lifetime of the drive, or worse : Consumer drives may be designed such that this GC-like activity is expected to take place when the drive is idle, which it may well be for much of the time, in a laptop. However, in a server subject to a constant load, there may never be idle time. As a result the drive may all of a sudden decide to stop processing host I/O operations while it reshuffles its blocks. Enterprise drives are designed to address this problem and are specified for longevity under a constant high workload. Performance is similarly specified over worst-case lifetime conditions (which could explain why consumer drives appear to be faster, at least initially). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SSD Drives
It would be useful to know more details -- how much storage space you need for example. fwiw I considered all of these issues when we first deployed SSDs and decided to not use RAID controllers. There have not been any reasons to re-think that decision since. However, it depends on your specific needs I think. We prefer to think in terms of a single machine as the unit of service failure -- a machine is either working, or not working, and we ensure state is replicated to several machines for durability. Therefore a storage solution on each machine that is more reliable than the machine itself is not useful. In our deployments we can't max out even one SSD, so there isn't anything a RAID controller can add in terms of performance, but your case could be different. You might also want to consider the power dissipated by the RAID controller : I was quite surprised by how much heat they generate, but this was a couple of years ago. Possibly there are lower power controllers available now. You need the capacitor on the SSD -- a RAID controller with BBU will not fix a non-power-fail-safe SSD. On 4/4/2014 10:04 AM, Steve Crawford wrote: I've been looking into upgrading to SSD and wondering about RAID and where to apply $$$ as well. In particular I'm curious about any real-world PostgreSQL-oriented performance and data-protections advice in the following areas: 1. With SSDs being orders of magnitude faster than spinning media, when does the RAID controller rather than the storage become the bottleneck? 2. Do I need both BBU on the RAID *and* capacitor on the SSD or just on one? Which one? I'm suspecting capacitor on the SSD and write-through on the RAID. 2. Current thoughts on hardware vs. software RAID - especially since many of the current SSD solutions plug straight into the bus. 3. Potential issues or conflicts with SSD-specific requirements like TRIM. 4. Manufacturers, models or technologies to seek out or avoid. 5. At what point do we consider the RAID controller an additional SPOF that decreases instead of increases reliability? 6. Thoughts on best bang for the buck? For example, am I better off dropping the RAID cards and additional drives and instead adding another standby server? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SSD Drives
On 4/4/2014 3:57 PM, Steve Crawford wrote: Judicious archiving allows us to keep our total OS+data storage requirements under 100GB. Usually. So we should be able to easily stay in the $500/drive price range (200GB S3700) and still have plenty of headroom for wear-leveling. One option I'm considering is no RAID at all but spend the savings from the controllers and extra drives toward an additional standby server. This very similar to our workload. We use a single 200G or 300G Intel SSD per machine, directly attached to the motherboard SATA controller. No RAID controller. We run 7 servers at present in this configuration in a single cluster. Roughly 120W per box peak (8-core, 64G RAM). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SSD Drives
On 4/3/2014 2:00 PM, John R Pierce wrote: an important thing in getting decent wear leveling life with SSDs is to keep them under about 70% full. This depends on the drive : drives with higher specified write endurance already have significant overprovisioning, before the user sees the space. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SSD Drives
While I have two friends who work at FusionIO, and have great confidence in their products, we like to deploy more conventional SATA SSDs at present in our servers. We have been running various versions of Intel's enterprise and data center SSDs in production for several years now and couldn't be happier with their performance. The oldest in service at present are 710 series that have been subjected to a ~500wtps PG load 7*24 for the past 28 months. They still show zero wearout indication in the SMART stats. As others have mentioned, power-fail protection (supercap) is the thing to look for, and also some sort of concrete specification for drive write endurance unless you have made a deliberate decision to trade off endurance vs. cost in the context of your deployment. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] High Level Committers Wanted
On 3/12/2014 9:34 AM, Adrian Klaver wrote: Columbia the country or the District? There's also the river... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] High Level Committers Wanted
On 3/12/2014 9:49 AM, alexandros_e wrote: It seems like spam to me. Where is the guy's name or credentials? If I would request something, I would sign with my name, government email and telephone. Or it is a 20-day-early April 1 email. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deploying PostgreSQL on CentOS with SSD and Hardware RAID
On 5/19/2013 7:19 PM, Toby Corkindale wrote: On 13/05/13 11:23, David Boreham wrote: btw we deploy on CentOS6. The only things we change from the default are: 1. add relatime,discard options to the mount (check whether the most recent CentOS6 does this itself -- it didn't back when we first deployed on 6.0). While it is important to let the SSD know about space that can be reclaimed, I gather the operation does not perform well. I *think* current advice is to leave 'discard' off the mount options, and instead run a nightly cron job to call 'fstrim' on the mount point instead. (In really high write situations, you'd be looking at calling that every hour instead I suppose) I have to admit to have just gone with the advice, rather than benchmarking it thoroughly. The guy who blogged about this a couple of years ago was using a Sandforce controller drive. I'm not sure there is a similar issue with other drives. Certainly we've never noticed a problematic delay in file deletes. That said, our applications don't delete files too often (log file purging is probably the only place it happens regularly). Personally, in the absence of a clear and present issue, I'd prefer to go the kernel guys and drive firmware guys will take care of this route, and just enable discard on the mount. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deploying PostgreSQL on CentOS with SSD and Hardware RAID
On 5/11/2013 3:10 AM, Matt Brock wrote: On 10 May 2013, at 16:25, David Boreham david_l...@boreham.org wrote: I've never looked at SLC drives in the past few years and don't know anyone who uses them these days. Because SLCs are still more expensive? Because MLCs are now almost as good as SLCs for performance/endurance? Not quite. More like : a) I don't know where to buy SLC drives in 2013 (all the drives for example for sale on newegg.com are MLC) and b) today's MLC drives are quite good enough for me (and I'd venture to say any database-related purpose). I should point out that this database will be the backend for a high-transaction gaming site with very heavy database usage including a lot of writes. Disk IO on the database server has always been our bottleneck so far. Sure, same here. I wouldn't be replying if all I did was run an SSD drive in my laptop ;) Also, the database is kept comparatively very small - about 25 GB currently, and it will grow to perhaps 50 GB this year as a result of new content and traffic coming in. Our clustering partitions the user population between servers such that each server has a database about the same size as yours. We tend to use 200 or 300G drives on each box, allowing plenty space for the DB, a copy of another server's DB, and log files. I've asked our HP dealer for this information since unfortunately it doesn't appear to be available on the HP website - hopefully it will be forthcoming at some point. This is a bit of a red flag for me. During the qualification process for our SSD drives we: read the technical papers from Intel; ran lab tests where we saturated a drive with writes for weeks, checking the write endurance SMART data and operation latency; modified smartools so it could read all the useful drive counters, and also reset the wear estimation counters; performed power cable pull tests; read everything posted on this list by people who had done serious testing in addition to the tests we ran in house. I'm not sure I'd want to deploy Joe Random SSD du jour that HP decided to ship me. You might consider buying boxes sans drives and fitting your own, of a known trusted type. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deploying PostgreSQL on CentOS with SSD and Hardware RAID
btw we deploy on CentOS6. The only things we change from the default are: 1. add relatime,discard options to the mount (check whether the most recent CentOS6 does this itself -- it didn't back when we first deployed on 6.0). 2. Disable swap. This isn't strictly an SSD tweak, since we have enough physical memory to not need to swap, but it is a useful measure for us since the default install always creates a swap partition which a) uses valuable space on the smaller-sized SSDs, and b) if there are ever writes to the swap partition it would be bad for wear on the entire drive. We also setup monitoring of the drives' smart wear counter to ensure early warning of any drive coming close to wear out. We do not use (and don't like) RAID with SSDs. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deploying PostgreSQL on CentOS with SSD and Hardware RAID
On 5/12/2013 7:20 PM, John R Pierce wrote: the real SLC drives end up OEM branded in large SAN systems, such as sold by Netapp, EMC, and are made by companies like STEC that have zero presence in the 'whitebox' resale markets like Newegg. Agreed. I don't go near the likes of Simple, HGST, F-IO, SMART, et al. For me this is SAS and SCSI re-born -- an excuse to charge very high prices for a product not significantly different from a much cheaper mainstream alternative, by exploiting unsophisticated purchasers with tales of enterprise snake oil. ymmv, but since I am spending my own $$, I'll stick to product I can order from the likes of Newegg and Amazon. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deploying PostgreSQL on CentOS with SSD and Hardware RAID
On 5/10/2013 9:19 AM, Matt Brock wrote: After googling this for a while, it seems that High Endurance MLC is only starting to rival SLC for endurance and write performance in the very latest, cutting-edge hardware. In general, though, it seems it would be fair to say that SLCs are still a better bet for databases than MLC? I've never looked at SLC drives in the past few years and don't know anyone who uses them these days. The number and capacity of drives is small in this instance, and the price difference between the two for HP SSDs isn't very wide, so cost isn't really an issue. We just want to use whichever is better for the database. Could you post some specific drive models please ? HP probably doesn't make the drives, and it really helps to know what devices you're using since they are not nearly as generic in behavior and features as magnetic drives. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deploying PostgreSQL on CentOS with SSD and Hardware RAID
On 5/10/2013 10:21 AM, Merlin Moncure wrote: As it turns out the list of flash drives are suitable for database use is surprisingly small. The s3700 I noted upthread seems to be specifically built with databases in mind and is likely the best choice for new deployments. The older Intel 320 is also a good choice. I think that's pretty much it until you get into expensive pci-e based gear. This may have been a typo : did you mean Intel 710 series rather than 320 ? While the 320 has the supercap, it isn't specified for high write endurance. Definitely usable for a database, and a better choice than most of the alternatives, but I'd have listed the 710 ahead of the 320. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deploying PostgreSQL on CentOS with SSD and Hardware RAID
On 5/10/2013 11:20 AM, Merlin Moncure wrote: I find the s3700 to be superior to the 710 in just about every way (although you're right -- it is suitable for database use). merlin The s3700 series replaces the 710 so it should be superior :) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Deploying PostgreSQL on CentOS with SSD and Hardware RAID
On 5/10/2013 11:23 AM, Lonni J Friedman wrote: There's also the 520 series, which has better performance than the 320 series (which is EOL now). I wouldn't use the 520 series for production database storage -- it has the Sandforce controller and apparently no power failure protection. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hosting PG on AWS in 2013
On 4/8/2013 3:15 AM, Vincent Veyron wrote: Could someone explain to me the point of using an AWS instance in the case of the OP, whose site is apparently very busy, versus renting a bare metal server in a datacenter? I am the OP, but I can't provide a complete answer, since personally (e.g. all servers that my income depends on), I do not use cloud hosting. However, some reasons I have heard mentioned include: 1. The rest of the site is already hosted in AWS so deploying a DB outside AWS adds to network costs, latency, and adds yet more moving parts and things to worry about. 2. These days many companies just do not have the capability to deploy bare metal. The people who understand how to do it are gone, the management feel like it is outside their comfort zone vs. Cloud, and so on. Conversely, there are plenty of people you can hire who are familiar with AWS, its deployment tools, management, monitoring and so on. 3. Peer pressure to use AWS (from finance people, VCs, industry pundits, etc). 4. AWS is the new IBM Mainframe (nobody ever got fired for buying one...). If 1/2 the Internet is on AWS, and something breaks, then well...you can point to the 1/2 the Internet that's also down.. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Hosting PG on AWS in 2013
I thanks very much for your detailed response. A few answers below inline: On 4/7/2013 9:38 AM, Tomas Vondra wrote: As for the performance, AFAIK the EBS volumes always had, and probably will have, a 32 MB/s limit. Thanks to caching, built into the EBS, the performance may seem much better initially (say twice as good), but after a sustained write workload (say 15-30 minutes), you're back at the 32 MB/s per volume. The main problem with regular EBS is the variability - the numbers above are for cases where everything operates fine. When something goes wrong, you can get 1 MB/s for a period of time. And when you create 10 volumes, each will have a bit different performance. There are ways to handle this, though - the old way is to build a RAID10 array on top of regular EBS volumes, the new way is to use EBS with Provisioned IOPS (possibly with RAID0). Just to set the scene -- the application is a very high traffic web service where any down time is very costly, processing a few hundred transactions/s. What high traffic means for the database? Does that mean a lot of reads or writes, or something else? I should have been more clear : the transactions/s above is all writes. The read load is effectively cached. My assessment is that the load is high enough that careful attention must be paid to I/O performance, but no so high that sharding/partitioning is required (yet). Part of the site is already using RDS with PIOPS, and runs at a constant 500 w/s, as viewed in CloudWatch. I don't know for sure how the PG-based elements relate to this on load -- they back different functional areas of the site. Scanning through the latest list of AWS instance types, I can see two plausible approaches: 1. High I/O Instances: (regular AWS instance but with SSD local storage) + some form of replication. Replication would be needed because (as I understand it) any AWS instance can be vanished at any time due to Amazon screwing something up, maintenance on the host, etc (I believe the term of art is ephemeral). Yes. You'll get great I/O performance with these SSD-based instances (easily ~1GB/s in), so you'll probably hit CPU bottlenecks instead. You're right that to handle the instance / ephemeral failures, you'll have to use some sort of replication - might be your custom application-specific application, or some sort of built-in (async/sync streamin, log shipping, Slony, Londiste, whatever suits your needs ...). If you really value the availability, you should deploy the replica in different availability zone or data center. 2. EBS-Optimized Instances: these allow the use of EBS storage (SAN-type service) from regular AWS instances. Assuming that EBS is maintained to a high level of availability and performance (it doesn't, afaik, feature the vanishing property of AWS machines), this should in theory work out much the same as a traditional cluster of physical machines using a shared SAN, with the appropriate voodoo to fail over between nodes. No, that's not what EBS Optimized instances are for. All AWS instance types can use EBS, using a SHARED network link. That means that e.g. HTTP or SSH traffic influences EBS performance, because they use the same ethernet link. The EBS Optimized says that the instance has a network link dedicated for EBS traffic, with guaranteed throughput. Ah, thanks for clarifying that. I knew about PIOPS, but hadn't realized that EBS Optimized meant a dedicated SAN cable. Makes sense... That is not going to fix the variability or EBS performance, though ... What you're looking for is called Provisioned IOPS (PIOPS) which guarantees the EBS volume performance, in terms of IOPS with 16kB block. For example you may create an EBS volume with 2000 IOPS, which is ~32MB/s (with 16kB blocks). It's not much, but it's much easier to build RAID0 array on top of those volumes. We're using this for some of our databases and are very happy with it. Obviously, you want to use PIOPS with EBS Optimized instances. I don't see much point in using only one of them. But still, depends on the required I/O performance - you can't really get above 125MB/s (m2.4xlarge) or 250MB/s (cc2.8xlarge). I don't forsee this application being limited by bulk data throughput (MB/s). It will be limited more by writes/s due to the small transaction, OLTP-type workload. And you can't really rely on this if you need quick failover to a different availability zone or data center, because it's quite likely the EBS is going to be hit by the issue (read the analysis of AWS outage from April 2011: http://aws.amazon.com/message/65648/). Right, assume that there can be cascading and correlated failures. I'm not sure I could ever convince myself that a cloud-hosted solution is really safe, because honestly I don't trust Amazon to design out their single failure points and thermal-runaway problems. However in the industry now there seems to be wide acceptance of the view that if you're
[GENERAL] Hosting PG on AWS in 2013
First I need to say that I'm asking this question on behalf of a friend, who asked me what I thought on the subject -- I host all the databases important to me and my livelihood, on physical machines I own outright. That said, I'm curious as to the current thinking on a) whether it is wise, and b) if so how to deploy, PG servers on AWS. As I recall, a couple years ago it just wasn't a wise plan because Amazon's I/O performance and reliability wasn't acceptable. Perhaps that's no longer the case.. Just to set the scene -- the application is a very high traffic web service where any down time is very costly, processing a few hundred transactions/s. Scanning through the latest list of AWS instance types, I can see two plausible approaches: 1. High I/O Instances: (regular AWS instance but with SSD local storage) + some form of replication. Replication would be needed because (as I understand it) any AWS instance can be vanished at any time due to Amazon screwing something up, maintenance on the host, etc (I believe the term of art is ephemeral). 2. EBS-Optimized Instances: these allow the use of EBS storage (SAN-type service) from regular AWS instances. Assuming that EBS is maintained to a high level of availability and performance (it doesn't, afaik, feature the vanishing property of AWS machines), this should in theory work out much the same as a traditional cluster of physical machines using a shared SAN, with the appropriate voodoo to fail over between nodes. Any thoughts, wisdom, and especially from-the-trenches experience, would be appreciated. In the Googlesphere I found this interesting presentation : http://www.pgcon.org/2012/schedule/attachments/256_pg-aws.pdf which appears to support option #2 with s/w (obviously) RAID on the PG hosts, but with replication rather than SAN cluster-style failover, or perhaps in addition to. Note that I'm not looking for recommendations on PG hosting providers (in fact my friend is looking to transition off one of them, to bare-AWS machines, for a variety of reasons). Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Linux Distribution Preferences?
I'm not sure the last time I saw this discussion, but I was somewhat curious: what would be your ideal Linux distribution for a nice solid PostgreSQL installation? We've kinda bounced back and forth between RHEL, CentOS, and Ubuntu LTS, so I was wondering what everyone else thought. We run CentOS (mixture of 5 and 6, but 6 in all newer installations). I've never used Ubuntu so can't comment on it. We get PG from the PGDG repository, after disabling the distribution's PG installation in order to maintain tight control over the build/version. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] large database
On 12/10/2012 1:26 PM, Mihai Popa wrote: Second, where should I deploy it? The cloud or a dedicated box? Amazon seems like the sensible choice; you can scale it up and down as needed and backup is handled automatically. I was thinking of an x-large RDS instance with 1 IOPS and 1 TB of storage. Would this do, or will I end up with a larger/ more expensive instance? Alternatively I looked at a Dell server with 32 GB of RAM and some really good hard drives. But such a box does not come cheap and I don't want to keep the pieces if it doesn't cut it Note that it will be much cheaper to buy a machine from the likes of Newegg or Amazon as parts and put it together yourself, if you have the time to spare and don't care about a Dell warranty. We've been deploying 64G hex-core Xeon Ivy Bridge boxes with 300G SSD for about $2500. Another 300G SSD for your database size would add about $1200. The newer Intel DC-series SSDs should be cheaper, but are not yet available. So as someone else pointed out you could buy a very capable box outright for the cost of a few months Amazon fees. I'm not sure I'd worry too much about how to do backups (Amazon just copies the data the same as you can, to an external drive) but if you need things like spare machines, pay a human to manage them and so on, then there are cost benefits to the cloud approach. It also allows you to blame someone else if something goes wrong. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] large database
On 12/11/2012 8:28 AM, Mihai Popa wrote: I guess Chris was right, I have to better understand the usage pattern and do some testing of my own. I was just hoping my hunch about Amazon being the better alternative would be confirmed, but this does not seem to be the case; most of you recommend purchasing a box. Amazon (or another PG cloud provider such as Heroku) is a great choice if you want to do some sizing tests. However, beware that if you need big instances running for more than a week or two, you may spend as much in fees as it would have cost to buy a big machine outright. Cloud services are highly economic where you need resources that are significantly _less_ than a present-day physical machine provides. For example I have a VM with 500MB at Rackspace that I can use to run Nagios to check on my physical servers, located in a different state on different peering. I think that costs something like $15/mo. I couldn't locate any kind of physical box in a new data center for anything like that little. But where we need all the resources provided by the biggest box you can buy today (and several of those), it is an order of magnitude cheaper to buy them and pay for colo space. Similarly, if I needed machines for only a short time (to test something, for example), cloud hosting is a nice option vs having a big pile of metal in the corner of the office that you don't know what to do with... Finally, note that there is a middle-ground available between cloud hosting and outright machine purchase -- providers such as Linode and SoftLayer will sell physical machines in a way that gives much of the convenience of cloud hosting, but with the resource dedication and consistency of performance of physical machines. Still not as cheap as buying your own machines of course, if you need them long-term. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] large database
On 12/11/2012 2:03 PM, Mihai Popa wrote: I actually looked at Linode, but Amazon looked more competitive... Checking Linode's web site just now it looks like they have removed physical machines as an option. Try SoftLayer instead for physical machines delivered on-demand : http://www.softlayer.com/dedicated-servers/ If you're looking for low cost virtual hosting alternative to Amazon, try Rackspace. Different providers offer different features too. For example Rackspace allows you to add SSD persistent storage to any node (at a price) whereas Amazon currently doesn't offer that capability. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL and a clustered file system
On 11/12/2012 1:52 PM, Gunnar Nick Bluth wrote: Am 12.11.2012 11:03, schrieb Ivan Voras: Is anyone running PostgreSQL on a clustered file system on Linux? By clustered I actually mean shared, such that the same storage is mounted by different servers at the same time (of course, only one instance of PostgreSQL on only one server can be running on such a setup, and there are a lot of other precautions that need to be satisfied). TBTH, I don't see the potential benefit. Clustered filesystems have benefits for special use cases (e.g. redundant fileservers or applications that actually can work in parallel, relying on file locking, DB clusters that coordinate writes themselves, ...), but PG certainly is not one of these... Although I'm also a non-fan of database over clustered filesystems, I wanted to speak up here since I have a hunch the OP wasn't looking for the solution you're thinking of. I think he's asking if folk have run an HA setup with PG where the database files are stored in a dual-ported/clustered filesystem, the idea being that you can have a failure in the hardware for one DB server, and make the other one take over using the same underlying files. I've seen this done. You need some plumbing to ensure that only one PG instance can run at the same time. From memory, there are HA monitor tools available that do this in a generic way. As far as PG is concerned it has no idea there is a shared access filesystem, and so it has no need to perform file-level locking. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Message: incomplete startup packet
On 11/8/2012 2:05 PM, Rodrigo Pereira da Silva wrote: Hi Guys, We are having a problem with our pgsql 9.1 on Linux(Debian). Suddently, the database stop working and the logs shows the statements below just before the problem. Any thoughts? Just a word of caution that there may be no cause/effect relationship between the log messages and the non-working-ness. PG will print this message in the log if any TCP client simply connects to it, then closes the connection. For example if you have some kind of service liveness checker (nagios, etc) that checks that something is accepting connections, you'll see these messages. Note that they have time stamps exactly one minute apart. 2012-11-08 02:47:12.529 CST 08P01 509b7190.4583LOG: incomplete startup packet 2012-11-08 02:48:12.534 CST 08P01 509b71cc.458bLOG: incomplete startup packet 2012-11-08 02:49:12.541 CST 08P01 509b7208.4593LOG: incomplete startup packet -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Plug-pull testing worked, diskchecker.pl failed
On 11/7/2012 3:17 PM, Vick Khera wrote: My most recent big box(es) are built using all Intel 3xx series drives. Like you said, the 7xx series was way too expensive. I have to raise my hand to say that for us 710 series drives are an unbelievable bargain and we buy nothing else now for production servers. When you compare vs the setup you'd need to achieve the same tps using rotating media, and especially considering the power and cooling saved, they're really cheap. YMMV of course.. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Too far out of the mainstream
I dunno, perhaps I don't get out the office enough, but I just don't hear about MySQL any more. I think this thread is tilting at windmills. A few years ago about 1 in 2 contracts we had was with a start-up using MySQL. The other half were using either PG or Oracle or SQLServer. The years before that, pre-dot-com-crash, every start-up used Oracle, presumably because Larry had some Vulcan mind grip over the VCs. Then Oracle acquired MySQL any anyone with a brain and some imagination figured out where that would lead eventually. So today everyone I meet is either using PostgreSQL or some web scale store like Raik, MondoDB, Cassandra. MySQL is nowhere to be seen. I'm not sure if that's because folks migrated from MySQL to something else, or because the MySQL-using companies were the ones that went out of business. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Too far out of the mainstream
On 9/1/2012 6:42 AM, Edson Richter wrote: Nevertheless, when we present our product to customers, they won't get satisfied until we guarantee we can run same product with major paid versions (Oracle, MS SQL, and so on). I think this is a business problem not a technology problem. Forget trying to persuade these folks that your solution is a good one. It is better instead to just say ok, you can have X (Oracle, whatever) and the price will be y (quite large number). In my experience a customer in this situation will suddenly become much less entrenched in their belief that your solution is not suitable. Shift the frame to be about money (easily quantifiable, and something the customer wants to keep) rather than technical arguments (hard to quantify, win, pin down, cheap and easy for the customer to argue about). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Amazon High I/O instances
On 8/21/2012 7:10 AM, Oliver Kohll - Mailing Lists wrote: This is a general 'cloud or dedicated' question, I won't go into it but I believe cloud proponents cite management ease, scalability etc. I'm sure there's a place for every type of hosting. However I would be interested in hearing some experiences of PostgreSQL on an Amazon high I/O instance, given a client has just proposed running on one. If there are none forthcoming in the short term I may be in a position to provide some results myself in a month or two. Amazon don't say what vendor's SSDs they are using, which is a little worrying to me -- when we deployed our SSD-based machines last year, much work was done to address the risk of write endurance problems. Now, an AWS instance and its ephemeral storage isn't expected to live forever (keep that in mind when storing data on one!) so perhaps one can ignore write endurance as a concern in this case since we'd already be worried about (and have a plan to address) entire machine endurance. For sure performance on these instances for any I/O limited application is going to be great. I have a friend who is looking at them for his big data analytics application which spends most of its time sorting Tb sized files. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Amazon High I/O instances
On 8/21/2012 2:18 AM, Vincent Veyron wrote: I wonder : is there a reason why you have to go through the complexity of such a setup, rather than simply use bare metal and get good performance with simplicity? In general I agree -- it is much (much!) cheaper to buy tin and deploy yourself vs any of the current cloud services. However, there are plenty of counterexample use cases : for example what if you want one of these machines for a week only? Another one : what if you are a venture capitalist funding 10 companies with questionable business models where you expect only one to succeed? AWS saves you from the headache of selling 500 machines on eBay... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Backing up through a database connection (not pg_dump)
fwiw we run db_dump locally, compress the resulting file and scp or rsync it to the remote server. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Server choice for small workload : raptors or SSD?
We've used Raptors in production for a few years. They've been about as reliable as you'd expect for hard drives, with the additional excitement of a firmware bug early on that led to data loss and considerable expense. New machines deployed since November last year have 710 SSDs. No problems so far, blindingly fast, very low power. Unless I needed to store vast amounts of data, or was required to use very cheap hardware, there's nothing that would make me go back to hard drives. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] what Linux to run
Long thread - figured may as well toss in some data: We use CentOS 5 and 6 and install PG from the yum repository detailed on the postgresql.org web site. We've found that the PG shipped as part of the OS can never be trusted for production use, so we don't care what version ships with the OS -- we'll never use it. Regarding JDK7 : some interesting GC features, but as a whole too scary from a stability perspective to commit to in production. Considering most of the good engineers have likely left due to Oracle management, this is an area where we'll let others debug for a year or so before considering adopting. Regarding missing packages from desktop install : for production servers we use the minimal install then explicitly add the packages we need that are not part of that. From experience desktop distributions will install stuff that a) creates security risks and/or b) creates stability risks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] what Linux to run
On 3/3/2012 7:05 PM, Tom Lane wrote: [ raised eyebrow... ] As the person responsible for the packaging you're dissing, I'd be interested to know exactly why you feel that the Red Hat/CentOS PG packages can never be trusted. Certainly they tend to be from older release branches as a result of Red Hat's desire to not break applications after a RHEL branch is released, but they're not generally broken AFAIK. No dissing intended. I didn't say or mean that OS-delivered PG builds were generally broken (although I wouldn't be entirely surprised to see that happen in some distributions, present company excluded). I'm concerned about things like : a) Picking a sufficiently recent version to get the benefit of performance optimizations, new features and bug fixes. b) Picking a sufficiently old version to reduce the risk of instability. c) Picking a version that is compatible with the on-disk data I already have on some set of existing production machines. d) Deciding which point releases contain fixes that are relevant to our deployment. Respectfully, I don't trust you to come to the correct choice on these issues for me every time, or even once. I stick by my opinion that anyone who goes with the OS-bundled version of a database server, for any sort of serious production use, is making a mistake. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incomplete startup packet help needed
On 1/23/2012 5:24 PM, David Johnston wrote: Immediately upon starting the server I get an incomplete startup packet log message. Just prior there is an autovacuum launcher started message. We've found that this message is printed in the log if a client makes a TCP connection to the PG server, but sends no traffic. For example it happens with our monitoring system, which checks that it can open a TCP connection to the PG port, then closes it. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Recommendations for SSDs in production?
On 11/4/2011 8:26 AM, Yeb Havinga wrote: First, if your'e interested in doing a test like this yourself, I'm testing on ubuntu 11.10, but even though this is a brand new distribution, the smart database was a few months old. 'update-smart-drivedb' had as effect that the names of the values turned into something useful: instead of #LBA's written, it now shows #32MiB's written. Also there are now three 'workload' related parameters. I submitted the patch for these to smartmontools a few weeks ago and it is now in the current db but not yet in any of the distro update packages. I probably forgot to mention in my post here that you need the latest db for the 710. Also, if you pull the trunk source code and build it yourself it has the ability to decode the drive stats log data (example pasted below). I haven't yet found a use for this myself, but it does seem to have a little more informaiton than the SMART attributes. (Thanks to Christian Franke of the smartmontools project for implementing this feature) Your figures from the workload wear roughly match mine. In production we don't expect to subject the drives to anything close to 100% of the pgbench workload (probably around 1/10 of that on average), so the predicted wear life of the drive is 10+ years in our estimates, under production loads. The big question of course is can the drive's wearout estimate be trusted ? A little more information from Intel about how it is calculated would help allay concerns in this area. # ./smartctl -l devstat,0 /dev/sda smartctl 5.42 2011-10-10 r3434 [x86_64-linux-2.6.32-71.29.1.el6.x86_64] (local build) Copyright (C) 2002-11 by Bruce Allen,http://smartmontools.sourceforge.net Device Statistics (GP Log 0x04) supported pages Page Description 0 List of supported log pages 1 General Statistics 4 General Errors Statistics 5 Temperature Statistics 6 Transport Statistics 7 Solid State Device Statistics # ./smartctl -l devstat /dev/sda smartctl 5.42 2011-10-10 r3434 [x86_64-linux-2.6.32-71.29.1.el6.x86_64] (local build) Copyright (C) 2002-11 by Bruce Allen,http://smartmontools.sourceforge.net Device Statistics (GP Log 0x04) Page Offset Flg Size Value Description 1 = == = = == General Statistics (rev 2) == 1 0x008 V- 4 10 Lifetime Power-On Resets 1 0x010 V- 4200 Power-on Hours 1 0x018 V- 6 3366822529 Logical Sectors Written 1 0x020 V- 6 248189788 Number of Write Commands 1 0x028 V- 6 54653524 Logical Sectors Read 1 0x030 V- 62626204 Number of Read Commands 4 = == = = == General Errors Statistics (rev 1) == 4 0x008 V- 4 0 Number of Reported Uncorrectable Errors 4 0x010 V- 4 0 Resets Between Cmd Acceptance and Completion 5 = == = = == Temperature Statistics (rev 1) == 5 0x008 V- 1 21 Current Temperature 5 0x010 V- 1 20 Average Short Term Temperature 5 0x018 -- 1 20 Average Long Term Temperature 5 0x020 V- 1 30 Highest Temperature 5 0x028 V- 1 17 Lowest Temperature 5 0x030 V- 1 23 Highest Average Short Term Temperature 5 0x038 V- 1 18 Lowest Average Short Term Temperature 5 0x040 -- 1 -128 Highest Average Long Term Temperature 5 0x048 -- 1 -128 Lowest Average Long Term Temperature 5 0x050 V- 4 0 Time in Over-Temperature 5 0x058 V- 1 70 Specified Maximum Operating Temperature 5 0x060 V- 4 0 Time in Under-Temperature 5 0x068 V- 1 0 Specified Minimum Operating Temperature 6 = == = = == Transport Statistics (rev 1) == 6 0x008 V- 4 77 Number of hardware resets 6 0x010 V- 4 22 Number of ASR Events 6 0x018 V- 4 0 Number of Interface CRC Errors 7 = == = = == Solid State Device Statistics (rev 1) == 7 0x008 V- 1 0 Percentage Used Endurance Indicator ||_ N normalized |__ V valid -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Recommendations for SSDs in production?
On 11/2/2011 11:01 AM, Benjamin Smith wrote: 2) Intel X25E - good reputation, significantly slower than the Vertex3. We're buying some to reduce downtime. If you don't mind spending money, look at the new 710 Series from Intel. Not SLC like the X25E, but still specified with a very high write endurance. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Another RAID controller recommendation question
On 6/18/2011 1:22 AM, Greg Smith wrote: That said, the card itself looks like plain old simple LSI MegaRAID. Get the battery backup unit Thanks. Dell's web site drives me insane, and it appears I can save 20% or more by going white-box. One thing I don't understand is why is the BBU option never available with integrated LSI controllers? It appears that anything with a BBU/WBC capability immediately adds $700 or so to the machine price. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Another RAID controller recommendation question
We're looking to deploy a bunch of new machines. Our DB is fairly small and write-intensive. Most of the disk traffic is PG WAL. Historically we've avoided RAID controllers for various reasons, but this new deployment will be done with them (also for various reasons ;) We like to use white-boxish machines and we run CentOS. This would be a good example of the kind of machine we'd buy: http://www.newegg.com/Product/Product.aspx?Item=N82E16816101339 manufacturer page : http://www.supermicro.com/products/system/1U/6016/SYS-6016T-URF4_.cfm?UIO=N these boxes have a proprietary controller slot, with these cards: http://www.supermicro.com/products/nfo/UIO.cfm#Adapters specifically this LSI-based one which seems to be the newest/fastest, with BBWBC: http://www.supermicro.com/products/accessories/addon/AOC-USAS2LP-H8iR.cfm I'd be interested to hear any options good or bad on these controllers, or ideas for alternatives. These machines are operated in a lights-out mode, and will handle heavy constant load (hundreds of write txn/s) with 15K SAS drives in a RAID-1 setup (2 drives, or 2 + 2 with data and WAL split between spindle groups). Thanks! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Mixed up protocol packets in server response?
transaction. No passing connections by hand anywhere, everything should be nicely thread-bound. Still, if not here, where could it go wrong? I have seen two cases in my career where there was an evil box on the network that corrupted the traffic. The first was a very long time ago (in the late '80s) but the second was only a couple of years ago and presented with very similar symptoms to your report. This happened at a consulting client's site (actually between two sites). Weird franken-packets showed up once in a while, leading to a protocol decode failure. Luckily we had been involved in writing both the client and the server, and therefore had a high degree of confidence that they were correct. The network administrators denied strongly that they had any equipment deployed that touched the payload of any packet. They denied this several times. Eventually we were able to take packet traces on both client and server machines, correlate the traffic (not necessarily an easy task), and prove conclusively that what had been sent from one end did not show up intact at the other end. A few days later the network people revealed that they had some sort of firewall/traffic management box that was mangling the traffic. Having said that, bugs in buffer management code are also not uncommon, and can manifest intermittently since they may be triggered by specific boundary conditions, specific received data buffer size, and so on. I have also seen once case of data leaking between threads in an unpleasant and intermittent way in a Java application, in buffer management code that attempted to avoid GC overhead by re-using buffers across sessions. So that's definitely a non-zero possibility too. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Fwd: Re: [GENERAL] SSDD reliability
On 5/5/2011 2:36 AM, Florian Weimer wrote: I'm a bit concerned with usage-dependent failures. Presumably, two SDDs in a RAID-1 configuration are weared down in the same way, and it would be rather inconvenient if they failed at the same point. With hard disks, this doesn't seem to happen; even bad batches fail pretty much randomly. fwiw this _can_ happen with traditional drives : we had a bunch of WD 300G velociraptor drives that had a firmware bug related to a 32-bit counter roll-over. This happened at exactly the same time for all drives in a machine (because the counter counted since power-up time). Needless to say this was quite frustrating ! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SSDD reliability
On 5/4/2011 11:50 PM, Toby Corkindale wrote: In what way has the SMART read failed? (I get the relevant values out successfully myself, and have Munin graph them.) Mis-parse :) It was my _attempts_ to read SMART that failed. Specifically, I was able to read a table of numbers from the drive, but none of the numbers looked particularly useful or likely to be a time to live number. Similar to traditional drives, where you get this table of numbers that are either zero or random, that you look at saying Huh?, all of which are flagged as failing. Perhaps I'm using the wrong SMART groking tools ? I do have to wonder if this Portman Wills guy was somehow Doing It Wrong to get a 100% failure rate over eight disks.. There are people out there who are especially highly charged. So if he didn't wear out the drives, the next most likely cause I'd suspect is that he ESD zapped them. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SSDD reliability
On 5/5/2011 8:04 AM, Scott Ribe wrote: Actually, any of us who really tried could probably come up with a dozen examples--more if we've been around for a while. Original design cutting corners on power regulation; final manufacturers cutting corners on specs; component manufacturers cutting corners on specs or selling outright counterfeit parts... These are excellent examples of failure causes for electronics, but they are not counter-examples. They're unrelated to the discussion about SSD early lifetime hard failures. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SSDD reliability
On 5/4/2011 11:15 AM, Scott Ribe wrote: Sigh... Step 2: paste link in ;-) http://www.codinghorror.com/blog/2011/05/the-hot-crazy-solid-state-drive-scale.html To be honest, like the article author, I'd be happy with 300+ days to failure, IF the drives provide an accurate predictor of impending doom. That is, if I can be notified this drive will probably quit working in 30 days, then I'd arrange to cycle in a new drive. The performance benefits vs rotating drives are for me worth this hassle. OTOH if the drive says it is just fine and happy, then suddenly quits working, that's bad. Given the physical characteristics of the cell wear-out mechanism, I think it should be possible to provide a reasonable accurate remaining lifetime estimate, but so far my attempts to read this information via SMART have failed, for the drives we have in use here. FWIW I have a server with 481 days uptime, and 31 months operating that has an el-cheapo SSD for its boot/OS drive. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Fwd: Re: [GENERAL] SSDD reliability
No problem with that, for a first step. ***BUT*** the failures in this article and many others I've read about are not in high-write db workloads, so they're not write wear, they're just crappy electronics failing. As a (lapsed) electronics design engineer, I'm suspicious of the notion that a subassembly consisting of solid state devices surface-mounted on FR4 substrate will fail except in very rare (and of great interest to the manufacturer) circumstances. And especially suspicious that one product category (SSD) happens to have a much higher failure rate than all others. Consider that an SSD is much simpler (just considering the electronics) than a traditional disk drive, and subject to less vibration and heat. Therefore one should see disk drives failing at the same (or higher rate). Even if the owner is highly statically charged, you'd expect the to destroy all categories of electronics at roughly the same rate (rather than just SSDs). So if someone says that SSDs have failed, I'll assume that they suffered from Flash cell wear-out unless there is compelling proof to the contrary. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Fwd: Re: [GENERAL] SSDD reliability
On 5/4/2011 6:02 PM, Greg Smith wrote: On 05/04/2011 03:24 PM, David Boreham wrote: So if someone says that SSDs have failed, I'll assume that they suffered from Flash cell wear-out unless there is compelling proof to the contrary. I've been involved in four recovery situations similar to the one described in that coding horror article, and zero of them were flash wear-out issues. The telling sign is that the device should fail to read-only mode if it wears out. That's not what I've seen happen though; what reports from the field are saying is that sudden, complete failures are the more likely event. Sorry to harp on this (last time I promise), but I somewhat do know what I'm talking about, and I'm quite motivated to get to the bottom of this SSDs fail, but not for the reason you'd suspect syndrome (because we want to deploy SSDs in production soon). Here's my best theory at present : the failures ARE caused by cell wear-out, but the SSD firmware is buggy in so far as it fails to boot up and respond to host commands due to the wear-out state. So rather than the expected outcome (SSD responds but has read-only behavior), it appears to be (and is) dead. At least to my mind, this is a more plausible explanation for the reported failures vs. the alternative (SSD vendors are uniquely clueless at making basic electronics subassemblies), especially considering the difficulty in testing the firmware under all possible wear-out conditions. One question worth asking is : in the cases you were involved in, was manufacturer failure analysis performed (and if so what was the failure cause reported?). The environment inside a PC of any sort, desktop or particularly portable, is not a predictable environment. Just because the drives should be less prone to heat and vibration issues doesn't mean individual components can't slide out of spec because of them. And hard drive manufacturers have a giant head start at working out reliability bugs in that area. You can't design that sort of issue out of a new product in advance; all you can do is analyze returns from the field, see what you screwed up, and do another design rev to address it. That's not really how it works (I've been the guy responsible for this for 10 years in a prior career, so I feel somewhat qualified to argue about this). The technology and manufacturing processes are common across many different types of product. They either all work , or they all fail. In fact, I'll eat my keyboard if SSDs are not manufactured on the exact same production lines as regular disk drives, DRAM modules, and so on (manufacturing tends to be contracted to high volume factories that make all kinds of things on the same lines). The only different thing about SSDs vs. any other electronics you'd come across is the Flash devices themselves. However, those are used in extraordinary high volumes all over the place and if there were a failure mode with the incidence suggested by these stories, I suspect we'd be reading about it on the front page of the WSJ. Intel claims their Annual Failure Rate (AFR) on their SSDs in IT deployments (not OEM ones) is 0.6%. Typical measured AFR rates for mechanical drives is around 2% during their first year, spiking to 5% afterwards. I suspect that Intel's numbers are actually much better than the other manufacturers here, so a SSD from anyone else can easily be less reliable than a regular hard drive still. Hmm, this is speculation I don't support (non-intel vendors have a 10x worse early failure rate). The entire industry uses very similar processes (often the same factories). One rogue vendor with a bad process...sure, but all of them ?? For the benefit of anyone reading this who may have a failed SSD : all the tier 1 manufacturers have departments dedicated to the analysis of product that fails in the field. With some persistence, you can usually get them to take a failed unit and put it through the FA process (and tell you why it failed). For example, here's a job posting for someone who would do this work : http://www.internmatch.com/internships/4620/intel/ssd-failure-analysis-intern-592345 I'd encourage you to at least try to get your failed devices into the failure analysis pile. If units are not returned, the manufacturer never finds out what broke, and therefore can't fix the problem. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: Fwd: Re: [GENERAL] SSDD reliability
On 5/4/2011 9:06 PM, Scott Marlowe wrote: Most of it is. But certain parts are fairly new, i.e. the controllers. It is quite possible that all these various failing drives share some long term ~ 1 year degradation issue like the 6Gb/s SAS ports on the early sandybridge Intel CPUs. If that's the case then the just plain up and dying thing makes some sense. That Intel SATA port circuit issue was an extraordinarily rare screwup. So ok, yeah...I said that chips don't just keel over and die mid-life and you came up with the one counterexample in the history of the industry :) When I worked in the business in the 80's and 90's we had a few things like this happen, but they're very rare and typically don't escape into the wild (as Intel's pretty much didn't). If a similar problem affected SSDs, they would have been recalled and lawsuits would be underway. SSDs are just not that different from anything else. No special voodoo technology (besides the Flash devices themselves). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SSDs with Postgresql?
One thing to remember in this discussion about SSD longevity is that the underlying value of interest is the total number of erase cycles, per block, on the flash devices. Vendors quote lifetime as a number of bytes, but this is calculated using an assumed write amplification factor. That constant varies with the workload, and I suspect that a database WAL will not produce the value used in the vendor marketing material. I don't think you can simply say that I am writing so many Gb WAL files, therefore according to the vendor's spec the device should have lifetime of X years -- presumably you'd need to know how many fsync() calls per second were being made, and then how those translate to flash block erase rate on the far side of the SSD controller firmware. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] SSDs with Postgresql?
On 4/28/2011 8:20 AM, Scott Ribe wrote: I don't think you can simply say that I am writing so many Gb WAL files, therefore according to the vendor's spec Also, I fully expect the vendors lie about erase cycles as baldly as they lie about MTBF, so I would divide by a very healthy skepticism factor. As a former card-carrying semiconductor company employee, I'm not so sure about this. I'd expect a healthy guard-band on the erase cycles spec (so if they say 100K, the devices will actually achieve better than 2X). MTBF otoh is a mythical computed value that everyone takes with a big pinch of salt, in my experience. I'm not sure it is possible to lie about MTBF since it's essentially just the result of a calculation based on the number of contacts, cells, traces, etc and the known failure mechanisms for those things. Therefore it will be true, but not necessarily useful (e.g it does not take account of process defects or an aging mechanism that were not known at the time of manufacture). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] The first dedicated PostgreSQL forum
On 11/13/2010 3:31 PM, LazyTrek wrote: Do the long standing members not have problems with spam? As you can see I use a list alias. However, in my experience the notion that you can avoid spam by not frequenting mailing lists is quaint to say the least. The spammers have had ways to find, steal and guess your email address for many years. I'll say that if you use an address at all (meaning, you send people email using it), then you'll receive spam (and lots of it). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why facebook used mysql ?
On 11/9/2010 10:27 AM, Graham Leggett wrote: This is covered by the GPL license. Once you have released code under the GPL, all derivative code - ie upgrades - have to also be released in source form, under the GPL license. Sorry but this is 100% not true. It may be true for a 3rd party (you release something under the GPL, I enhance it, therefore I am required to release my enhancement under the GPL). But Oracle owns the copyright to the MySql code and therefore they can decide to do whatever they want with it. The only thing they can't do is to 'un-release' existing code released under the GPL. Everything else is possible. Ownership of the copyright trumps the GPL. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why facebook used mysql ?
In addition to the license a product is currently available under, you need to also consider who owns its copyright; who owns its test suite (which may not be open source at all); who employs all the people who understand the code and who owns the trademarks that identify the product. Red Hat owns none of these things with respect to Linux (although they do for various other products such as their Directory Server and JBoss). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why facebook used mysql ?
On 11/9/2010 10:45 AM, Andy wrote: As a condition of getting European Commission's approval of its acquisition of Sun/MySQL, Oracle had to agree to continue the GPL release. In case anyone is interested in what specifically Oracle agreed to do, this is the text from the decision (they agreed to do the following for 5 years post-deal-closing) : Commitment to enhance MySQL in the future under the GPL. Oracle shall continue to enhance MySQL and make subsequent versions of MySQL, including Version 6, available under the GPL. Oracle will not release any new, enhanced version of MySQL Enterprise Edition without contemporaneously releasing a new, also enhanced version of MySQL Community Edition licensed under the GPL. Oracle shall continue to make the source code of all versions of MySQL Community Edition publicly available at no charge. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why facebook used mysql ?
On 11/9/2010 11:10 AM, Sandeep Srinivasa wrote: It was about the technical discussion on Highscalability - I have been trying to wrap my head around the concept of multiple core scaling for Postgres, especially beyond 8 core (like Scott's Magny Coeurs example). My doubt arises from whether Postgres depends on the kernel scheduler for multiple CPU/core utilization. If that is the case, then does using FreeBSD vs Linux give rise to any differences in scaling? Hmm...typically multi-core scaling issues are in the area of memory contention and cache coherence (and therefore are for the most part not dependent on the OS and its scheduler). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why facebook used mysql ?
Also there's the strange and mysterious valley group-think syndrome. I've seen this with several products/technologies over the years. I suspect it comes from the VCs, but I'm not sure. The latest example is you should be using EC2. There always follows a discussion where I can present 50 concrete reasons based on hard experience why the suggestion is a bad idea and the other person presents nothing besides everyone's doing it. I saw exactly the same thing with MySQL a few years ago. Before that it was Oracle. It's often easier to go along with the flow and get some work done vs. trying to argue. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why facebook used mysql ?
On 11/9/2010 11:36 AM, Sandeep Srinivasa wrote: If it is independent of the OS, then how does one go about tuning it. Consider this - I get a 12 core server on which I want multiple webserver instances + DB. Can one create CPU pools (say core 1,2,3 for webservers, 4,5,6,7 for DB, etc.) ? I know about taskset, but should one be using it ? There are plenty of things you might do, but first you need to figure out what problem you're solving. I'd suggest deploying a relatively simple configuration then evaluate its capacity under your workload. Does it run fast enough? If so, then job done. If not then why not, and so on... The simplest configuration would be one web server instance and one DB instance. I don't think you should be looking at process partitioning and core affinity unless you have already proved that you have processes that don't scale over the cores you have, to deliver the throughput you need. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Why facebook used mysql ?
On 11/9/2010 5:05 PM, Scott Marlowe wrote: Note that you're likely to get FAR more out of processor affinity with multiple NICs assigned each to its own core / set of cores that share L3 cache and such.Having the nics and maybe RAID controllers and / or fibre channel cards etc on their own set of cores in one group can make a big difference. Be careful though: this phenomenon typically only comes into play at very high I/O rates. I wouldn't want to send the OP down this path without first verifying that he has a problem. Most folks are not trying to push 100k requests/s out their web servers.. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Linux
On 11/4/2010 9:00 AM, Michael Gould wrote: What and why should I look at certain distributions? It appears from what I read, Ubanta is a good desktop but not a server. We use CentOS. I don't know of a good reason to look at other distributions for a server today. You may or may not see a performance difference. Typically the DB will perform the same on the same hardware regardless of OS, but there are a few reasons you might see differences at the margin or under specific loads. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] NoSQL -vs- SQL
On 10/11/2010 5:46 PM, Carlos Mennens wrote: Just wondering how you guys feel about NoSQL and I just wanted to share the following article... http://www.linuxjournal.com/article/10770 Looking to read your feedback and / or opinions. http://www.xtranormal.com/watch/6995033/ (warning: may not be sfw). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_filedump binary for CentOS
On 9/27/2010 6:51 AM, Devrim GÜNDÜZ wrote: They are ready: http://yum.pgrpms.org/8.3/redhat/rhel-5-x86_64/repoview/pg_filedump.html http://yum.pgrpms.org/8.3/redhat/rhel-5.0-i386/repoview/pg_filedump.html Thanks ! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] zero_damaged_pages doesn't work
Is the zero_damaged_pages feature expected to work in 8.3.11 ? I have a fair bit of evidence that it doesn't (you get nice messages in saying that the page is being zeroed, but the on-disk data does not change). I also see quite a few folk reporting similar findings in various form and mailing list posts over the past few years. I can use dd to zero the on-disk data, but it'd be nice to know definitively if this feature is expected to work, and if so under what conditions it might not. fwiw I am enabling zero_damaged_pages using a set command in a client session, not in the server's config file. The symptoms I observe are that a query that previously errored out due to a bad page header error will succeed when zero_damaged_pages is enabled, the log says that the page is being zeroed. However the same query run subsequently without zero_damaged_pages will again fail, and pg_filedump shows that the on-disk data hasn't changed. Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] zero_damaged_pages doesn't work
On 9/27/2010 4:40 PM, Jeff Davis wrote: It does zero the page in the buffer, but I don't think it marks it as dirty. So, it never really makes it to disk as all-zeros. Ah ha ! This is certainly consistent with the observed behavior. zero_damaged_pages is not meant as a recovery tool. It's meant to allow you to pg_dump whatever data is not damaged, so that you can restore into a fresh location. It'd be useful for future generations if this were included in the doc. The latest version : http://www.postgresql.org/docs/9.0/static/runtime-config-developer.html still talks about destroying data (which at least to me implies a persistent change to the on-disk bits) and fails to mention that the zeroing only occurs in the page pool sans write-back. If it helps, I'd be happy to contribute some time to fix up the docs, but imho a simple copy/paste of your text above would be sufficient. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] zero_damaged_pages doesn't work
On 9/27/2010 4:53 PM, Tom Lane wrote: The reason it tells you that data will be destroyed is that that could very well happen. If the system decides to put new data into what will appear to it to be an empty page, then the damaged data on disk will be overwritten, and then there's no hope of recovering anything. Like Jeff said, this is not a recovery tool. It's certainly not meant to be something that you keep turned on for any length of time, and so the possibility of repeat messages is really not a design consideration at all. No argument with any of this, although I'm not the intended audience for these warnings -- I know what I'm doing ;) I'm not sure though if you're disagreeing with my suggestion that the documentation be improved/corrected though. Is that the case ? (if so then I will argue) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] zero_damaged_pages doesn't work
On 9/27/2010 4:53 PM, Tom Lane wrote: The reason it tells you that data will be destroyed is that that could very well happen. Re-parsing this, I think there was a mis-communication : I'm not at all suggesting that the doc should _not_ say that data will be corrupted. I'm suggesting that in addition to what it currently says, it also should say that the on-disk data won't be changed by the page zeroing mode. In my searching I found countless people over the past few years who had been similarly confused into believing that it would write back the zeroed page to disk. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_filedump binary for CentOS
As far as I can see there is no pre-built pg_filedump binary for the PDGD yum repository (8.3.11 for RHEL5). Before I embark on building it from source I figured I'd ask here if I'm correct that there is no binary hidden somewhere in the packages. Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MySQL versus Postgres
About the shared buffers size configuration discussion: Like a few others here, I've spent a sizable proportion of my career dealing with this issue (not with PG, with other products I've developed that had a similar in-memory page pool). There are roughly six stages in understanding this problem: Stage 1: Just make it figure out how much memory the system has, and go use all of it. Stage 2: Oh, oops, often that's too much. The process won't start or the system becomes unstable. Darn. Stage 3: Ok, how about we make it use a big pile of the system's memory, but not so much that it won't start and the system won't become unstable. Stage 4: Oh, there's no practical way to achieve #3 (e.g. the filesystem cache completes with your buffer space in unpredictable and unstable ways). Oops. Stage 5: Ah...you know, using all the available memory isn't even necessarily the goal of the system's owner -- they may be running other applications that should be allowed to use most of the available memory. Stage 6: Rats, without some kind of resource policy allocation mechanism built into the OS, this problem is intractable, let's try to document the issue as best we can and define a moderately sized default so nobody shoots their feet off. The filesystem cache does a reasonable job for most deployments anyway.. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] MySQL versus Postgres
On 8/7/2010 4:24 AM, சிவகுமார் மா wrote: 4. A pet name Is it possible to have a pet name which can be used in casual conversation easily? PG -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Open Source Forum Software using PostgreSQL?
On 7/4/2010 8:10 AM, Andre Lopes wrote: I need to use an Forum Software. There is any Open Souce Forum Script using PostgreSQL? We use jForum. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [PERFORM] PostgreSQL - case studies
Kevin Grittner (kevin.gritt...@wicourts.gov) wrote: Could some of you please share some info on such scenarios- where you are supporting/designing/developing databases that run into at least a few hundred GBs of data (I know, that is small by todays' standards)? At NuevaSync we use PG in a one-database-per-server design, with our own replication system between cluster nodes. The largest node has more than 200G online. This is an OLTP type workload. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Justifying a PG over MySQL approach to a project
Lincoln Yeoh wrote: It seems you currently can only control outbound traffic from an interface, so you'd have to set stuff on both interfaces to shape upstream and downstream - this is not so convenient in some network topologies. This is more a property of the universe than the software ;) However, there are tricks that can be used with a virtual nic driver to give the effect of 'inbound' shaping in the case that you don't have control over the sending interface. In our project we deployed a dedicated shaping machine with a bunch of nics that connected to each test hosts. Then wrote scripts to setup the shaping and the test host routing to emulate the desired network characteristics. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Justifying a PG over MySQL approach to a project
Scott Marlowe wrote: I would recommend using a traffic shaping router (like the one built into the linux kernel and controlled by tc / iptables) to simulate a long distance connection and testing this yourself to see which replication engine will work best for you. Netem : http://www.linuxfoundation.org/collaborate/workgroups/networking/netem We used this to make a test rig for Directory Server replication, to verify a re-design that added pipelining to the replication protocol. It's already in the modern Linuxes--just needs to be configured. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Unexpected EOF on client connection
Howard Cole wrote: Howard Cole wrote: Thanks Francisco - I currently have MinPoolSize set to 3 (I have a lot of databases on this cluster), I think this copes 90% of the time but I shall set it to 10 and see what happens. Sampling the number of connections on my database I decided that the number of connections settled at 6 so I changed my MinPoolSize from 3 to 6. I checked the current state of the database and the number of connections is currently 12. Tonight I shall change the MinPoolSize to 8 and I am wondering if the number of connections used is going to appear as 16! Pretty soon you'll discover that you have two instances of the pool :) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to find the row corresponding to a given toast value?
I have a (large) corrupted 8.3.7 database that I'd like to fix. It has this problem : pg_dump: SQL command failed pg_dump: Error message from server: ERROR: missing chunk number 2 for toast value 10114 in pg_toast_16426 I've seen this particular syndrome before and fixed it by deleting the table row that refers to the missing toast value. The table row was discovered by chance because the user to whom the data belonged complained that his service wasn't working. In this current case that hasn't happened, so I'm clueless as to which row I need to delete. I've tried dumping the table to see if the records happen to be in primary key order (hence the N+1'th record would be the bad one). Unfortunately this didn't help because the records appear to be out of order in the dump. Hence my question : is there an efficient way to determine which table row references that missing toad value ? My best option right now is to issue SELECT ... LIMIT .. OFFSET ... queries to identify the row. This is likely to take a while though because there's tens of GBytes in the table, and the database is quite heavily loaded. Any better ideas are most welcome, thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
