Bonjour et d'avance merci de prendre le temps de me lire.

Actuellement je dispose de 2 serveurs mysql ( 4.0.24 je suis encore en Debian Sarge).
Un des 2 serveurs est utilisé actuellement comme slave du premier, et me sert uniquement à avoir un backup de mes bases du master.

Mon master commence à être pas mal sollicité et commence à montrer ses limites, j'envisage donc d'attaquer le slave en lecture afin de soulage mon master. Le ratio écritures/lectures avoisine les 5%.

Je suis donc à la recherche de solutions/d'idées afin de réaliser ceci

- master : lectures et ecritures
- slave : lectures

Les bases ne sont attaquées qu'à partir de script php.

D'avance merci pour toutes astuces, liens, idées, etc ...

Je ne sais pas comment s'organisent tes tables, mais le problème, c'est que quand tu écriras sur le serveur maître, il faudra peut être à chaque fois mettre à jour le serveur esclave.
Tu devrais peut être donc plutôt te tourner vers une solution de "RAID1" appliqué aux serveurs sql, en écrivant sur les 2 serveurs à chaque opération d'écriture (vu qu'il n'y a que 5% d'écritures, ca reste léger), et pour la lecture de faire un random pour choisir le serveur sur lequel lire...
Tu gardes ainsi le backup (si un serveur crashe, les données restent), tout en assurant les performances (par contre c'est vrai que si ya une couille niveau sécurité sur le site, les 2 serveurs peuvent être affectés, donc faut faire des dump réguliers du coup...)

Merci de ta réponse

Je ne sais pas comment s'organisent tes tables, mais le problème, c'est que quand tu écriras sur le serveur maître, il faudra peut être à chaque fois mettre à jour le serveur esclave.

Comme dit mes serveurs fonctionnent en réplication. Donc les données des bases de mes 2 serveurs sont identiques.
Une ecriture dans une table sur mon master est aussitot répliquée sur le slave. Donc de ce coté là pas de probleme.

Aurais tu plus d'infos quand à la selection random du serveur lors de la lecture ? (et uniquement pour les lectures)
C'est justement ca que je cherche à faire !

La chose la plus importante est la suivante : comment exécutes-tu tes requêtes ? Si tu utilises directement mysql_query() alors il va te falloir changer pas mal de trucs dans tes scripts pour utiliser le bon lien pour la bonne requête. (à savoir, les écritures sur le master uniquement, le reste réparti sur les serveurs) Si tu utilises une couche d'abstraction à la base alors c'est plus facile car la sélection du lien se fera à l'intérieur de la couche d'abstraction. Sinon il te faudra modifier pas mal de trucs.

Autre chose, comment s'effectue la connexion à la base ? Un fichier inclus automatiquement ? Les détails sont les bienvenus

Ah, j'allais oublier. Si mes souvenirs sont bons il existe un logiciel qui fait ça automatiquement. En gros au lieu de se connecter à MySQL on se connecte à ce serveur XYZ qui ensuite transmet les requêtes au bon serveur. Toutefois son nom m'échappe pour l'instant. Si quelqu'un voit de quoi je veux parler...

Edit: trouvé, c'est SQL Relay

Salut,

Souvent, on se torture l'esprit pour essayer de faire gagner
un peu de performances aux serveur Mysql en optimisant,
en trouvant des solution de partage de ressources, en changeant
les buffers.

Mais avant tout, ce qui fait gagner beaucoup de performances c'est
l'architecture des tables.
L'optimisation des requêtes.
Le bon choix des types de champs.

Regardes les logs et analyse quelles sont les requêtes les plus fréquentes ?

J'espère que ça pourra t'aider.

Dis moi, peux tu regarder sur phpMyAdmin le nombre de requêtes
qui sont executées par seconde ?

A mon avis, si on en est à utiliser plusieurs serveurs SQL, c'est qu'on a déjà tout optimisé derrière.
Au niveau du random, tu fais un

mt_rand (1,2);

qui va t'indiquer d'utiliser le serveur 1 ou 2, et ceci de manière aléatoire, tu auras donc une probabilité théoriquement égale que le script utilise le serveur 1 ou le 2, suffit juste ensuite de sélectionner la connexion correspondante.

Oui mais non, parce que les requêtes qui modifient les données doivent être envoyées au maître, c'est pour ça que je disais qu'il fallait modifier tous les scripts si on n'utilise pas de couche d'abstraction.

Oui très juste, va soit falloir créer une fonction spéciale pour la lecture, et une pour l'écriture, soit filtrer les requètes selon qu'elles écrivent ou non...

Dans tous les cas, si ca n'est pas déjà fait, il faut créer une classe sql pour gérer tout ca, sinon ca va être le bordel.

Merci pour vos réponses :)

