1

Topic: Data migration from the table in the table with mathematics

All good afternoon
There was such problem, is implemented now on PHP, but very much it would be desirable to transfer this process on MySQL
Essence
There are two tables
The first cpu_live
The second cpu
To the table cpu_live with periodicity in a second it is written the order of 100-200 records of which we on a site build the live schedule
Table fields:
id, machine_id, cpu, cpu1, cpu2, cpu3, cpu4, cpu5, cpu6, cpu7, cpu8, cpu9, cpu10, cpu11, cpu12, cpu13, cpu14, cpu15, cpu16, data_add
In the second table all is identical
It would be desirable to receive the following
To select it is admissible 10 oldest records on machine_id, to receive mean values of all columns and to save received by single line in cpu.
After that to delete these 10 oldest records
Then once again the same and so until while in cpu_live there is no data in the last 30 minutes
Like all is simple, but if is fair already all head broke smile
Just in case I will explain a problem entirely somebody can prompts more refined decision
Indications of processors are written to the table with periodicity in a second, I will repeat from them the live schedule for last 20-10 is under construction minutes.
By itself the schedule is drawn by request in
Faced that that when we try to make sampling in an hour, we have certain complexities as schedule detailing turns out to within a second, all right, hour still tolerantly, and here indications for a day set the server thinking...
While I see only two decisions or to do sampling with decimation, but in 30 minutes after arrival of the data to us them to store in this type there is already no sense or where that to store the data already in the thinned out type, and it actually a subject)
In advance thanks

2

Re: Data migration from the table in the table with mathematics

Commonplace - calculation of the integrated statistics and cleaning detailed.
I would suggest to lay calculation on  (CREATE EVENT) - averaging jobs, say, minute, ten-minute, hour, daily is required some... And cleaning out-of-date given to replace on  on date-time, with preliminary preparation of partitions for the future periods and switch-off of the old partitions which have lost an urgency which can both be deleted and to archive.
Still I would suggest to think strongly, whether it is necessary here strictly "10 old". If, say, in the table falls on one record from the machine in a second - whether to average for 10 seconds, for example is easier? At first, it is not necessary to digitize pieces which it is explicit in the table is not present, secondly, to build schedules when all argument values are aligned easier.

3

Re: Data migration from the table in the table with mathematics

Akina;
I will specify, 10 oldest indications from the machine.
From each machine of times in a second indications from processors and as you wrote above to us fall this data after 30 minutes is not necessary, it is enough to store the average data for 10 seconds.
Thus the schedule will be drawn much faster and more conveniently.
Yes, probably it helps, now I will try to make

4

Re: Data migration from the table in the table with mathematics

Gexan wrote:

10 oldest indications from the machine.

Gexan wrote:

it is enough to store the average data for 10 seconds.

You are defined. These are different things. Not the fact that in the table 10 points for 10 seconds - and well lay down all as one is passed...