Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 30 of 85 Next 30 Older Entries

Displaying posts with tag: postgres (reset)

Which tech do startups use most?
+0 Vote Up -0Vote Down
Leo Polovets of Susa Ventures publishes an excellent blog called Coding VC. There you can find some excellent posts, such as pitches by analogy, and an algorithm for seed round valuations and analyzing product hunt data. He recently wrote a blog post about a topic near and dear to my heart, Which Technologies do Startups […]
MySQL, ASCII Null, and Data Migration
+0 Vote Up -0Vote Down

Data migrations always have a wide range of challenges. I recently took on a request to determine the difficulty of converting an ecommerce shop's MySQL 5.0 database to PostgreSQL 9.3, with the first (presumably "easier") step being just getting the schema converted and data imported before tackling the more challenging aspect of doing a full assessment of the site's query base to re-write the large number of custom queries that leverage MySQL-specific language elements into their PostgreSQL counterparts.

During the course of this first part, which had contained a number of difficulties I had anticipated, I hit one that I definitely had not anticipated:

ERROR:  value too long for type character varying(20)

Surely, the error message is absolutely clear, but how could this possibly be? The obvious answer--that the varchar definitions were different lengths

  [Read more...]
SFTP virtual users with ProFTPD and Rails: Part 2
+0 Vote Up -0Vote Down

In Part 1 of "SFTP virtual users with ProFTPD and Rails", I introduced ProFTPD's virtual users and presented my annotated proftpd.conf that I used to integrate virtual users with a Rails application. Here in Part 2, I'll show how we generate virtual user credentials, how we display them to the user, as well as our SftpUser ActiveRecord model that does the heavy lifting.

Let's start at the top with the SFTP credentials UI. Our app's main workflow actually has users doing most of their uploads through a sweet Plupload widget. So, by default, the SFTP functionality is hidden behind a simple button sitting to the right of the Plupload widget:



  [Read more...]
Foreign Data Wrappers
+0 Vote Up -0Vote Down

Original images from Flickr user jenniferwilliams

One of our clients, for various historical reasons, runs both MySQL and PostgreSQL to support their website. Information for user login lives in one database, but their customer activity lives in the other. The eventual plan is to consolidate these databases, but thus far, other concerns have been more pressing. So when they needed a report combining user account information and customer activity, the involvement of two separate databases became a significant complicating factor.

In similar situations in the past, using earlier

  [Read more...]
Benchmarking Postgres on AWS 4,000 PIOPs EBS instances
+0 Vote Up -0Vote Down
Introduction

Disk I/O is frequently the performance bottleneck with relational databases. With AWS recently releasing 4,000 PIOPs EBS volumes, I wanted to do some benchmarking with pgbench and PostgreSQL 9.2. Prior to this release the maximum available I/O capacity was 2,000 IOPs per volume. EBS IOPs are read and written in 16Kb chunks with their performance limited by both the I/O capacity of the EBS volumes and the network bandwidth between an EC2 instance and the EBS network. My goal isn't to provide a PostgreSQL tuning guide, an EC2 tuning guide, or a database deathmatch complete with graphs; I'll just be displaying what kind of performance is available out-of-the-box without substantive tuning. In other words, this is an exploratory benchmark not a comparative benchmark. I would have liked to compare the performance of 4,000 PIOPs EBS volumes with 2,000

  [Read more...]
Benchmarking Postgres on AWS 4,000 PIOPs EBS instances
+0 Vote Up -0Vote Down
Introduction

Disk I/O is frequently the performance bottleneck with relational databases. With AWS recently releasing 4,000 PIOPs EBS volumes, I wanted to do some benchmarking with pgbench and PostgreSQL 9.2. Prior to this release the maximum available I/O capacity was 2,000 IOPs per volume. EBS IOPs are read and written in 16Kb chunks with their performance limited by both the I/O capacity of the EBS volumes and the network bandwidth between an EC2 instance and the EBS network. My goal isn't to provide a PostgreSQL tuning guide, an EC2 tuning guide, or a database deathmatch complete with graphs; I'll just be displaying what kind of performance is available out-of-the-box without substantive tuning. In other words, this is an exploratory benchmark not a comparative benchmark. I would have liked to compare the performance of 4,000 PIOPs EBS volumes with

  [Read more...]
I'd rather not always be compacting
+1 Vote Up -0Vote Down
I read a post about the need to defragment MongoDB databases. I liked the title but prefer to avoid a DBMS that requires routine maintenance. Alas I don't know what the author means by routine. There is a big difference between having to defragment a database once a week versus once a quarter. This isn't an online operation per the manual so it can have a big impact on service availability.

PostgreSQL isn't immune to this problem. It has a long history with vacuum and each release makes things better. But I haven't run either PostgreSQL or MongoDB in production for a busy OLTP workload so I don't know whether

  [Read more...]