Je vais commencer par répondre aux questions posées:
Actuellement les connexions à la bases s'effectuent à l'aide d'un mysql_query et effectivement un fichier est inclus pour les connexions. S'il faut plus de détails, je peux demander ça aux développeurs, puisque ce n'est pas ma partie, bien que les deux parties se rejoignent sur certains points.

Je ne dispose pas de phpmyadmin sur les serveurs en production, par contre, pour avoir une idée, en 2 semaines et 18h le serveur maitre à traité 289081981 requetes.
On arrive à peu près à 226 requetes/s tout en sachant que la nuit l'activité est très faible, ceci est donc une moyenne sur 2 semaines, vous vous doutez bien qu'il y a de belles pointes.

Je m'étais deja penché du coté de SQLRelay, je n'ai pas plus approfondi, je voulais dans un premier temps voir la methode la plus employée. Mais SQLRelay est au programme de mes tests :)
Il existe d'autres proxy pour mysql, mais ce dernier semble le plus utilisé et le mieux maintenu.

Concernant le tunning de mes serveurs mysql effectivement il a dejà été fait en long et en large, et je pense être au maximum de ce que peuvent m'apporter ces machines. Une upgrade vers une version 5 de mysql serait la bienvenue, mais pour l'instant il n'en est pas question, mais ca sera au programme d'ici à la fin de l'été.

J'en arrive à ma question: avez-vous deja eu l'occasion d'utiliser des classes pour un tel type de fonctionnement ?

Mettre en place une replication bi-directionnelle, et acceder en lecture/ecriture de facon random sur les 2 serveurs ne me tente pas trop, j'ai pu lire pas mal de problemes avec cette methode (des données différentes d'un serveur à l'autre)
Je compte donc rester sur ma premiere idée a savoir:

-Master: Lectures/ecritures
-Slave: Lectures

Encore merci de votre participation à ce thread ;)

Tiens, voilà une idée de ce à quoi ta classe pourrait ressembler:

class psai_db_mysql4
{
	/**
	* @param	array	Connexions ouvertes vers les serveurs
	*/
	private $links = array();

	/**
	* @param	array	Paramètres de configuration
	*/
	private $config;

	public function __destruct()
	{
		foreach ($this->links as $link)
		{
			mysql_close($link);
		}
		$this->links = array();
	}

	public function query($sql)
	{
		$link = (preg_match('#^SELECT#i', $sql)) ? 'slave' : 'master';
		$this->connect($link);
		return psai_result(mysql_query($sql, $this->links[$link]));
	}

	private function connect($link)
	{
		if (isset($this->links[$link]))
		{
			return;
		}

		if (!isset($this->config))
		{
			$this->config = include('/path/to/config.php');
		}

		$this->links[$link] = mysql_connect(
			$this->config[$link]['host'],
			$this->config[$link]['username'],
			$this->config[$link]['password']
		);
		mysql_select_db($this->config[$link]['database'], $this->links[$link]);
	}
}

class psai_db_mysql5
{
	/**
	* @param	array	Connexions ouvertes vers les serveurs
	*/
	private $links = array();

	/**
	* @param	array	Paramètres de configuration
	*/
	private $config;

	public function __destruct()
	{
		foreach ($this->links as $link)
		{
			$link->close();
		}
		$this->links = array();
	}

	public function query($sql)
	{
		$link = (preg_match('#^SELECT#i', $sql)) ? 'slave' : 'master';
		$this->connect($link);
		return $this->links[$link]->query($sql);
	}

	private function connect($link)
	{
		if (isset($this->links[$link]))
		{
			return;
		}

		if (!isset($this->config))
		{
			$this->config = include('/path/to/config.php');
		}

		$this->links[$link] = new mysqli(
			$this->config[$link]['host'],
			$this->config[$link]['username'],
			$this->config[$link]['password'],
			$this->config[$link]['database']
			// ne pas oublier les ports, sockets, etc...
		);
	}
}

