MySQL Forums
Forum List  »  Newbie

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.

Options: ReplyQuote


Subject
Written By
Posted
Re: Partitioning or Not Partitioning?
November 25, 2014 11:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.