| Subcribe via RSS
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.
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!
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
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 ...
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:
Tags: PostgreSQLComing 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:
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.”
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!
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!
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: HandlerSocket, MySQL, NoSQL