class psai_result
{
	private $result;

	public function __construct($result)
	{
		$this->result = $result;
	}

	public function __destruct()
	{
		if (isset($this->result))
		{
			mysql_close($this->result);
			unset($this->result);
		}
	}

	public function close()
	{
		$this->__destruct();
	}

	public function fetch_assoc()
	{
		if ($row = mysql_fetch_assoc($result))
		{
			return $row;
		}

		/**
		* On a fini de traiter les résultats, on libère le buffer
		*/
		$this->__destruct();
		return false;
	}
}

$db = new db;
$result = $db->query('SELECT ...');

while ($row = $result->fetch_assoc())
{
	// ...
}
$result->close();

L'astuce que je recommande c'est de se baser sur MySQLi pour les fonctions et la manière dont les résultats sont retournés. Notamment, dans cet exemple j'utilise une classe "psai_result" destinée à posséder la même signature que mysqli_result. De cette façon, dès que tu passes à MySQL 5 (en imaginant que tu utilises PHP 5 et MySQLi) tu n'as qu'à remplacer la classe d'accès à la base de données et tes scripts ne sont pas affectés (au lieu de recevoir des objets psai_result ils recoivent des mysqli_result, mais leurs signatures étant identiques pas de bobo).

Note : reste à gérer les questions de "affected_rows" et "last_insert_id". Étant donné que ces informations ne sont pertinentes que pour le master tu peux les implémenter sous forme de méthode dans psai_db, ou encore sous forme de propriétés à la manière de mysqli.

Merci de ta réponse et du temps pris pour !

Je vais voir ca avec les developpeurs s'ils ont possibilités d'intégrer ca et de modifier sans trop de mal leur partie.
Je vais également me pencher du coté des proxy SQL, je passerai vous donner le feedback.
Ne soyez pas trop pressé pour le feedback, le temps des tests etc ... on est pas encore au bout du tunnel :)

Mettre en place une replication bi-directionnelle, et acceder en lecture/ecriture de facon random sur les 2 serveurs ne me tente pas trop, j'ai pu lire pas mal de problemes avec cette methode (des données différentes d'un serveur à l'autre)

C'est ce que je te disais, lorsque tu écris, tu dois écrire sur les 2 serveurs, c'est quand tu lis que tu choisis un des 2 serveurs...

J'ai déjà mis deux serveur en réplication mais je trouve ça
dangereux si c'est mal maîtrisé.

Si un des deux serveurs tombes.
Il faut le relancer en lui indiquant où les requêtes se sont
arrêtés lorqu'il est tombé .

On est dans une configuration mâitre esclave. Il faut mieux
ne pas faire de requêtes d'écritures des deux côtés.

Il faut créer un utilisateur de réplication.

Tout est bien expliqué dans la doc mais je crois que pour
gérer plusieurs serveurs ça deviendra vite un casse-tête.

Le nombre de requêtes que tu as communiqué paraît gros.
Tu gères quoi comme site ?

ça dois sûrement pouvoir s'optimiser si les développeurs
ne sont pas trop feignants.

Pour ma part je ne vois rien de dangereux à la replication.
Jusqu'à present je n'ai pas eu de soucis si ce n'est un remplacement de disque (qui atteignait trop d'heures de vol à mon gout) sur le slave.
Etant donné que je conserve plusieurs giga de logs binaire, le slave arrive à ratrapper le retard du master sans trop de peine et en peu de temps.
Je remonte un snapshot et laisse le slave faire son job à partir des logs.

Encore une fois, le pourcentage d'ecriture etant ridicule, il n'est pas question pour moi de "load balancer" ces ecritures, mais uniquement les lectures.
Ecrire sur le master et sur le slave ne m'apporterai que des ennuis, rien en terme de perfs.

Concernant le nombre de requetes il n'y a pas d'erreurs sur le chiffre communiqué, mais je ne peux pas en dire plus quand à la nature du site (mais c'est une utilisation légale celà va de soit).

Quand aux developpeurs ... ils lisent également ce thread (sans intervenir), mais je suis sur qu'ils en prennent bonne note ;)