MySQL Performance: MySQL => Replication : How to load balance queries ?

Members

Help

Home

Home » Performance » MySQL » Replication : How to load balance queries ?

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

Replication : How to load balance queries ? [message #570]

Wed, 10 January 2007 09:18

delirii
Messages: 5
Registered: December 2006

Junior Member

ng One table VAL_FAKTA_VH containing 5.000.000 rows and one table VAL_DIM_AVTAL containing 34 rows. The query takes 15 s using Index and 7 s not using index, how is this possible!!! It is not a question about IO, no IOWAIT but alot of CPU 99% (on machines with single CPU and 49.9 om machines with 2 CPU, MySQL does not seems to utilize both CPU:s)

HW
2*2Ghz CPU
8G RAM

I have used huge-conf with the following add:
join_buffer_size 131072
key_buffer_size 3221225472
tmp_table_size 67108864
read-only
and some more

DB:
VAL_FAKTA_VH.MYD ~ 600M
VAL_FAKTA_VH.MYI ~ 400M
VAL_DIM_AVTAL.MYI ~ 2M
VAL_DIM_AVTAL.MYD ~ 1M

mysql> explain SELECT avtal.avtal, sum(utfallkronor) FROM VAL_FAKTA_VH V force index(Index__avtalid)

Report message to a moderator

Re: Replication : How to load balance queries ? [message #574 is a reply to message #570 ]

Wed, 10 January 2007 13:05

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

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: Replication : How to load balance queries ? [message #579 is a reply to message #574 ]

Thu, 11 January 2007 04:05

delirii
Messages: 5
Registered: December 2006

Junior Member

t_join other questions suffer.
Should not the optimizer se this and do the appropriate thing in this case? Can the optimizer learn from earlier queries?

Background to problem:
We will be creating this kind of questions from our Cognos-platform and are kean not to build these kind of exceptions in to our model. The Cognos environment are to be exposed to 1000 end users and these questions are generated by Cognos. In our model now with SQL Server this is not a problem.

Is there a way

Report message to a moderator

Re: Replication : How to load balance queries ? [message #594 is a reply to message #579 ]

Mon, 15 January 2007 10:07

delirii
Messages: 5
Registered: December 2006

Junior Member

For an unknown reason this subject becomes blank...
Was it off topic ? If so, just tell me.

Anyway, if someone is looking for such a solution, here is mine (at least for now) :

2 data sources : one "read only" and the second one read/write.

- The read only data source uses the MySQL ReplicationDriver, to load balance queries between the slaves

- The R/W DS uses the MySQL fail over feature in the default Driver impl. That way, it's possible to manage a server crash (or anything else) and to redirect write queries to the second master server.

NOTE : this is a master-master circular replication architecture.

Report message to a moderator

Previous Topic:	How to recreate data base from Query Log
Next Topic:	faster not using Index????

Goto Forum:

-=] Back to Top [=-

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

]

Current Time: Sun Jul 5 17:06:30 EDT 2009

Total time taken to generate the page: 0.06711 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.