Postgres XC - explained
+0 Vote Up -0Vote Down

Postgres XC explained 

Users demand faster processing and as the processing and reports get faster so do the expectation for even faster performance and scalability. Now days its unthinkable not to deliver the performance  and availability.
All of those challenges require a constant search/development of new and improves solutions. Enter Postgres XC just when you were thinking that RDBMS is dead it comes back with the vengeance. So what is exactly Postgres XC and why should anyone care? 
Postgres XC shares a common name with the Postgres RDBMS but this is where the similarities stop, Postgres

  [Read more...]
Postgres replication for MySQL DBA's
+0 Vote Up -0Vote Down

Postgres replication for MySQL DBA's

Working a with Postgers and MySQL replication I noticed that many MySQL DBA's have hard time grasping Postgres replication and how it works,  you may ask why would you need to know how to setup and use Postgres replication - but lets face it Postgres is becoming ever more popular and many MySQL shops also have Postgres databases. Overall a lot of startups are using a collection of databases  that serve various purposes. I"ll attempt to explain Postgres replication in plain English and use MySQL terms to help adsorb the seemingly complex subject. 
Postgres  "streaming replication" was introduced since Rel. 9.0 of Postgres and is a very useful addition that many of us were waiting for years, personally I strongly believe that the ack of replication kept Postgres from wide adoption unlike MySQL
  [Read more...]
SFTP virtual users with ProFTPD and Rails: Part 1
+0 Vote Up -1Vote Down

I recently worked on a Rails 3.2 project that used the sweet PLupload JavaScript/Flash upload tool to upload files to the web app. To make it easier for users to upload large and/or remote files to the app, we also wanted to let them upload via SFTP. The catch was, our users didn't have SFTP accounts on our server and we didn't want to get into the business of creating and managing SFTP accounts. Enter: ProFTPD and virtual users.

ProFTPD's virtual users concept allows you to point ProFTPD at a SQL database for your user and group authentication. This means SFTP logins don't need actual system logins (although you can mix and match if you want). Naturally, this is perfect for dynamically creating and

  [Read more...]
Two Cons against NoSQL. Part I.
+1 Vote Up -1Vote Down
Two cons against NoSQL data stores read like this: 1. It’s very hard to move data out from one NoSQL to some other system, even other NoSQL. There is a very hard lock in when it comes to NoSQL. If you ever have to move to another database, you have basically to re-implement a lot [...]
Predicting Postgres Performance By Looking At Old MySQL Bugs
+1 Vote Up -0Vote Down
While putting PostgreSQL 9.2 through it’s paces, I noticed some behavior that was eerily familiar. Back in January of 2006, Peter Zaitsev opened a bug against MySQL 4.1 that complained of a comparison of an out-of-range constant triggering a key lookup (later distilled to a feature request to “statically evaluate predicates using implicit type constraints”). [...]
Approximating Postgres Replication Delay
+0 Vote Up -0Vote Down
Coming from the MySQL world, I’m used to being able to easily determine the replication delay (in seconds) via the SHOW SLAVE STATUS command: mysql> show slave status\G *************************** 1. row *************************** ... Seconds_Behind_Master: 0 ... 1 row in set (0.00 sec) Unfortunately, there is no such comparable command in PostgreSQL. The official docs propose [...]
Exploring Configuration Management with Ansible
+0 Vote Up -0Vote Down

 

What is Ansible?

Ansible is a configuration management and deployment system, like Puppet, Capistrano, Fabric, and Chef. Its aim is to be radically simple and let you use your existing scripts to help with cluster configuration and software deployment whenever possible. Here are the ways that Ansible differentiates itself.

Simplicity

Ansible does not include a client/server architecture with pull-based clients (although in more recent versions, it does include pull-based configuration and deployment). Rather, it uses pre-existing network infrastructure: SSH. Every company has SSH installed on their cluster servers, and Ansible simply rides on top of this infrastructure to get the code and configuration out to the nodes.

Language

  [Read more...]
Developer-Driven Databases
+0 Vote Up -0Vote Down

Even though I have come late to the party of professional development, relatively speaking, I am acutely aware of the conflict that seems to pervade the developer-DBA relationship. This is what I gather about why this is: DBAs used to be paid better that developers, and often this was because they were able to reduce the overall license and hardware costs of large database installations. Both the size and proprietary nature of databases made them incredibly expensive, so paying an individual gobs of money to make sure they ran efficiently and that the data was preserved was worth it.

Several trends have changed the playing field. The first is the arrival of small, commodity server hardware that makes mainframes or large servers unnecessary, and thus the cost is pushed down dramatically for most

  [Read more...]
