Re: Partitioning or Not Partitioning?
Posted by:
Rick James
Date: November 25, 2014 11:45AM
> INT or BIGINT or also FLOAT (may be float not appropriate in this case?)
INT and FLOAT are 4 bytes; BIGINT is 8 bytes.
FLOAT holds 6-7 'significant' digits, and is happy to contain integers. 6-7 digits is probably more precision than you have for anything you are measuring.
I think DECIMAL(4,2) is 2 bytes and has range of -99.99 to 99.99. TINYINT UNSIGNED is 1 byte with range of 0 to 255. Etc.
> The only select in 'realtime' table is to select the last value to wiew this in the pc monitor
* How often is an AJAX request coming?
* Do you need only one row per client? Only 1 second's worth, not 60?
So, saving up 60 seconds may (or may not) need to be handled by something other than `realtime`.
> a table of 90MB in RAM
That should be no problem. You will need to SET max_heap_table_size just before doing CREATE TABLE. (And I cannot predict whether 90M is correct; various things could be 'off': 250 bytes/row, overhead in MEMORY tables, etc. Try a smaller value; take note of when the INSERT fails; extrapolate; add a fudge factor.
Please experiment with a full 6000 clients and inserting 1 row of 25 items per second for a full 60 seconds. Plus the desired SELECTs. This is pushing the limits, and may need further tweaking.
> one measure each minute or each 30 second
I am less concerned about `history`, even if you keep 2 averages per minute.
> so I thought at one single server that do all the work ... it's correct for you?
It sounds feasible, but tight. How about a compromise... Your "product" is a server that will handle, say, 2000 clients. Then sell 3 of them to the customer that has 6000 clients. This way, you can easily scale beyond "6000", simply by selling more machines.
Another item in the 2000/machine -- The customer could have a single Apache talking to all 3 machines. That way, it could consolidate all 6000 'things' into a single web page. Yes, more code; but, again, more scalable.
With a smaller limit, disk, ram, and cores could be smaller, hence a little cheaper.
Back to table design...
There are 3 uses of the values...
* AJAX to show 'latest' _one_ second;
* Save up 30 or 60 seconds for averaging;
* 'History' for lots of SELECTs.
What if there were 3 tables:
* `Latest` -- 6000 rows with 25 settings each; UPDATE to replace the row.
* `Staging` -- 6000*60 (or *30) rows. When time, read entire table, then TRUNCATE it. (TRUNCATE is faster than DELETE.)
* `History` -- as already discussed.
Staging --> History: INSERT INTO History SELECT ...AVG... FROM Staging GROUP BY client;
Each time a reading comes in, it is UPDATEd in Latest and INSERTed into Staging. Another program deals with INSERTing into History and TRUNCATEing Staging.
I am concerned about `realtime` and 6000 because there is another thread where they found that they could not INSERT into a MEMORY table faster than 7000 rows/sec. 6000 is "too close for comfort". And neither of you have taken into consideration the need to read from the table. Each INSERT takes out a _table_ lock. (InnoDB would do _row_ locking, but would hit the disk too hard.)
Dropping to a max of 2000 (or whatever the right number is) would solve these concerns.
Multiple tables _might_ work. PARTITIONing _might_ work. Both of those have other overheads that _might_ cause trouble.
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
November 25, 2014 04:35AM
Re: Partitioning or Not Partitioning?
November 25, 2014 11:45AM
November 25, 2014 11:55AM
November 29, 2014 03:42PM
November 24, 2014 05:14PM