Problèmes avec les grosses tables

**sami_c** · 29/01/2013, 11h01

Bonjour,
J'ai une application PHP exploitant une bdd mysql avec plusieurs tables, parmi ces tables 2 sont assez grosse : l'une contient 2 500 000 enregistrements et pèse 380 Mo, l'autre contient 1 000 000 enregistrements pour 124 Mo. Seul l'admin de l'application insère les données, les utilisateurs ne lancent que des requêtes d'update de quelques champs uniquement. Le problème c'est qu'environ chaque 1 ou 2 mois j'ai les erreurs du genre :

Warning: mysql_query() [function.mysql-query]: Unable to save result set in /home/web/.../script.php on line X

Je vais donc dans phpmyadmin et je lance une réparation des 2 tables, ça prend quelques minutes et me confirme qu'il y avait des erreurs dans ces 2 tables, et après le script php fonctionne bien !

Le problème est à quel niveau ? du script PHP ? de mysql ? ou du serveur ? Comment je peux savoir et résoudre ce problème une fois pour toute ? Une solution que j'ai trouvé consiste à créer un script php qui lance des requêtes de réparation et que j'appelle via cron chaque dimanche par exemple, mais c'est un peu tiré par les cheveux je trouve !!

Merci d'avance

**CinePhil** · 29/01/2013, 14h32

Pour pouvoir répondre efficacement, il faudrait connaître :
- la structure de la BDD ;
- les caractéristiques techniques du serveur ;
- le paramétrage de MySQL,
- le genre de requête qui plante ;
- le processus qui construit et soumet la requête ;
- le nombre d'utilisateurs connectés en même temps au site ;
- si le serveur de BDD est différend du serveur applicatif ou non ;
- si c'est ou ce sont un (des) serveu(s) dédié(s) ou non...

**sami_c** · 07/02/2013, 14h55

Merci pour la réponse

