Archive for the ‘PostgreSQL’ Category

Same query, 3 databases, 3 different results

Октябрь 12th, 2011
The SQL standard leaves a lot of room for different implementations. This is a little demonstration of one of such differences.

SQLite  3.7.4
sqlite> create table t1 (id serial, t time);
sqlite> insert into t1(t) values ('00:05:10');
sqlite> select t,t*1.5 from t1;
00:05:10|0.0

MySQL 5.6.4-m5
mysql> create table t1 (id serial, t time);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1(t) values ('00:05:10');
Query OK, 1 row affected (0.00 sec)

mysql> select t,t*1.5 from t1;
+----------+-------+
| t        | t*1.5 |
+----------+-------+
| 00:05:10 |   765 |
+----------+-------+
1 row in set (0.00 sec)

PostgreSQL 9.0.3
test=# create table t1 (id serial, t time);
NOTICE:  CREATE TABLE will create implicit sequence "t1_id_seq" for serial column "t1.id"
CREATE TABLE
test=# insert into t1(t) values ('00:05:10');
INSERT 0 1
test=# select t,t*1.5 from t1;
    t     | ?column?
----------+----------
 00:05:10 | 00:07:45
(1 row)

I think that the behaviour from PostgreSQL is the correct one. MySQL will just remove the :'s to get the string 000510 and then multiplies that value. The behaviour from SQLite is even more strange.

Of course for MySQL you could use the time_to_sec and sec_to_time functions.

mysql> select t,sec_to_time(time_to_sec(t)*1.5) from t1;
+----------+-------------------------------+
| t        | sec_to_time(time_to_sec(t)*2) |
+----------+-------------------------------+
| 00:05:10 | 00:07:45                      |
+----------+-------------------------------+
1 row in set (0.00 sec)

But those functions are not available on SQLite.

sqlite> select t,sec_to_time(time_to_sec(t)*1.5) from t1;
Error: no such function: sec_to_time

PlanetMySQL Voting: Vote UP / Vote DOWN

When systems scale better than linearly

Октябрь 7th, 2011

I’ve been seeing a few occasions where Neil J. Gunther’s Universal Scalability Law doesn’t seem to model all of the important factors in a system as it scales. Models are only models, and they’re not the whole truth, so they never match reality perfectly. But there appear to be a small number of cases where systems can actually scale a bit better than linearly over a portion of the domain, due to what I’ve been calling an “economy of scale.” I believe that the Universal Scalability Law might need a third factor (seriality, coherency, and the new factor, economy of scale). I don’t think that the results I’m seeing can be modeled adequately with only two parameters.

Here are two publicly available cases that appear to demonstrate this phenomenon: Robert Haas’s recent blog post on PostgreSQL, titled Scalability, in Graphical Form, Analyzed and Mikael Ronstrom’s post from May on MySQL (NDB) Cluster, titled Better than Linear Scaling is Possible.

Dr. Ronstrom’s post discusses the mechanics of the phenomenon, and speculates (I’m not sure it’s conclusive) that it is from a combination of partitioning and better use of CPU caches. Now someone needs to do the math to figure out how to include this factor into the equation.

The good thing about the Universal Scalability Law is how simple and applicable it is for many systems. It’s nice that this economy-of-scale factor seems to be unusual and the simpler model remains easy to apply for a large variety of tasks.

Further Reading:


PlanetMySQL Voting: Vote UP / Vote DOWN

Open Source Hardware

Октябрь 1st, 2011
Back in 2010 I stopped buying test servers from Dell and began building them from components using Intel i7 processors, X58-based mother boards, and modular power supplies from Ultra.  It was a good way to learn about hardware.  Besides, it was getting old to pay for Dell desktop systems with Windows, which I would then wipe off when installing Linux.  Between the educational value of understanding the systems better, selecting the exact components I wanted, and being able to fix problems quickly, it has been one of the best investments I have ever made.  And it didn't cost any more than equivalent Dell servers.

