Exemples pour expliquer l'optimisation de la base de données

La recherche d'optimisation de base de données sur Internet optimise essentiellement à partir du niveau SQL et mentionne rarement l'optimisation d'instance de la base de données elle-même. Même s'il y en a, tout est basé sur l'optimisation d'instance d'une base de données spécifique. Cet article couvre l'optimisation d'instance de toutes les bases de données grand public actuellement sur le marché (Oralce, MySQL, POSTGRES, Dameng). Selon la configuration de l'article, vous pouvez). utilisez 80 % ou plus des performances de votre base de données ci-dessus.

Méthodologie d'optimisation de base de données

Cette partie est une connaissance théorique. Les étudiants qui ne sont pas intéressés peuvent passer directement à la partie configuration des paramètres plus tard. .

Objectifs d'optimisation de la base de données

"tutoriel vidéo mysql" recommandé

Selon différents rôles, L'optimisation de la base de données est divisée selon les objectifs suivants :

Perspective commerciale (utilisateurs clés) :

Réduire le temps de réponse des pages utilisateur

Perspective base de données (développement) :

Réduire le temps de réponse SQL de la base de données

Perspective serveur de base de données (opération Dimension) :

Exploiter pleinement les ressources physiques du serveur de base de données

Réduire l'utilisation du processeur du serveur de base de données

Réduire l'utilisation des E/S du serveur de base de données

Réduire l'utilisation de la mémoire du serveur de base de données

Indicateurs

1 . Le temps de réponse SQL moyen devient plus court

a. Avant optimisation : le temps de réponse moyen de la base de données est de 500 ms

b. 2. Serveur de base de données L'utilisation du processeur diminue

a. Avant l'optimisation : l'utilisation du processeur est de 70 % pendant la période de pointe de la base de données

b. >

3. L'utilisation des IO du serveur de base de données devient faible

a. Avant l'optimisation : l'IO WAIT de la base de données est de 30 %

b. >

Malentendus dans l'optimisation de la base de données

Il peut y avoir les malentendus suivants lors de l'optimisation de la base de données :Avant d'optimiser, vous devez avoir une compréhension approfondie de la base de données. principes internes de la base de données

Il existe des « routines » pour l'optimisation, et vous pouvez également bien terminer l'optimisation de la base de données en suivant ces « routines »

2 L'ajustement continu des paramètres de la base de données peut finalement réaliser l'optimisation

2. 🎜>

Parfois, la conception Peu importe à quel point les paramètres sont ajustés de manière déraisonnable, cela ne fonctionnera pas

3 L'ajustement continu des paramètres du système d'exploitation peut finalement permettre une optimisation

Idem que ci-dessus

.

4. Les performances de la base de données sont déterminées par l'application et l'architecture de la base de données, et cela n'a pas grand-chose à voir avec le développement d'applications

Au contraire, cela a beaucoup à voir avec le développement d'applications

5. Il faut séparer la lecture et l'écriture, et il faut séparer les bases de données et les tableaux

Quantité de données Ce n'est que lorsque le niveau atteint un certain rapport qu'il est nécessaire de séparer la lecture et l'écriture, et de diviser les tableaux dans des bases de données distinctes, sinon cela ne fera qu’augmenter la complexité. D'une manière générale, la taille d'une table unique d'Oracle peut atteindre 100 millions et celle de MySQL peut atteindre 10 millions ~ 20 millions

Processus d'optimisation de la base de données

L'optimisation complète de la base de données Le processus est le suivant :

Tout d'abord, vous devez comprendre le problème d'optimisation autant que possible, collecter les informations système pendant le problème et les archiver. Développer des objectifs d'optimisation basés sur les performances actuelles des problèmes du système et communiquer avec les clients pour parvenir à un accord sur les objectifs ; analyser les problèmes du système grâce à une série d'outils et formuler des plans d'optimisation une fois l'examen du plan terminé, chaque personne responsable le mettra en œuvre. Si l'objectif d'optimisation est atteint, un rapport d'optimisation sera rédigé, sinon le plan d'optimisation devra être reformulé.

Optimisation de l'instance de base de données