- la structure de la BDD ;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE IF NOT EXISTS `mails` (
  `id` bigint(20) NOT NULL auto_increment,
  `id_modele` bigint(20) default NULL,
  `mail` varchar(50) NOT NULL default '',
  `ip` varchar(15) default NULL,
  `navigator` varchar(255) default NULL,
  `block` tinyint(4) default '0',
  `n_envoi` tinyint(4) default '0',
  `d_envoi` datetime default NULL,
  `d_lecture` datetime default NULL,
  `n_lecture` tinyint(4) default '0',
  `testeur` varchar(100) default NULL,
  `nom` varchar(255) default NULL,
  `secteur` varchar(255) default NULL,
  `gouvernorat` varchar(255) default NULL,
  `is_adr_perso` tinyint(4) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id_modele_2` (`id_modele`,`mail`),
  KEY `id_modele` (`id_modele`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13650766 ;

- les caractéristiques techniques du serveur ;
C'est un VDS : serveur dédié virtuel (chez SIVIT)
Debian Linux 5.0
Noyau et CPU :Linux 2.6.16.33-xenU sur i686
Mémoire réelle : 512.16 MB total
Espace disque local : 14.42 GB total, 7.80 GB utilisé

- le paramétrage de MySQL,
c-a-d ? j'ai trouvé ceci dans "mysql system variables" de webmin :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
	auto_increment_increment 	1
	auto_increment_offset 	1
	automatic_sp_privileges 	ON
	back_log 	50
	basedir 	/usr/
	binlog_cache_size 	32768
	bulk_insert_buffer_size 	8388608
	character_set_client 	latin1
	character_set_connection 	latin1
	character_set_database 	latin1
	character_set_filesystem 	binary
	character_set_results 	latin1
	character_set_server 	latin1
	character_set_system 	utf8
	character_sets_dir 	/usr/share/mysql/charsets/
	collation_connection 	latin1_swedish_ci
	collation_database 	latin1_swedish_ci
	collation_server 	latin1_swedish_ci
	completion_type 	0
	concurrent_insert 	1
	connect_timeout 	5
	datadir 	/home/mysql-data/
	date_format 	%Y-%m-%d
	datetime_format 	%Y-%m-%d %H:%i:%s
	default_week_format 	0
	delay_key_write 	ON
	delayed_insert_limit 	100
	delayed_insert_timeout 	300
	delayed_queue_size 	1000
	div_precision_increment 	4
	keep_files_on_create 	OFF
	engine_condition_pushdown 	OFF
	expire_logs_days 	10
	flush 	OFF
	flush_time 	0
	ft_boolean_syntax 	+ -><()~*:""&|
	ft_max_word_len 	84
	ft_min_word_len 	4
	ft_query_expansion_limit 	20
	ft_stopword_file 	(built-in)
	group_concat_max_len 	1024
	have_archive 	YES
	have_bdb 	NO
	have_blackhole_engine 	YES
	have_compress 	YES
	have_crypt 	YES
	have_csv 	YES
	have_dynamic_loading 	YES
	have_example_engine 	NO
	have_federated_engine 	YES
	have_geometry 	YES
	have_innodb 	YES
	have_isam 	NO
	have_merge_engine 	YES
	have_ndbcluster 	DISABLED
	have_openssl 	DISABLED
	have_ssl 	DISABLED
	have_query_cache 	YES
	have_raid 	NO
	have_rtree_keys 	YES
	have_symlink 	YES
	hostname 	vdsXX.sivit.org
	init_connect 	
	init_file 	
	init_slave 	
	innodb_additional_mem_pool_size 	1048576
	innodb_autoextend_increment 	8
	innodb_buffer_pool_awe_mem_mb 	0
	innodb_buffer_pool_size 	8388608
	innodb_checksums 	ON
	innodb_commit_concurrency 	0
	innodb_concurrency_tickets 	500
	innodb_data_file_path 	ibdata1:10M:autoextend
	innodb_data_home_dir 	
	innodb_doublewrite 	ON
	innodb_fast_shutdown 	1
	innodb_file_io_threads 	4
	innodb_file_per_table 	OFF
	innodb_flush_log_at_trx_commit 	1
	innodb_flush_method 	
	innodb_force_recovery 	0
	innodb_lock_wait_timeout 	50
	innodb_locks_unsafe_for_binlog 	OFF
	innodb_log_arch_dir 	
	innodb_log_archive 	OFF
	innodb_log_buffer_size 	1048576
	innodb_log_file_size 	5242880
	innodb_log_files_in_group 	2
	innodb_log_group_home_dir 	./
	innodb_max_dirty_pages_pct 	90
	innodb_max_purge_lag 	0
	innodb_mirrored_log_groups 	1
	innodb_open_files 	300
	innodb_rollback_on_timeout 	OFF
	innodb_support_xa 	ON
	innodb_sync_spin_loops 	20
	innodb_table_locks 	ON
	innodb_thread_concurrency 	8
	innodb_thread_sleep_delay 	10000
	interactive_timeout 	28800
	join_buffer_size 	131072
	key_buffer_size 	16777216
	key_cache_age_threshold 	300
	key_cache_block_size 	1024
	key_cache_division_limit 	100
	language 	/usr/share/mysql/french/
	large_files_support 	ON
	large_page_size 	0
	large_pages 	OFF
	lc_time_names 	en_US
	license 	GPL
	local_infile 	ON
	locked_in_memory 	OFF
	log 	OFF
	log_bin 	ON
	log_bin_trust_function_creators 	OFF
	log_error 	
	log_queries_not_using_indexes 	OFF
	log_slave_updates 	OFF
	log_slow_queries 	OFF
	log_warnings 	1
	long_query_time 	4
	low_priority_updates 	OFF
	lower_case_file_system 	OFF
	lower_case_table_names 	0
	max_allowed_packet 	16776192
	max_binlog_cache_size 	4294967295
	max_binlog_size 	104857600
	max_connect_errors 	10
	max_connections 	100
	max_delayed_threads 	20
	max_error_count 	64
	max_heap_table_size 	16777216
	max_insert_delayed_threads 	20
	max_join_size 	18446744073709551615
	max_length_for_sort_data 	1024
	max_prepared_stmt_count 	16382
	max_relay_log_size 	0
	max_seeks_for_key 	4294967295
	max_sort_length 	1024
	max_sp_recursion_depth 	0
	max_tmp_tables 	32
	max_user_connections 	0
	max_write_lock_count 	4294967295
	multi_range_count 	256
	myisam_data_pointer_size 	6
	myisam_max_sort_file_size 	2147483647
	myisam_recover_options 	OFF
	myisam_repair_threads 	1
	myisam_sort_buffer_size 	8388608
	myisam_stats_method 	nulls_unequal
	ndb_autoincrement_prefetch_sz 	32
	ndb_force_send 	ON
	ndb_use_exact_count 	ON
	ndb_use_transactions 	ON
	ndb_cache_check_time 	0
	ndb_connectstring 	
	net_buffer_length 	16384
	net_read_timeout 	30
	net_retry_count 	10
	net_write_timeout 	60
	new 	OFF
	old_passwords 	ON
	open_files_limit 	1024
	optimizer_prune_level 	1
	optimizer_search_depth 	62
	pid_file 	/var/run/mysqld/mysqld.pid
	port 	3306
	preload_buffer_size 	32768
	profiling 	OFF
	profiling_history_size 	15
	protocol_version 	10
	query_alloc_block_size 	8192
	query_cache_limit 	1048576
	query_cache_min_res_unit 	4096
	query_cache_size 	16777216
	query_cache_type 	ON
	query_cache_wlock_invalidate 	OFF
	query_prealloc_size 	8192
	range_alloc_block_size 	2048
	read_buffer_size 	131072
	read_only 	OFF
	read_rnd_buffer_size 	262144
	relay_log_purge 	ON
	relay_log_space_limit 	0
	rpl_recovery_rank 	0
	secure_auth 	OFF
	secure_file_priv 	
	server_id 	1
	skip_external_locking 	ON
	skip_networking 	OFF
	skip_show_database 	OFF
	slave_compressed_protocol 	OFF
	slave_load_tmpdir 	/tmp/
	slave_net_timeout 	3600
	slave_skip_errors 	OFF
	slave_transaction_retries 	10
	slow_launch_time 	2
	socket 	/var/run/mysqld/mysqld.sock
	sort_buffer_size 	2097144
	sql_big_selects 	ON
	sql_mode 	
	sql_notes 	ON
	sql_warnings 	OFF
	ssl_ca 	
	ssl_capath 	
	ssl_cert 	
	ssl_cipher 	
	ssl_key 	
	storage_engine 	MyISAM
	sync_binlog 	0
	sync_frm 	ON
	system_time_zone 	CET
	table_cache 	64
	table_lock_wait_timeout 	50
	table_type 	MyISAM
	thread_cache_size 	8
	thread_stack 	131072
	time_format 	%H:%i:%s
	time_zone 	SYSTEM
	timed_mutexes 	OFF
	tmp_table_size 	33554432
	tmpdir 	/tmp
	transaction_alloc_block_size 	8192
	transaction_prealloc_size 	4096
	tx_isolation 	REPEATABLE-READ
	updatable_views_with_limit 	YES
	version 	5.0.51a-24+lenny1-log
	version_comment 	(Debian)
	version_compile_machine 	i486
	version_compile_os 	debian-linux-gnu
	wait_timeout 	28800

- le genre de requête qui plante ;
qui plante ou qui fait planter ? n'importe quelle requête ne fonctionne plus après le problème évoqué, mais je pense que ce sont les updates qui génères les erreurs. Le principe de l'app est le suivant : j'importe les milliers d'adresses mail une fois pour toute et je lance l'envoi, ce dernier fait un update pour chaque enregistrement. Chaque destinataire qui li le mail lance un update.

- le processus qui construit et soumet la requête ;
c-a-d ? les requêtes sont toutes exécutées par un script PHP

- le nombre d'utilisateurs connectés en même temps au site ;
je ne sais pas, mais je ne pense pas que c'est nombreux, il peut y avoir un pic mais tt le monde ne consulte pas leur mails en même temps

- si le serveur de BDD est différend du serveur applicatif ou non ;
tout est sur le même serveur

- si c'est ou ce sont un (des) serveu(s) dédié(s) ou non...
dédié virtuel

PS : la même appli sur un autre serveur dédié virtuel OVH génère parfois les même problèmes.

**CinePhil** · 07/02/2013, 15h37

parmi ces tables 2 sont assez grosse : l'une contient 2 500 000 enregistrements et pèse 380 Mo, l'autre contient 1 000 000 enregistrements pour 124 Mo.

Soit déjà 504 Mo.

Mémoire réelle : 512.16 MB total

Donc quand des requêtes doivent lire les tables entièrement, ça swape sur le disque, ce qui est très lent et peu provoquer un dépassement de temps de requête PHP ou autres soucis du même genre.

512 Mo pour un serveur de BDD, ce n'est pas assez car un SGBD travaille en mémoire pour accélérer ses traitements.

Je ne suis pas très calé en réglage des paramètres de MySQL mais vu la taille de certaines tables, il y a probablement des paramètres numériques à revoir (d'une manière générale, ceux terminant par _size sont à examiner de plus près). Tu trouveras sûrement de la doc sur le sujet.
Voir aussi les paramètres de PHP.

En dehors de ça, ton processus ressemble à du spam publicitaire et qu'il tombe en panne ne me dérange pas du tout !

**sami_c** · 12/02/2013, 15h29

Envoyé par CinePhil

Soit déjà 504 Mo.
Donc quand des requêtes doivent lire les tables entièrement, ça swape sur le disque, ce qui est très lent et peu provoquer un dépassement de temps de requête PHP ou autres soucis du même genre.

Il n'y pas de requête qui lise et traite tous les enregistrement en même temps ! L'extraction se fait par tranche de X enregistrement à la fois, ensuite le script se recharge et traire une autre tranche ...
Pour les mise à jours, vu que tout le monde ne consulte pas ses mails en même temps, on ne peut pas avoir un update de tous les enregistrements !! Donc la je me dis que peut être le fait d'avoir plusieurs update, même espacés de qq secondes, soit la cause du problème !

Envoyé par CinePhil

En dehors de ça, ton processus ressemble à du spam publicitaire et qu'il tombe en panne ne me dérange pas du tout !

Je fourni la plateforme uniquement, mes clients gèrent eux même la liste de leur prospects qui devrait normalement respecter les règles du CNIL !

**CinePhil** · 12/02/2013, 16h07

Envoyé par sami_c

Il n'y pas de requête qui lise et traite tous les enregistrement en même temps ! L'extraction se fait par tranche de X enregistrement à la fois, ensuite le script se recharge et traire une autre tranche ...
Pour les mise à jours, vu que tout le monde ne consulte pas ses mails en même temps, on ne peut pas avoir un update de tous les enregistrements !! Donc la je me dis que peut être le fait d'avoir plusieurs update, même espacés de qq secondes, soit la cause du problème !!

Ce n'est pas parce que ton processus applicatif ne demande pas toutes les lignes d'une table en même temps que le SGBD n'a pas besoin de lire la table entièrement pour pouvoir en extraire les données souhaitées !
Par exemple, si aucun index ne peut être utilisé par la requête, la table sera parcourue entièrement pour être sûr de ne pas oublier de lignes, même si le résultat de la requête ne donne que quelques pourcents du nombre total de lignes.
Ça peut être le cas lorsqu'il y a une clause de restriction avec LIKE '%dutexte%' ou une fonction de transformation ou d'extraction comme WHERE EXTRACT(MONTH FROM la_colonne_de_type_date) = 7.

Les INSERT ou les UPDATE réorganisent les index et je crois qu'avec le mauvais MySQL, ça peut bloquer la table et ne pas rendre la main.
Il peut être plus efficace de désactiver les index, d'insérer ou de mettre à jour en masse puis de reconstruire les index.

Quant à la structure de ta table, tu devrais la revoir pour l'alléger en externalisant des données redondantes telles que navigator, testeur, gouvernorat qui n'ont probablement qu'un nombre limité de valeur possibles.
Il est plus économique de stocker une seule fois 'Internet Explorer' dans une table des navigateurs et de mettre une clé étrangère de type TINYINT dans la grosse table.

**sami_c** · 14/02/2013, 12h40

merci pour les conseils

**sami_c** · 14/02/2013, 12h41

question : vu le grand nombre d'enregistrement, est-ce qu'une bdd NoSQL serait plus appropriée ? quels sont les plus d'une telle bdd ?

**CinePhil** · 14/02/2013, 14h49

Je ne connais pas noSQL et le peu que j'en ai lu ne me donne pas envie d'en connaître davantage !

Les SGBD sont fait pour traiter de gros volumes de données.
J'ai personnellement fait des jointures entre des tables de plusieurs dizaines de millions de lignes avec des temps de réponse tout à fait corrects sur une machine assez basique.
Mais j'avais construit un modèle de données normalisé, choisi les types et tailles des colonnes avec soin, placé les index qu'il fallait...

**ericd69** · 14/02/2013, 17h24

salut,

un serveur bien configuré, des tables bien indexées et normalisées, comme le dit cinephil, voir l'utilisation du partitionnement pour certaines tables dont la taille est vraiment grande et/ou susceptibles de grandir énormément mais où les requêtes ne ciblent qu'une quantité limitée de lignes, peuvent très bien suffire à ton besoin...
le réglage de base de mysql est pensé pour des petites ou moyennes applications...

le nosql en fait existe depuis bien plus longtemps que les sgbd... l'idée est de brasser des collections contenant des données... pas vraiment de tables puisque que chaque élément (l'équivalent d'une ligne dans une table) n'a pas à avoir une structure déterminée et attendue... tu peux avoir plusieurs niveaux de collections... il n'y a pas de notion de jointure, clé primaire ou étrangère, etc...

la force est donc de permettre une recherche très efficace à travers un très gros ensemble de données mais tu es limité à ça en gros et après c'est à l'application de se débrouiller à faire des recoupement éventuels...

en gros un sgbd permet de brasser des données normalisées de manière très efficace sur des jeux de données de l'ordre de plusieurs dizaines de millions de lignes dans des temps acceptable (certains comme oracle y arrive en des temps hyper courts sur des architectures serveur dédiées hyper chères)... alors que le nosql ne sait faire que peu d'opération de recherche mais sur des jeux de données peu structuré et pouvant être beaucoup plus gros que ceux gérable par les sgbd...

c'est pas du tout la même approche... y a un forum dédié à nosql ici va y poser des questions si tu veux...

**sami_c** · 21/02/2013, 09h34

merci pour les explications

Problèmes avec les grosses tables

Requêtes MySQL

Discussions similaires

Partager

Partager