On Clouds And Data
+0 Vote Up -0Vote Down
I'm sitting in SFO tonight, awaiting my return trip back to Hurricane Pending Maryland. (As a former Floridian, I must of course scoff at any notions that this hurricane is significant). Walking through the airport I noticed a large billboard about "Big Data and the Cloud". This is the kind of billboard you only see in Silicon Valley; I don't see signs like that in Portland or Ottawa, and certainly not when I had to change flights in Detroit this year.

Anyway, these two buzz words aren't a local phenomenon, and are actually taking the tech world by storm. Big Data has become serious enough that there are multiple conferences now for folks interested in the topic. And cloud, well, perhaps harder to define, but more and more businesses are moving to the cloud every day. The problem here is that, most of the traditional ideas on big data run entirely counter to the ideas that work

  [Read more...]
Paying Attention Pays Off
+0 Vote Up -0Vote Down
I often run my ops like I take care of data; a bit overzealously. Case in point, when setting up a new database, I like to throw on a metric for database size, which gets turned into both a graph for trending, but also an alert on database size. Everyone is always on board with trending database size in a graph, but the alert is one people tend to question. This is not entirely without justification.

On a new database, with no data or activity, deciding when to alert is pretty fuzzy. When we set up a new client within our managed hosting service, I usually just toss up an arbitrary number, like 2GB or something. The idea isn't that a 2GB database is a problem, it's that when we cross 2GB, we should probably take a look at the trending graph and do a projection. Depending on how things look, we'll bump

  [Read more...]
Maybe they just like it better?
+1 Vote Up -0Vote Down
There has been a lot of chatter the past week about Apple replacing MySQL with Postgres in the new OSX Lion Server [U.S. | England | New Zealand ]. Most of it seems to tie things back to Oracle's new stewardship over the MySQL project, a lot of that stemming from what I would say is FUD from the EnterpriseDB folks, regarding doom and gloom about the way Oracle might handle the project in the future. Not that the FUD is entirely unwarrented; While Oracle has done a pretty decent job with MySQL so far, looking at what Oracle has done to projects like Open Solaris certainly would make one queasy.  [Read more...]
Developer Week in Review: Lion drops pre-installed MySQL
+1 Vote Up -1Vote Down


