Muzak replication advice and techniques

Question

I am attempting my first large scale database project on my own. I have a myisam mysql db on server 1 with a php app consuming large amount of various data. I have mysql myisam on server 2 with php app selecting and displaying data.

I want to replicate this data on server 2.

Questions:

1. Should I change server 1 mysql db to innodb
2. Can you replicate server1 innodb to server2 myisam
3. I'm storing media as blobs with intention of using cache to offload stress on live server. Should I use filesystem storage and rsync.
4. Any general advice from other experienced people ?

Answer 1

Here's what I suggest based on my experience.

1. You can use one type of engine (MyISAM or InnoDB) for both servers. I you mix both engines, you might get Deadlock, transaction problems etc... and time fixing them can be painful. I had problems a little while ago with InnoDB -> MyISAM. Now I used MyISAM on all servers.

2. For storing media (such as images, video or documents) you can create a NFS and mount a folder like /usermedia/ that both servers access. Therefore you don't have to rsync every time. In addition, you can save the meta data or media information to the database for reference and where the file is saved on the disk. Note: using a blob to save files can be good depending on the media. If you have a file that is approximately 1 gig might not be a good idea to save on the database for example).

3. Use a caching system to retrieve data (such as memcached). For example, if you request data and you need to display them to the user, look first in the cache. If it's not in the cache, query the database, save it to the cache and display. Next time the same information is requested, you won't request it from the server but from memory. This solution will avoid numerous calls on the Database server which will improve performance.

Let me know if you need additional help.

Answer 2

I would recommend InnoDB (for transactions, row locking and not table locking) and redis as the caching very fast and efficient