L'optimisation de l'instance de base de données suit trois mantras : Le journal ne peut pas être petit, le cache doit être suffisamment grand et la connexion doit être suffisante .

Une fois la transaction de base de données soumise, les modifications apportées à la page de données par la transaction doivent être vidées (fsync) sur le disque pour garantir la durabilité des données. Ce vidage de disque est une écriture aléatoire avec de faibles performances. Si le disque est vidé à chaque fois qu'une transaction est soumise, cela affectera considérablement les performances de la base de données. La base de données adoptera les deux techniques d'optimisation suivantes dans la conception de l'architecture :

a. Écrivez d'abord la transaction dans le fichier journal RedoLog (WAL) et optimisez l'écriture aléatoire en écriture séquentielle

b. Ajoutez-en un La structure de cache de couche Buffer optimise chaque écriture en une écriture séquentielle

, la journalisation et la mise en cache sont donc particulièrement importantes pour les instances de base de données. S'il n'y a pas suffisamment de connexions, la base de données lancera directement une exception et le système sera inaccessible.

Optimisation des paramètres de base de données

Les architectures de bases de données grand public ont toutes les points communs suivants :

Cache de données

Zone d'analyse SQL

Trier la mémoire

REDO et UNDO

Verrouillage, LATCH, MUTEX

Surveillance et connexion

Performances de lecture et d'écriture de fichiers

Ensuite, nous ajustons les paramètres en fonction de différentes bases de données pour obtenir les meilleures performances de la base de données.

ORACLE

参数分类	参数名	参数值	备注
数据缓存	SGA_TAGET、MEMORY_TARGET	物理内存70-80%	越大越好
数据缓存	DB_CACHE_SIZE	物理内存70-80%	越大越好
SQL解析	SHARED_POOL_SIZE	4-16G	不建议设置过大
监听及连接	PROCESSES、SESSIONS、OPEN_CURSORS	根据业务需求设置	一般为业务预估连接数的120%
其他	SESSION_CACHED_CURSORS	大于200	软软解析

MYSQL(INNODB)

参数分类	参数名	参数值	备注
数据缓存	INNODB_BUFFER_POOL_SIZE	物理内存50-80%	一般来说越大性能越好
日志相关	Innodb_log_buffer_size	16-32M	根据运行情况调整
日志相关	sync_binlog	1、100、0	1安全性最好
监听及连接	max_connections	根据业务情况调整	可以预留一部分值
文件读写性能	innodb_flush_log_at_trx_commit	2	安全和性能的折中考虑
其他	wait_timeout，interactive_timeout	28800	避免应用连接定时中断

POSTGRES

参数分类	参数名	参数值	备注
数据缓存	SHARED_BUFFERS	物理内存10-25%
数据缓存	CACHE_BUFFER_SIZE	物理内存50-60%
日志相关	wal_buffer	8-64M	不建议设置过大过小
监听及连接	max_connections	根据业务情况调整	一般为业务预估连接数的120%
其他	maintenance_work_mem	512M或更大
其他	work_mem	8-16M	原始配置1M过小
其他	checkpoint_segments	32或者更大

Base de données Dameng

参数分类	参数名	参数值	备注
数据缓存	MEMROY_TARGET、MEMROY_POOL	物理内存90%
数据缓存	BUFFER	物理内存60%	数据缓存
数据缓存	MAX_BUFFER	物理内存70%	最大数据缓存
监听及连接	max_sessions	根据业务需求设置	一般为业务预估连接数的120%

Résumé

Il existe trop de façons d'optimiser la base de données, notamment le remplacement des baies de disques et la mise à niveau du matériel, la réécriture des scripts SQL pour ajouter des index et l'ajustement des paramètres de la base de données. Optimisez les performances et ajustez même le schéma de la base de données. Cet article optimise les paramètres de la base de données elle-même. En ajustant les paramètres dans les tableaux ci-dessus, vous pouvez essentiellement obtenir 80 % des meilleures performances de la base de données.

Cet article provient du site Web php chinois, colonne tutoriel mysql, bienvenue pour apprendre !

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!