MySQL Performance: MySQL => SELECTs to slave, INSERTs to master by application?

Members

Help

Home

Home » Performance » MySQL » SELECTs to slave, INSERTs to master by application?

Show: Today's Messages :: Show Polls :: Message Navigator

SELECTs to slave, INSERTs to master by application? [message #822]

Tue, 27 February 2007 09:48

soularis
Messages: 1
Registered: February 2007
Location: Hamburg, Germany

Junior Member

Hi,

I have a question and would be very thankful on any helping comments whatsoever.

I am running a PHP web application on a two webserver cluster. I also have two master-slave-replicated MySQL 5x databases.

What I want to do now is to send read-only-queries to the slave and all others queries to the master, or, better yet, to send write-queries to the master only and read-only-queries to 70% to the slave and 30% to the master (as I expect around 90% read-only-queries after all).

I already tried the not-yet-documented "mysqli_slave_query" and "mysqli_master_query" functions, but it did not work. I also tried "mysqli_enable_rpl_parse", which at least did not put out any error messages but all queries were sent to the master.

Last thing I tried was to open two database connections and do the parsing myself, which led me into having only half the performance. Not good.

I also googled myself through thousends of sites but could not find any helpful advice, maybe I do here?

Thanks a lot!
Stefan.

Report message to a moderator

Re: SELECTs to slave, INSERTs to master by application? [message #823 is a reply to message #822 ]

Tue, 27 February 2007 10:29

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

These functions never really worked, so do not use them and just do the things manually - connect to the slave and use it for queries which you can use it for.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: SELECTs to slave, INSERTs to master by application? [message #851 is a reply to message #823 ]

Sun, 04 March 2007 11:26

aleksis
Messages: 1
Registered: March 2007

Junior Member

Stefan,

I have a similar setup and we choose what server to use at application level. We also use hostnames to connect, as that way if the slave were to go down, it would be easy to direct traffic towards the master.

Report message to a moderator

Re: SELECTs to slave, INSERTs to master by application? [message #1372 is a reply to message #822 ]

Thu, 07 June 2007 05:42

erick_p
Messages: 3
Registered: June 2007

Junior Member

Hi,

I am new to this replication thing. I want a simple setup where the "master" is the DB where I do inserts, updates, etc. I want the "slave" to focus on selects. I want to keep them almost at the same level of update (i.e., in microseconds, so that when one updates a record, it is immediately available through the slave for selection too. But when I am doing updates on the master, and master is therefore blocked (due to the grand MYISAM table-locking idiocy), I want the slave to be available througout this update time.

Is this what replication is for? A search for things like "read-only mysql replication" comes up on google with NOTHING, not a single guide on the net that tells me how to do this. I'd like a simple step by step guide -- and I have only one dedicated server, although I have 5 IPs, so I don't have a physical "cluster" or anything, I just want two separate processes running. Would appreciate any pointers!

Thanks
.ep

[Updated on: Thu, 07 June 2007 05:43]

Report message to a moderator

Re: SELECTs to slave, INSERTs to master by application? [message #1375 is a reply to message #1372 ]

Thu, 07 June 2007 18:57

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

erick_p wrote on Thu, 07 June 2007 11:42

I want to keep them almost at the same level of update (i.e., in microseconds, so that when one updates a record, it is immediately available through the slave for selection too.

The replication is normally carried out as soon as possible. Which is when the query has been performed on the master and logged. More in the millisecond range than in the microsecond.

erick_p wrote on Thu, 07 June 2007 11:42

But when I am doing updates on the master, and master is therefore blocked (due to the grand MYISAM table-locking idiocy), I want the slave to be available througout this update time.

You can't really do that because the exact same changes will be performed on the slave also and then the slave will be locked for reading while the updates is performed.

erick_p wrote on Thu, 07 June 2007 11:42

and I have only one dedicated server, although I have 5 IPs, so I don't have a physical "cluster" or anything, I just want two separate processes running.

You don't want to do this because you loose more than you gain.
When you have two processes running it means that they can't share the same cache memory etc. And you also consuming CPU since these two processes needs replication to be in sync.

If you have run into the performance problem with SELECT's that take a long time and a lot of INSERT/UPDATE/DELETE's you should change to InnoDB table type instead.
It uses row level locking instead and then you avoid this problem.

[Updated on: Thu, 07 June 2007 19:00]

Report message to a moderator

Re: SELECTs to slave, INSERTs to master by application? [message #1376 is a reply to message #822 ]

Fri, 08 June 2007 00:22

erick_p
Messages: 3
Registered: June 2007

Junior Member

Thank you. I have tried InnoDB but it is horribly slow. I was quite sold on the idea of row-level locking and tried all sorts of settings of memory and so forth, but InnoDB just does not cut it in terms of raw SELECT speed, which is critical for my application. Which is why I wanted my master to be InnoDB and my slave (for SELECTs) to be MYISAM, but on the same machine. Thanks for your informative post, I suppose I shouldn't do what I was thinking.

Report message to a moderator

Re: SELECTs to slave, INSERTs to master by application? [message #1377 is a reply to message #822 ]

Fri, 08 June 2007 04:25

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

I am bit curious what you tested if you felt that InnoDB was slow.
There are quite a few things that you can change to speed it up.

The most important startup variables:

Quote:

your innodb_buffer_pool_size = 80% * [AVAILABLE RAM]

Should be set to 80% of available RAM.
This is the internal cache for the InnoDB and when innodb has to read/write a lot of things to disk all the time it can't optimize these operations and can be horribly slow.

InnoDB is default setup for integrity because not that many people need raw speed but if you are willing to sacrifice integrity for speed then you can continue with these:

Quote:

innodb_flush_log_at_trx_commit = 0

These are for writing the logfiles that should be used in case of a crash to restore the DB up to the same point.
It is default set to 1 which means that the log is flushed out to disk after _each_ transaction and if you have slow disks this can really make your application go very slow.
So if you need more speed and can live with that you can loose about 1 second of transactions in case of a crash you should set this to 0.

Those two are the most important when it comes to speed for InnoDB.

There are 1 more that can also be useful as a last step and that is innodb_doublewrite which turns of writing to the doublewrite buffer but generally the two first is enough.

Report message to a moderator

Re: SELECTs to slave, INSERTs to master by application? [message #1379 is a reply to message #822 ]

Fri, 08 June 2007 06:59

erick_p
Messages: 3
Registered: June 2007

Junior Member

Thanks for these tips. I am on a MySQL server on a dedicated host which has 2 dual core CPUs and 4GB memory. When I try the following settings in my

my.cnf

, it does not even start!

#skip-innodb
innodb_buffer_pool_size=3G
innodb_flush_log_at_trx_commit=0

If I remove the comment from ski-innodb then it starts ok.

Report message to a moderator

Re: SELECTs to slave, INSERTs to master by application? [message #1382 is a reply to message #822 ]

Fri, 08 June 2007 08:27

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

Start by testing:
innodb_buffer_pool_size=2G

The famous 2GB limit might come in play here.

[Updated on: Fri, 08 June 2007 08:28]

Report message to a moderator

Previous Topic:	should a count with an index take this long?
Next Topic:	mysql restart problems....please help!!!

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Sun Jul 5 17:10:25 EDT 2009

Total time taken to generate the page: 0.05039 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.