How to store and write to the database a large number of rows?

Question

PHP

Hello,
I need to write each user's document.referrer to the database every new session.
Site attendance is 100,000 per day, 3500 lines per 500 kb were created in 2 hours (is this a lot?)

Please tell me how you can optimize the load?

Answer 1

Let's start with the math first.
3500 for 2 hours is 49000 for 24 hours (but rather less, the load is probably more during the day than at night). No longer 100,000.
How much is 500Kb? it all depends on the storage depth, server performance and what you plan to do with this data.
depth: 500kb(for 2 hours)*12 = 6Mb. 180 mb per month, 2.2 giga per year. I have a 12 gigabyte database on one of the servers, with tables of 7-8 million rows. works quite slick.
performance: If you decide to store data, say, 10 years, and get 20-30 gigs of data as a result, then it's stupid to expect to spin all this wealth on a server with 1-2 GB of memory and slow disks.
usage: If this data will only be accumulated and occasionally retrieved, this is one thing. If in parallel with the record there will be active selects, this is different. But here again, everything will affect the performance requirements.
let's just say, on any modern hardware with a normal amount of memory (from 8 gigs), your base will feel fine in the foreseeable future (2-3 years). Although the activity of requests for a selection can make adjustments to the estimates.

Answer 2

500kb is not much. Perhaps it is worth somehow clarifying the need for insertion. For example, if the specified data has not changed. Alternatively, if this data is not vital, but is used for logging, then you can store it in another database (remove the load from the main one) or write it in XML, but this will complicate processing.
UPD: You are not writing a large number of lines to the database, but making MANY requests to add. The difference is big enough. Your wording might suggest that you are adding hundreds of lines at a time, which is not true.