Re: Partitioning or Not Partitioning?
> 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!
Subject
Written By
Posted
November 05, 2014 12:11PM
November 05, 2014 04:53PM
November 06, 2014 02:57AM
November 06, 2014 07:16PM
November 07, 2014 02:07AM
November 08, 2014 05:10PM
November 12, 2014 05:33PM
November 17, 2014 11:32AM
November 19, 2014 04:42PM
November 20, 2014 04:53AM
November 21, 2014 08:26PM
November 24, 2014 07:30AM
November 24, 2014 07:21PM
Re: Partitioning or Not Partitioning?
November 25, 2014 04:35AM
November 25, 2014 11:45AM
November 25, 2014 11:55AM
November 29, 2014 03:42PM
November 24, 2014 05:14PM