| Subcribe via RSS

Creating Stacked Line Graphs in R with ggplot2

February 20th, 2013 | No Comments | Posted in R, Random Tech

I spent a few hours yesterday banging my head against the wall trying to create stacked line graphs (aka area charts) using R. There are a number of tutorials available, but none resulted in the target graph type, so I hope this simple post saves somebody from a whole lot of trial and error.

More »

Sessions I’d like to see at Percona Live MySQL Conference and Expo 2013

February 13th, 2013 | No Comments | Posted in Conferences, MySQL

I’ve finally gotten around to starting to write my presentations for PLMCE2013. So, in the spirit of procrastination, I went through all of the sessions and highlighted some of the ones that I want to attend!

As Baron noted in his post on the subject, the real highlight of the conference will be seeing and talking to everyone. I hope to see you all there!

Predicting Postgres Performance By Looking At Old MySQL Bugs

September 20th, 2012 | 3 Comments | Posted in Performance, Postgres

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”). Let me explain. Assume the following table:

postgres=# \d t1
         Table "public.t1"
 Column |     Type      | Modifiers 
--------+---------------+-----------
 id     | character(10) | not null

How would we expect the following query to be handled

postgres=# select count(*) from t1 WHERE id = 'aoeuaoeuaoeu';
-[ RECORD 1 ]
count | 0

More »

pg_stat_activity shows future queries!

September 11th, 2012 | 2 Comments | Posted in Postgres, Uncategorized

This morning I was working on getting some statistics on average transaction open time on a relatively underutilized host when I noticed some oddities. Periodically, I would see that my average duration of open transactions would dip below zero seconds. As I looked into it, I came across the following result from a simple query to pg_stat_activity:

postgres=# SELECT xact_start, NOW() AS now FROM pg_stat_activity WHERE xact_start IS NOT NULL;
-[ RECORD 1 ]----------------------------
xact_start | 2012-09-11 19:33:41.253139+00 < -- Note this happens AFTER NOW() (i.e. in the future)
now        | 2012-09-11 19:33:41.252795+00
...

More »

Postgres unaware of it’s own connection limits?

September 5th, 2012 | 8 Comments | Posted in Bug, Postgres

Normally I’d just file a bug with something like this, but there’s no PostgreSQL bug tracker. So I’m left with a blog post …

I was doing some testing with PostgreSQL memory usage and needed to know what the maximum value of max_connections is. So naturally, I consulted the documentation. It stated that this was an integer, so again, I consulted documentation which stated that any value between -2147483648 and +2147483647 was valid.

Even though -2147483648 is clearly invalid, I wanted to try it. On restart, I got the following message in pgstartup.log:

More »

Tags:

Approximating Postgres Replication Delay

August 30th, 2012 | 2 Comments | Posted in Postgres, Replication

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 the following approach:

More »

PostgreSQL 9.1 max_wal_senders – The Missing Documentation

August 8th, 2012 | No Comments | Posted in Postgres

When setting up Log Shipping or Streaming Replication with PostgreSQL 9.1, you’ll inevitably come across the max_wal_senders configuration variable. The docs on this are sparse, saying only:

“Specifies the maximum number of concurrent connections from standby servers or streaming base backup clients (i.e., the maximum number of simultaneously running WAL sender processes). The default is zero. This parameter can only be set at server start. wal_level must be set to archive or hot_standby to allow connections from standby servers.”

More »

Installing HandlerSocket on RHEL 6 + Percona-Server 5.5 in Five(ish) Easy Steps

As a followup to my previous post on installing HandlerSocket on CentOS 5 + Percona-Server 5.1, I’ve been asked to provide an updated HOWTO for RHEL 6 + Percona-Server 5.5. Although very similar to the original steps, there are some new traps (RHEL 6 comes with SELinux enabled) and there are some new ec2 gotchas!

More »

(More) Numbers Everyone Should Know

September 27th, 2011 | No Comments | Posted in Performance, Random Tech

By now, everybody should be aware of Jeff Dean’s Numbers Everyone Should Know:

L1 cache reference 0.5 ns
Branch mispredict 5 ns
L2 cache reference 7 ns
Mutex lock/unlock 100 ns
Main memory reference 100 ns
Compress 1K bytes with Zippy 10,000 ns
Send 2K bytes over 1 Gbps network 20,000 ns
Read 1 MB sequentially from memory 250,000 ns
Round trip within same datacenter 500,000 ns
Disk seek 10,000,000 ns
Read 1 MB sequentially from network 10,000,000 ns
Read 1 MB sequentially from disk 30,000,000 ns
Send packet CA->Netherlands->CA 150,000,000 ns

But there are some additional numbers that should be readily available at the front of your mind:

Uptime Percentage Downtime (per year)
99% 87.6 hours
99.9% 8.76 hours
99.99% 52.56 minutes
99.999% (Five Nines) 5.26 minutes
99.9999% 31.5 seconds
99.99999% (Seven Nines) 3 seconds

For whatever reason, these numbers tend to surprise people. An additional consideration when deciding your high availability needs is that with each additional Nine, the cost of implementation and ongoing maintenance rises exponentially!

HandlerSocket execute_multi Curiosities

April 4th, 2011 | No Comments | Posted in HandlerSocket, InnoDB, MySQL, NoSQL, Random Tech

A post on the HandlerSocket-dev mailing list the other day got me thinking about the performance of MySQL’s IN() construct versus HandlerSocket’s execute_multi. So I started a little test, using MySQL 5.5 + HandlerSocket’s latest commits:

mysql> CREATE TABLE `test`.`t1` (
    ->   `id` int unsigned NOT NULL AUTO_INCREMENT,
    ->   `val` char(32) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
 More »
Tags: , ,