Entries by Jory Stone

I’ve been working with moving large databases in PostgreSQL and have found that PostgreSQL is a true memory hog when inserting millions of rows in one statement. If the target table has a number of foreign key references PostgreSQL seems to choke horribly and use insane amounts of memory, 1GB per million rows in my original case.
We have a rather simple table that is composed of six integer columns, two timestamps, and one boolean. I first ran into this issue when inserting with a select statement that had a simple join and where clause. Initially I thought the select statement was the one causing the out of memory issues. But I found running the select alone worked and changing the statement to a select into piped the data into a new table without any issues.

I created a simple test case to reproduce this with the following schema,

CREATE TABLE table1 (
table1_id integer NOT NULL,
name character varying NOT NULL,
PRIMARY KEY(table1_id)
);
CREATE TABLE table2 (
table2_id integer NOT NULL,
name character varying NOT NULL,
PRIMARY KEY(table2_id)
);
CREATE TABLE table3 (
table3_id integer NOT NULL,
name character varying NOT NULL,
PRIMARY KEY(table3_id)
);
CREATE TABLE table4 (
table4_id integer NOT NULL,
name character varying NOT NULL,
PRIMARY KEY(table4_id)
);
CREATE TABLE test_target (
table1_id integer NOT NULL,
table2_id integer NOT NULL,
visible boolean NOT NULL,
date_added timestamp with time zone NOT NULL,
date_updated timestamp with time zone NOT NULL,
table3_id1 integer NOT NULL,
table3_id2 integer NOT NULL,
table4_id1 integer NOT NULL,
table4_id2 integer NOT NULL,
FOREIGN KEY (table1_id) REFERENCES table1(table1_id),
FOREIGN KEY (table2_id) REFERENCES table2(table2_id),
FOREIGN KEY (table3_id1) REFERENCES table3(table3_id),
FOREIGN KEY (table3_id2) REFERENCES table3(table3_id),
FOREIGN KEY (table4_id1) REFERENCES table4(table4_id),
FOREIGN KEY (table4_id2) REFERENCES table4(table4_id)
);

Running this INSERT / SELECT below can cause the PostgreSQL to keep expanding in memory usage. On a 32-bit machine it aborts due to an out of memory error around 2GB, but on a 64-bit Linux machine it keeps using memory until all of the main memory and swap is full and then the oom-killer process is spawned and starts killing processes.

INSERT INTO test_target
(
table1_id, table2_id, visible,
date_added, date_updated,
table3_id1, table3_id2,
table4_id1, table4_id2
)
SELECT
0 as table1_id, 0 as table2_id,
TRUE as visible,
now() as date_added,
now() as date_updated,
0 as table3_id1, 0 as table3_id2,
0 as table4_id1, 0 as table4_id2
FROM generate_series(1, 13000000);

I've tried this on PostgreSQL 8.3.7 under Linux and I found the same behavior in PostgreSQL 8.4 Beta for Windows.

A simple workaround I found is to simply drop the foreign keys before the insert, and re-add the foreign keys after the data is loaded into the table.

I have registered a project for Munin Node for Windows on SourceForge and am awaiting approval.
http://sourceforge.net/projects/munin-nodewin32/

Once it is approved I'll upload the source code to the Subversion repo for public access.

I've posted v1.5 of Munin Node for Windows.

Summary
Munin Node for Windows AKA munin-node-win32, is a Windows client for the Munin monitoring system.

It is written in C++ with most plugins built into the executable. Which is different from the standard munin-node client, which only uses external plugins written as shell and Perl scripts.

Change Log for v1.5
+ Added readme documentation
+ Updated -install / -uninstall switches to handle Window Firewall exceptions
+ Added SpeedFan plugin
+ Added External Plugins plugin, external plugins are now supported
+ Added Generic Performance Counter plugin
+ Removed Uptime, Disktime plugins. Replaced with the Generic Performance Counter plugin
+ Improved error handling in Cpu plugin
+ Now Unicode compatible
+ Fixed memory leaks
+ Improved multi-thread safety
+ Added better Event Log support

Download
MSI Installer (225KB)
Binary (135KB)
Source Code (423KB)

I've created a plugin for foobar2000 v0.9, it stores all your plays in a SQLite database.

In addition to storing details on every play, the time and duration, this plugin automatically scores every played track. It uses a formula similar to AmroK scoring with a range of 0-100.
You can access the score for each track via the %score% title formatting string.

I'm hoping to create a viewer for the database so that you can see detailed trends as every play is stored in the database.

Here are the downloads,
foo_playcount_db v0.4 Binary (253KB)
foo_playcount_db v0.4 Source Code + Binary (1.31MB)

I've released a new version of munin-node-win32 today.
It is an Windows client for the Munin monitoring system. It supports getting disk usage, HDD temperatures, memory usage, and uptime. If you have Motherboard Monitor installed and running, munin-node-win32 can query it for system temperatures, voltages, and fan speeds.

The major new feature for this release is the addition of a cpu monitoring plugin and a configuration file that allows disabling and enabling plugins.

Binary + Source Code ZIP (141KB)
Beta MSI Installer (168KB)

I'm current working on embedding Python so Python scripts can be used to write new plugins for munin-node-win32.

I've moved my blog to my primary domain, www.jory.info, with hosting by 1and1, a fairly decent host with good prices.

munin-node-win32 v1.1 (30KB)

An Windows client for the Munin monitoring system. Supports getting disk usage, HDD temperatures, memory usage, and uptime. If you have Motherboard Monitor installed and running, munin-node-win32 can query it for system temperatures, voltages, and fan speeds.

Binary and Source Code included.

Use the -install command line switch to install as
a service, -uninstall will remove the service.

munin-node-win32 v0.1 (30KB)

An Windows client for the Munin
monitoring system. Supports getting disk usage, memory usage, and
uptime. If you have Motherboard
Monitor installed and running, munin-node-win32 can query it
for system temperatures, voltages, and fan speeds.

Binary and Source Code included.

Use the -install command line switch to install as
a service, -uninstall will remove the service.

I've setup yet another website.
My previous host Dreamhost deleted my host account without warning and hasn't responded to my questions yet.

Now I've moved my site to silenceisdefeat.org, which is quite an awesome free service ($1 donation required to get an account). They've been stable for over 2yrs and provide 50MB of free space.

My site is also now in blog format, hopefully it will make it easier to update

Well, I've redone my website. Gone is the Mambo CMS PHP and back to nice lean XHTML and CSS. The Mambo CMS was easy to use, but required being online to make any changes. It also was a pain to backup, you had to backup both the files and the MySQL database. Also it was exploitable, being PHP, I really should have keep it updated to the latest Mambo version but that was too much work for a website as small as mine.