What is the best way to store traffic data in a database?

Question

MySQL

Hello!
It is necessary to store traffic data in mysql database. Each entry can have a value from 100 bytes to 100 terabytes. Various actions will be performed with these records, for example, summation, etc.
Tell me, how best to store all this stuff in the database? I thought to store in one value, for example, in bytes, but something too big numbers are obtained.

Answer 1

Tell me, how best to store all this stuff in the database? I thought to store in one value, for example, in bytes, but something too big numbers are obtained.

If these numbers do not go beyond the maximum allowable size (range), for example, 9223372036854775807 - signed, 18446744073709551615 - unsigned BIGINT, then most likely, nothing is better for storing numbers (than a special type of database designed to store exactly numbers) - you will not find .
PS If you need accuracy to bytes - you need to store in bytes. If up to megabytes - acc. round values ​​and store in megabytes. That is, depending on the required accuracy, you can choose the final value. Processing large numbers (integers) is not a problem for a computer, numbers are processed in some cases better than, for example, text.

Answer 2

Store as separate units. Those. for one record you will have the fields "terabyte" "gigabyte" "megabyte" "byte" and write functions to convert to the values ​​you need. Those. if you add 1 megabyte and you already have 1024 megabytes in this entry, then you do "terabyte"+1, megabyte =1. Separately, the function of summing all fields, etc. In general, it is best to avoid working and math with huge numbers. In addition, it will be much more convenient to operate with rounded values. If you store all this in one field, then for example, a record of 1 terabyte will be 1e + 12 bytes, which is simply terrible.

Answer 3

Your problem is not with large numbers, bigint will solve them, but a large amount of data, millions and billions of records will put your base and create fucking problems in the future
. Therefore, immediately lay down table partitioning by time intervals, and not necessarily by means of a database, it is enough to create a new one yourself table for each next time interval (weeks, months - depends on your workload).
why do you want to store simple linear logs in a db?
what else do you need to do with the data besides filtering and summing?
if all the same, store in the database, then do not create indexes on such fields as traffic volume and url, this is the stupidest mistake, while writing to the table, calculate the necessary parameters in advance, select important data from the url, calculate the domain, ip (today it is one, tomorrow another ), collect amounts for traffic in a separate table, if the load allows, you can count by ip (or by zones), as a result, you will not work with raw data, but already aggregated and calculated, they are an order of magnitude smaller and they are more convenient to use.

Answer 4

Want to save money? "Collapse" on the go and lay out the finished result into groups (reports).