Insert a database row on each pageview

Question

I am working on a system that should log every kind of page in a MySQL table. The view will be registered only if the visitor has not been on this page before in the past 24 hours. I am wondering if this would be a problem in terms of performance and database size.

The site that needs to do this is about 60,000 unique pageviews per day, so approximately 60,000 new lines are added per day (a little less than 1 time in 2 seconds). The table has only 3 columns: i_id , ip_address , timestamp . i_id is a foreign key to another table.

The table will be cleared at the end of each day using a CRON script.

Will there be any instant overstrain of the database by doing this? For example, if a site receives a surge in traffic (it runs fairly regularly), it can take up to 200,000 page views per day, which means more than 2 requests per second.

Answer 1

The general agreement should not contain restrictions (primary, external, etc.) in the audit table and, of course, not indexes - all of the above slows down the insertion.

Bulk insertion will work taking into account - batch insertions to reduce the number of connections required for the database, the amount of time spent on operations (one versus a large number). In addition, if transaction logs are written for this, minimize their recording, because the database may be affected by the need to write to IO if you want to be able to resurrect the database at a specific point in time.

I see no reason to clear records at the end of the day - what about traffic that occurs two days later? Separating MySQL is likely to be a better idea .

Answer 2

Your problem is not page views per day. You should consider how many page views you have to serve every second during peak hours. If pageviews are evenly distributed and you only have 2 pageviews per second, on an average non-shared server this will not be a problem.

But it is impossible to determine without additional data, for example, what equipment you use, the distribution of the actual page load, etc.

Answer 3

A few comments:

• Make sure this is an InnoDB table. MyISAM locks the entire table for each insert or update, while InnoDB uses row-level locking.
• Use the smallest numeric data types corresponding to each column.
• Two queries per second? MySQL eats two queries per second before breakfast. Seriously, you must be able to withstand hundreds.
• If you're still worried, be sure to use MySQL 5.1 or later, as it allows much better concurrency in InnoDB tables.
• The “foreign” key should, I hope, be applied only through code and agreement, and not as a strict restriction, as this will slow down the insertion.

Answer 4

Just make sure your table has the correct index to select. Database management systems are designed to withstand much more.

Answer 5

I think you should:

• Delete foreign keys. It seems that in this case they are redundant. When you use FK on every INSERT / UPDATE / DELETE, db spends additional resources to check the integrity of the table data. This is not necessary for logging. We need performance and quick response.
• Use myisam. The MyIsam mechanism is simpler, and it does not spend resources on various additional things, such as transaction logging, logging, etc., How it is done in Innodb.
• Use INSERT DELAYED to insert and flush indices not for a single row, but for a batch. More details http://dev.mysql.com/doc/refman/5.5/en/insert-delayed.html . Each db insert request performs some operations, and one of them is a cleanup index. If you run 20 queries, 20 flushes will be made. INSERT DELAYED selects queries in batch mode and runs them as a single query. Thus, you get only one wash.

Answer 6

You probably want to make sure your mysql clusters are optimized and there may be strains, just make sure they are ready for a similar hit.