1

As part of a hobby-project I have a number of servers on my network that measure various parameters (both system statistics and domotica) and store the results (mostly as 5-minute averages) in various tables in a MySQL database running on one of the servers.

Using bash each server periodically queries the MySQL server to get a subset of the stored data in a local file. Then, I use python and gnuplot to turn the raw data in some nice trend graphs.

I currently make hourly, daily and weekly graphs. I already have a lot of data and I would like to make a couple of yearly graphs, however, I'm seeing that queries for a week's worth of data already take some time (query + network + local diskwrites) so I'm hesitant to proceed.

I was wondering if you guys have experience with this type of problem and know of / have experience with standard solutions that I have not found.

Are there magic queries, compression techniques or local storage solutions that I might want to investigate? The goal being to reduce the network load and reduce the MySQL server workload by preventing it to have to server the same data over and over again.

Mausy5043
  • 93
  • 8

1 Answers1

1

A few considerations:

Ensure your tables have indexes which serve the queries well. The best designs have an index on any column which appears in a where clause.

You can also have multiple columns in an index. See composite indexes.

If your accessing the same data multiple times you can look into caching, with something like memcached.

If you regularly summarise the data and access that same summarised data regularly you could put stored procedures in your database to update a summary table every time you enter new data into your fact table.

By no means the best possible answer but definitely the first things you should consider on a growing dataset.

I'll add some referencing links later but I'm on mobile right now.

Adam Copley
  • 304
  • 1
  • 3
  • 12
  • On the first point you make: I had no indexes defined. So I added (just) one. The query time dropped from seconds to milliseconds! AMAZING. Querying 400 days worth of temperatures used to take almost a minute. Now: 2s! – Mausy5043 Sep 14 '16 at 15:54
  • memcached sounds interesting. I' put that on my to-check list. – Mausy5043 Sep 14 '16 at 15:56