Re: Partitioning or Not Partitioning?

[Manuel Gentili]

> Is that 50 rows INSERTed per second across all tables? Or 50 per second per client?

I must insert 25 record per second per client, 50 rows had a maximum value in witch I consider some mesurament that are not very important (yesterday I tried to use a different table for every client, so every client have a identical table for data storage, it seems to found good)

> If you are pruning 'old' data, that is a good reason for using PARTITIONing. If the table(s) were PARTITION BY RANGE with each partition being one month, then DROP PARTITION would very efficiently do the pruning.

Yes, now I use this solution:
I need 2 kind of data:
1 - real time value, that I use to see in a display (for example 25 rows per second that correspond to 25 different value like temperature, voltage, ampere ecc..., so 1500 value per minute)
2 - historical value, that depend from the point 1. For example, I decide to take a data every 60 second, so I need to do a middle value for every kind of data, do I will insert 25 rows every 60 second that are the 25 middle value of the 25 different kind of data.
So I decided to use 2 table for every client: one for the point 1, that is like a RAM memory, in witch I insert data for 60 second and than I delete all data and restart for other 60 second and so on (so I have a small table with max 1500 rows)
and one with the historica data, that will storage 25 rows every minute, so I will have about 36000 recod for day. I hope that it'is clear for you.

> Once you have the summary data, do you need the "raw" data? That is, do you really need 3 million rows (500000+2500000)? Maybe you could store only one month's worth?

No, I need for example 25 rows per minute, I can talk with the compay to reduce to 25 rows per 2 or 3 minute if necessary, but the range is about 1-5 minutes

> Do you mean "average" (also called "mean")? Or do you mean "median"? Average is easily available in MySQL; median is best done by application code.

I mean AVERAGE

> Based on the numbers you gave, there is about 500GB of data to store on the disk. Does that agree with the numbers you have calculated?

I have not done the calculations, but I think that with this new post the calculation must be do again, and I am very happy to know the data quantity for one month or one year because I need to buy an hardware that it will able to do this

> If there are to be millions of rows in dati_rilevati, and if you need to fetch selected rows by the datetime field, note that it is missing any useful index for such.

Yes, I have two kind of information, like I explained before . One information in real time, that is produced by a select in the "real_time" table of every client, in witch I consider the last datetime, and one information witch is produced by some SELECT query that consider a single value (for example temperature) in a time period, for example between last month to today.

I hope that this information help you in your valutation, thank you for your help!