From: Marcus Bointon Date: December 20 2007 9:49am Subject: Re: load balanced config List-Archive: http://lists.mysql.com/replication/1075 Message-Id: MIME-Version: 1.0 (Apple Message framework v915) Content-Type: multipart/signed; boundary=Apple-Mail-26-793637957; micalg=sha1; protocol="application/pkcs7-signature" --Apple-Mail-26-793637957 Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit On 20 Dec 2007, at 08:39, Nicola Delbono wrote: > By load balancing I mean just directing requests to 2 or more servers > (read/write) > keeping all of them updated (if possible in reality) Normal replication in MySQL does not allow you to implement load balancing for writes. That said, there are two ways you can achieve it: by using partitioning, either manually in your clients, in the database server using MySQL 5.1, or by using Sequoia between client and servers. Replication will allow you to gain redundancy, but since every server has to do every write, you gain nothing from a load point of view. You might find that implementing a caching layer using something like memcache brings better results than using load balanced DBs - doubling your number of DB servers can give you a theoretical maximum of a 2x speed increase (which you're unlikely to get), whereas caching alone could get you 200x on the queries that matter. Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ UK resellers of info@hand CRM solutions marcus@stripped | http://www.synchromedia.co.uk/ --Apple-Mail-26-793637957 Content-Disposition: attachment; filename=smime.p7s Content-Type: application/pkcs7-signature; name=smime.p7s Content-Transfer-Encoding: base64 MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIILLzCCBN0w ggPFoAMCAQICEHGS++YZX6xNEoV0cTSiGKcwDQYJKoZIhvcNAQEFBQAwezELMAkGA1UEBhMCR0Ix GzAZBgNVBAgMEkdyZWF0ZXIgTWFuY2hlc3RlcjEQMA4GA1UEBwwHU2FsZm9yZDEaMBgGA1UECgwR Q29tb2RvIENBIExpbWl0ZWQxITAfBgNVBAMMGEFBQSBDZXJ0aWZpY2F0ZSBTZXJ2aWNlczAeFw0w NDAxMDEwMDAwMDBaFw0yODEyMzEyMzU5NTlaMIGuMQswCQYDVQQGEwJVUzELMAkGA1UECBMCVVQx FzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNUIE5ldHdvcmsx ITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2MDQGA1UEAxMtVVROLVVTRVJGaXJz dC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsMIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8A MIIBCgKCAQEAsjmFpPJ9q0E7YkY3rs3BYHW8OWX5ShpHornMSMxqmNVNNRm5pELlzkniii8efNIx B8dOtINknS4p1aJkxIW9hVE1eaROaJB7HHqkkqgX8pgV8pPMyaQylbsMTzC9mKALi+VuG6JG+ni8 om+rWV6lL8/K2m2qL+usobNqqrcuZzWLeeEeaYji5kbNoKXqvgvOdjp6Dpvq/NonWz1zHyLmSGHG TPNpsaguG7bUMSAsvIKKjqQOpdeJQ/wWWq8dcdcRWdq6hw2v+vPhwvCkxWeM1tZUOt4KpLoDd7Nl yP0e03RiqhjKaJMeoYV+9Udly/hNVyh00jT/MLbu9mIwFIws6wIDAQABo4IBJzCCASMwHwYDVR0j BBgwFoAUoBEKIz6W8Qfs4q8p74Klf9AwpLQwHQYDVR0OBBYEFImCZ33EnSZwAEu0UEh83j2uBG59 MA4GA1UdDwEB/wQEAwIBBjAPBgNVHRMBAf8EBTADAQH/MB0GA1UdJQQWMBQGCCsGAQUFBwMCBggr BgEFBQcDBDARBgNVHSAECjAIMAYGBFUdIAAwewYDVR0fBHQwcjA4oDagNIYyaHR0cDovL2NybC5j b21vZG9jYS5jb20vQUFBQ2VydGlmaWNhdGVTZXJ2aWNlcy5jcmwwNqA0oDKGMGh0dHA6Ly9jcmwu Y29tb2RvLm5ldC9BQUFDZXJ0aWZpY2F0ZVNlcnZpY2VzLmNybDARBglghkgBhvhCAQEEBAMCAQYw DQYJKoZIhvcNAQEFBQADggEBAJ2Vyzy4fqUJxB6/C8LHdo45PJTGEKpPDMngq4RdiVTgZTvzbRx8 NywlVF+WIfw3hJGdFdwUT4HPVB1rbEVgxy35l1FM+WbKPKCCjKbI8OLp1Er57D9Wyd12jMOCAU9s APMeGmF0BEcDqcZAV5G8ZSLFJ2dPV9tkWtmNH7qGL/QGrpxp7en0zykX2OBKnxogL5dMUbtGB8SK N04g4wkxaMeexIud6H4RvDJoEJYRmETYKlFgTYjrdDrfQwYyyDlWjDoRUtNBpEMD9O3vMyfbOeAU TibJ2PU54om4k123KSZB6rObroP8d3XK6Mq1/uJlSmM+RMTQw16Hc6mYHK9/FX8wggZKMIIFMqAD AgECAhABjmOx1E79nPKh12HwFZY8MA0GCSqGSIb3DQEBBQUAMIGuMQswCQYDVQQGEwJVUzELMAkG A1UECBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNU IE5ldHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNlcnRydXN0LmNvbTE2MDQGA1UEAxMtVVRO LVVTRVJGaXJzdC1DbGllbnQgQXV0aGVudGljYXRpb24gYW5kIEVtYWlsMB4XDTA3MTEyODAwMDAw MFoXDTA4MTEyNzIzNTk1OVowgeMxNTAzBgNVBAsTLENvbW9kbyBUcnVzdCBOZXR3b3JrIC0gUEVS U09OQSBOT1QgVkFMSURBVEVEMUYwRAYDVQQLEz1UZXJtcyBhbmQgQ29uZGl0aW9ucyBvZiB1c2U6 IGh0dHA6Ly93d3cuY29tb2RvLm5ldC9yZXBvc2l0b3J5MR8wHQYDVQQLExYoYykyMDAzIENvbW9k byBMaW1pdGVkMRcwFQYDVQQDEw5NYXJjdXMgQm9pbnRvbjEoMCYGCSqGSIb3DQEJARYZbWFyY3Vz QHN5bmNocm9tZWRpYS5jby51azCCASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAMfdkFu7 ZFntxqysPCX4ZELsCGET2maGRji8pFDF0+kqR5yGUrI+de7BheAIy0IL7h89UeMSnJfYtnxb901y LBFwIRC0R77i9ShXg4W3UC0hy3IfM0OTUZyjDWpdv3K7lRHDjEQy2iqF3fJSRchxQE6XfQiALWxF foeU3NdTmjfqGcmPOiyv6ngDeEtx2766btbQMWaB8gp/kesA79jBfkip34bK7SE5r7K33iyCKQFB nJkBydGk/MMRMPev3HX879YpBKthhbfvHNrDalpiRf6R78CV6L9DfpcyUvfbTYTf+fIEb3kxP3Q8 nShXggq1scwfWVtfwBQKORmbVO5rlaMCAwEAAaOCAiswggInMB8GA1UdIwQYMBaAFImCZ33EnSZw AEu0UEh83j2uBG59MB0GA1UdDgQWBBRqnns+AZrtY4U86tNs3z+p9BTPYzAOBgNVHQ8BAf8EBAMC BaAwDAYDVR0TAQH/BAIwADAgBgNVHSUEGTAXBggrBgEFBQcDBAYLKwYBBAGyMQEDBQIwEQYJYIZI AYb4QgEBBAQDAgUgMEYGA1UdIAQ/MD0wOwYMKwYBBAGyMQECAQEBMCswKQYIKwYBBQUHAgEWHWh0 dHBzOi8vc2VjdXJlLmNvbW9kby5uZXQvQ1BTMIGlBgNVHR8EgZ0wgZowTKBKoEiGRmh0dHA6Ly9j cmwuY29tb2RvY2EuY29tL1VUTi1VU0VSRmlyc3QtQ2xpZW50QXV0aGVudGljYXRpb25hbmRFbWFp bC5jcmwwSqBIoEaGRGh0dHA6Ly9jcmwuY29tb2RvLm5ldC9VVE4tVVNFUkZpcnN0LUNsaWVudEF1 dGhlbnRpY2F0aW9uYW5kRW1haWwuY3JsMHwGCCsGAQUFBwEBBHAwbjA2BggrBgEFBQcwAoYqaHR0 cDovL2NydC5jb21vZG9jYS5jb20vVVROQUFBQ2xpZW50Q0EuY3J0MDQGCCsGAQUFBzAChihodHRw Oi8vY3J0LmNvbW9kby5uZXQvVVROQUFBQ2xpZW50Q0EuY3J0MCQGA1UdEQQdMBuBGW1hcmN1c0Bz eW5jaHJvbWVkaWEuY28udWswDQYJKoZIhvcNAQEFBQADggEBAITHsTuM4xG7XfdPBrS0Vtqy81ch waexQ8BBWlWY0SA50eELvK+6gk89y5q6/zxOgUlTyXDRpVwwZGt96KBfyxpd20WHVRBsGtYSCTAP Vt0ahdVNIrm2hWzPnAF9Vb8rczhaiLN7dgz43q9XgfOsBbXYL5erkZppsJS/v18etsFzeWkZ4Xkz kZ1JTp7qCRMPswKsPV7JrtToP0eA+Meuhb2NaoZZwZwwoTJnuN845yzLx6TF/qHmUQFVfT6rF9/6 Qv2rjp2ZmQAj18IrhsgFFL/abTK7fTGfwyMa8RJgShpYRLF1doTrbJL300T+upnQ/ceNkGtdDNJW eynQjSNka3cxggP8MIID+AIBATCBwzCBrjELMAkGA1UEBhMCVVMxCzAJBgNVBAgTAlVUMRcwFQYD VQQHEw5TYWx0IExha2UgQ2l0eTEeMBwGA1UEChMVVGhlIFVTRVJUUlVTVCBOZXR3b3JrMSEwHwYD VQQLExhodHRwOi8vd3d3LnVzZXJ0cnVzdC5jb20xNjA0BgNVBAMTLVVUTi1VU0VSRmlyc3QtQ2xp ZW50IEF1dGhlbnRpY2F0aW9uIGFuZCBFbWFpbAIQAY5jsdRO/Zzyoddh8BWWPDAJBgUrDgMCGgUA oIICDTAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw0wNzEyMjAwOTQ5 MzBaMCMGCSqGSIb3DQEJBDEWBBSWAo1NigfgprVb968+IrMpYw8QxDCB1AYJKwYBBAGCNxAEMYHG MIHDMIGuMQswCQYDVQQGEwJVUzELMAkGA1UECBMCVVQxFzAVBgNVBAcTDlNhbHQgTGFrZSBDaXR5 MR4wHAYDVQQKExVUaGUgVVNFUlRSVVNUIE5ldHdvcmsxITAfBgNVBAsTGGh0dHA6Ly93d3cudXNl cnRydXN0LmNvbTE2MDQGA1UEAxMtVVROLVVTRVJGaXJzdC1DbGllbnQgQXV0aGVudGljYXRpb24g YW5kIEVtYWlsAhABjmOx1E79nPKh12HwFZY8MIHWBgsqhkiG9w0BCRACCzGBxqCBwzCBrjELMAkG A1UEBhMCVVMxCzAJBgNVBAgTAlVUMRcwFQYDVQQHEw5TYWx0IExha2UgQ2l0eTEeMBwGA1UEChMV VGhlIFVTRVJUUlVTVCBOZXR3b3JrMSEwHwYDVQQLExhodHRwOi8vd3d3LnVzZXJ0cnVzdC5jb20x NjA0BgNVBAMTLVVUTi1VU0VSRmlyc3QtQ2xpZW50IEF1dGhlbnRpY2F0aW9uIGFuZCBFbWFpbAIQ AY5jsdRO/Zzyoddh8BWWPDANBgkqhkiG9w0BAQEFAASCAQBHWHirWi+IUvUw5PbehTkecN8ZAKdr S61XzFchxRHy9+BktSsHFqP4vhPkEHnJWWrXSqAM8AT08S3a8owbQ5c35/OsqVjmfwnCTWELZjxX t45Mjmq/l7kCogwejakrmLbvouN9f3Ot1M3pvhO6OKdlzabHcixjXmP9U5e62yVqPuwkl7HcABld P6kHU9oKHfP/XzYushBloxrqKYKjPQ5BuFsoeX3CTEze2q6QjcRgC4bTpMKZOk8kaRmsmSBBYS6Y Qdnkjdxf+iscH9UDnr0nx+/p7DNKGTkyq8LBW9USHyUioimJ8lmOEYSUAvWi5XCGZ5saI+ftEx1/ U31K5Wf/AAAAAAAA --Apple-Mail-26-793637957--