Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

my Use-Case :

The solution I am developing is very data oriented. I run a cron job every 3 hrs to read lots of records from a file, process them and store them in the database. I also need to generate reports, which I do by querying database.

So, I was thinking of having MySQL replication in Master and slave so that I can do all the inserts / updates on Master and use the slave just to read data to generate reports.

I read this other post on the site and it got me thinking.

Here is my concern: Both my master and slave will be sharing the same resources here as they are on same physical server.

Is it good idea to do so?

share|improve this question
add comment

2 Answers

I don't think so. You might reduce locking issues (but I'm not sure of that), but you are adding replication overhead without increasing your hardware resources, which means you'll be doing more with the same resources, which probably means decreased performance.

share|improve this answer
    
And by hosting both instances on the same hardware you miss out on a major part of replication; backup/redundance. If your master data disk goes down the drain, it takes the replicated data along for the ride. I second Rolando, get another VPN (on another machine) and run the slave there. –  Bing Nov 21 '12 at 9:09
add comment

All you would have to do is use different data disks for the master and slave.

For example:

/dev/sda1 on / type ext3 (rw)
proc on /proc type proc (rw)
sysfs on /sys type sysfs (rw)
devpts on /dev/pts type devpts (rw,gid=5,mode=620)
tmpfs on /dev/shm type tmpfs (rw)
/dev/sdb1 on /var/lib/mysql type ext3 (rw)
/dev/sdc1 on /var/lib/mysqlslave type ext3 (rw)

Make sure the slave's datadir is pointing to whichever disk the slave is to write to

You should also be conservative in setting up buffer sizes since the Master and Slave share them. You can lessen that concern by just upgrading the RAM.

If the master and slave must reside on the same disk, a low-write/read-heavy with a moderate-sized dataset could fair well. Any other traffic patterns and it's farewell to performance.

If all your data on the master is InnoDB, just make sure you have innodb_buffer_pool_size able to accommodate your working dataset (which may be less than all your data). For the slave, since it should be for reporting and adhoc queries, all tables on the slave can be converted to MyISAM. Then, you only have to set the key_buffer_size to accommodate MyISAM indexes.

IMHO Since you are using VPS, you should get another VPS and put the slave on it to assure having a separate disk.

share|improve this answer
    
Hey Rolando, this solution is hosted on a VPS, I can have increased RAM but I am not sure I could use different data disks for master and slave. –  Rajesh Nov 30 '11 at 20:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.