For this reason, a couple of recent articles about computer hardware caught my attention.  First, Dell is losing business as companies like Facebook build their own customized servers.  Open source database performance experts like Peter Zaitsev have been talking about full-stack optimization including hardware for years.  Google built their original servers using off-the-shelf parts.  Vertical integration of applications and hardware has since gone mainstream.  If you deploy the same application(s) on many machines, balancing characteristics like cost, performance, and power utilization is no longer a specialist activity but a necessity of business.  It's not just cutting out the Microsoft tax but many other optimizations as well.

Second, developments in hardware itself are making custom systems more attractive to a wide range of users.  A recent blog post by Bunnie Huang describes how decreases in the slope of CPU clock speed increase over time mean you can get better cost/performance by building optimized, application-specific systems now than waiting for across-the-board improvements.  Stable standards also drive down the difficulty of rolling your own.  Components on mid-range servers are sufficiently standardized it is easier to build basic systems from components than to put together a bicycle from scratch.  Try building your own wheels sometime if you don't believe this.

Easily customizable hardware has important consequences.  At a business level, Dell and other mainline hardware vendors will adapt to lower margins, but the market for generic, mid-range appliances has evaporated.  Starting around 2005 there was a wave of companies trying to sell open source databases, memcached, and datamarts on custom hardware.   Most seem to have moved away from hardware, like Schooner,  or folded entirely (like Gear6 and Kickfire).  The long-term market for such appliances, to the extent it exists, is in the cloud.

The other consequence is potentially far more significant.  The traditional walls that encapsulated hardware and software design are breaking down.  Big web properties or large ISPs like Rackspace run lean design teams that integrate hardware with open source software deployment.  This not just a matter of software engineers learning about hardware or vice-versa.  It is the tip of a much bigger iceberg.  Facebook recently started the Open Compute Project, which is a community-based effort to design server infrastructure.   In their own words:
By releasing Open Compute Project technologies as open hardware, our goal is to develop servers and data centers following the model traditionally associated with open source software projects. That’s where you come in.
Facebook and others are opening up data center design.  Gamers have been building their own systems for years.  Assuming Bunnie's logic is correct, open hardware will apply to wide range of devices from phones up to massive clusters.  Community-based, customized system designs are no longer an oddity but part of a broad movement that will change the way all of us think about building and deploying applications on any kind of physical hardware.  It will upset current companies but also create opportunities for new kinds of businesses.  The "cloud" is not the only revolution in computing.  Open source hardware has arrived.  

PlanetMySQL Voting: Vote UP / Vote DOWN

Blog Summary for Week of September 5

Сентябрь 11th, 2011

1. Apache and MySQL Logging with Syslog-ng
This article shows how to use the popular system logging tool Syslog-ng to log Apache and MySQL events. Apache does not log via syslog-ng by default so we go over two methods of easily remedying this. We also show how to use SQL queries to view syslog-ng data.

2. Using M3 to take System Monitors to the Next Level
Monitis provides built in functionality to monitor a wide variety of system statistics as well as the ability to create custom system monitors. Monitis Monitor Manager, or M3 for short, allows you to take these custom monitors even further by providing you with a simple framework to use the incredible power of regular expressions to pull and format literally any kind of data and automatically send it over the wire to your Monitis dashboard.

3. How to Log to PostgreSQL with Syslog-ng
Here we show how to use syslog-ng to log and view PostgreSQL log data. We also include some tips on troubleshooting and avoiding annoyances.

4. 30 VMware vSphere Performance Tips
There’s a multitude of optimizations that can be made to make your vSphere setup perform at optimal levels . This article covers 30 tips and tricks that will ensure the best performance from VMware’s vSphere and some even apply to other virtualization products. Some of the highlights are to Use only VMware-compatible hardware and Run your system through a burn-in/stress test.

Share Now:
  • del.icio.us
  • Digg
  • Facebook
  • LinkedIn
  • BlinkList
  • DZone
  • Google Bookmarks
  • Reddit
  • StumbleUpon
  • Twitter
  • RSS

