Solved

Consolidating MySQL data into a new table or?

Posted on 2014-10-23
7
216 Views
Last Modified: 2014-11-18
In an application I have, we collect ping data from various networks to keep track of any problems.
We basically need an ongoing history of how circuits are doing.
The servers are sending ping results every 5 minutes and since there are many servers, that ping data is adding up and becomes somewhat useless after a while.

I currently have 73687 rows of data in my pings table and much of that data is now useless and that is the point of this question.

I order to keep an ongoing history which has some value, I am trying to figure out what I should be keeping and what I should be deleting. In terms of keeping, I think that I need to run a script which consolidates the data on a regular basis.

Here is an example of what the data looks like;

	id 	time 	client_id 	loss 	min 	max 	avg 	outage
	1 	2014-09-11 00:18:14 	9 	0 	25.081 	73.032 	37.190 	
	2 	2014-09-11 00:18:45 	41 	0 	24.113 	30.712 	26.753 	
	3 	2014-09-11 00:18:58 	162 	0 	21.935 	22.399 	22.112 	
	4 	2014-09-11 00:19:10 	157 	0 	99.395 	101.644 	100.854 	
	5 	2014-09-11 00:19:13 	8 	0 	21.843 	105.745 	45.488 	
	6 	2014-09-11 00:19:23 	9 	0 	24.918 	25.789 	25.234 	
	8 	2014-09-11 00:19:52 	41 	0 	21.632 	34.260 	26.638 	
	9 	2014-09-11 00:20:06 	162 	0 	22.106 	23.326 	22.835 	
	10 	2014-09-11 00:20:21 	8 	0 	23.290 	79.051 	38.768 	
	11 	2014-09-11 00:20:28 	157 	0 	99.585 	102.277 	100.737 	
	12 	2014-09-11 00:20:32 	9 	0 	24.993 	25.453 	25.193 	

Open in new window


The clientid simply means which server sent the data.

This is what the ongoing graph looks like;

2014-10-23-154825.png
The above graph is simply to show a range of time but beyond that, I would like to consolidate this somehow and in some meaningful way.
There is another section however for long term ongoing averages which shows min/max/avg and this is where I would like to display stats in some meaningful manner.

For example, when the user clicks on ping averages, I would like to show averages for the entire term, for the last year and the last month. This is where I would remove old data from the current table and create new data in a new table specifically for ongoing historical data.

This is where I seek help from anyone who has done this sort of thing before. I am not sure at all how I would consolidate this as useful data.
0
Comment
Question by:projects
  • 3
  • 3
7 Comments
 
LVL 7

Accepted Solution

by:
Stampel earned 250 total points
ID: 40401189
You could split / archive your data in archive_ping_table, to get a reasonnable active history in the main table and have always fast queries response on it.
Then you could have a stored procedure that would move the data from active table to archive table on a regular basis.
This way when you need full history you can query both tables using union in your query
0
 
LVL 25

Assisted Solution

by:Tomas Helgi Johannsson
Tomas Helgi Johannsson earned 250 total points
ID: 40402198
Hi!

Have you considered to partitioning the table(s).
That could help you in maintaining the data and roll out old data when no longer needed.
Partition pruning would then help in your queries shortening the execution time as the query only reads the partitions that meet it's predicates and not the whole table(s).

http://dev.mysql.com/doc/refman/5.5/en/partitioning.html

Regards,
     Tomas Helgi
0
 

Author Comment

by:projects
ID: 40402476
@Stampel;
Yes, one of the problems I am facing at the moment is server load. Ping data is being saved every 5 minutes. So if I show a default of 48hrs worth of pings for each graph/person, that ends up being quite the Mysql read.

It would be much more efficient to only keep what ever amount of data I need in the raw table at any given time. So if my default view is set to 48hrs, then only have 48hrs worth of ping data for any given clientid in the table, the rest removed/consolidated.

The stored procedure you mention, is this something the mysql server would do on it's own or via an externally run task such as a cron job?

@TomasHelgi;
Yes, the server does support this function so this could be used.

I basically just don't want to waste the data since we've collected it but it's worthless as raw data beyond the 'graph' for a quick view of a short range of time. After that, it would have more value if it could be consolidated, taking up way less rows as well.

The thing is, part of my question is how to deal with such data, in order to make it useful, historical. Solving the problem with this table will help me to find a solution for other tables I am having a similar issue with.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 7

Expert Comment

by:Stampel
ID: 40403181
Yes sure, that is exactly what i mean : keep only minimu data you need in the active table, and archive the rest that you would query only in rare case (when you need more history).

You can call a MySQL stored procedure from a MySQL trigger. But you can also launch a cron job to call the stored proc on a regular time basis.
0
 

Author Comment

by:projects
ID: 40403276
Is this something I can do on my own right now by following some simple steps or do I need a DBA?
I am often able to get things done with enough input.
0
 
LVL 7

Expert Comment

by:Stampel
ID: 40403872
Depending on your will, you probably can sort this alone by yourself.
I will do my best to help you.
First you need to create an archive table. For that i would use the following :
Create table ping_archive as (select * from ping);

Then try to create your first stored procedure to understand the basic things with this URL
http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx

Also have a look at triggers (you may use scheduled event trigger)
http://www.mysqltutorial.org/mysql-triggers.aspx
Let me know what you need help with
0
 

Author Comment

by:projects
ID: 40404939
I'll work on this tomorrow and post as I do.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Over the years I've spent many an hour playing on hardened, DMZ'd servers, with only a sub-set of the usual GNU toy's to keep me company; frequently I've needed to save and send log or data extracts from these server back to my PC, or to others, and…
How to remove superseded packages in windows w60 or w61 installation media (.wim) or online system to prevent unnecessary space. w60 means Windows Vista or Windows Server 2008. w61 means Windows 7 or Windows Server 2008 R2. There are various …
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question