Toaster future release planning
In order to select a data store technology, we investigated several possible backends for storing data generated by a build. These are the results of the investigation documenting the final choice.
Criteria and investigation technique
We selected the following criteria, in descending importance order, as we target optimization in normal data usage. The pattern we expect is to insert for a run a set of records in the range on 10.000 entries, and then repeatedly read them in order to compute statistics and display information to users.
The criteria selected was:
- Time to read records (seconds/1000 records)
- Time to write records (seconds/1000 records)
- Space on disk (Mb/1000 records)
- Ease to install / deploy (subjective, 5 stars best, 1 star worst)
The investigation was done by generating around 8000 records from actual builds and then replicating these records to a high number in order to do simulate a high load on the data store.
Timings were performed on the client-side access of the data store, using timestamps to mark start and end of a batch run for read/writes. Disk space measurement was taken manually at the end of data generation.
The technologies measured were:
Results from investigation
1. Time to write (1000 records) - time is measured in seconds. Ran 3 times.
- MySQL: 35.4514319897 / 30.2873010635 / 30.2174918652
- CouchDB: 83.7538969517 / 83.9673860073 / 86.0426850319
- SQLite: 194.561517954 / 176.219516039 / 172.443398952
2. Time to read (1000 records) - time is in seconds Ran 3 times => Command SELECT * FROM Events LIMIT 1000
- MySQL: 0.00492382049561 / 0.0043830871582 / 0.00462698936462
- SQLite: 0.000476837158203 / 0.000610828399658 / 0.000468015670776
3. Space on Disk:
- MySQL: 40.6 MB (3044814 records)
- CouchDB: 2.3 GB (13737 documents)
- SQLlite: 2,2 MB (24772 records)
4. Ease to install /deploy
- MySQL: 5 stars
- CouchDB: 3 stars
- SQLite: 4 stars
sqlite was very slow on inserting new data, inserting 3 mil records would have taken too long, so a smaller ammount of data was chosen (~ 25k). CouchDB is also slow on inserting data, and occupies a large space. It shows that it has a 2.3 GB size for only 13k of records, so this option was dropped from the list.
Analyzing the results obtained, it seems that sqlite is faster at reading than MySQL and this is very good for WebHOB, as reads are done more often than writes.
We're going to use MySQL for storing data in development and go for sqlite in Production. If tests show that this is ineffective, we will make the necessary changes to achive the best results.