Mammoth
  • |
  • Contact
  • |
CMD | Command Prompt, Inc. - PostgreSQL Solutions, Support & Hosting
  • |
  • |
  • |
  • |
  • |
WhatcomPUG meeting last night on: sqitch and... bitcoin friends were made!
Posted Wednesday Mar 18th, 2015 09:28am
by Joshua Drake
| Permalink

Last night I attended the second WhatcomPUG. This meeting was about Sqitch, a interesting database revision control mechanism. The system is written in Perl and was developed by David Wheeler of PgTap fame. It looks and feels like git. As it is written in Perl it definitely has too many options. That said, what we were shown works, works well and appears to be a solid and thorough system for the job.


I also met a couple of people from CoinBeyond. They are a point-of-sale software vendor that specializes in letting "regular" people (read: not I or likely the people reading this blog) use Bitcoin!


That's right folks, the hottest young currency in the market today is using the hottest middle aged technology for their database, PostgreSQL. It was great to see that they are also located in Whatcom County. The longer I am here, the more I am convinced that Whatcom County (and especially Bellingham) is a quiet tech center working on profitable ventures without the noise of places like Silicon Valley. I just keep running into people doing interesting things with technology.


Oh, for reference:


  • Twitter: @coinbeyond
  • Facebook: CoinBeyond
  • LinkedIn: Linkedin



    Categories: Business, OpenSource, PostgreSQL, SQL

  • Stomping to PgConf.US: Webscale is Dead; PostgreSQL is King! A challenge, do you accept?
    Posted Tuesday Mar 17th, 2015 08:35am
    by Joshua Drake
    | Permalink

    I submitted to PgConf.US. I submitted talks from my general pool. All of them have been recently updated. They are also all solid talks that have been well received in the past. I thought I would end up giving my, "Practical PostgreSQL Performance: AWS Edition" talk. It is a good talk, is relevant to today and the community knows of my elevated opinion of using AWS with PostgreSQL (there are many times it works just great, until it doesn't and then you may be stuck).


    I also submitted a talk entitled: "Suck it! Webscale is Dead; PostgreSQL is King!". This talk was submitted as a joke. I never expected it to be accepted, it hadn't been written, the abstract was submitted on the fly, improvised and in one take. Guess which talk was accepted? "Webscale is Dead; PostgreSQL is King!". They changed the first sentence of the title which is absolutely acceptable. The conference organizers know their audience best and what should be presented.


    What I have since learned is that the talk submission committee was looking for dynamic talks, dynamic content, and new, inspired ideas. A lot of talks that would have been accepted in years past weren't and my attempt at humor fits the desired outcome. At first I thought they were nuts but then I primed the talk at SDPUG/PgUS PgDay @ Southern California Linux Expo.


    I was the second to last presenter on Thursday. I was one hour off the plane. I was only staying the night and flying home the next morning, early. The talk was easily the best received talk I have given. The talk went long, the audience was engaged, laughter, knowledge and opinions were abound. When the talk was over, the talk was given enthusiastic applause and with a definite need for water, I left the room.


    I was followed by at least 20 people, if not more. I don't know how many there were but it was more than I have ever had follow me after a talk before. I was deeply honored by the reception. One set of guys that approached me said something to the effect of: "You seem like you don't mind expressing your opinions". At this point, some of you reading may need to get a paper towel for your coffee because those that know me, know I will readily express an opinion. I don't care about activist morality or political correctness. If you don't agree with me, cool. Just don't expect me to agree with you. My soapbox is my own, rent is 2500.00 a minute, get in line. I digress, what did those guys ask me about? Systemd, I don't think they were expecting my answer, because I don't really have a problem with Systemd.


    Where am I going with this post? I am stomping my way to PgConf.US with an updated version of this talk (You always learn a few things after giving a performance). I am speaking in the first slot on Friday and I am going to do everything I can to bring it. I can't promise to be the best, I can promise to do everything in my power to be my best. I am being recorded this time. My performance will be on the inner tubes forever. I have no choice.


    A challenge, do you accept?


    I challenge all speakers at this voyage of PgConf.US to take it up a notch. If you were accepted, you have a responsibility to do so. Now, now, don't get me wrong. I am not suggesting that you put on a chicken suit and Fox News t-shirt to present. I am however suggesting that if you are a monotone speaker, try not to be. If you are boring, your audience will be bored and that is the last thing the conference, you or the audience wants. So speak from your diaphragm, engage the audience and make their time worth it!


    Categories: Business, OpenSource, PostgreSQL, Python, SQL

    PostgreSQL is King! Last week was quite busy being a servant.
    Posted Wednesday Feb 25th, 2015 10:13am
    by Joshua Drake
    | Permalink

    Last week was one of the busiest community weeks I have had in a long time. It started with an excellent time in Vancouver, B.C. giving my presentation, "An evening with PostgreSQL!" at VanLUG. These are a great group of people. They took all my jibes with good humor (Canadians gave us Maple Syrup, we gave them Fox News) and we enjoyed not only technical discussion but discussions on technology in general. It is still amazing to me how many people don't realize that Linux 3.2 - 3.8 is a dead end for random IO performance.


    After VanLUG I spent the next morning at the Vancouver Aquarium with my ladies. Nothing like beautiful weather, dolphins and jelly fish to brighten the week. Once back in Bellingham, we moved on to a WhatcomPUG meeting where I presented, "Practical PostgreSQL: AWS Edition". It was the inaugural meeting but was attended by more than just the founders which is a great start!


    I got to rest from community work on Wednesday and instead dug my head into some performance problems on a client High Availability Cluster. It is amazing that even with proper provisioning how much faster ASYNC rep is over SYNC rep. Some detailed diagnosis and proving data demonstrated, we switched to ASYNC rep and all critical problems were resolved.


    On Thursday it was off to Southern California Linux Expo where I presented, "Suck it! Webscale is dead; long live PostgreSQL!". The room was packed, people laughed and for those who might have been offended, I warned you. Your offense is your problem. Look inside yourself for your insecurities! All my talks are PG-13 and it is rare that I will shy away from any topic. My disclosure aside, I had two favorite moments:



    1. When someone was willing to admit they hadn't seen Terminator. I doubt that person will ever raise his hand to one of my questions again.
    2. When Berkus (who knew the real answer) suggested it was Elton John that wrote the lyrics at the end of the presentation.


    After I spent the evening with JimmyM (BigJim, my brother), Joe Conway of SDPUG/Credativ , Jim Nasby of the fledgling bird that is Blue Treble and the very enjoyable, I don't remember her name but she works at Enova (a well known PostgreSQL installation). Flying out the next morning at 8am probably should have been avoided though.


    I am glad to be on the ground for the next few weeks before I head off to PgConf.US. It is looking like this conference is once again prove why PostgreSQL is King! Bring your people from all the lands, you are about to enter utopia.


    Categories: Business, OpenSource, PostgreSQL, SQL

    AWS performance: Results included
    Posted Wednesday Nov 12th, 2014 09:00am
    by Joshua Drake
    | Permalink

    I am not a big fan of AWS. It is a closed platform. It is designed to be the Apple of the Cloud to the Eve of Postgres users. That said, customers drive business and some of our customers use AWS, even if begrudgingly. Because of these factors we are getting very good at getting PostgreSQL to perform on AWS/EBS, albeit with some disclosures:

    1. That high IO latency is an acceptable business requirement.
    2. That you are willing to spend a lot of money to get performance you can get for less money using bare metal: rented or not. Note: This is a cloud issue not an AWS issue.


    Using the following base configuration (see adjustments for each configuration after the graphic):

    port = 5432                             
    max_connections = 500                   
    ssl = true                              
    shared_buffers = 4GB                    
    temp_buffers = 8MB                      
    work_mem = 47MB                         
    maintenance_work_mem = 512MB            
    wal_level = hot_standby                 
    synchronous_commit = on         
    commit_delay = 0                        
    commit_siblings = 5                     
    checkpoint_segments = 30               
    checkpoint_timeout = 10min              
    checkpoint_completion_target = 0.9      
    random_page_cost = 1.0                  
    effective_cache_size = 26GB
    


    Each test was run using pgbench against 9.1 except for configuration 9 which was 9.3:

    pgbench -F 100 -s 100 postgres -c 500 -j10 -t1000 -p5433


    Here are some of our latest findings:






    The AWS configuration is:


    16 Cores

    30G of memory (free -h reports 29G)

    (2) PIOPS volumes at 2000 IOPS a piece.

    The PIOPS volumes are not in A RAID and are mounted separately.

    The PIOPS volumes are formatted with xfs and default options

    The PIOPS volumes were warmed.


    1. Configuration 1:

      $PGDATA and pg_xlog on the same partition

      synchronous_commit = on


    2. Configuration 2:

      $PGDATA and pg_xlog on the same partition

      synchronous_commit = off


    3. Configuration 3:

      $PGDATA and pg_xlog on the same partition

      synchronous_commit = off

      commit_delay = 100000

      commit_siblings = 50


    4. Configuration 4:

      $PGDATA and pg_xlog on the same partition

      synchronous_commit = off

      commit_delay = 100000

      commit_siblings = 500


    5. Configuration 5:

      $PGDATA and pg_xlog on different partitions

      synchronous_commit = off

      commit_delay = 100000

      commit_siblings = 500


    6. Configuration 6:

      $PGDATA and pg_xlog on different partitions

      synchronous_commit = on

      commit_delay = 100000

      commit_siblings = 500


    7. Configuration 7:

      $PGDATA and pg_xlog on different partitions

      synchronous_commit = on

      commit_delay = 0

      commit_siblings = 5


    8. Configuration 8:

      $PGDATA and pg_xlog on different partitions

      synchronous_commit = on

      checkpoint_segments = 300

      checkpoint_timeout = 60min


    9. Configuration 9:

      $PGDATA and pg_xlog on different partitions

      PostgreSQL 9.3

      synchronous_commit = on

      checkpoint_segments = 300

      checkpoint_timeout = 60min




    Categories: Business, OpenSource, PostgreSQL, Python, SQL

    Don't kill yourself
    Posted Tuesday Oct 7th, 2014 10:11am
    by Joshua Drake
    | Permalink

    As a PostgreSQL consultant you end up working with a lot of different types of
    clients and these clients tend to all have different requirements. One client
    may need high-availability, while another needs a DBA, while yet another
    is in desperate need of being hit with a clue stick and while it is true that
    there can be difficult clients, there is no bad client.



    What!!! Surely you can't be serious?


    Don't call me shirley.


    I am absolutely serious.



    A bad client is only a reflection of a consultants inability to manage that
    client. It is true that there are difficult clients. They set unrealistic
    expectations, try to low ball you by with things like: "We can get your
    expertise for 30.00/hr from India" or my favorite: calling you directly when
    it is after hours to "chat".


    How are these not bad clients? They are not bad clients because it is you that
    controls the relationship with the client. You as the consultant have to set
    proper boundaries with the client to insure that the relationship as a whole
    is positive and profitable. If you can't manage that relationship you have two
    choices:



    1. Hire someone who can
    2. Fire the client


    Woah! Fire the client? Yes. Terminate the relationship with the client.


    It is always amazing to me how many people can't fathom the idea of firing a
    client. It is always some sacred vow that a client can fire you but you are
    left holding the bag, somehow that bag is filled with the feces of some
    dog and you are expected to light it on fire and leave it on the porch of
    some unsuspecting high-school football coach.[1]


    The counter argument to this is usually "I need the money". This is
    a valid argument but do you need the money so badly that you are willing to
    sacrifice your health or your relationships? It is astonishing how many
    consultants are willing to do exactly that. In the words of the legendary
    band Big Fun, "Suicide, don't do it"[2].


    The better you manage a client, the better the relationship. Good luck!


    1. http://en.wikipedia.org/wiki/All_the_Right_Moves_(film)

    2. https://www.youtube.com/watch?v=i-w1GeH8KPU



    Categories: Business, OpenSource, PostgreSQL, Python, SQL

    Along the lines of GCE, here are some prices
    Posted Thursday Sep 18th, 2014 01:38pm
    by Joshua Drake
    | Permalink

    I was doing some research for a customer who wanted to know where the real value to performance is. Here are some pricing structures between GCE, AWS and Softlayer. For comparison Softlayer is bare metal versus virtual.


    GCE: 670.00

    16 CPUS

    60G Memory

    2500GB HD space


    GCE: 763.08

    16 CPUS

    104G Memory

    2500GB HD space


    Amazon: 911.88

    16 CPUS

    30G Memory

    3000GB HD Space


    Amazon: 1534.00

    r3.4xlarge

    16 CPUS

    122.0 Memory

    SSD 1 x 320

    3000GB HD Space


    Amazon: 1679.00

    c3.8xlarge

    32 CPUS

    60.0 Memory

    SSD 2 x 320

    3000GB HD Space


    None of the above include egress bandwidth charges. Ingress is free.


    Softlayer: ~815 (with 72GB memory ~ 950)

    16 Cores

    RAID 10

    4TB (4 2TB drives)

    48GB Memory



    Softlayer: ~1035 (with 72GB memory ~ 1150)

    16 Cores

    RAID 10

    3TB (6 1TB drives, I also looked at 8-750GB and the price was the same. Lastly I also looked at using 2TB drives but the cost is all about the same)

    48GB Memory


    Categories: Business, OpenSource, PostgreSQL, Python, SQL

    GCE, A little advertised cloud service that is perfect for PostgreSQL
    Posted Monday Sep 15th, 2014 09:48am
    by Joshua Drake
    | Permalink

    Maybe...


    I have yet to run PostgreSQL on GCE in production. I am still testing it but I have learned the following:



    1. A standard provision disk for GCE will give you ~ 80MB/s random write.
    2. A standard SSD provisioned disk for GCE will give you ~ 240MB/s.


    Either disk can be provisioned as a raw device allowing you to use Linux Software Raid to build a RAID 10 which even further increases speed and reliability. Think about that, 4 SSD provisioned disks in a RAID 10...


    The downside I see outside of the general arguments against cloud services (shared tenancy, all your data in a big brother, lack of control over your resources, general distaste for $vendor, or whatever else we in our right minds can think up) is that GCE is current limited to 16 virtual CPUS and 104GB of memory.


    What does that mean? Well it means that it is likely that GCE is perfect for 99% of PostgreSQL workloads. By far the majority of PostgreSQL need less than 104GB of memory. Granted, we have customers that have 256GB, 512GB and even more but those are few and far between.


    It also means that EC2 is no longer your only choice for dynamic cloud provisioned VMs for PostgreSQL. Give it a shot, the more competition in this space the better.


    Categories: Business, OpenSource, PostgreSQL, Python, SQL

    PDXPGDay 2014
    Posted Monday Sep 8th, 2014 11:54am
    by Joshua Drake
    | Permalink

    I had the honor of being asked to give the introduction at PDXPGDay 2014 this past Saturday. I didn't speak very long but it was great to see a lot of the old stomping ground. It had been quite some time since I had been in the group of Wheeler, Roth, Wong, Berkus and a few others.


    The conference was really a mini-conference but it was great. It was held in the exact same room that PostgreSQL Conference West was held all the way back in 2007. It is hard to believe that was so long ago. I will say it was absolutely awesome that PDX still has the exact same vibe and presentation! (Read: I got to wear shorts and a t-shirt).


    Some items of note: Somebody was peverse enough to write a FUSE driver for PostgreSQL and it was even bi-directional. This means that PostgreSQL gets mounted as a filesystem and you can even use Joe (or yes VIM) to edit values and it saves them back to the table.


    Not nearly enough of the audience was aware of PGXN. This was a shock to me and illustrates a need for better documentation and visibility through .Org.


    The success of this PgDay continues to illustrate that other PUGS should be looking at doing the same, perhaps annually!


    Thanks again Gab and Mark for entrusting me with introducing your conference!


    Categories: Business, OpenSource, PostgreSQL, Python

    apt.postgresql.org... a wonderful if flawed apt repository
    Posted Wednesday Sep 3rd, 2014 10:52am
    by Joshua Drake
    | Permalink

    The site apt.postgresql.org is a great resource for those who live in the Debian derived world. It keeps up to date with the latest postgresql packages and has a whole team dedicated to creating these packages. Of course, this is the Open Source world so not everyone agrees 100% with the way things are done in this project. As I noted here, there are some issues.


    These issues are not to detract from otherwise excellent work but a note to those who use the repository to look for further problems. I also have a video displaying specifically what the issues are, here.



    Categories: Business, OpenSource, PostgreSQL, SQL

    Kicking the Donkey of PostgreSQL Replication
    Posted Tuesday Feb 4th, 2014 12:50pm
    by Joshua Drake
    | Permalink

    This is the title of a talk I am developing for the matured PostgreSQL Conference:
    PGConf NYC 2014 . Formerly a PgDay, this is now a full blown conference extending
    two days with three tracks. From all reports it is set to be the largest
    PostgreSQL Conference ever in the United States, surpassing even the old West and
    East series (which no conference in the U.S. has done to date). It is truly exciting
    times for our community.


    This talk will be a departure from my standby talks of PostgreSQL Performance and
    Choosing the right hardware. Katz asked me, "to bring your full East Coast from the West Coast personality.". I plan on doing so. So cinch up the boot straps it is going to be a ride. In classic JD style I am going to be blunt and to the point about the good, the bad, and the, "WTH were they thinking" of PostgreSQL
    Replication.


    So outside of personality what am I really trying to deliver to the community? I
    think the description of the talk says it all:



  • Have you ever wondered how to configure PostgreSQL Replication?

  • Have you ever wondered how to configure PostgreSQL Log Shipping?

  • Have you ever wondered: Which one is best for my application?

  • Are you aware of the pitfalls of Replication? Where it breaks? When it will
    act in a way that is counter-intuitive? Do you know how to fix it?

  • Do you know how to monitor it?


  • If you have asked yourself any of these questions, this is the talk for you. We
    will step through PostgreSQL replication, the technology involved, the
    configuration parameters and how to configure it in a manner that isn't fragile.
    We will also cover gotcha's how to prepare for them and understanding what replication
    is doing.


    This talk is not not for the faint of heart. I will take a no holds barred approach
    and give you the real deal, the dirt and the gold that is one of the most sought
    after PostgreSQL features.


    On a closing note, this conference is showing how a PostgreSQL User Group, combined with the resources of United States PostgreSQL can help grow and educate the community. They don't just help NYCPUG, they also help PDXPUG, PHILLYPUG and SEAPUG. Maybe your PUG should consider working with PgUS? If so, give "Jonathan S. Katz" [jonathan.katz {@} excoventures.com] a jingle.


    Categories: Business, OpenSource, PostgreSQL, Python, SQL


    Copyright © 2000-2015 Command Prompt, Inc. All Rights Reserved. All trademarks property of their respective owners.