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 ?

Shwalb

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.

Pontificate

Thanks. The media files are images. 3 versions of same image at different sizes. File size range from about 10k to 80k and is not likely to be much more than that. With that in mind it's still going to be a huge db in size. Does that matter? Or is mysql tough enough for the job. Of course memcached will be utilized on live side. – Shwalb

I like the idea of nfs. But as this is a personal project my funds are limited. I have a good live dedicated server. In a data centre with unmetered bandwidth and t1 connection. I have an internal server in the office but is constrained by the Internet upstream connection which is about 512up 20mbs down. So with this iconnection nfs connection on the in-house server will slow the consuming software down - right? – Shwalb

Are the servers in the same data center? can they talk to each other without VPN? check this links how-to.linuxcareer.com/how-to-configure-nfs-on-linux. Maybe you can start storing images in the database since they are small and then move to the NFS solution if you upgrade to bigger media. – Pontificate

No not in same data centre. I guess you are right but I'm am concerned that I might make wrong choice and go too far down wrong road. – Shwalb

I never liked the idea of storing media in database. Have you thought about using some third party solution? like akamai.com – Pontificate

Doesn't this cost a lot though ? I'm on a one man band budget – Shwalb

Yes that of course cost money. Well if money is #1, try to stop the images on database. As time goes by, you will find out what to do when the money starts to show up! – Pontificate

Good answer have you think about VARNISH @ en.wikipedia.org/wiki/Varnish_(software) ? – Impost

G-WAN + REDIS = very reliable and fast – Mauretta

Answer 2

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

Woadwaxen