PlanetMySQL Voting: Vote UP / Vote DOWN

What’s Next for Tungsten Replicator

Сентябрь 8th, 2011
As Giuseppe Maxia recently posted we released Tungsten Replicator 2.0.4 this week.  It has a raft of bug fixes and new features of which one-line installations are the single biggest improvement.  I set up replicators dozens of times a day and having a single command for standard cluster topologies is a huge step forward.  Kudos to Jeff Mace for getting this nailed down.

So what's next?  You can get see what we are up to in general by looking at our issues list.  We cannot do everything at once, but here are the current priorities for Tungsten Replicator 2.0.5.
  • Parallel replication speed and robustness.  I'm currently working on eliminating choke points in performance (like this one) as well as eliminating corner cases that cause the replicator to require manual intervention, such as aging out logs that are still needed by slaves.  
  • Multi-master replication.  This includes better support for system of record architectures, many masters to one slave, and replication between the same databases on different sites.  Stephane Giron nailed a key MyISAM multi-master bug for the last release.  We will continue to polish this as we work through our current projects.   
  • Better installations for more types of databases.  Jeff recently hacked in support for PostgreSQL as well as Oracle slaves, and we are contemplating addition of MongoDB support.  Heterogeneous replication is getting simpler to set up.  
  • Filter usability.  Giuseppe has a list of improvements for filters, which are one of the most powerful Tungsten Replicator features but not as easy for non-developers to use as we would like.  Better installation support is first on the list followed by ability to load and unload dynamically.  
  • Data warehouse loading.  We have a design for fast data warehouse loading that I hope we'll be able to implement in the next few weeks.  Linas Virbalas has also been working on this problem along with a number of other heterogeneous projects for customers.  
This is a lot of work and not everything will necessarily be finished when 2.0.5 goes out.  However, I hope we'll make progress on all of them.  In case you are wondering how we pick things, replicator development is largely driven by customer projects.   If you have something you need in the replicator, please contact Continuent.

After this build we will... Er, let's get 2.0.5 done first.  Suffice it to say we have a long list of useful and interesting features to discuss in future blog articles.

PlanetMySQL Voting: Vote UP / Vote DOWN

451 CAOS Links 2011.08.31

Август 31st, 2011

MapR and Funambol raise funding. VMware virtually supports PostgreSQL. And more.

# MapR raised $20m series B for its Hadoop distribution from Redpoint Ventures, Lightspeed Venture Partners and NEA.

# Funambol raised $3m in funding from previous investors HIG Ventures, Pacven Walden Ventures and Nexit Infocom.

# VMware launched vFabric Postgres as part of vFabric Data Director database-as-a-service launch.

# Citrix released a new edition of CloudStack, making the whole cloud management product available using the GNU GPLv3.

# Yahoo has contributed 84% of Apache Hadoop lines of code and 72% of patches, according to Hortonworks’ analysis.

# Red Hat invited Red Hat Enterprise Linux users to help discuss features for Red Hat Enterprise Linux 7.

# Talend announced that Peter Gyenes has joined its Board of Directors.

# Mandriva announced the release of Mandriva 2011.

# The Document Foundation announced the release of version LibreOffice 3.4.3, intended for enterprise deployments.

# Zmanda announced the availability of Zmanda Cloud Backup (ZCB) 4.0.

# The 10th Circuit Court of Appeals ruled against on SCO’s appeal that it, and not Novell, owned the Unix copyrights.

# Oracle retired its licence for distributing its Java with Linux.

# Bruce Byfield wrote an interesting article on how Linus Torvalds and other open source developers avoid burnout.


PlanetMySQL Voting: Vote UP / Vote DOWN

Mobile Internet Access in Germany for Open Source Road Warriors

