Slow insert speed in Postgresql memory tablespace

Posted by Prashant on Stack Overflow See other posts from Stack Overflow or by Prashant
Published on 2010-05-28T06:30:57Z Indexed on 2010/05/28 6:31 UTC
Read the original article Hit count: 399

Filed under:

postgresql

|

insertion

Hi,

I have a requirement where I need to store the records at rate of 10,000 records/sec into a database (with indexing on a few fields). Number of columns in one record is 25. I am doing a batch insert of 100,000 records in one transaction block. To improve the insertion rate, I changed the tablespace from disk to RAM.With that I am able to achieve only 5,000 inserts per second.

I have also done the following tuning in the postgres config:

Indexes : no
fsync : false
logging : disabled

Other information: - Tablespace : RAM - Number of columns in one row : 25 (mostly integers) - CPU : 4 core, 2.5 GHz - RAM : 48 GB

I am wondering why a single insert query is taking around 0.2 msec on average when database is not writing anything on disk (as I am using RAM based tablespace). Is there something I am doing wrong?

Help appreciated.

Prashant

© Stack Overflow or respective owner

Related posts about postgresql

Postgresql fails to start on Ubuntu 10.04.4 LTS

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
I installed postgresql 9.2 from add-apt-repository ppa:pitti/postgresql using apt-get install postgresql-9.2 At the end of the install and every time I try to launch postgresql by using the following command /etc/init.d/postgresql start or service postgresql start I get this error: Error:… >>> More
can't install psycopg2 in my env on mac os x lion

as seen on Server Fault - Search for 'Server Fault'
I tried install psycopg2 via pip in my virtual env, but got this error: ld: library not found for -lpq (full log here: http://pastebin.com/XdmGyJ4u ) I tried install postgres 9.1 from .dmg and via port, (gksks)iMac-Alexander:~ lorddaedra$ locate libpq /Developer/SDKs/MacOSX10.7.sdk/usr/include/libpq /Developer/SDKs/MacOSX10… >>> More
Postgresql has broken apt-get on Ubuntu

as seen on Super User - Search for 'Super User'
On ubuntu 12.04, whenever I try to install a package using apt-get I'm greeted by: The following packages have unmet dependencies: postgresql-9.1 : Depends: postgresql-client-9.1 but it is not going to be instal led E: Unmet dependencies. Try 'apt-get -f install' with no packages (or specify a so lution)… >>> More
Installing PostgreSQL on FreeBSD (with ports)

as seen on Server Fault - Search for 'Server Fault'
Hey everyone, I am trying to install (using ports) PostgreSQL on a virtual server, running FreeBSD. My one question is this: Which of the following should I install? postgresql-contrib postgresql-docs postgresql-jdbc postgresql-libpgeasy postgresql-libpq++ postgresql-libpqxx postgresql-odbc … >>> More
Strange permission errors in new PostgreSQL installation

as seen on Server Fault - Search for 'Server Fault'
A freshly installed PostgreSQL (with configuration overwritten) won't start: $ sudo service postgresql start * Starting PostgreSQL 9.1 database server * Error: could not read /etc/postgresql/9.1/main/postgresql.conf: Permission denied Looks like it should be able to read it though: $ ls -l postgresql… >>> More

Related posts about insertion

MySQL Unique hash insertion

as seen on Stack Overflow - Search for 'Stack Overflow'
So, imagine a mysql table with a few simple columns, an auto increment, and a hash (varchar, UNIQUE). Is it possible to give mysql a query that will add a column, and generate a unique hash without multiple queries? Currently, the only way I can think of to achieve this is with a while, which I… >>> More
SSIS flat file insertion failure to rollback

as seen on Stack Overflow - Search for 'Stack Overflow'
I have simple SSIS package which reads data from flat file and insert into SQL database. The file has 90K rows and sometimes because of bad data package fails but it insert the partial records before it fails. What I need is if insertion fails at any time between, no records should be inserted into… >>> More
MySQL InnoDB insertion is very slow

as seen on Stack Overflow - Search for 'Stack Overflow'
We use MySQL server 5.1.43 64-bit edition. InnoDB is used as engine. We have a sql script which we execute every time we build the application. On ubuntu machine with MySQL server and InnoDB engine it takes about 55 seconds to complete the execution. If I run the same script on OSX, it takes close… >>> More
Form loop db insertion + javascript altering

as seen on Stack Overflow - Search for 'Stack Overflow'
I basically need to check if there is an easier way to do this before I rewrite all the code. I have a fairly large form that I have each input named with []'s on the end so I can loop through via php for easy insertion. <input type="hidden" name="currentdate[]" value="<?php echo… >>> More
overloaded stream insertion operator with a vector

as seen on Stack Overflow - Search for 'Stack Overflow'
hi, i'm trying to write an overloaded stream insertion operator for a class who's only member is a vector. i dont really know what i'm doing. (lets make that clear) it's a vector of "Points" which is a struct containing two doubles. i figure what i want is to insert user input (a bunch of doubles)… >>> More