Re: Partitioning or Not Partitioning?

[Manuel Gentili]

> The main issue in a high INSERT rate is the amount of I/O that will be done

Ok, I think so too. So, if I understand, we can do this:

1 - decrease record size by removing drt_nomevariabile (varchar), and replacing drt_valore that now is a VARCHAR (note that the data contained in this filed is always a number WITHOUT COMMA) with an INT or BIGINT or also FLOAT (may be float not appropriate in this case?)
2 - decrease number of rows (from 25 to 1, with your solution of each data in the same row)
3 - minimize indexing work: using a single primary key and no other index, but we must talk about this in consideration to the SELECT that I will use. The only select in 'realtime' table is to select the last value to wiew this in the pc monitor with an AJAX function, that take the 25 values one time every second. The query SELECT in 'datirilevati' table may be various, but the most important will be by date, so the last month values that is one average value for every day, so I have for example 30 values of voltage, one for day.
4 - make 'realtime' table as a Memory table, so it can be processed in RAM.
5 - insert data in history table 'more frequently', to avoid data loss (I well understand?), so we can talk about the frequency and the history table weight in one year for example.

Doing a little calculation, I have 1 row of 250 bytes for 6000 clients for every second, so I have 1,5MB for second in RAM. If I use my actual policy, 60 second before average, insert in history and delete, I have a table of 90MB in RAM, is ok? so I think that I have not a RAM size problem (or I make an error?)...

So, I think that it's a good way to proceed. But now I'd like to know what do you think about my way to resolve the problem, because with these considerations I think that it's important to discuss about this. So, my answer is: OK, we can follow this way, but what do you think about the use of these 2 kind of table, realtime and history, also considering the RAM and the 'more frequent' average insert?

And also: if you were to do a system, that will have about 6000 client, and must show 25 kind of measure in real time, and have an history of about one measure each minute or each 30 second, with these kind of query, the project would be similar to mine or not? I ask you this because I need to proceed and I see that all my competitors use a stand-alone mini computer near each sensor, so they have a mini pc for each 25 measure, that do all the work for these measures. And than, when they want to do some select, query all the mini pc of the desired customer and than elaborate the values. But I think that it's more expensive for 6000 clients, so I thought at one single server that do all the work with an APACHE,PHP,MYSQL ambient, that help me to semplify the administration with a simple browser... it's correct for you?

Thank you so much!