Июль 24th, 2011
Reliable Internet access is a long-standing problem for road warriors visiting foreign countries.  Open source developers in particular have problems reconciling travel with addiction to high-bandwidth network access from laptop computers.  Wi-Fi hotspots are scarce, costly, often slow, and in some cases complicated by inconvenient local laws like Italy's Pisanu Decree.  International mobile network access plans are ridiculously expensive or like DROAM have download limits that make them useless for serious programming.

The best solution in many cases is to look for a local pre-paid mobile access plan in each country you visit.   Mobile networks are widely available and fast in developed regions, and there are cheap plans that limit the amount you pay while providing solid connectivity.  On a recent trip to Germany I found a great solution for local Internet access from FONIC, which offers a prepaid data plan using their FONIC Surf-Stick.  The Surf-Stick is a USB modem that plugs into a USB port on your laptop and looks like the following. 



After you buy the Surf Stick, you can add money to it using credits purchased in local stores.  It's a relatively simple solution provided you understand a little about networking and can work your way through a bit of German.

The rest of this article is a description of how to use FONIC in Germany, as well as a review of the performance and a couple of downsides.  I do not have any connection with FONIC other than using their products.  You may have different experiences or find something better.  If so, please write an article about it.

Getting Started

I bought got my Surf Stick for 49 Euro at Saturn, a big German consumer electronics chain.  You get the modem itself plus a SIM card and one day of free surfing.  You can get also the stick from other FONIC partners or order it off the FONIC website.  Surprisingly it does not appear to be available in places like Frankfurt or Munich Airport.

The first step once you have the modem is to initialize the SIM on FONIC's site by registering yourself as a new user.  To do this you'll need to have your FONIC phone number and activation code ("Freischaltungsnummer"), which are written on the side of the envelope that contains the SIM and that you should try to avoid losing.  You'll also need a German address that you enter as part of the sign-up.  A hotel address is fine.  It takes a couple of hours after registration to complete provisioning.  

Once provisioned, connecting is easy.  Put the SIM in the surf stick and plug the stick in a USB port on your computer.  On Mac OS X, you'll see an application called "Mobile Partner."  Here are the steps to activate a connection: 
  1. Click the Mobile Partner app.  
  2. Enter your PIN (also on the SIM envelope) and press Return. 
  3. The application will stall for a few seconds while it looks for the modem.  Once complete it will show a screen entitled "Verbinden" (connect).   
  4. Click Verbinden.  A couple of pop-ups will appear and you are online. 
  5. When you want to drop the connection click "Trennen" (disconnect).  
You will now need to load some money into your account.  There's no way to top up online with a credit card--you either have to register a German bank account or purchase credits.  For foreigners the only practical thing is therefore to buy a credit, which is called an "Aufladebon."  You can get them at stores like Lidl, Rossman, and Jet as well as Esso gas stations.  Just ask for the following in German:  FONIC Aufladebon für 20 Euro, which is a 20 Euro credit and gives you 8 days of surfing.   I recommend buying this at the same store where you get your modem, which means the start-up cost is about 70 Euros.  

The Aufladebon itself a bit dissapointing:  a printed receipt with a 12 digit number and some instructions in German.  Start your Mobile Partner app, connect to the Internet, and click on "Guthaben verwalten" (manage credit).  Select "Guthabenkarte aufladen" (load credit), enter your 12 digit number, and press enter.   Your credit is now topped up for a while.  You can check the level using the "Guthabenabfrage" (check credit) button.  

Performance

I used the FONIC modem for about a week.  It functioned well in every location that had at all reasonable connectivity.  FONIC handles transitions across cells really well--provided there's connectivity it seems to use the fastest available protocol, such as HSPA, and switches seamlessly down to lower capacity protocols like Edge if that is all that is available.  You can use it in a car or train without experiencing application problems--I used it on the Autobahn between Hamburg and Lübeck and it worked surprisingly well.   (Just be clear, somebody else was driving at the time.) 

With good connectivity, the rated speed is 7.2Mbps down and 5.76 upload, which is comparable to average household connections.  PDFs and similar files downloaded at 150K/sec.  Upload was similarly snappy.  

