How to optimize mysql indexes so that INSERT operations happen quickly on a large table with frequent writes and reads?

How to optimize mysql indexes so that INSERT operations happen quickly on a large table with frequent writes and reads?

I have a table watchlist containing today almost 3Mil records.

mysql>  select count(*) from watchlist;  +----------+  | count(*) |  +----------+  |  2957994 |  +----------+  

It is used as a log to record product-page-views on a large e-commerce site (50,000+ products). It records the productID of the viewed product, the IP address and USER_AGENT of the viewer. And a timestamp of when it happens:

mysql> show columns from watchlist;  +-----------+--------------+------+-----+-------------------+-------+  | Field     | Type         | Null | Key | Default           | Extra |  +-----------+--------------+------+-----+-------------------+-------+  | productID | int(11)      | NO   | MUL | 0                 |       |  | ip        | varchar(16)  | YES  |     | NULL              |       |  | added_on  | timestamp    | NO   | MUL | CURRENT_TIMESTAMP |       |  | agent     | varchar(220) | YES  | MUL | NULL              |       |  +-----------+--------------+------+-----+-------------------+-------+  

The data is then reported on several pages throughout the site on both the back-end (e.g. checking what GoogleBot is indexing), and front-end (e.g. a side-bar box for "Recently Viewed Products" and a page showing users what "People from your region also liked" etc.).

So that these "report" pages and side-bars load quickly I put indexes on relevant fields:

mysql> show indexes from watchlist;  +-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+  | Table     | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |  +-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+  | watchlist |          1 | added_on  |            1 | added_on    | A         |        NULL |     NULL | NULL   |      | BTREE      |         |  | watchlist |          1 | productID |            1 | productID   | A         |        NULL |     NULL | NULL   |      | BTREE      |         |  | watchlist |          1 | agent     |            1 | agent       | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |  +-----------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+  

Without the INDEXES, pages with the side-bar for example would spend about 30-45sec executing a query to get the 7 most-recent ProductIDs. With the indexes it takes <0.2sec.

The problem is that with the INDEXES the product pages themselves are taking longer and longer to load because as the table grows the write operations are taking upwards of 5sec. In addition there is a spike on the mysqld process amounting to 10-15% of available CPU each time a product page is viewed (roughly once every 2sec). We already had to upgrade the server hardware because on a previous server it was reaching 100% and caused mysqld to crash.

My plan is to attempt a 2-table solution. One table for INSERT operations, and another for SELECT operations. I plan to purge the INSERT table whenever it reaches 1000 records using a TRIGGER, and copy the oldest 900 records into the SELECT table. The report pages are a mixture of real-time (recently viewed) and analytics (which region), but the real-time pages tend to only need a handful of fresh records while the analytical pages don't need to know about the most recent trend (i.e. last 1000 views). So I can use the small table for the former and the large table for the latter reports.

---

My question: Is this an ideal solution to this problem?

Also: With TRIGGERS in MySQL is it possible to nice the trigger_statement so that it takes longer, but doesn't consume much CPU? Would running a cron job every 30min

that is niced, and which performs the purging if required be a better solution?

Answer by Adam Batkin for How to optimize mysql indexes so that INSERT operations happen quickly on a large table with frequent writes and reads?

Something that often helps when doing bulk loads is to drop any indexes, do the bulk load, then recreate the indexes. This is generally much faster than the database having to constantly update the index for each and every row inserted.

Answer by Emil H for How to optimize mysql indexes so that INSERT operations happen quickly on a large table with frequent writes and reads?

A quick fixed might be to use the INSERT DELAYED syntax, which allows mysql to queue the inserts and execute them when it has time. That's probably not a very scalable solution though.

I actually think that the principles of what you will be attempting is sound, although I wouldn't use a trigger. My suggested solution would be to let the data accumulate for a day and then purge the data to the secondary log table with a batch script that runs at night. This is mainly because these frequent transfers of a thousand rows would still put a rather heavy load on the server, and because I don't really trust the MySQL trigger implementation (although that isn't based on any real substance).

Answer by richardtallent for How to optimize mysql indexes so that INSERT operations happen quickly on a large table with frequent writes and reads?

Write operations for a single row into a data table should not take 5 seconds, regardless how big the table gets.

Is your clustered index based on the timestamp field? If not, it should be, so you're not writing into the middle of your table somewhere. Also, make sure you are using InnoDB tables - MyISAM is not optimized for writes.

I would propose writing into two tables: one long-term table, one short-term reporting table with little or no indexing, which is then dumped as needed.

Another solution would be to use memcached or an in-memory database for the live reporting data, so there's no hit on the production database.

One more thought: exactly how "live" must either of these reports be? Perhaps retrieving a new list on a timed basis versus once for every page view would be sufficient.

Answer by Denis Bazhenov for How to optimize mysql indexes so that INSERT operations happen quickly on a large table with frequent writes and reads?

Instead of optimizing indexes you could use some sort of database write offload. You could delegate writing to some background process via asynchronous queue (ActiveMQ for example). Inserting a message into ActiveMQ queue is very fast. We are using ActiveMQ and have about 10-20K insert operations on test platform (and this is single threaded test application! So you could have more).

Answer by Lucas for How to optimize mysql indexes so that INSERT operations happen quickly on a large table with frequent writes and reads?

Look for 'shadow tables' when reconstructing tables this way, you don't need to write to the production table.

Answer by Lucho Cabrejo for How to optimize mysql indexes so that INSERT operations happen quickly on a large table with frequent writes and reads?

I had the same issue even using InnoDB tables or MyISAM as mention before, not optimized for writes, and solved it by using a second table to write temp data (that can periodically update master huge table). Master table over 18 million records, used to read only records and write result on to second small table.

The problem is the insert/update onto the big master table, takes a while, and worse if there are several updates or inserts on the queue awaiting, even with the INSERT DELAYED or UPDATE [LOW_PRIORITY] options enabled

To make it even faster, do read the small secondary table first, when searching a record, if te record is there, then work on the second table only. Use the master big table for reference and picking up new data record only *if data is not on the secondary small table, you just go and read the record from the master (Read is fast on InnoDB tables or MyISAM schemes)and then insert that record on the small second table.

Works like a charm, takes much less than 5 seconds to read from huge master 20 Million record and write on to second small table 100K to 300K records in less than a second.

This works just fine.

Regards

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72