Reference > Log settings > Database mode > Performance

Database Performance

<< Click to Display Table of Contents >>

Navigation:  Reference > Log settings > Database mode >

Database Performance

Comparison between different database systems

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

 

The importance of indexes

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.