Applications like ssh and even remote debugging/profiling worked very well.  In my case this included running Yourkit Java profiler connecting into an application running on servers elsewhere in Germany.  I also hosted GotoMeeting web conferences.  The latency seems higher than DSL--typical latencies run about 100 to 200ms, which is about the same latency you get when connecting from the US West Coast to sites in Europe. 

Daily transfer limits are very competitive.  The current limit is 500Mb per day at full speed access, though in practice it seemed to cut off around 600Mb for me.  This is better than offerings from the big guys like T-Mobile and Vodafone.  FONIC implements the limit quite elegantly.  First you get a nice SMS that you are near the limit, followed by another that you are over the limit.  However, after that you do not get cut off.  The connection just switches to 64kbps until the next day.  This is still faster than the Wi-Fi connectivity in many hotel rooms.

If you don't want to wait for the friendly SMS message about limits, you can check your limits easily on using the Mobile Partner app.



Speaking of the app, Mobile Partner seems really solid.  There were no bugs, crashes or weird hangs the way you get with some of the US providers like Verizon's prepaid Internet app. I only ran it on Mac OS X but I would guess it is just as good on Windows.  

Caveats

In general FONIC is outstanding.   Life is not perfect, so here are a few of the limitations I found.   
  1. German.  FONIC is 100% German, including the website and the app.  You'll need to know some German or make friends fast.  Also, the data plan apparently only works in Germany.  
  2. Phone connectivity.  If your cell phone does not work, FONIC won't either.  The nice thing is that you can have connectivity drop out for a bit while crossing cells in a train or car without losing TCP/IP sessions.  
  3. Occasional routing problems. On one evening in one particular location the FONIC modem did not work at all well, reporting ping latencies of 50+ seconds or dropping out entirely.  It was not apparent whether this was due to poor cell coverage or another issue.  It worked fine everywhere else.
There appear to be some competitors to FONIC but I have no idea how well they work.  One of the advantages of FONIC is that they have many partners in Germany that sell both modems as well as the credits.  That would also be a consideration in evaluating competing products.

Summary

Some day it may be possible to get decent mobile data plans that work for international travel.  Given the complexity of telecom regulation and the motivations of local carriers to prevent competition, you probably don't want to hold your breath until they arrive.

In the meantime, FONIC looks like a good Internet access solution for anybody traveling to Germany for more than a few days who needs constant, high-capacity access.  I was stuck in a couple of locations that had no network access, so it was a life-saver in those cases.  However, FONIC is also cheaper than hotel Wi-Fi and has better performance than many Wi-Fi hotspots.   If you move about or uncertain about the quality of the Internet connectivity where you are staying, I highly recommend it.

If you know of similar plans for other countries, please share your experiences.  I would love to find something like FONIC for Italy, Spain, and the US.  

PlanetMySQL Voting: Vote UP / Vote DOWN

I’ll be presenting at Postgres Open 2011

Июль 20th, 2011

I’ve been accepted to present at the brand-new and very exciting Postgres Open 2011 about system scaling, TCP traffic, and mathematical modeling. I’m really looking forward to it — it will be my first PostgreSQL conference in a couple of years! See you there.

Related posts:

  1. Postgres folks, consider the 2011 MySQL conference
  2. O’Reilly MySQL 2011 conference CfP is open
  3. My sessions at the O’Reilly MySQL Conference 2011
  4. I’m a Postgres user, as it turns out
  5. Awesome Postgres/MySQL cross-pollination


PlanetMySQL Voting: Vote UP / Vote DOWN

PostGIS 1.5 in Postgresql 9.0 install on CentOS 5.6

Июль 14th, 2011

I love short and consise install instructions. I know this is a MySQL blog but our good friend PostGreSQL has a great GIS library. This is what I learned upgrading our PostGIS system to GIS 1.5. Much thanks to Jeremy Tunnell for give this document it’s start.

Start with CentOS 5.6 x86_64 basic install.

