1

Topic: How to make service of statistics for 20 records a day MySql?

Kind day
I ask all experts in a subject highload to help with the following job:
1) On the basis of PHP+MySQL it is necessary to implement service of reception of statistics of the game data.
C:
- ID the player
- Game time of event
- ID devices (a line, 64 characters)
- Device platform (iPad, iPhone)
- Dial-up of the arbitrary data (for example, the device sends event with parameters Event=Start, Money=15444 etc.)
2) Requirements:
- Service should withstand reception of 20 million calls a day.
- It is necessary to provide possibility of sampling and removal of the data previous day (for example, 10 th get and deleted the data for 9)
- Service should answer the client that reception transited successfully or unsuccessfully
The ideas:
1. DB structure
Since the data it will be written often and much, and to be deleted and read rarely that I see two variants of structure of a DB:
1.1 every day  (cron) to create the table next day - where the data will be added.
Then removal and data reading will not render influence on again writeable data.
CREATE TABLE IF NOT EXISTS ` day_data ` (
` id ` int (10) NOT NULL AUTO_INCREMENT;
` user_id ` int (10) NOT NULL;
` datetime ` int (10) NOT NULL;
` device_id ` varchar (64) NOT NULL;
` platform ` enum (' ipad ', ' iphone ') NOT NULL;
` data ` text NOT NULL;
PRIMARY KEY (` id `)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0;
1.2 Table forms one, but in it partitions (PARTITION) on day of month (1-31) become

CREATE TABLE IF NOT EXISTS ` day_data ` (
` id ` int (10) NOT NULL AUTO_INCREMENT;
` day_of_month ` tinyint (4) NOT NULL;
` user_id ` int (10) NOT NULL;
` datetime ` int (10) NOT NULL;
` device_id ` varchar (64) NOT NULL;
` platform ` enum (' ipad ', ' iphone ') NOT NULL;
` data ` text NOT NULL;
PRIMARY KEY (` id `, ` day_of_month `)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=0
PARTITION BY LIST (day_of_month)
(PARTITION p01 VALUES IN (1) ENGINE = InnoDB;
PARTITION p02 VALUES IN (2) ENGINE = InnoDB;
PARTITION p03 VALUES IN (3) ENGINE = InnoDB;
PARTITION p04 VALUES IN (4) ENGINE = InnoDB;
PARTITION p05 VALUES IN (5) ENGINE = InnoDB;
PARTITION p06 VALUES IN (6) ENGINE = InnoDB;
PARTITION p07 VALUES IN (7) ENGINE = InnoDB;
PARTITION p08 VALUES IN (8) ENGINE = InnoDB;
PARTITION p09 VALUES IN (9) ENGINE = InnoDB;
PARTITION p10 VALUES IN (10) ENGINE = InnoDB;
PARTITION p11 VALUES IN (11) ENGINE = InnoDB;
PARTITION p12 VALUES IN (12) ENGINE = InnoDB;
PARTITION p13 VALUES IN (13) ENGINE = InnoDB;
PARTITION p14 VALUES IN (14) ENGINE = InnoDB;
PARTITION p15 VALUES IN (15) ENGINE = InnoDB;
PARTITION p16 VALUES IN (16) ENGINE = InnoDB;
PARTITION p17 VALUES IN (17) ENGINE = InnoDB;
PARTITION p18 VALUES IN (18) ENGINE = InnoDB;
PARTITION p19 VALUES IN (19) ENGINE = InnoDB;
PARTITION p20 VALUES IN (20) ENGINE = InnoDB;
PARTITION p21 VALUES IN (21) ENGINE = InnoDB;
PARTITION p22 VALUES IN (22) ENGINE = InnoDB;
PARTITION p23 VALUES IN (23) ENGINE = InnoDB;
PARTITION p24 VALUES IN (24) ENGINE = InnoDB;
PARTITION p25 VALUES IN (25) ENGINE = InnoDB;
PARTITION p26 VALUES IN (26) ENGINE = InnoDB;
PARTITION p27 VALUES IN (27) ENGINE = InnoDB;
PARTITION p28 VALUES IN (28) ENGINE = InnoDB;
PARTITION p29 VALUES IN (29) ENGINE = InnoDB;
PARTITION p30 VALUES IN (30) ENGINE = InnoDB;
PARTITION p31 VALUES IN (31) ENGINE = InnoDB);

2. Removal
With removal of problems is not present since in the first variant DROP of the table happens quickly enough
In the second variant it is done ALTER TABLE ` day_data ` TRUNCATE PARTITION p11 is too quickly since if very roughly Mysql deletes the old data file and creates the new. And this operation is fulfilled much faster row-wise removal.
3. Reading
To avoid loss of the data and not to hang up the server on a gallop 20 records on the fly - beforehand it is saved this data in csv a file the server and for a gallop - we give the reference to a file to the server.
Saving it is produced in a cycle on 10000 records - installing a flag in a DB for the data already  in a file.
4. Return to the client
The answer  in the form of JSON
In case of good luck it is sent ok
In case of failure:
If the data incomplete - an error with the information on the incomplete data
If is  at an insertion in a DB - the information on an insertion error in a DB and error number
5. DB adjustment
For performance gain - it is possibly necessary to adjust in addition a DB affecting parameters:
innodb_buffer_pool_size
innodb_log_file_size
innodb_log_buffer_size
innodb_file_per_table
innodb_flush_method
innodb_flush_log_at_trx_commit
It is possible to prompt something here
In advance thanks all who answers.

2

Re: How to make service of statistics for 20 records a day MySql?

...How the data is interposed? 20 million day, 0.9 million at an o'clock;
250  in a second on the average, can be 500 or 1000
Transaction in peak. PHP it will be strongly surprised...
...In several projects on average iron a web a part
Normally 10 times longer than  requests to basis.
I.e. to you it is necessary either very serious computer or a cluster
Web of servers at least on 10-20 machines....
1000 insertions in a second -  for average iron...
Look still:
*  fast operating systems
* Not   - as it can beat out in a separate file
...Well and  not  too a long field DATE
* To think over as will be  ?
If it is necessary only CSV - that  simply not to write down a file?
(I not in course but can file recording there will be stupidly faster records in basis)
* To disconnect all transaction
* It seems  not the fastest  on reception and request processing
* ngnix faster Apaches....

3

Re: How to make service of statistics for 20 records a day MySql?

It is necessary to write all statistics at first to a cache (redis or ), and then , one request, from a cache all to write to basis.

4

Re: How to make service of statistics for 20 records a day MySql?

alex-zzx wrote:

I see two variants of structure of a DB:
1.1 every day  (cron) to create the table next day
1.2 Table forms one, but in it partitions (PARTITION) on day of month (1-31)

become
So this almost same. Also optimizes actually from the sounded tasks only cleaning - thus that it is operation almost one-time.
Would be not present to reflect on the sectioning reducing loading on one section from the main type of request - insertions. I.e., for example, at sectioning by date each section  on, say, ID the player. Time you predict 20 requests a day, i.e. 230 requests in a second, with, for example, 4 peak, that is sense to think of 8 or 16 sections at binary ID (the last 3 or 4 bits) or about 10 sections for decimal (the last digit) ID. And sections should be created not for tomorrow, and on fairly forward (well the minimum on ten days, and even is more - all the same empty sections of a place do not eat, and in a circuit of checks performance does not reach them).

5

Re: How to make service of statistics for 20 records a day MySql?

alex-zzx;
In the described requirements I do not see, whether it is necessary to do something with the data in portions smaller, than day.
If it is not necessary, then the DBMS is not so necessary. To form on a csv-file on every day enough.
If nevertheless it is necessary to hold the data in the form of separate records, what requirements to reliability and insertion time delays?
If, for example, to buffer the data on (for example, the sentry) to portions in a file and then to interpose this file  command LOAD DATA 20 million a day do not look something excessive.

6

Re: How to make service of statistics for 20 records a day MySql?

javajdbc wrote:

... In several projects on average iron a web a part
Normally 10 times longer than  requests to basis.
I.e. to you it is necessary either very serious computer or a cluster
Web of servers at least on 10-20 machines.....

Layout on  servers is planned - with it like problems should not be.

javajdbc wrote:

* not   - as it can beat out in a separate file

It agree. Tried to deliver varchar 250 - to 500 me rebuked - why so a little. And exact it is long the data it is not specified sad

javajdbc wrote:

* to think over as will be  ?
If it is necessary only CSV - that  simply not to write down a file?

Like I thought over the mechanism. In a cycle we read the data of previous day and we write them in csv a file. On the read data we put a flag in the table.
Too thought at once to write in csv a file but on conditions - the decision on the basis of PHP+MySQL is necessary

javajdbc wrote:

* to disconnect all transaction

Too as the variant, but integrity of the data can suffer at failures is not present?
Anyway thanks for remarks.

7

Re: How to make service of statistics for 20 records a day MySql?

machetero wrote:

it is necessary to write All statistics at first to a cache (redis or ), and then , one request, from a cache all to write to basis.

As though yes, but on conditions while it is necessary to do PHP+MySQL
Thanks for the remark.

8

Re: How to make service of statistics for 20 records a day MySql?

Akina wrote:

it is passed...
So this almost same. Also optimizes actually from the sounded tasks only cleaning - thus that it is operation almost one-time.
Would be not present to reflect on the sectioning reducing loading on one section from the main type of request - insertions. I.e., for example, at sectioning by date each section  on, say, ID the player. Time you predict 20 requests a day, i.e. 230 requests in a second, with, for example, 4 peak, that is sense to think of 8 or 16 sections at binary ID (the last 3 or 4 bits) or about 10 sections for decimal (the last digit) ID. And sections should be created not for tomorrow, and on fairly forward (well the minimum on ten days, and even is more - all the same empty sections of a place do not eat, and in a circuit of checks performance does not reach them).

Excellent idea. Itself thought of such third variant:
To do tables on every day and in these tables to partition the data on what or to a field. It turns out such "pseudo-horizontal sharding"
Whether but there will be a productivity gain at  - I do not know.

9

Re: How to make service of statistics for 20 records a day MySql?

miksoft wrote:

In the described requirements I do not see, whether it is necessary to do something with the data in portions smaller, than day.
If it is not necessary, then the DBMS is not so necessary. To form on a csv-file on every day enough.

Dataful to do it is necessary nothing. They need to be downloaded only in a type csv a file and then to delete the data for the selected day is all.
Too thought to form csv files per every day but conditions are that that it is impossible so to do sad
Can be at you is what or  confirming that file recording will be faster than in Mysql?

miksoft wrote:

If nevertheless it is necessary to hold the data in the form of separate records, what requirements to reliability and insertion time delays?
If, for example, to buffer the data on (for example, the sentry) to portions in a file and then to interpose this file  command LOAD DATA 20 million a day do not look something excessive.

I do not have these requirements sad
Too like quite good a variant
Thanks for remarks.

10

Re: How to make service of statistics for 20 records a day MySql?

alex-zzx wrote:

Can be at you is what or  confirming that file recording will be faster than in Mysql?

it is so obvious that at all I do not know.
MySQL too the data stores not in vacuum, and in a file. Esteem the device of files of engine InnoDB, there all is uneasy. And it "uneasy" manages not free of charge.

11

Re: How to make service of statistics for 20 records a day MySql?

alex-zzx wrote:

Dataful to do it is necessary nothing. They need to be downloaded only in a type csv a file and then to delete the data for the selected day is all.
Too thought to form csv files per every day but conditions are that that it is impossible so to do sad

Then at record in MySQL it is not mandatory  the initial data on fields/records. It is possible to write in a crowd/portions to BLOB-fields (the same TEXT, but without an overhead charge for the coding).

12

Re: How to make service of statistics for 20 records a day MySql?

javajdbc wrote:

1000 insertions in a second -  for average iron...

It is Generally small even for an old notebook, but the sheaf with PHP, of course, spoils all raspberry.

13

Re: How to make service of statistics for 20 records a day MySql?

miksoft wrote:

it is passed...
It is so obvious that at all I do not know.
MySQL too the data stores not in vacuum, and in a file. Esteem the device of files of engine InnoDB, there all is uneasy. And it "uneasy" manages not free of charge.

Like and yes but if simultaneously 200 persons try to write down in a file and in loading peak the whole one thousand - that while 1 writes remaining it is necessary to wait. And long wait they can.
I ask to correct if I am mistaken.

14

Re: How to make service of statistics for 20 records a day MySql?

miksoft wrote:

it is passed...
Then at record in MySQL it is not mandatory  the initial data on fields/records. It is possible to write in a crowd/portions to BLOB-fields (the same TEXT, but without an overhead charge for the coding).

It is simply ingenious!!! Thanks!
As far as I understand - pushing all in one field - it turns out to spare a heap of resources.
As a result came to such decision
1) All data come from 1 client I write at once to one field BLOB
2) I Do splitting of tables on days - reading and removal previous day does not brake in any way record of the new data.
3) For each day table I do sectioning through HASH or KEY on id records is allows to accelerate simultaneous data writing.
Correct if I am mistaken.

15

Re: How to make service of statistics for 20 records a day MySql?

What for sectioning? In the table there will be  records, or generally one. What there to partition?

16

Re: How to make service of statistics for 20 records a day MySql?

miksoft;
And why there there will be 1 record?
Means that in the table there will be 1 record, in this record there will be field DATA of type BLOB and in it the data will be added?

17

Re: How to make service of statistics for 20 records a day MySql?

At first thought that on each request there will be a record in the table
id, data
And in the field data in format BLOB the accepted data will be written
And such records in 1 table will be 20 million

18

Re: How to make service of statistics for 20 records a day MySql?

alex-zzx wrote:

miksoft;
And why there there will be 1 record?
Means that in the table there will be 1 record, in this record there will be field DATA of type BLOB and in it the data will be added?

well, as a file, only in MySQL.
Though it is possible to write also each reversal new record, depends on project details.

19

Re: How to make service of statistics for 20 records a day MySql?

miksoft;
Tested speed of an insertion through INSERT
And through UPDATE
As a result it turned out - if we  the same field - permanently adding in it - that speed of operation in comparison with  noticeably more low