|
<< Click to Display Table of Contents >> Navigation: Reference > Log settings > Database mode > Database Performance |
•Use SQLite (Default database) if you have no database knowledge. The insertion speed is good and the query speed is good enough after a warm up (may be very slow for the first query)
•Use SQLServer if you can get a license. The free edition SQL Express can also be used but the 10 GB limit can be problematic. SQL server will give you very good insertion speed and good query speed without the need of tweaking the settings.
•Use MySQL if you know how to tweak it. Depending on the settings and the version you use can get bad insertion speed and bad query speed. MariaDB (10.3.7) is recommended over MySQL (8.0.11) which seems to be slower (at least on Windows 10).
Benchmarks on Windows 10 1803 Core i3 3GHz 12 GB RAM SSD 250 GB
The application was configured to load 100000 rows from the database at max. The database contained 9 millions log rows. The pages view filter is the one from the predefined filters.
Database system |
Insertions (rows/s) |
Load (no filter) (rows/s) |
Load (page views filter) first time (rows/s) |
Load (page views filter) second time (rows/s) |
|---|---|---|---|---|
SQLite 3 (built in version) |
2500 |
25000 |
4500 |
18000 |
SQL Server 2016 |
3200 |
25000 |
13000 |
21000 |
MariaDB 10.3.7 |
1100 |
30000 |
24000 |
27000 |
MySQL 8.0.11 |
800 |
24000 |
1400 |
1400 |
If you want to do fast queries in a big database you need to use indexed fields. However not all fields can be indexed because it would use a lot of disk space and would slow down database insertions.
By default the application creates an index for the following fields:
•UrlPah
•EventTime, RowNumber
•Status
•Method
So for example it is much faster to do a filter on the Status field than on the RequestStatus or the EventType fields with similar result. So you need to keep this in mind when you choose the field you want to filter on.
If you specify a tracking field an index on the tracking field and the request number is also added. If you use the client IP address as tracking field the index would be on the ClientIP, RequestNumber fields.
So if you regularly filter on the client IP address it is recommended to use the ClientIP field as tracking field.
If needed you can add your own indexes with the configuration tools specific to the chosen database system. Just keep in mind that every time you clear the database you will need to create them again by yourself.