CODEWITHSUNDEEP
phpmysql
Josh
Josh

Reputation: 690

performance concerns when storing log records in MySQL

I have setup a 15 min cron job to monitor 10+ websites' performance (all hosted on different servers). Every 15 mins I will check if the server is up/down, the response time in ms etc.

I would like to save these information into MySQL instead of a log.txt file, so that it can be easily retrieve, query and analysis. (ie. the server performance on x day or x month or server performance between x and y days)

here is my table's going to look like:

id website_ip recorded_timestamp response_time_in_ms website_status

If I insert a new entry for each website, every day I'll have 1440 records for each website (15 x 4 x 24), then for 10 websites it will be 14400 records every day!!

so, I'm thinking of creating only 1 entry each hour / website. In this way instead of creating 14400 records every day, I'll only have 24 x 10 = 240 records every day for 10 websites.

But still, it's not perfect, what if I want to know keep the records for the whole year? then I'll have 87600 records for 365 years for 10 websites.

is 87600 records alot? my biggest concern is the difference between the server local time and client local time. How to improve the design without screwing up the accuracy and timezone?

Upvotes: 0

Views: 44

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

This is a bit long or too short for a comment. The simple answer to your question is "no".

No, 87,600 records is not a lot of records. In fact, your full data with 5,256,000 records per year is not that much. It could be a lot of data if you had really wide records, but your record is at most a few tens of bytes. The resulting table would still have less than a gigabyte per year. Not very much at all, really.

Databases are designed to have big tables. You have the opportunity to do things to speed your queries on the log files. The most obvious is to create indexes on columns that would often be used for selection purposes. Another opportunity is to use partitioning to break up the storage of the tables into separate "files" (technically table spaces), so queries require less I/O.

You may want to periodically summarize more recent records and store the results in summary tables for your most common queries. However, that level of detail is beyond the scope of this answer.

Upvotes: 1

Related Questions