Pensieri di un lunatico minore
I have been alternating, back and forth, between my MacBook Pro and a Windows XP notebook for development, depending on where I’m at. This has been relatively easy through the use of Subversion, however, I keep running into a consistent problem when using Rails and PostgreSQL together:
./script/../config/../vendor/rails/railties/lib/commands/runner.rb:47: c:/ruby/lib/ruby/1.8/readbytes.rb:21:in `read': Invalid argument (Errno::EINVAL)
from c:/ruby/lib/ruby/1.8/readbytes.rb:21:in `readbytes'
from c:/ruby/lib/ruby/gems/1.8/gems/postgres-pr-0.4.0/lib/postgres-pr/message.rb:32:in `read'
from c:/ruby/lib/ruby/gems/1.8/gems/postgres-pr-0.4.0/lib/postgres-pr/connection.rb:30:in `initialize'
from c:/ruby/lib/ruby/gems/1.8/gems/postgres-pr-0.4.0/lib/postgres-pr/connection.rb:29:in `loop'
from c:/ruby/lib/ruby/gems/1.8/gems/postgres-pr-0.4.0/lib/postgres-pr/connection.rb:29:in `initialize'
from c:/ruby/lib/ruby/gems/1.8/gems/postgres-pr-0.4.0/lib/postgres-pr/postgres-compat.rb:23:in `new'
from c:/ruby/lib/ruby/gems/1.8/gems/postgres-pr-0.4.0/lib/postgres-pr/postgres-compat.rb:23:in `initialize'
from ./script/../config/../vendor/rails/activerecord/lib/active_record/connection_adapters/postgresql_adapter.rb:24:in `connect'
... 14 levels...
from ./script/../config/../vendor/rails/railties/lib/commands/runner.rb:47
from c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `gem_original_require'
from c:/ruby/lib/ruby/site_ruby/1.8/rubygems/custom_require.rb:27:in `require'
from script/runner:3
This seems to coincide with another problem I have, which is the PostgreSQL daemon looses contact with things like pgAdmin, so I don’t honestly think it’s Ruby-related, but instead something in my PostgreSQL configuration. I am running 8.1.4 on Windows XP SP2. I’m bonding to localhost to get connected.
Update: Looking through the PostgreSQL logs, I find this:
2006-10-24 23:33:10 LOG: could not receive data from client: An operation was attempted on something that is not a socket.
2006-10-24 23:33:10 LOG: incomplete startup packet
It seems to happen, whether I am bound to the loopback or another address.
No thoughts
If you’re like me—and I hope you are—you use PostgreSQL and believe in implementing multiple layers of data protection, where appropriate. For example, I do implement foreign keys and nullability in the database. To me this is just good behavior. One of the things I ran into with using the Rails migration capability was dealing with some of the more advanced capabilities of PostgreSQL. Often, I’d just roll the foreign keys and nullability stuff in the DDL and go from there. Unfortunately, as I’m building incrementally, I really don’t want everything at once. So, after reading a post by Robby, I decided to use his strategy and implement a schema version that adds constraints. This lets me decide when to bring them into the equation. Too early, and it’s hard to sketch out the application; too late, and data could be corrupted.
I also use another version to bring in the preloaded data in the database. Nifty. Now, if only I could use the schema creation facility that doesn’t require me using DDL, but I’m simply too dependent on certain fixtures in PostgreSQL that aren’t available yet.
No thoughts
Trying to run down some performance problems, where I’m driving the PostgreSQL client psql programatically, using a pipe and expect-style code, someone pointed out that maybe the readline functionality was tripping me up. Strange, that seems odd. But guess what, it shaves more than a 1/10th of a second off each iteration, and more importantly, it seems to degrade faster with readline than without. I’ve not got strong numbers yet, but interestingly it does.
To understand how different it is, look at this plot.
No thoughts
A while back, I had posted some benchmarking tests I was running on Linux. I had tried a bunch of different things, but after a bunch of research, I’ve found that mounting the ext3 filesystem with data=writeback, which makes journaling only for metadata, and then modifying the scheduling algorithm by altering the kernel boot parameters to elevator=deadline has substantially increased performance.
I don’t have actual numbers yet, but it is looking like it’s at least 2x the earlier performance, and might actually hold “flat” longer.
I was delusional when I wrote that. It started out a lot faster, but it plummeted off a cliff, going from nearly 5,000 rows/second down to under 400/second in not too long. Continual loading on this machine sends the load average over 9, and the system in pure iowait contention. WAL logs, I’m looking at you.
No thoughts
So, I’ve been working on trying to track down some performance issues with PostgreSQL under “non-normal” usage. Basically, I need to sustain 500-1000 rows/second being added to the database. Now, I’m not so stupid as to just write single INSERT statements in a transaction for that, and I originally started out using them batched into groups of 500, which worked OK. Then, I moved to using the COPY syntax in a subprocess. Works great, reliable, but it hits a wall, as you can see on the graph below (x = insert, y = time):
If you click on it and look at the larger graph, you’ll notice that I hit a wall at a certain point. The midpoint is where I switch to writing in a fresh table. As far as I can tell, this is due to index behavior (there’s 5 indexes, including the primary key), and perhaps running into too small a keyspace for this size of a data set.
More work ahead. Running the exact same benchmark with just the one index.
No thoughts
In PostgreSQL, I’m wondering about how partial indexes get integrated into the query optimizer. Let’s say I have a table, with 100M rows, and there are 99 indexes, which each cover 1M rows, but the last 1M rows haven’t had an index created yet. What happens? Can PostgreSQL even handle hundreds of indexes? I would think that it would complicate optimization of the query substantially.
Time to add that to the list of factors to look at. There’s an option which is to, in the lulls, rewrite the indexes, and “expand them” to potentially cover some amount of historical data.
Bah! Sometimes I wonder if RDBMS are even worth the effort?
No thoughts
So, I’ve been toying with storing huge amounts of data in PostgreSQL, mostly to avoid dealing with Oracle, and to find out if an open-source database can compete on the larger projects. By “huge amounts,” we’re talking hundreds of millions of rows, not just a few thousand.
The first problem I hit was INSERT performance, which is obvious once I thought about it. Since I’ve used SQL*Loader in Oracle a lot, I just switched over to using the COPY SQL syntax, which gave me anything from a 20-100x increase in performance. With that solved, I had to look at table organization and indexing.
I need to keep roughly 6 indexes on the table, one on a string, but the rest on integer fields. Unfortunately, I’ve found that performance of inserts seems to arithmetically degrade as you get above 1M rows. Before that it’s pretty flat, for all intents and purposes. My personal box drops from being able to do 5,000 rows/second to doing 500 rows/second, and so I’m investigating two options to deal with this, as it seems to be index-driven:
- Use partial indexing, based on some other factor, like time
- Use views to hide multiple tables
- Leverage inheritance in PostgreSQL
The first is more “normal” of a solution, and similar to how one might do data partitioning on Oracle, although Oracle would also partition the data. Since I’d like to partition the data, I’m looking at inheritance as a solution. This appeals to me more than doing views, since it doesn’t have the maintenance overhead of keeping a view up to date. The idea being that I have a “root” in my inheritance graph:
CREATE TABLE fooroot (
...
...
...);
That creates everything I need in all the partitioned tables. From there, I just do this:
CREATE TABLE fooinst1 () INHERITS (fooroot);
That means queries work right, etc., but I do have to make sure each time I create one of these partition tables, I have to rebuild the indexes and foreign keys. Fortunately, this table only has a few foreign keys, and everything else can be rebuilt on the fly in a templated manner.
The question then becomes, can I apply the use of tablespaces to solve the partitioning of data at that point? That would transform the DDL to:
CREATE TABLE fooinst1 () INHERITS (fooroot) TABLESPACE foo2;
I’ve got to test it out first, then build some tools to benchmark it, and make sure that I can build the tablespaces on the fly, and come up with a scheme for allocating them and determining when/where to break. The problem is that PostgreSQL, like Oracle, and any other MVCC, some simple queries with aggregates, like:
SELECT COUNT(*) FROM FOO;
which would normally give you a row count, can be very expensive operations, because they require a sequential scan due to the nature of multiple-versions being visible at the same time. That means either I need to trip off an internal counter, which isn’t so bad since I know who is doing the loading (me), or use a “range” setting, like time.
Either way, I’ll then need to look at the performance differences between the two approaches, especially on concurrent retrieval time.
1 thought
From their own documentation:
Error handling in InnoDB is not always the same as specified in the SQL standard. According to the standard, any error during an SQL statement should cause the rollback of that statement. InnoDB sometimes rolls back only part of the statement, or the whole transaction.
Um, no, last I read the spec, and certainly the way ACID-compliant databases are supposed to behave, is that if you do something wrong inside a transaction, the entire transaction is rolled back. When in interactive mode, there’s some shakiness about whether a syntax error should abort the transaction, but often you’re in “auto-commit” mode, which is treating each statement as its own transaction.
The idea that you might “roll back only part of the statement” is terrifying. I mean it’s wrong on a scale that shows a complete disregard for anything vaguely having to do with data integrity. This is a very “simple” concept, and while implementation can be complicated, it’s not an unknown. Not implementing transactional semantics like this correctly means you basically don’t have transactions.
You can call them transactions, but they’re not if they don’t behave according to ACID guidelines.
3 thoughts
So I’ve been working on a lot of things that are using PostgreSQL lately, and I just happen to have a table that now has about 2.4M rows in it, and I happened to type:
SELECT COUNT(columnid) FROM tablename;
Just to find out exactly how many rows were in it. It took forever, and an EXPLAIN on it, gives me this:
QUERY PLAN
--------------------------------------------------------------------------
Aggregate (cost=135570.35..135570.35 rows=1 width=8)
-> Seq Scan on tablename (cost=0.00..129531.68 rows=2415468 width=8)
(2 rows)
So, that made me start wondering why it was a sequential scan, rather than using the existing B-tree index that’s on the table, as columnid is the primary key on the table. Turns out that the index might have tuples in it that aren’t yet visible, largely because of the MVCC architecture of PostgreSQL, so it has to do a sequential scan of the table itself. If you run VACUUM ANALYZE on a regular basis, you can probably get the information out of the system tables.
By the way, Oracle, last I knew, used a similar sequential scan for this kind of information for somewhat similar reasons, as Oracle uses an MVCC architecture as well. More details can be found here.
No thoughts
