Thread: Heavy contgnous load
Dear Anybody! I use pgr to store records. But the characterisitc of the record traffic are special. For example 50 of them arrived in one sec contignously trough weeks and aligned interally trough tables. To absorb this traffic I put the pgr database to ramdisk (fast as possible). But after more day work the pgr slowing down. What is important think for this task I do not need any tranasction. So the COMMIT and ROLLBACK feature is useless. The question is how I minimize the rollback activity to free resoureces? Thanks for any idea. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Heavy-contgnous-load-tp4913425p4913425.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
kzsolt <kzsoltkzsolt@freemail.hu> wrote: > I use pgr to store records. But the characterisitc of the record > traffic are special. For example 50 of them arrived in one sec > contignously trough weeks and aligned interally trough tables. > To absorb this traffic I put the pgr database to ramdisk (fast as > possible). Could you just stream it to disk files on the ramdisk and COPY it in to PostgreSQL in batches? > But after more day work the pgr slowing down. We'd need a lot more information to guess why. > What is important think for this task I do not need any > tranasction. So the COMMIT and ROLLBACK feature is useless. Batching multiple inserts into a single transaction can *speed* data loads. > The question is how I minimize the rollback activity to free > resoureces? Rollback activity? What rollback activity? When you're doing what? What is the exact message? -Kevin
On 10/18/2011 08:09 PM, kzsolt wrote: > What is important think for this task I do not need any tranasction. So the > COMMIT and ROLLBACK feature is useless. > The question is how I minimize the rollback activity to free resoureces? Actually, you do need transactions, because they're what prevents your database from being corrupted or left in a half-updated state if/when the database server loses power, crashes, etc. Presumably when you say "rollback activity" you mean the overheads involved in supporting transactional, atomic updates? If so, there isn't much you can do in an INSERT-only database except try to have as few indexes as possible and do your inserts inside transactions in batches, rather than one-by-one as individual statements. Consider logging to a flat file to accumulate data, then COPYing data in batches into PostgreSQL. An alternative would be to write your data to an unlogged table (PostgreSQL 9.1+ only) then `INSERT INTO ... SELECT ...' it into the main table(s) in batches. Unlogged tables avoid most of the overheads of the write-ahead log crash safety, but they do that by NOT BEING CRASH SAFE. If your server, or the PostgreSQL process, crashes then unlogged tables will be ERASED. If you can afford to lose a little data in this case, you can use unlogged tables as a staging area. -- Craig Ringer
"try to have as few indexes as possible and do your inserts inside transactions in batches, rather than one-by-one as individual statements. " That is the main problem. I use now few index as possible. Unfortunately the one-by-one INSERT is nature of or system. To join (batch) inserts is require spacial cache with inactivity timeout. But this timout are make more trouble for our application. The flat file has same problem. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Heavy-contgnous-load-tp4913425p4919006.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
Kevin Grittner wrote: > Rollback activity? What rollback activity? When you're doing what? > What is the exact message? I mean here some kind of option to save reources. For example mysql has table (storage) type where no transaction support (rollback) in. This make the all server faster and use less resources. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Heavy-contgnous-load-tp4913425p4919050.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
On 10/20/2011 02:55 AM, kzsolt wrote: > "try to have as few indexes as possible and do your inserts inside > transactions in batches, rather than one-by-one as individual statements. " > That is the main problem. I use now few index as possible. Unfortunately the > one-by-one INSERT is nature of or system. To join (batch) inserts is require > spacial cache with inactivity timeout. But this timout are make more trouble > for our application. The flat file has same problem. Well, then you'll have to use an unlogged table (9.1 or newer only) to insert into, then periodically copy rows from the unlogged table into the main table using something like PgAgent to schedule the copy. An unlogged table is a tiny bit more like MySQL's MyISAM tables in that it doesn't have any crash recovery features. It still supports transactions, of course, and you won't find any way to remove transaction support in PostgreSQL. One of the reasons MySQL has historically had so many bizarre behaviours, like (by default) writing invalid data as NULL, inserting zeroes for invalid dates, etc is because MyISAM can't roll back transactions when it discovers a problem partway through, so it has to finish the job badly rather than error out and leave the job half-completed. If you really need absolutely maximum insert performance, you should just use a flat file or a different database system. Relational databases like PostgreSQL are designed for reliable concurrency, crash safety, fast querying, and data integrity, and they provide those at the cost of slower data insertion among other things. -- Craig Ringer
On Tue, Oct 18, 2011 at 5:09 AM, kzsolt <kzsoltkzsolt@freemail.hu> wrote: > Dear Anybody! > > I use pgr to store records. But the characterisitc of the record traffic are > special. For example 50 of them arrived in one sec contignously trough weeks > and aligned interally trough tables. What happens if the database has a hiccup and can't accept records for a few seconds or minutes? Do the processes that insert the records just buffer them up, or drop them, or crash? > To absorb this traffic I put the pgr database to ramdisk (fast as possible). > But after more day work the pgr slowing down. > What is important think for this task I do not need any tranasction. So the > COMMIT and ROLLBACK feature is useless. > The question is how I minimize the rollback activity to free resoureces? If you really don't need transactions, then you are incurring an awful lot of overhead by using a transactional database. In any case, this seem like a case for synchronous_commit=off. If the database crashes, you might be missing a few seconds of recent transaction when it comes back up. But, if the records are still being generated while the database is down, you are losing those ones anyway, so losing a few more retroactively may not be a big deal. Cheers, Jeff
So guys, lot of thank you for all of the explanation and ideas! Jeff Janes wrote: > What happens if the database has a hiccup and can't accept records for > a few seconds or minutes? Craig Ringer wrote: > If you really need absolutely maximum insert performance, you should > just use a flat file or a different database system. This need some explanation: Just for easy explanation our system constructed by Pmodules called PMs. The transport between PMs is a special reliable protocol with elastic high capacity buffers. This absorb the peaks of asynchrnous event storm. The related (small) part of our system called A_PM. This A_PM accept asynchrnous event from many (can be more dozen) other PMs, format it and store onto record of SQL table. After the record inserted all must be open for complex querys requested by 3 or more PM. Othersides we need to provide common public access to this records (and to many other functions). This is why we use SQL database server for. But the requirement is the user can be select freely the vendor of database server from four database server set (one of is PGR). To implement this we have twin interface. The synchronous_commit=off and unlogged table are good idea. I try it. The crash make mouch more trouble for our system than trouble generated by loss of 200-300 record... -- View this message in context: http://postgresql.1045698.n5.nabble.com/Heavy-contgnous-load-tp4913425p4922748.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
Looks like I found more magic. My table is: each record near 1kbyte, contain dozen col some text some numeric, some of the numeric columns are indexed. The database located at ramdisk (tmpfs) ((I hope)). The table is contignously filled with rows. If the table has less than 4Mrec then looks like everythink is fine. But near at 6Mrec the CPU load is go to very high and even the COUNT(*) need 8sec executing time (1sec/Mrec). The insert is slowing down too. But more stange if I try to search a record by indexed col then the server bring up it very quick! My server config is: / max_connections = 24 shared_buffers = 256MB log_destination = 'stderr' # Valid values are combinations of logging_collector = true silent_mode = on # Run server silently. log_line_prefix = '%t %d %u ' datestyle = 'iso, ymd' lc_messages = 'hu_HU' # locale for system error message lc_monetary = 'hu_HU' # locale for monetary formatting lc_numeric = 'hu_HU' # locale for number formatting lc_time = 'hu_HU' # locale for time formatting default_text_search_config = 'pg_catalog.hungarian' port = 9033 unix_socket_directory = standard disk log_directory = standard disk log_filename = 'sqld.log' effective_cache_size = 8MB checkpoint_segments = 16 synchronous_commit = off / Any idea how it possible to increase the performance? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Heavy-contgnous-load-tp4913425p4965371.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
I try to found out more information. Looks like the COUNT(*) is not the strongest part of pgr therfore I do a worakround. After this I have the folwing result: Below the 1Mrec the row insert time is ~23msec. Above the 7Mrec the insert time is ~180msec. I belive I use the fastest index type (default). So any idea to make postgres faster at higher number of records? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Heavy-contgnous-load-tp4913425p4978893.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
Hello 2011/11/9 kzsolt <kzsoltkzsolt@freemail.hu>: > I try to found out more information. Looks like the COUNT(*) is not the > strongest part of pgr therfore I do a worakround. After this I have the > folwing result: > > Below the 1Mrec the row insert time is ~23msec. Above the 7Mrec the insert > time is ~180msec. > * use a copy statement * use a explicit transaction * if you can disable triggers (and RI) * if you cannot and use a RI, unsures a indexes on PK and FK Regards Pavel Stehule > I belive I use the fastest index type (default). > > So any idea to make postgres faster at higher number of records? > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/Heavy-contgnous-load-tp4913425p4978893.html > Sent from the PostgreSQL - performance mailing list archive at Nabble.com. > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance >