Dear experts, I am creating a (simple) model of the disc and I/O usage of my SQLite database so I can advise my customers on hardware requirements.
My database schema is very simple. It contains only one table with a single column with a primary key: CREATE TABLE T ( K varchar(22) PRIMARY KEY ); I run the database (3.7.7.1) in WAL mode, with checkpointing performed at 10 minute intervals. >From tests performed on an HP-UX Itanium server I have the following figures: Processing rate: 135,416 inserts/min (2,257 inserts/s) Total Inserts @ checkpoint (10 min): 1,354,167 After inserting 2,500,000 records, the file sizes (in bytes) on disc were as follows: Data file: 174,230,528 (174 MB) Log file: 2,050,719,096 (2 GB) These file sizes of the log and data files were obtained after the final checkpoint. So, from these figures I calculate the following: Total bytes per record in the data file = 174,230,528 / 2,500,000 = 70 bytes/record Total bytes per record in the log file = 2,050,719,096 / 1,354,167 = 1514 bytes/record In the case of the data file, since my record is only 22 bytes wide, am I correct in assuming that the extra 48 bytes are for the index? Now in terms of the I/O bandwidth, if an entry of approximately 1514 bytes is written to the log file for every record inserted, am I correct in assuming that if normal processing runs at N records/s, that the I/O (writing) generated is approximately 1514*N bytes/s? In the example above, that is approximately 3.26 MB/s. When it comes to checkpointing (every 10 minutes), I assume the log file is read once in its entirety, and the data file written once for every record inserted. In our system we have several of these SQLite-based processing units running in parallel together with a large number of other related applications. Disc I/O has proven to be a major limiting factor in system performance and the more accurately we can model the generated I/O, the better we can estimate the hardware requirements. Any feedback would be much appreciated. Best regards, Jaco _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