A busy week at Casa Turner, as the infamous Home Renovations of Doom wrap up, I finish the final chapters of "Developing Enterprise iOS Applications" (buy a copy for all your friends, it's a real page turner!), pack for two weeks of vacation with the family in California (Palm Springs in August, 120 degrees, woohoo!), and celebrate both a birthday and an anniversary.



But never fear, WIR fans, I'll continue to supply the news, even as my MacBook melts in the sun and the buzzards start to circle overhead.

The law of unintended consequences

If you decide to install Lion Server, you may notice something





  [Read more...]
451 CAOS Links 2011.07.26
+0 Vote Up -0Vote Down

CloudBees raises $10.5m. Microsoft commits $100m to SUSE. And more.

# CloudBees secured $10.5m in Series B venture funding.

# Microsoft renewed its vows with Attachmate’s SUSE business unit, committing to invest $100m in new SUSE Linux Enterprise certificates over the next four years.

# Oracle announced that it has acquired Ksplice, twhioch offers zero downtime update technology for Linux.

# Ingres announced that Steve Shine has been named Chief Executive Officer and President.

# Dell

  [Read more...]
Upserting via Writeable CTE
+0 Vote Up -0Vote Down
Earlier today my colleague Depesz posted a nice write up showing one of the use cases for the new 9.1 feature, "Writable CTE's". It certainly shows one handy thing that this feature will enable, but it's not the only one. Here's a quick little bit of SQL I have been playing with for some time that re-implements the infamous "UPSERT" command (a long time sticking point for people trying to make MySQL apps more cross-database compatible) into Postgres.

pagila=# select * from actor where first_name = 'AMBER' and last_name = 'LEE';
actor_id | first_name | last_name | last_update
----------+------------+-----------+-------------
(0 rows)

pagila=# with upsert as
pagila-# (update actor set last_update = now() where first_name = 'AMBER' and last_name = 'LEE'







  [Read more...]
How are index-only scans implemented in InnoDB?
+6 Vote Up -0Vote Down
There have been interesting discussions in the PostgreSQL community about adding support for index only scans. On several occasions people were curious about how InnoDB supports this. A recent post by the InnoDB team is an excellent overview. A brief summary of that post and other material is:

  • records in the clustered (primary) index store hidden columns (DB_TRX_ID, DB_ROLL_PTR)
  • records in the non-clustered (secondary) index do not store hidden columns
  • records in clustered and non-clustered indexes have a delete-mark flag
  • records are not updated in the secondary index, they are delete-marked on


  [Read more...]
random poking
+4 Vote Up -0Vote Down

These are some of my notes from some sysbench in-memory r/o testing in past day or so:

  • At ‘fetch data by primary key’ benchmark with separate read snapshots at each statement, MySQL shines until ~200 concurrent threads, then performance starts dropping slightly faster than one would want, I think mostly from table cache LOCK_open contention
  • auto-commit cost (establishing read snapshot per statement) for SELECTs is ~10% for MySQL, but for PG it can be +50% in plain SQL mode and +130% (!!!!!!!) when using prepared statements (this can be seen in a graph – obviously the global lock PG
  [Read more...]
Postgres kernel regressions
+6 Vote Up -0Vote Down

While Mark was looking at MongoDB, I was playing comparing various aspects of MySQL and Postgres performance. Certain PG performance numbers I saw (40kqps vs 110kqps from MySQL) made me really upset, so I ended up discussing with people at #postgresql – and started comparing various versions/configurations/machines/etc.

Apparently 2.6.32 kernel, which is in Ubuntu 10.04 LTS (Lucid Lynx) and is also basis for future RHEL6 kernel has nearly 20% performance degradation for PG (though not for MySQL, phew) – and apparently it was news to their community (I have started an email thread, will see where it goes).

While I was doing my research, of course I could observe plenty of gems of wisdom:

  [Read more...]
Now What? (wrt OpenSolaris and your database)
+0 Vote Up -0Vote Down
Last week's "announcement" of the death of OpenSolaris has steered a lot of questions my way about where people should go, and/or where OmniTI will go, now that OpenSolaris future looks non-existent. As one of the more open users of Solaris related technology, and running some beefy loads on top of it, it makes sense that people would be curious as to what we might be doing next. I would start with saying that as a company, we don't have an official policy on this yet, and probably won't. We evaluate each situation on a customer by customer basis, so what follows here is more my  [Read more...]
Databases, The End User Experience
+6 Vote Up -1Vote Down
Does it matter if the end user knows what the database is?

Recently I got a wonderful view of a database from the end user perspective.

While I was traveling I had found a restaurant where I had decided to let friends who live locally know where I was at. Part way through my food I got a message from a local friend that said "Don't eat there, their food always makes people sick!"

"Always" is a word that I would think would be a little too strong when applied to a restaurant, right?

Nope, the next day I got to feel the full truth of the word.

A couple of days later I am telling some friends about this and a local asked me "Where was this, I want to avoid them." I didn't get asked this question once, I got it asked a dozen times.

I don't know where the place is. Why is that? Because the system I was using lost the entire day worth of my data. I











  [Read more...]
Database Scalability Patterns - OSCon 2010
+0 Vote Up -0Vote Down
Howdy folks,

slides are up for my talk, "Database Scalability Patterns", which I gave this week at OSCon 2010. You can get them from the OSCon page, from slideshare, or just watch it below

Database Scalability PatternsView more presentations from Robert Treat.
Log Buffer #193 – A Carnival of The Vanities for DBAs
+1 Vote Up -0Vote Down

Welcome to Log Buffer, the weekly roundup of DBA industry happenings.

Read on for the latest updates in Log Buffer #193. Don’t forget, we’re always looking for volunteer editors to publish and host an issue of Log Buffer. If you’d like this to be you, contact the Log Buffer coordinator.

ODTUG/Kaleidoscope 2010 roundup:

Sheeri

  [Read more...]
OpenSQLCamp Boston Pages are online
+5 Vote Up -1Vote Down

OpenSQLCamp is less than 4 months away, and I have finally gotten around to updating the site. Special thanks go to Bradley Kuzsmaul and the folks at Tokutek for getting the ball rolling and making the reservation at MIT. Using MIT means that we will have *free* reliable wireless guest access and projects.

OpenSQL Camp is a free unconference for people interested in open source databases (MySQL, SQLite, Postgres, Drizzle), including non-relational databases, database alternatives like NoSQL stores, and database tools such as Gearman. We are not focusing on any one project, and hope to see representatives from a variety of open source database projects attend. As usual I am one of the main organizers of Open SQL Camp (in previous years, Baron Schwartz, Selena Deckelmann and Eric Day have been main organizers too; this year Bradley

  [Read more...]
watch for momentary monitoring
+0 Vote Up -0Vote Down
One of the things I preach about a lot is good monitoring of your database servers; having tools in place to tell you both what good looks like and when things go bad is critical for large scale success. But sometimes you just need to monitor a momentary process, where setting up a check in your normal monitoring software is overkill. In these cases one tool that can help out is the watch command.

Case in point, the other day I needed to back up a fairly large partitioned table (about 1.3TB on disk). The plan? A quick little script to pg_dump each of the partitions (about 325). Feed the script through xargs -P so I don't swamp the box, but I get some

  [Read more...]
Showing entries 1 to 30 of 85 Next 30 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.