• by Greg Smith at 10:31 PM

    Using the PostgreSQL System Columns

    There are a few parts of the PostgreSQL internals that poke out usefully if you look in the right place for them.  One useful set to know about are the System Columns, which you can explicitly request but don't see by default.  For example:

    psql -x -c "SELECT oid,* FROM pg_class LIMIT 1"
    There is no column named oid in the pg_class table, but it's there if you ask for it.  The oid used to be relied on more heavily in PostgreSQL as a way to identify rows.  That's not true for regular tables anymore, and you really don't want to start doing that for your own tables.  OIDs are mainly useful now when joining parts of the System Catalog together.  A good example is the Disk Usage query.  If you want to find the namespace a table is in, you need to know you can ask for its OID.  It's possible to get some of this data out of more portable views like information_schema.tables.  But many of the useful things in this area are PostgreSQL specific.  Sometimes I see people starting with the information_schema views and joining against other tables using its text name fields, such as the listed table_name.  That approach has several edge cases that don't work out correctly; not handling TOAST columns is a common example.  That makes them more prone to breaking on you later, probably after your system has gone into production, than an OID based join.

    There is also a tableoid system column.  As described in the documentation, its main use case is identifying which partition a row come from.  That's not a great thing to be driving application logic from, but it can be useful for monitoring or troubleshooting purposes.  For example, if you SELECT rows from the parent table in a partitioning inheritance scheme, it's normally expected that no rows will actually be stored there.  Checking the tableoid is one way to confirm that.  You might confirm that your INSERT/UPDATE trigger is moving rows to the right place using tableoid as well.  It's possible to do that for each individual partition section, but running a query against the parent will make sure you hit every row in the table.

    Another internal column related to uniquely identifying rows is the ctid.  The ctid is a direct pointer to the physical block (using PostgreSQL's 8K page size) and position of a row.  ctids are a pair of numbers, and the first row will be (0,1).  While this is the fastest way to find a row more than once in the same transaction block, these numbers are not stable in the long term.  Any UPDATE and some maintenance operations will change them.  One thing you can use these for is finding duplicate data in a table.  Let's say you're trying to add a unique constraint, but one row in the table is duplicated 3 times, which blocks the unique index from being created.  When rows are identical in every column, you can't write any simple SELECT statement to uniquely identify them.  That means deleting all of them but one copy requires some annoying and fragile SQL code, combining DELETE with LIMIT and/or OFFSET--which is always scary.  If you use the ctid instead, the implementation will be PostgreSQL specific, but it will also be faster and cleaner.  See Deleting Duplicate Records in a Table for an example of how that can be done.

    The other system columns all relate to transaction visibility:  xmin, cmin, xmax, cmax.  When you delete a row in PostgreSQL, it isn't eliminated from disk immediately.  It's possible that some other query that's executing at the same time will still need to see that row, and the transaction isolation in PostgreSQL worries about such things.  If you ever want to learn how that isolation works, the way the Multiversion Concurrency Control (MVCC) implementation is handled, you can watch parts of it happen.  Just open transactions in two different sessions, UPDATE/DELETE in one of them, and then look at those rows in the other.  You can still see them in the session where they weren't touched, but they'll be marked to expire in the future via their xmax being set.  To really pull that all together, you also need to know about some of the System Information Functions.  txid_current() is the most useful for this sort of learning experience, it provides a reference point for the always increasing system transaction ID.  You can find a more detailed exploration of using these functions and system columns in Bruce's MVCC Unmasked talk.  The "Routine Maintenance" chapter of my book also shows examples how how MVCC works through the perspective of the system columns.

  • by Peter Geoghegan at 6:08 PM

    Power consumption in Postgres 9.2

    One of the issues of major concern to CPU vendors is optimising the power consumption of their devices. In a world where increasingly, computing resources are purchased in terms of fairly abstract units of work, and where, when selecting the location of a major data-centre, the local price of a kilowatt hour is likely to be weighed just as heavily as the wholesale price of bandwidth, this is quite understandable.

    Globally, data centres consumed between 1.1 and 1.5 percent of electricity in 2010 (Source: Koomey). The economic and ecological importance of minimizing that number is fairly obvious.


    The broad trend towards increasing amounts of computing being performed within large data centres, with consolidated infrastructure, sold as a service rather than a product is undeniable. Of course, the term “cloud computing” is often applied to this phenomenon. That’s a term that I try to avoid, as it’s fairly ambiguous.

    There has been considerable effort to reduce wake-ups when idle in software in general, including everything from web browsers to word processors, which is related to the increasing importance of mobile and embedded platforms. However, this effort is most pronounced among developers of software that is expected to be deployed in virtualised environment on many servers, as wake-ups prevent CPUs from entering various idle states that allow them to save electricity, and when these wakeups are multiplied by thousands of VM instances, they add up very quickly.

    As part of 4CaaSt, a research project funded by the European Commission's Seventh Framework programme, that brings together members of industry and academia with the collective goal of producing an innovative platform-as-as-service offering, I spent time reducing the idle wake-ups per second in PostgreSQL. Postgres services firm 2ndQuadrant, where I work as a database architect, has had the development of several PostgreSQL features sponsored by 4CaaSt in furtherance of that goal, of which this is only one.

    Historically, PostgreSQL has been weak in this particular area. With a standard Postgres server, with no special configuration, I have measured the wake-ups when idle at 11.5 per second, using Intel’s powertop utility, as of the current 9.1 release. This was thought to be unacceptably high, for 4CaaSt, other solutions that leverage virtualisation extensively, and for embedded systems too.

    CPUs have a number of methods of reducing power consumption. These are specified by the ACPI standard (which covers discoverability, configuration and power-management), which in case you hadn't heard, is an open specification that makes minimal assumptions about the architecture or platform in use, and was written to help authors of operating system kernels.
    Briefly, ACPI describes the following states (I’ve avoided mentioning other states that have more to do with things like managing laptop hibernation):

    • Performance states P0 through to PN (i.e. the exact number of states is implementation-defined). Dynamic CPU frequency scaling states. This might be better known under marketing names for specific implementations, like “Intel SpeedStep technology”. Ever notice how the frequency reported for your CPU under /proc/cpuinfo varies from one moment to the next on Linux? This is why! This state tends to be a bit sticky, in that it might take a few seconds to observe changes in frequency, as it is increased to meet demand.

    • Processor states C0 through to C3. Processors will change this state very quickly, and we basically want to keep this as high as possible, as higher values are associated with using less power.
      • C0 is the operating state.
      • C1, or the halt state, is a state where the processor is not executing instructions, but can return to an executing state essentially instantaneously.
      • C2, or the Stop-Clock state, is a state where the processor maintains all software-visible state, but may take longer to wake up.
      • C3, or the sleep state, is a state where the processor does not need to maintain  cache coherency, but does maintain some other state. There can even be graduations of how deep a sleep this state represents, depending on the implementation - the Intel Core i5 chip in my laptop has a C4 state, for example.
    Postgres has a multi-process architecture, which includes at a minimum a number of “auxiliary processes”: processes that perform a single, well defined task across the installation. There is also a process associated with each connection, and autovacuum daemon. Out-of-the-box, you’ll see just the following processes, once the PostgreSQL server becomes idle:

    Postmaster. A “clearing-house process”, that manages all other processes, and is minimally exposed to installation-wide failures, so that it has a good chance of recovering the server in the event of an unanticipated failure. To simulate this, you can kill another auxiliary process, and watch as the postmaster starts it again.

    Background writer. A process that is charged with writing out “dirty”, or unwritten buffers, in the hope of preventing individual connection backends from ever having to.

    WAL Writer. A process that writes out WAL, log files that describe changes made to data in PostgreSQL databases. This is part of a whole subsystem through which the server efficiently maintains its crash-safety/durability guarantees.

    Autovacuum launcher. This process notices if there is a need to vacuum dead rows, which are an artifact of the Postgres MVCC implementation. It launches autovacuum worker processes as needed, to perform this garbage collection.

    Statistics collector. This process collects statistics on tables and queries, both to facilitate how autovacuum apportions work to vacuum dead rows and build more detailed statistics for the planner, and for general instrumentation.

    Checkpointer (new to 9.2). This process is responsible for managing checkpoints - smoothed writing of all data to disk, so that WAL files that describe those changes in sequence before a certain point can finally be truncated. This used to be an additional responsibility of the background writer.
    The reason that all these wake-ups had to occur within each auxiliary process was because they needed to check if the Postmaster was still alive very regularly, or if they had work assigned to them. If they took too long to notice that the Postmaster was dead (a major failure that neccessitates all processes immediately exiting), they would take too long to detach from shared memory, which would prevent the DBA from starting a new instance, as Postgres will refuse to start when it notices this to avoid data corruption.

    The solution was to amend the latch, a low-level facility to wait-sleep on an event that was already used for synchronous replication, to also monitor Postmaster death. This infrastructure was committed first. I then proceeded to write patches for each auxiliary process, most recently the Background writer, which was particularly tricky, though accounted for most of the wake-ups when idle among auxiliary processes - usually 5 per second.

    Some considerable progress has been made. Additional variability has been added to the number of wake-ups per second, but if you monitor the wake-ups per second using powertop at a sufficiently high granularity, it stabilises at:


      3.8% ( 35.0)   SignalSender
      3.0% ( 27.2)   [kernel scheduler] Load balancing tick
      2.8% ( 25.6)   kworker/0:0
      0.8% (  7.6)   postgres
      0.6% (  5.7)   [TLB shootdowns] <kernel IPI>
      0.6% (  5.6)   [kernel core] hrtimer_start (tick_sched_ti



    To give you some notion of how this relates to CPU states, this is an account of the time my laptop’s CPU spends in each of the states at one moment in time, according to powertop:


    Cn                Avg residency       P-states (frequencies)
    C0 (cpu running)        ( 1.5%)       Turbo Mode     3.0%
    polling           0.0ms ( 0.0%)         2.00 Ghz     0.1%
    C1 mwait          1.0ms ( 1.3%)         1.80 Ghz     0.1%
    C2 mwait          1.5ms ( 1.8%)         1200 Mhz     0.2%
    C3 mwait          1.4ms ( 0.4%)          800 Mhz    96.7%
    C4 mwait          7.9ms (95.0%)


    There is still some more work to do though. Simon Riggs and I submitted a patch to add group commit to PostgreSQL, which is being reviewed in the ongoing commitfest. This feature is anticipated to be very valuable to workloads that are bound by their commit rate, and a number of benchmarks that have been performed are very promising. That patch included support for allowing the WAL Writer to sleep. However, the exact details of group commit’s implementation have yet to be agreed upon, and it is not yet completely clear how effectively we will be able to reduce the WAL writer's idle wake-ups. However, I am hopeful that we will be able to eliminate them entirely, bringing the total number down to 2.6 per second for an idle Postgres 9.2 installation with standard settings. The WAL writer, much like the background writer, accounts for a relatively large 5 wake-ups per second (assuming default settings), and is similarly a bit tricky to adjust in this way.


    I’d previously measured the idle wake-ups per second for my distro’s mysqld at 2.2 (mysql-server version 5.1.56, Fedora 14), though when I check now, with mysql-server 5.5.19 on Fedora 16, that’s way up at consistently over 20 wake-ups per second. I’m not sure why that might be, but I welcome input as to what a fair, objective comparison would look like. I have made every effort to be fair here, and I'd speculate that this may have something to do with the storage engine in use in each case.

  • by Carlo Ascani at 11:26 AM

    Setting JDBC with Greenplum

    JDBC is the driver used to access a database with Java. Greenplum has a full working JDBC implementation. In this short article we'll see how to use it.

    Download and install

    It is possible to download the JDBC for Greenplum directly from the Greenplum Community Edition site (http://www.greenplum.com/community/downloads/database-ce/). Look for the "Connectivity Tools" file.

    You will receive a link to download the archive file. Extract the archive and run the binary extracted. Then follow the instructions on screen and in less than a minute you have installed JDBC.

    Prepare the Greenplum server

    After a successful installation, make sure that the server accepts TCP connections from the desired hosts. Check that listen_addresses is properly set in postgresql.conf.

    Note: by default, Greenplum listens to any address.

    Another aspect you have to consider is the user authentication, which is delegated to the pg_hba.conf file (please refer to page 36 of Greenplum AdminGuide for more information).

    After you have verified the user is able to connect to the database, you can go on and test JDBC.

    Connecting to a Greenplum Database with JDBC is a three steps procedure:

    • Import JDBC
    • Load the driver
    • Connect to the database

    To import JDBC, add this line to the top of your Java source:

    import java.sql.*;

    To load the driver, use this line:

    Class.forName("org.postgresql.Driver");

    Remember that the forName function can throw a ClassNotFoundException if the driver is not available. We do not try to catch that exception in the simple example below. You should in your production environment.

    To connect to a database using JDBC, you have to use a connection URL. It can be in one of these three forms:

    • jdbc:postgresql:databasename
    • jdbc:postgresql://host/databasename
    • jdbc:postgresql://host:port/databasename

    Build an URL that suits your needs and use it with the getConnection function. For example:

    Connection db = DriverManager.getConnection(url, username, password);

    Issuing a query

    To perform queries on the database, you have to use a Statement or a PreparedStatement instance. You can create a Statement object using the createStatement method of class Connection. The result of a query execution is a ResultSet object, containing th entire result.

    A ResultSet object can be iterated with the usual next() function, as shown in the example below.

    A simple example

    import java.sql.*;

public static void main(){

    Class.forName("org.postgresql.Driver");
    Connection db = DriverManager.getConnection("jdbc:postgresql://localhost/testdb","username", "passw0rd");

    Statement st = db.createStatement();
    ResultSet rs = st.executeQuery("SELECT * FROM mytable WHERE columnfoo = 500");
    while (rs.next()) {
        System.out.print("Column 1 returned ");
        System.out.println(rs.getString(1));
    }
    rs.close();
    st.close();
}

    It is very important to understand how to bind values in queries, in order to prevent from SQL injection issues. The following snippet is an example for that:

    int foovalue = 500;
PreparedStatement st = conn.prepareStatement("SELECT * FROM mytable WHERE columnfoo = ?");
st.setInt(1, foovalue);
ResultSet rs = st.executeQuery();
while (rs.next()) {
    System.out.print("Column 1 returned ");
    System.out.println(rs.getString(1));
}
rs.close();
st.close();

  • by Carlo Ascani at 9:39 AM

    Greenplum 4.2 is out!

    With an announce on the forum, Greenplum staff has spoke out about the new version of their Database Management System. I can't resist to blog about some of its new features.

    Cool new features

    You can find a detailed summary of the new features on Greenplum's website.

    The first in the list is Greenplum Database Extension Framework.

    You can think about it as a feature similar to Postgres 9.1 EXTENSION feature (even if there are no similarities in terms of implementation). That is an amazing way to simplify extensions installing, uninstalling and upgrading. I think that this feature will bring more logic into the database.

    Another interesting improvement is the High-Performance gNet for Hadoop.

    Everybody knows Hadoop, and even if you don't know it in details, you have at least heard about it. With this feature, you can import/export data from/to hadoop clusters so fast, using the gNet protocol. I advice you to try it, it is included in the Community Edition!

    Helpers for migrations from other database systems.

    Greenplum 4.2 offers some helpers to make a migration from other databases a lot easier. For example, there are 20 Oracle functions that runs natively on Greenplum 4.2. If you know Postgres, you could think about a sort of orafce. That is a delicate field, I feel to advice you not to underrate the workload of a migration - and do not think that everything can be made automatically.

    XML support.

    That's fantastic. I am a Postgres user, administrator and consultant, and I really love Postgres XML support. Now Greenplum has that feature. I am sure I will blog more about this in the future, with some practical usage.

    Targeted Performance Optimization.

    Greenplum 4.2 has some improvements regarding performance.

    The 4.2 version is a lot smarter, it transparently eliminate irrelevant partitions in a table, producing that smaller amount of data needs to be scanned to obtain query results. Another transparent operation is about memory: Greenplum 4.2 has a more efficent memory management, which results in a better memory utilization and higher concurrency.

    In the next weeks I will try some of these features and blog about them, waiting for the Community Edition to be out!

    So stay tuned with us on the Greenplum Community Forum and on 2ndQuadrant blog to get news about it.

  • by Carlo Ascani at 6:04 PM

    How to initialize Greenplum on multiple nodes

    In the previous article we have seen how to install Greenplum on multiple nodes. After installation steps, we must init the entire system. Let's see how.

    Actual situation

    If tou have followed previous article, you have a Greenplum installed on multiple nodes.

    Standar procedure when dealing with a Greenplum database, as well as Postgres, is composed by:

    • Installation
    • Initialization
    • Database Start

    In this article, we will see the second and third steps: initializating and starting the database.

    Database initialization

    The script that do the job here is gpinitsystem. gpinitsystem needs a special configuration file, who contains a list of segment host addresses only. Let's name it hostfile_gpinitsystem.

    For example:

    segment-hostname-1
segment-hostname-2
...

    One more file is needed, its name is gpinitsystemconfig. It contains a lot of parameters to configure your system. An example configration file, to be used as a template, is in $GPHOME/docs/clihelp/gpconfigs/gpinitsystem_config. You can copy that example file and modify it to suits your needs. A detailed list of all parameter meanings is on Admin Guide at page 67.

    The very important part of the file is:

    ARRAY_NAME="EMC Greenplum DW"
PORT_BASE=40000
declare -a DATA_DIRECTORY=(/data1/primary /data1/primary
/data1/primary /data2/primary /data2/primary /data2/primary)
MASTER_HOSTNAME=master-hostname
MASTER_DIRECTORY=/data/master
MASTER_PORT=5432

    Now you can run the gpinitsystem utility with those two files as parameters, this way:

    $ gpinitsystem -c gpinitsystem_config -h hostfile_gpinitsystem

    After a succesfully initialization you will see a kind message:

    Greenplum Database instance successfully create+.

    In case of failure, it is possible to get a partially installed system.

    Because of that, after a failure in gpinitsystem Greenplum automatically creates a "rollback" script that can be executed to cleanup things around.

    An example cleanup script file would be named:

    backout_gpinitsystem_gpadmin_20111216_121053

    Is it possible to execute that using GNU bash:

    $ sh backout_gpinitsystem_gpadmin_20111216_121053

    All Greenplum files partially installed will be removed.

    That's all for now. I hope that this article, together with the previous one, will show you how easy Greenplum installation is.

  • by Carlo Ascani at 5:39 PM

    A Greenplum 4.1 installation handbook

    One of the main advantages using Greenplum is that it gains power when it uses multiple nodes. Horizontal scalability is a main feature of Greenplum.

    Here is a compact handbook to install a multi-node Data Warehouse environment with Greenplum.

    Preparation steps

    This little guide covers Greenplum 4.1 installation. This is not intended to be a replacement for the official Install Guide, just a little handbook to keep on your desk.

    You have to tune your Operating System a little bit before installing Greenplum. That's a very well documented procedure, I advice you to read it in the Install Guide at page 18.

    Installing Greenplum

    First of all, you have to run the Greenplum installer script on master host, as root. The installer script can be downloaded from greenplum community site: http://www.greenplum.com/community/downloads/database-ce

    Make sure to download the correct version!

    The installer script displays some question and the license, simply follow instructions on video.

    Now comes the important part, you have tu run a special script, that setup Greenplum on a list of hosts for you. Awesome! It simply copies the Greenplum installation from the actual host to a list of specified hosts (it cares about ssh keys exchanging and gpadmin user creation).

    Specified where?

    The important file here is hostfile_exkeys, it must contains hostnames for each host in your Greenplum system. For example:

    master-hostname
master-segment-hostname
segment-hostname-1
segment-hostname-2
...

    this is enough to run gpseginstall, run in this way:

    # gpseginstall -f hostfile_exkeys -u gpadmin -p yourpassword

    Creating directories

    It's time to create the master directory on master host. Remember that real data are on segments, so no much space is needed here. For example:

    # mkdir /data/master
# chown gpadmin /data/master

    You have to create that directory on your master segment as well. Greenplum provides a useful script to do the job, it is called gpssh:

    # gpssh -h master-segment-hostname -e 'mkdir /data/master'
# gpssh -h master-segment-hostname -e 'chown gpadmin /data/mast

    Finally, you have to create data directories on all segments host, and tou can do that all at once, thanks to gpssh.

    Remember that real data goes there, so a lot of space is needed.

    Create a file called 

    hostfile_gpssh_segonly

    and place only segments hostnames in it. For example:

    segment-hostname-1
segment-hostname-2

    Now, run commands an all segments at once like this:

    # gpssh -f hostfile_gpssh_segonly -e 'mkdir /data/primary'
# gpssh -f hostfile_gpssh_segonly -e 'mkdir /data/mirror'
# gpssh -f hostfile_gpssh_segonly -e 'chown gpadmin /data/primary'
# gpssh -f hostfile_gpssh_segonly -e 'chown gpadmin /data/mirror'

    Conclusions

    Here's a list of steps to keep on your desk, I hope you will find it useful:

    • Configure your Operating System for Greenplum (as written in Install Guide)
    • Install Greenplum on master host
    • Run gpseginstall to install Greenplum on other hosts
    • Create master directory on the master
    • Create the same directory on master segment (gpssh can help here)
    • Create data directories on segments (gpssh can help here)

    In the next article, we will see how to init and start the Greenplum Database we have just installed. Stay tuned.

    Cheers

  • by Gabriele Bartolini at 12:51 PM

    PGDay.IT 2011 was "bellissimo"!

    The fifth edition of the Italian PGDay went well beyond our initial expectations. We had about 75 participants, a total of 95 people including staff and speakers.
    As I said during the event, rather than PGDay Italy, this should be named PGDay for Italian speakers given the presence of staff from Switzerland (Canton Ticino). Participants came from 12 regions: all regions but Val d'Aosta in the north/centre area, but also from Southern Italy (Naples and Calabria).
    In any case, it was fantastic to get back to Prato after 3 years, in the Monash  University Prato Centre. The atmosphere was very similar to the first editions and we are really happy to see the community grow (last year we had 60 participants in total, which means an increment of 60%).
    Even the quality of the talks was in my humble opinion very high.
    The audience paid a tribute to Magnus Hagander for his nomination in the core team, then Magnus wonderfully covered the new features of PostgreSQL 9.1.
    Magnus Hagander's keyone at PGDay.IT 2011
    Simon Riggs talks were on the future of PostgreSQL and on NoSQL databases. Very interesting. Andreas Scherbaum covered data warehouse topics.
    Other interesting talks were:

    •  the experience on open source and Postgres for CSI Piemonte, one of the main organisations working for local governments
    • ORM and Perl by Ferruccio Zamuner
    • Node.js and Postgres by Lucio Granzi
    • Serialisable snapshot isolation, covered by Marco Nenciarini
    • repmgr by Carlo Ascani
    • foreign tables and data wrappers by Giulio Calacoci


    Unfortunately I missed Gianni's talk on debugging with CTEs as I was giving my speech on the project we (as 2ndQuadrant Italia) have been working in the last months: BaRMan, backup and recovery manager for PostgreSQL. The feedback I received was excellent (especially by certified Oracle engineers). We hope we can release it by the end of 2011 (depending on sponsorships) or, at the latest, early 2012.

    In any case, even though organising this kind of events as community is not an easy task, the success of this edition is a source of motivation for all of us. We hope we can start much earlier with the organisation and we hope we can find more sponsors/partners for next year.

    I take the opportunity to thank all the speakers that took part to PGDay and all the staff members: Diego, Luca, Gianluca, Cosimo, Maurizio, Marco (Tofanari), Emanuele and the team from 2ndQuadrant Italia (Carlo, Giulio, Marco, Gianni and Simone).


  • by Carlo Ascani at 11:51 AM

    Using Greenplum 4.1 in Ubuntu 11.10

    Greenplum does not officially support Ubuntu Server 11.10 as underlying operating system. However, I needed to install it on the most recent Ubuntu server just to perform some tests and evaluate it.

    Some words on Ubuntu Server

    Ubuntu Server is increasing its popularity every day. Solutions like Canonical Landcape are getting more and more fans.

    In my case, I have to perform some tests with Greenplum and I have an Ubuntu Server available. I am not doing this for a production environment and I do not advice you to use Greenplum on Ubuntu on production systems. EMC does not officially support Ubuntu, and that should suffice.

    Greenplum on Ubuntu, a quick and dirty approach

    Assuming that you have an Ubuntu 11.10 Server up and running (64 bit version), here are some tips to install Greenplum 4.1.

    Greenplum installation script checks if you are running a CentOS operating system thanks to the presence of /etc/redhat-release file. Let's create that file on our Ubuntu Server, specifying a "fake" CentOS release:

    echo "RedHat/CentOS" > /etc/redhat-release

    Ok, that's an horrible dirty hack, but let's move on.

    The actual installation process

    You can follow the Greenplum 4.1.1 Installation Guide, download it from the community site. I have just tested a single node cluster.

    Conclusion

    Greenplum does not support Ubuntu Server. Please do not use Greenplum on Ubuntu Server for production environments, or if you wish so make sure you contact EMC. However, this article shows how easy it is to install Greenplum on the latest Ubuntu server for evaluation purposes.

  • by Greg Smith at 1:39 AM

    Global trends in deploying PostgreSQL

    This year's conference lineup led me all over the world, a giant rectangle triangle going from the west coast of the US, north to Canada, east to the UK and Amsterdam, then ending south in Brazil.  I've now locked myself in to focus on the 3rd CommitFest for PostgreSQL 9.2, which began a few days ago.  Check out the 2011-11 section of the CommitFest tracker to see what changes have been submitted, we're always looking for new volunteer patch reviewers.

    Talking to people deploying PostgreSQL in several countries during a short span of time has given me some interesting perspective on where the project is at.  I follow a lot of adoption trends in the US, and some of those I assume are quirks in how business is done in this country.  But when I hear the same sort of feedback from people in all four of the countries I've been to this year, too, it's clear this is a larger issue.

    The first thing I'm seeing a surprising amount of is satisfaction with the feature set in PostgreSQL.  A few years ago, conversations about what you could and couldn't do with PostgreSQL usually stalled on one of a few common requests.  There's a good survey of PostgreSQL feature feedback at User Voice.  13 important features originally on that list have been closed already, with Index Only scans as the next expected to fall in the upcoming 9.2.  PostgreSQL now includes regular and synchronous replication as of 9.1.  pg_upgrade has been getting an increasing amount of testing that proves it works for many in-place upgrade scenarios.  Extensions are dramatically easier to use now.

    It seems the total feature set has crossed the threshold where PostgreSQL is good enough for a whole lot more deployment situations than it used to be.  What I'm hearing from people all over the world now is that the basic feature set and performance of PostgreSQL isn't failing the "checkbox test" so often anymore, where business people require certain things before they'll even consider a database.  There are some major wants that are some distance off, such as materialized views and better OLAP support (cube/rollup/etc.)  And using partitioning for bigger data sets is harder than people would like.  But these are all things that are only needed for larger deployments, and some workarounds exist if you're willing to work at them.

    If the feature set isn't holding back as many deployments now, what is?  Well, the next thing I've been hearing everywhere is on that survey list too:  better administration and monitoring tools.  You really need a whole open-source stack to monitor PostgreSQL right now, from OS+database trending to query log analysis.  It's fine for these tools to live outside the database core, but some changes are clearly needed to make such tools easier to write.  For example, the one built-in tool that allows query monitoring is pg_stat_statements, and the limitations preventing it from being useful to most people are so obvious we've gotten two submissions to improve it in the last month.

    There are a few projects that aim at the monitoring/administration problem.  EnterpriseDB's PostgreSQL Enterprise Manager, Cybertec's pgwatch, OmniTI's Reconnoiter, the suite of smaller tools from End Point, and even the text UI of pg_statsinfo all hit the edges of this problem.  What I hear when I have my advocacy hat on is that the community needs a major open-source project bigger than any of these to make database monitoring easier.  That's now one of the major distinguishing features the commercial competition has.  Getting enough of the people developing in this area all pointing in the same direction and working together is a big challenge though.

    On a related note, now that the underlying features are there, it seem making replication easier to monitor and setup is a major issue too.  There are so many choices in replication technologies available for PostgreSQL it's easy for new people to get overwhelmed by them all.  And the documentation guides around this area are still filled with a lot of complications that aren't even really necessary to get started at this point.  It's easy for newcomers get dragged into details like how old style archiving works as a precusor to setting up even basic replication, despite that they're using the easier features in the current PostgreSQL instead.  This area still has some work in the core database happening in 9.2, and it will be important for the community to create replication guides that include current information covering both 9.1 and that release.  What I'm hear from every country I visit now is "I need material to help me compete against the idea of using Oracle RAC".

    The last of the global trends that have really jumped out at me is how companies everywhere are reinventing the development process around database applications.  In some places, mostly bigger companies and government installations in particular, the expected staff "stack" is business as usual; it hasn't changed in a long time.  New applications go from Developer to DBA to systems administrator.  Management ideas like DevOps are catching on to improvement interface between these roles, but not really upset its basic structure.  Everywhere I go now, I'm seeing everything but the developer role being squeezed out.  ORM-driven development is eliminating the DBA's role in database design.  Managed application hosting platforms are wiping out the systems administrator role.  Startups with an idea for a web application go right from developer to deployment, and happily this is increasingly happening with a PostgreSQL backend in the database role.  There isn't even the perception that DBA-like help might be needed until the application grows quite a bit.  I'm seeing the need for better database specific optimization skills than a typical developer has being deferred until the application has tens of gigabytes of data to sling around.

    Being able to deploy small PostgreSQL installs and grow them to a reasonable size without specialized DBA knowledge is a great thing as far as I'm concerned.  The exact advances in things like ORMs that have allowed reaching this point across the world are a topic that deserves its own long discussion.  I'm going to cut this off here and return to that later.  In this country, there's some concrete work around the 9.2 release that needs to get done this month.

  • by Carlo Ascani at 4:22 PM

    Mapreduce in Greenplum 4.1 - 2nd part

    Through this article, we are going to complete the MapReduce job started in the previous article.

    Take up the problem from the previous article

    In the previous article, we left with this MapReduce configuration file:

    %YAML 1.1
---

VERSION: 1.0.0.1
DATABASE: test_database
USER: gpadmin
HOST: localhost
DEFINE:
    - INPUT:
        NAME:  my_input_data
        QUERY: SELECT x,y FROM my_data

    - MAP:

        NAME: my_map_function
        LANGUAGE: PYTHON
        PARAMETERS: [ x integer , y float ]
        RETURNS: [key text, value float]
        FUNCTION: |
                yield {'key': 'Sum of x', 'value': x }
                yield {'key': 'Sum of y', 'value': y }

EXECUTE:
    - RUN:
        SOURCE: my_input_data
        MAP: my_map_function
        REDUCE: SUM

    Which produces the following output:

    key     |value
--------+-----
Sum of x|   15
Sum of y|  278
(2 rows)

    Naturally speaking, that job sums all values from two different columns of a test table.

    Our goal here, is to use execute a division of these two values, in particular 15 and 278.

    Let's check what the result is with a calculator, just to be sure that the MapReduce job will return the correct value:

    $ psql -c "SELECT 15/278::FLOAT AS result" test_database
       result
 0.0539568345323741
(1 row)

    Yes, we use Greenplum as a calculator :).

    Introducing "tasks"

    What we are doing here is to define a separate task that performs the sum. We will use the result of that task as input for a query that actually does the division step. Let's see it in practice.

    • Remove the EXECUTE part from test.yml. In details, these lines:
    EXECUTE:
    - RUN:
        SOURCE: my_input_data
        MAP: my_map_function
        REDUCE: SUM
    • Define a task, wich is responsible to execute the sum of x and y values. To do that, it reuses the old map function. Append this to test.yml:
    - TASK:
        NAME: sums
        SOURCE: my_input_data
        MAP: my_map_function
        REDUCE: SUM

    The useful characteristic of tasks is that they can be used as input for further processing stages.

    • Define the step that performs the division, actually. It is an SQL SELECT that use the task defined earlier as input. Append this to test.yml:
    - INPUT:
        NAME: division
        QUERY: |
            SELECT
                (SELECT value FROM sums where key = 'Sum of x') /
                (SELECT value FROM sums where key = 'Sum of y')
                AS final_division;

    As you can see, the FROM clause contains the name of the task defined above: sums.

    • Finally, execute the job and displays output. Append this to test.yml:
    EXECUTE:
    - RUN:
        SOURCE: division
        TARGET: STDOUT

    This step runs the division query and display the result via standard output.

    Put everything together

    This is the complete test.yml file:

    %YAML 1.1
---

VERSION: 1.0.0.1
DATABASE: test_database
USER: gpadmin
HOST: localhost
DEFINE:
    - INPUT:
        NAME:  my_input_data
        QUERY: SELECT x,y FROM my_data

    - MAP:

        NAME: my_map_function
        LANGUAGE: PYTHON
        PARAMETERS: [ x integer , y float ]
        RETURNS: [key text, value float]
        FUNCTION: |
                yield {'key': 'Sum of x', 'value': x }
                yield {'key': 'Sum of y', 'value': y }
    - TASK:
        NAME: sums
        SOURCE: my_input_data
        MAP: my_map_function
        REDUCE: SUM

    - INPUT:
        NAME: division
        QUERY: |
            SELECT
                (SELECT value FROM sums where key = 'Sum of x') /
                (SELECT value FROM sums where key = 'Sum of y')
                AS final_division;

EXECUTE:
    - RUN:
        SOURCE: division
        TARGET: STDOUT

    Execute the whole job with:

    $ gpmapreduce -f test.yml
mapreduce_2235_run_1
    final_division
0.0539568345323741
(1 row)

    Compare it with the calculator result. Ok, it matches.

    Conclusion

    The task is complete. We have calculated sum(x)/sum(y) correctly.

    The power of MapReduce is mainly in the number of servers involved in the calculation.

    Many servers accomplishes small calculation to get the final result. Maybe you will not notice the powerful of MapReduce here, but this is a good starting point.

  • Page 1 of 26 ( 251 posts )
  • >>

© 2001-2012 2ndQuadrant Ltd. All rights reserved. | Privacy Policy

Site by QueryClick