Sign in Join

Live chat for Free Pascal, Lazarus, Delphi and every Pascal programmer.

Mailing lists / lazarus / 2008-05

How big can DB transactions go?

5 messages · 3 people · –

Reply by email Subscribe to this list Your subscription

Read-only archive. Use j and k to move between messages.

Graeme Geldenhuys

Hi,

I'm working on a SQL RDBMS tool, testing Index performance. My overall goal is to insert 500,000 records in a master table and 5mil in a details table.

Using SqlDB, can I do this without transactions, as I don't care about data integrity. If I could, would that speed things up? Currently I'm working on a Firebird database, but once the tool is complete, I'll try other databases like MySQL, MS SQL Server etc...

At the moment I do a 'commit' every 100 master table records, which includes 10 times that number in details records. Could I go higher? Is there a optimal record count per transaction?

As a side note:
Due to initial Firebird databases being small 600K in size, the hard drive slows down the process a lot while the database grows. Inserting 20,000 master records increased the database size from 45Mb to 87Mb and that took 496s, which is damn slow I think. But then, my hard drive went crazy while inserting!

Can I pre-allocate a database size with Firebird? What about other database servers? I know with MS SQL Server you can.

Regards,
- Graeme -

_______________________________________________
fpGUI - a cross-platform Free Pascal GUI toolkit
http://opensoft.homeip.net/fpgui/

Reply by email

Michael Van Canneyt
to Graeme Geldenhuys
Graeme Geldenhuys wrote 14 lines
Hi, I'm working on a SQL RDBMS tool, testing Index performance. My overall goal is to insert 500,000 records in a master table and 5mil in a details table. Using SqlDB, can I do this without transactions, as I don't care about data integrity. If I could, would that speed things up? Currently I'm working on a Firebird database, but once the tool is complete, I'll try other databases like MySQL, MS SQL Server etc... At the moment I do a 'commit' every 100 master table records, which includes 10 times that number in details records. Could I go higher? Is there a optimal record count per transaction?

No.
I did a test once with 600.000 records in a single transaction. No problem.

Show quoted text 10 lines
As a side note: Due to initial Firebird databases being small 600K in size, the hard drive slows down the process a lot while the database grows. Inserting 20,000 master records increased the database size from 45Mb to 87Mb and that took 496s, which is damn slow I think. But then, my hard drive went crazy while inserting! Can I pre-allocate a database size with Firebird? What about other database servers? I know with MS SQL Server you can.

You cannot pre-allocate size. There should be no need for this.

Try to switch off 'forced writes' in your firebird config file, that should speed up things. And increase the number of in memory pages of your server. The default setting is ridiculously low.

Michael.

Reply by email

Graeme Geldenhuys
to Michael Van Canneyt
Michael Van Canneyt wrote
I did a test once with 600.000 records in a single transaction. No problem.

Thanks Michael, that's good to know.

Try to switch off 'forced writes' in your firebird config file, that should speed up things.

OK, I've done that and will try another huge insert to see how it performs.

And increase the number of in memory pages of your server. The default setting is ridiculously low.

I'm not familiar with tweaking Firebird, but hope to learn more about the internals and finer details of Firebird over the next few months. Could you be more specific as to what config setting you are referring to? There are a lot of settings in the firebird.conf file.

Yesterday I left the test program running which inserted the remainder of the records. 460k master records and 10 times that in details records. It took 285 minutes on my P4 2.40GHz with 1 Gig memory. :) I believe the allocating of disk space was the major cause for the slowness. I'm going to empty that database tables and hopefully the actual db file doesn't shrink - then run the insert again to see how long it takes without the need to allocate disk space.

hehe... This compares nothing against the 4-6Gig MS SQL Server databases we used in a UK job. Doing data conversion took 3.5 days non-stop on a quad processor with 8Gig memory and a mean RAID system.

Regards,
- Graeme -

_______________________________________________
fpGUI - a cross-platform Free Pascal GUI toolkit
http://opensoft.homeip.net/fpgui/

Reply by email

Michael Van Canneyt
to Graeme Geldenhuys
Graeme Geldenhuys wrote 21 lines
On 29/05/2008, Michael Van Canneyt wrote: > I did a test once with 600.000 records in a single transaction. > No problem. Thanks Michael, that's good to know. > > Try to switch off 'forced writes' in your firebird config file, that > should speed up things. OK, I've done that and will try another huge insert to see how it performs. > And increase the number of in memory pages of > your server. The default setting is ridiculously low. I'm not familiar with tweaking Firebird, but hope to learn more about the internals and finer details of Firebird over the next few months. Could you be more specific as to what config setting you are referring to? There are a lot of settings in the firebird.conf file.

DefaultDbCachePages = 2048

2048 pages is the default.
To calculate the amount of memory, multiply with the page size (8, 16 or 32 Kb)

I put this to 50000 or so, for large databases.

You may want to check
MaxUnflushedWrites
MaxUnflushedWriteTime
as well.

Show quoted text 7 lines
Yesterday I left the test program running which inserted the remainder of the records. 460k master records and 10 times that in details records. It took 285 minutes on my P4 2.40GHz with 1 Gig memory. :) I believe the allocating of disk space was the major cause for the slowness. I'm going to empty that database tables and hopefully the actual db file doesn't shrink - then run the insert again to see how long it takes without the need to allocate disk space.

Normally it does not shrink. Only a backup-restore cycle will do that.

Michael.

Reply by email

Damien Gerard
to Graeme Geldenhuys: Thanks Michael, that's good to know. OK, I've done that and will try another huge insert…

Le May 30, 2008 ? 9:37 AM, Graeme Geldenhuys a ?crit :

Show quoted text 5 lines
On 29/05/2008, Michael Van Canneyt wrote: > I did a test once with 600.000 records in a single transaction. > No problem. Thanks Michael, that's good to know.

Transactions should be stored in memory. So it depends of your memory capacity.

Damien Gerard "Intelligence is 10 million rules." -- Douglas Lenat

Reply by email