Real-time Environmental Monitoring

This topic has 3 replies, 2 voices, and was last updated 9 years, 8 months ago by uRADMonitor.

Viewing 4 posts - 1 through 4 (of 4 total)

Author

Posts
February 8, 2015 at 8:09 pm #1304
vinz
Member
Hello,

while I did some tests on the dev-database, I saw SELECTing information for a specific device is very slow:
```
SELECT COUNT(idx) FROM uradmonitor WHERE db_devid="11000035";
7.54 sec
SELECT * FROM uradmonitor WHERE db_devid="11000035" ORDER BY db_servertime DESC LIMIT 1;
8.88 sec
```
No speedup if you repeat this requests several times.

So I inserted a new Index for field ‘db_devid’.
```
CREATE INDEX idx_db_devid ON uradmonitor (db_devid);
Query OK, 15082335 rows affected (3 min 45.82 sec)
```
so the same queries are faster several times:
```
SELECT COUNT(idx) FROM uradmonitor WHERE db_devid="11000035";
0.78 sec
SELECT * FROM uradmonitor WHERE db_devid="11000035" ORDER BY db_servertime DESC LIMIT 1;
1.59 sec
```
Be aware, at the moment I don’t know if the db is write-protected during the initial creation of the index (we have to clear that). Maybe we could miss new entries …

Indices will reduce the INSERT-performance of this table, so I watched the cpu-load before and after the creation of the index. I could not see any difference. Maybe someone knows a better way to record this.

Vinz
February 9, 2015 at 12:19 am #1306

uRADMonitor
Keymaster

Thanks Vinz, I’ll add it to the prod server too since select by id is often used.

Would be very helpful if Ally or the other guys with expertise in DBs would step in for some more hints/comments.

February 9, 2015 at 8:38 pm #1315

vinz
Member

Improve of this new index is significant!
It should also improve your creation of the current charts.

But don’t use too much indices, cause they will lower your insert-perfomance.
… but at the moment (you have a maximum of 160 inserts per minute) this will not have influence. 🙂

February 11, 2015 at 10:13 am #1328
uRADMonitor
Keymaster
```
 Your SQL query has been executed successfully (Query took 196.2649 sec)
CREATE INDEX idx_db_devid ON uradmonitor(
db_devid
)
```
I was just creating 7 charts in parallel for some experiment with various tubes. It was hard for the server, but after adding this additional index all 7 were generated instantly!

Speed improvement: amazing!

Thanks
Author

Posts

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic.

db-performance