Your favourite database for db logging?

[vogler]

What would be your favourite database for history data logging in WinCC OA?

I wanna evaluate some databases:
http://www.rocworks.at/wordpress/?p=671
For example I can see that the access/response times are increasing (linearly?) with the amount of data on MS SQL Server... more than on the other databases.

And NoSQL databases:
http://www.rocworks.at/wordpress/?p=675

Four Ctrl-Scripts are querying data with dpGetPeriod every 10 seconds, random time frames (between 1 and 2 hours of data).

There are now about 160.000.000 values in each database (MSSQL,Oracle,Phoenix,InfluxDB). About 3.600.000 values are added every hour (1000v/s).

Any input and experiences would be great!

regards,
Andy

[tmalone]

In the US, Microsoft SQL Server is most popular in our market space.

Each of the top 5 competitors use MSSQL as standard database.

[fmulder]

In Holland the most favorite database seems to be Microsoft SQL Server.
Personally I've read some stories about MonetDB ( https://en.wikipedia.org/wiki/MonetDB ). To me it seems like a possible candidate for storing large(!) amounts of data.

I think that in recent years the call is to store more-and-more data ( Big data ? ). Maybe there are other data models that would better fit this need.

[vogler]

Thx. i have already a logger plugin for SQL-Server (currently just event logging and dpGetPeriod for reading).

MonetDB seems to be interesting - never read about it before.

For BigData i am testing
* OpenTSDB (based on Hadoop/HBase) - which is really fast ~ 62kHz -
* Phoenix (relational database layer on top of Haddop/HBase).
* Cassandra
* ElasticSearch

regards,
Andy

[Gertjan van Schijndel]

Perhaps you could also include Redis in your BigData test.

Have you created the same circumstances for all databases for this test? Or are you running an optimized RDB/Oracle solution with multiple tablespaces against plain databases without any optimization?

[vogler]

Redis seems to be a in memory data store, so I don't think it is affordable for history data...
No optimized Oracle DB, every DB was running on desktop/home Hardware. Virtualized. Two striped spinning disks...

[Gertjan van Schijndel]

I mentioned Redis, because it is one of the biggest NoSQL databases. But I am really looking forward to the results with the Cassandra database.

In my experience with MS SQL Server the performance degrades, because the indexes gets fragmented over time and needs to be rebuild to improve the performance again. Do the other databases execute these kind of maintenance jobs out of the box?

[fmulder]

Andy,

As you know, we are currently using Oracle in our project. This is the first time we do so and our experiences sof ar are quite positive. let me please mention the top 4 reasons why like the Oracle solution. Please take these into consideration when selecting a database:

1. The redu switching time. When you use the normal raima and value archives then starting your redu server can take quite a while. It can quite easily take half an hour. We did the following test : we turned of one server, we generated 800.000 alarms and then started the second server. The time to start the server was quite impressive. There was no difference between the normal start and this 'start with synchronisation' (because there is no sycnhronisation)
2. The performance in the clients. runRealSQLQuery allows us to retrieve data or alarms lightning fast
3. The UDAG's are perfect to add our additional tables

Good luck

Frenk

[thalter]

Frenk,
which Oracle Edition do you use in the project?

[fmulder]

Oracle 12