How to effectively INSERT multiple tables at once?

Question

MySQL

There are tables:
visitors_daily (date, ip, count)
visitors_weekly (date, ip, count)
visitors_monthly(date, ip, count)
visitors_yearly(date, ip, count)
We need to record visitor data. There are a lot of visitors. For example 1000 per second.
What is the most efficient way to write data to multiple tables at once? It is necessary to minimize the traffic to the DBMS and the load on the DBMS.
For a visitor with IP=1.2.3.4 from 2015-07-18
, write
2015-07-18 into daily
2015-07-06 (Monday) into weekly
2015-07-01 (first of the month) into montly
2015-01-01 per yearly.
The primary key is date-ip. In case of duplicates, increase count by one.
Desirable solution for both Postgres and MySQL.

Answer 1

Write to files on each server separately Collect
files from servers
Read from files
Aggregate
Insert via transaction
Create weekly and monthly when their time
comes baduu does this via scribe, but rsync is also suitable for you
https://www.youtube.com/watch? v=LtdsjspZ4xM
In general, you lose a lot of information about the user like that

Answer 2

use triggers.

Answer 3

You need to make a STORED PROCEDURE!

Answer 4

And how often will this data be sampled? Maybe it's easier to record only the counter by day, and calculate the rest in real time?