Over the course of running Anime Characters Database, I've been faced with many challenges regarding the speed of the site database.
Running a concurrent MySQL database has many well defined challenges when you have several people at once selecting data, updating data, deleting data, and inserting data.
The books will tell you to use the appropriate storage engine for the situation.
MyISAM has only table locks, but performs well on selects.
InnoDB has row level locks and table locks.
mysql> show status like '%wait%';
+-------------------------------+--------+
| Variable_name | Value |
+-------------------------------+--------+
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_waits | 105983 |
| Table_locks_waited | 500813 |
| Tc_log_page_waits | 0 |
+-------------------------------+--------+
My question was... what if I could reduce all those wait times to 0?
Thus I began a years long pursuit into the question of how to make something that is by definition impossible, possible!
And it has been done~
mysql> show status like '%wait%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_waits | 0 |
| Table_locks_waited | 0 |
| Tc_log_page_waits | 0 |
+-------------------------------+-------+
By offloading nearly 80% of the site traffic to a Read Only database, I've managed to buy us a long term huge performance boost.
In the end, the project can be explained in 9 simple steps, should you want to do it too.
1) get src
2) configure
3) make
4) install
5) bootstrap
6) start
7) connect
8) take snapshot
9) replay snapshot
Understanding each of those steps could take months of study mind you.
------------------
In case anyone is wondering, "What will I notice as different?" there was a problem with adding characters where the site would lock up / lag for an average period of 9 seconds. That should no longer be the case.