1

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?

Community
  • 1
Rajesh
  • 11
  • 1

2 Answers2

2

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.

alex
  • 402
  • 3
  • 9
1

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.

Community
  • 1
RolandoMySQLDBA
  • 185,223
  • 33
  • 326
  • 536