Обсуждение: Performance - fsync-off
Hi, I just got my Postgres-database up and running, putting in data using a Python-script, using the latest version of the PyGres-module by Darcy@druid.net. It works great! But insertion seems slow, even though I just got a few indexes. How can I turn off the fsync-variable? There must be a way to turn it off when starting the database, like postmaster -x where x is some command-line option or a setting in a config-file etc. I haven`t found anything in my search so far. What will this do in terms of performance ( 1-5 times ??? ) and is it considered safe? ( I`m running Linux ) How does PostgreSQL compare in terms of speed compared to other databases, commercial products included?? Thomas
Thomas Weholt wrote: > > Hi, > I just got my Postgres-database up and running, putting in data using a > Python-script, using the latest version of the PyGres-module by > Darcy@druid.net. It works great! Python is pretty. But it can suffer from poor scripting, from excessive overhead (quite common in OO). > But insertion seems slow, even though I > just got a few indexes. How slow is it? > How can I turn off the fsync-variable? There must be > a way to turn it off when starting the database, like postmaster -x where x > is some command-line option or a setting in a config-file etc. -F http://www.postgresql.org/mhonarc/pgsql-general/1999-11/msg00127.html > I haven`t > found anything in my search so far. What will this do in terms of > performance ( 1-5 times ??? ) See: http://www.postgresql.org/mhonarc/pgsql-general/1999-12/msg00476.html > and is it considered safe? ( I`m running Linux) as long as you don't crash or lose power. :-) > How does PostgreSQL compare in terms of speed compared to other databases, > commercial products included?? It's slower than MySQL on simple tables, but that's because you can do much more complex tables. > I need to insert about 2000-10000 records into a database. I`m using Python > to do this. Is there a way to use some form of bulk copy that will do this > faster than plain-old insert for each and every record? On an extremely fast system, with Fsync off, this should take less than 30 seconds. On slower systems, or with fsync on, your performance will degrade greatly. I've seen 468 machines running at 2 inserts a second... Aside from that backend speed, you might try converting the text data you have into SQL _first_, and then feeding that in as a file. If you are reading a line, parsing, inserting, reading the next line, parsing, inserting... well, you get the picture. That loop will cost you. -Ronabop -- --2D426F70|759328624|00101101010000100110111101110000 Brought to you from iBop the iMac, a MacOS, Win95, Win98, LinuxPPC machine, which is currently in LinuxPPC land. Your bopping may vary. Do not eat contents. Cape does not enable wearer to fly. Use only as directed.
* Thomas Weholt <Thomas@cintra.no> [000602 01:45] wrote: > Hi, > > I just got my Postgres-database up and running, putting in data using a > Python-script, using the latest version of the PyGres-module by > Darcy@druid.net. It works great! But insertion seems slow, even though I > just got a few indexes. How can I turn off the fsync-variable? There must be > a way to turn it off when starting the database, like postmaster -x where x > is some command-line option or a setting in a config-file etc. I haven`t > found anything in my search so far. What will this do in terms of > performance ( 1-5 times ??? ) and is it considered safe? ( I`m running Linux > ) Running fsync off is not very safe, the option to turn it off is one that is supplied to the 'postgres' process, see the postmaster manpage to discover how to pass options to postgres and see the postgres manpage for the option. > How does PostgreSQL compare in terms of speed compared to other databases, > commercial products included?? Pretty darn good. :) -- -Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org] "I have the heart of a child; I keep it in a jar on my desk."
Ron Chmara wrote: > On an extremely fast system, with Fsync off, this should take less than > 30 seconds. On slower systems, or with fsync on, your performance will > degrade greatly. I've seen 468 machines running at 2 inserts a second... One thing I've noticed with loading lots of data using INSERTs is that if you wrap the whole thing in a transaction, it goes heaps faster (presumably because it only does one fsync, when the transaction is complete). -- Martijn van Oosterhout <kleptog@cupid.suninternet.com> http://cupid.suninternet.com/~kleptog/