Johnny got his gun
02-20-2005, 13:41
I've been investigating the speed of mysql vs sqlite. It sure takes careful benchmarking to get proper results. Here's what I have so far:
Starting benchmark using "mysql", database "bench" at "127.0.0.1".
Iterations: Insert = 50000, Select = 100, Replace = 100000
Many connections: no (using transactions)
Created table.
Inserted data. start = 1108923983, end = 1108923993, difference = 10 seconds
Selected data. start = 1108923993, end = 1108924000, difference = 7 seconds
Replaced data. start = 1108924000, end = 1108924022, difference = 22 seconds
Benchmark done! Total time: 39 seconds
Performed 4 connections.
Starting benchmark using "sqlite", database "bench" at "local disk file ;-)".
Iterations: Insert = 50000, Select = 100, Replace = 100000
Many connections: no (using transactions)
Created table.
Inserted data. start = 1108923939, end = 1108923944, difference = 5 seconds
Selected data. start = 1108923944, end = 1108923950, difference = 6 seconds
Replaced data. start = 1108923950, end = 1108923960, difference = 10 seconds
Benchmark done! Total time: 21 seconds
Performed 4 connections.
The benchmark does the following:
1. Drops previous table in case it already exists and recreates the table.
2. Inserts X amount of records (50000 above). Logs time taken.
3. Selects (and retrieves using dbi_field on all columns) all of the inserted records X times (100). Logs time taken.
4. Replace all records plus an additional X records with new values (50000 + 50000 = 100000). Logs time taken.
5. Logs overall time taken.
At first I got some horrible results for sqlite when inserting/replacing data. This was due to the fact that I didn't do proper transactions which really is a must if you're doing many inserts/updates/replaces. Inserting 100 records into an sqlite database took 11-12 seconds (!!!). Mysql only takes 0 seconds (yes not even a second). With transactions on, sqlite is down to 0 seconds as well for 100 records. And as you can see when trying with more records, it's even faster, at least on my system.
These numbers should probably be taken with a grain of salt because in the end there are a lot of other factors affecting the outcome speed. If the mysql database is on another computer on another network, it will of course take longer time. But sqlite doesn't even have that feature, so what do you do. :-)
Also you have to be very delicate about how you query the database. Not using transactions really made sqlite stink insert-wise. Maybe it's a good practice to always use transactions when inserting data, to be honest this was nothing I knew much about until I researched the horrible insert times of sqlite.
Don't think anyone's interested, but if you are I'm uploading the benchmarking plugin. It should work for the other dbi modules as well, provided that the SQL syntax is right.
Making SQL code that will work well for both mysql and sqlite seems a little tricky. I wonder how Mysql can get that low insert times without using transactions when sqlite obviously can't.
Starting benchmark using "mysql", database "bench" at "127.0.0.1".
Iterations: Insert = 50000, Select = 100, Replace = 100000
Many connections: no (using transactions)
Created table.
Inserted data. start = 1108923983, end = 1108923993, difference = 10 seconds
Selected data. start = 1108923993, end = 1108924000, difference = 7 seconds
Replaced data. start = 1108924000, end = 1108924022, difference = 22 seconds
Benchmark done! Total time: 39 seconds
Performed 4 connections.
Starting benchmark using "sqlite", database "bench" at "local disk file ;-)".
Iterations: Insert = 50000, Select = 100, Replace = 100000
Many connections: no (using transactions)
Created table.
Inserted data. start = 1108923939, end = 1108923944, difference = 5 seconds
Selected data. start = 1108923944, end = 1108923950, difference = 6 seconds
Replaced data. start = 1108923950, end = 1108923960, difference = 10 seconds
Benchmark done! Total time: 21 seconds
Performed 4 connections.
The benchmark does the following:
1. Drops previous table in case it already exists and recreates the table.
2. Inserts X amount of records (50000 above). Logs time taken.
3. Selects (and retrieves using dbi_field on all columns) all of the inserted records X times (100). Logs time taken.
4. Replace all records plus an additional X records with new values (50000 + 50000 = 100000). Logs time taken.
5. Logs overall time taken.
At first I got some horrible results for sqlite when inserting/replacing data. This was due to the fact that I didn't do proper transactions which really is a must if you're doing many inserts/updates/replaces. Inserting 100 records into an sqlite database took 11-12 seconds (!!!). Mysql only takes 0 seconds (yes not even a second). With transactions on, sqlite is down to 0 seconds as well for 100 records. And as you can see when trying with more records, it's even faster, at least on my system.
These numbers should probably be taken with a grain of salt because in the end there are a lot of other factors affecting the outcome speed. If the mysql database is on another computer on another network, it will of course take longer time. But sqlite doesn't even have that feature, so what do you do. :-)
Also you have to be very delicate about how you query the database. Not using transactions really made sqlite stink insert-wise. Maybe it's a good practice to always use transactions when inserting data, to be honest this was nothing I knew much about until I researched the horrible insert times of sqlite.
Don't think anyone's interested, but if you are I'm uploading the benchmarking plugin. It should work for the other dbi modules as well, provided that the SQL syntax is right.
Making SQL code that will work well for both mysql and sqlite seems a little tricky. I wonder how Mysql can get that low insert times without using transactions when sqlite obviously can't.