Hi, I'm new to postgres and I have the next question.
I have a php program that makes 100000 inserts in my database.
autoincrement numbers inserted into a table with 5 columns.
The script takes about 4 minutes from a webserver
Is it a normal time?
How could reduce this time by a bulce of inserts?
When I turn off fsync get much more performance, but it is not ideal in power failure
Hardware: 2 disks 1TB 7200 rpm with software raid 1 (gmirror raid)
8 Gb RAM
CPU Intel Quad Core 2.4 Ghz
OS: Freebsd 8.2
Postgres version: 9.0.4
My postgres config:
listen_addresses = '*'
wal_level = archive
fsync = on
archive_mode = on
archive_command = 'exit 0'
maintenance_work_mem = 480MB
checkpoint_completion_target = 0.5
effective_cache_size = 5632MB
work_mem = 40MB
wal_buffers = 16MB
checkpoint_segments = 30
shared_buffers = 1920MB
max_connections = 40
My execution time of my script:
[root@webserver ~]# time php script.php
real 4m54.846s
user 0m2.695s
sys 0m1.775s
My scipt:
<?php
pg_connect("host=host port=port dbname=db user=user password=pass") or die ("No me conecto...");
for ( $var = 1; $var <= 100000 ; $var++ )
{
$sql = "INSERT INTO server (aa, bb, cc, dd, ee) VALUES ('$var','$var','$var','$var','$var')";
pg_query($sql);
}
?>
my dd test is:
#time sh -c "dd if=/dev/zero of=/tmp/test count=500000 && fsync"
500000+0 records in
500000+0 records out
256000000 bytes transferred in 2.147917 secs (119185237 bytes/sec)
usage: fsync file ...
real 0m2.177s
user 0m0.188s
sys 0m0.876s
Thanks, any help will be well recived,