Add the PostgreSQL Yum repository to your system.

 $ wget http://yum.pgrpms.org/reporpms/9.0/pgdg-centos-9.0-2.noarch.rpm
 $ rpm -i pgdg-centos-9.0-2.noarch.rpm

Another location for these is DAG. I have to tried these so your results may very.

You will need to exclude the packages CentOS provide by added two lines to the BASE and UPDATE sections of /etc/yum.repos.d/CentOS-Base.repo. They are:

exclude=postgresql*
exclude=geos*

You you are ready to install the needed packages. This includes proj version 4 and geos version 3.

 $ yum install postgresql90-contrib.x86_64
 $ yum install postgis90.x86_64
 $ yum install proj
 $ yum install geos
 $ yum install php-pear
 $ yum install php-devel

GEOS

There may be some dependencies you will have to work through. If you are using PDO and haven’t installed the pgsql PDO drivers, you can do it now:

 $ pecl install pdo_pgsql

PL/pgSQL

You you are ready to initialize the database files and start postgresql.

 $ service postgresql-9.0 initdb
 $ service postgresql-9.0 start

Now you can change to the postgres user and begin installing the functions for GIS. You have to start with defining the language.

 $ su – postgres
 $ psql
 # create language plpgsql ;
 # \q

Now you can create your database and add the GIS functions calls to it.

 $ createdb geos
 $ psql -d geos -f /usr/pgsql-9.0/share/contrib/postgis-1.5/postgis.sql
 $ psql -d geos -f /usr/pgsql-9.0/share/contrib/postgis-1.5/spatial_ref_sys.sql

You you can verify the install.

 $ psql geos
 # select postgis_full_version();
                                              postgis_full_version
——————————————————————————————————–
 POSTGIS=”1.5.2″ GEOS=”3.2.2-CAPI-1.6.2″ PROJ=”Rel. 4.7.1, 23 September 2009″ LIBXML=”2.6.26″ USE_STATS
(1 row)

For more on using PostGIS check out Jeremy’s “PostGIS part 2”.

.

Tweet


PlanetMySQL Voting: Vote UP / Vote DOWN

New Aspersa I/O analysis tool, diskstats

Февраль 6th, 2011

I’ve just committed some changes to diskstats, an I/O analysis tool in Aspersa that’s actually been in the Subversion repository for a long time, but in a barely usable fashion and with no documentation. Now it’s usable and documented.

It is basically a reimplementation of iostat in awk. Why on earth would I reinvent that wheel? Because I spend a lot of time gathering and analyzing raw data from /proc/diskstats, which is vital to really understanding what the storage subsystem is doing. The iostat tool hides important details. Seeing that detail has immediately solved many a disk performance problem and proven SAN vendors wrong, for instance. (I used to do this the old-fashioned way.) Disk performance, of course, is one of the most important things to analyze in a database server that’s struggling.

Also, iostat isn’t interactive, and I wanted an interactive, menu-driven tool to quickly slice and dice the data and drill down into what is happening with I/O. The data it accepts is in the same format as that stored by the stalk and collect tools, which is my default post-mortem toolset. And finally — and I know this might be hard to believe — I’ve been asked to fix problems many times on systems that don’t have iostat and I am not allowed to install it.

And wouldn’t you know it, as I wrote the user’s manual I found a bug, after all my ranting about how other tools show I/O stats wrong. I don’t have time to diagnose or fix the bug right now, so maybe someone else can contribute that. There is a test suite (remind me to explain sometime how I make Bash scripts highly testable) so if we find the problem and fix it, it’ll stay fixed. Contribute your fix to the bug report :-)

Related posts:

  1. How to read Linux’s /proc/diskstats easily
  2. Aspersa, a new opensource toolkit
  3. Using Aspersa to capture diagnostic data
  4. Apsersa’s summary tool supports Adaptec and MegaRAID controllers
  5. Aspersa gets a user manual


PlanetMySQL Voting: Vote UP / Vote DOWN