Master - Slave Solution

We encourage users to post events happening in the community to the community events group on https://www.drupal.org.
ay4you's picture

Hi,
Please bear with me if i am creating this question in the wrong place i am new to Drupal groups.
I manage an enterprise solution for 4 drupal sites very high transactional and with high traffic.
i use pressflow on 6 web servers with memcached and a master slave replication, they seem to be fine but i want to reduce the load on the master so i want to make 2 slave part of the solution, since i am using pressflow with supports master-slave replication solution.

My question is how does pressflow know which slave it should use from the array assuming i have 3 slaves, and does it change slave on each query session or how does it work.

Thanks

Comments

You've got the right place.

dalin's picture

You've got the right place. I've only dabbled with sending queries to slaves. Thus far I'm finding that other modes of performance tuning offer more than enough advantages (DB servers on bare metal, slow query log analysis, minimizing write queries, MySQL config tuning, SSDs for MySQL data files and temp files, setup Memcache).

To see how it works just look at the code. I believe a slave query randomly chooses a slave, not sure if it's per query, or per session. I believe there's also some sort of fallback mechanism in place.

Your bigger question is probably how to utilize this. For D6 see here:
http://www.lullabot.com/articles/querying-slave-database-with-views
for D7 I believe this is already built into Views.

--


Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his

Thanks

ay4you's picture

Thanks Dalin,
i have most of the performance tuning already in place my main aim is to reduce database connections, do you know of any other module to reduce connections to the database.

Slaves

nickteagle's picture

Hi I've used this code http://groups.drupal.org/node/27820 (last but one comment) which increased the queries going to the slave I also updated the match statement which helped some more.

preg_match("/\balter\b|\binsert\b|\bupdate\b|\bdrop\b|\bdelete\b|\bflush\b|\block|\bcreate\b|last_insert_id|found_rows|sql_calc_found_rows|row_count/", $query))

Cheer Nick

Didn't Abraham Lincoln

zkrebs's picture

Didn't Abraham Lincoln already provide a solution to this ?

Not really, slavery still

catch's picture

Not really, slavery still exists in quite a few places around the world, and not only computers.

And Lincoln wasn't able to

dalin's picture

And Lincoln wasn't able to abolish slavery from the US either. There's an estimated 20,000 slaves in the US today:
http://news.bbc.co.uk/2/hi/africa/3589646.stm

But we digress.

--


Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his

You aren't the real Zizek.

BrandonJSchwartz's picture

You aren't the real Zizek.

Contact & Websites:
brandon [at] boomajoom.com
My website
Languapedia - Information on Languages & Linguistics

If you're just talking about

catch's picture

If you're just talking about reducing number of connections, have you tried the path alias cache module shipped in Pressflow? That should remove dozens of queries from most pages. There is a node/user caching fork on launchpad as well.

Here are a few resources that

halcyonCorsair's picture

Here are a few resources that should help:

DB Replication with Pressflow 5 and 6
The master should probably also be added to the db_slave_url array, or it will never get picked for those queries...

Patch to make Drupal utilise the slave for more safe database queries.

And mysql replication setup.
Note: That if mysql master gets restarted, you'll have to restart the replication manually.