Table pleine à 4 Go Mysql innodb windows 10 NTFS

Version imprimable

Bonjour,

J'obtiens une erreur "table pleine" à hauteur de 4 Go. J'utilise MySQL 5.7, configuré avec innodb, et innodb_file_per_table = on.
D'après ce que je lis je devrais pouvoir obtenir une table de plusieurs terra, mais ça plante à 4 Go.
Je suis sur un disque de 2T en NTFS, avec 1,5T disponible...

Si vous avez une idée, je suis preneur.
Merci d'avance,

JP

Voici la config innodb:

Code:

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
+------------------------------------------+------------------------+
| Variable_name                            | Value                  |
+------------------------------------------+------------------------+
| innodb_adaptive_flushing                 | ON                     |
| innodb_adaptive_flushing_lwm             | 10                     |
| innodb_adaptive_hash_index               | ON                     |
| innodb_adaptive_hash_index_parts         | 8                      |
| innodb_adaptive_max_sleep_delay          | 150000                 |
| innodb_api_bk_commit_interval            | 5                      |
| innodb_api_disable_rowlock               | OFF                    |
| innodb_api_enable_binlog                 | OFF                    |
| innodb_api_enable_mdl                    | OFF                    |
| innodb_api_trx_level                     | 0                      |
| innodb_autoextend_increment              | 64                     |
| innodb_autoinc_lock_mode                 | 1                      |
| innodb_buffer_pool_chunk_size            | 134217728              |
| innodb_buffer_pool_dump_at_shutdown      | ON                     |
| innodb_buffer_pool_dump_now              | OFF                    |
| innodb_buffer_pool_dump_pct              | 25                     |
| innodb_buffer_pool_filename              | ib_buffer_pool         |
| innodb_buffer_pool_instances             | 1                      |
| innodb_buffer_pool_load_abort            | OFF                    |
| innodb_buffer_pool_load_at_startup       | ON                     |
| innodb_buffer_pool_load_now              | OFF                    |
| innodb_buffer_pool_size                  | 134217728              |
| innodb_change_buffer_max_size            | 25                     |
| innodb_change_buffering                  | all                    |
| innodb_checksum_algorithm                | crc32                  |
| innodb_checksums                         | ON                     |
| innodb_cmp_per_index_enabled             | OFF                    |
| innodb_commit_concurrency                | 0                      |
| innodb_compression_failure_threshold_pct | 5                      |
| innodb_compression_level                 | 6                      |
| innodb_compression_pad_pct_max           | 50                     |
| innodb_concurrency_tickets               | 5000                   |
| innodb_data_file_path                    | ibdata1:12M:autoextend |
| innodb_data_home_dir                     |                        |
| innodb_deadlock_detect                   | ON                     |
| innodb_default_row_format                | dynamic                |
| innodb_disable_sort_file_cache           | OFF                    |
| innodb_doublewrite                       | ON                     |
| innodb_fast_shutdown                     | 1                      |
| innodb_file_format                       | Barracuda              |
| innodb_file_format_check                 | ON                     |
| innodb_file_format_max                   | Barracuda              |
| innodb_file_per_table                    | ON                     |
| innodb_fill_factor                       | 100                    |
| innodb_flush_log_at_timeout              | 1                      |
| innodb_flush_log_at_trx_commit           | 1                      |
| innodb_flush_method                      |                        |
| innodb_flush_neighbors                   | 1                      |
| innodb_flush_sync                        | ON                     |
| innodb_flushing_avg_loops                | 30                     |
| innodb_force_load_corrupted              | OFF                    |
| innodb_force_recovery                    | 0                      |
| innodb_ft_aux_table                      |                        |
| innodb_ft_cache_size                     | 8000000                |
| innodb_ft_enable_diag_print              | OFF                    |
| innodb_ft_enable_stopword                | ON                     |
| innodb_ft_max_token_size                 | 84                     |
| innodb_ft_min_token_size                 | 3                      |
| innodb_ft_num_word_optimize              | 2000                   |
| innodb_ft_result_cache_limit             | 2000000000             |
| innodb_ft_server_stopword_table          |                        |
| innodb_ft_sort_pll_degree                | 2                      |
| innodb_ft_total_cache_size               | 640000000              |
| innodb_ft_user_stopword_table            |                        |
| innodb_io_capacity                       | 200                    |
| innodb_io_capacity_max                   | 2000                   |
| innodb_large_prefix                      | ON                     |
| innodb_lock_wait_timeout                 | 50                     |
| innodb_locks_unsafe_for_binlog           | OFF                    |
| innodb_log_buffer_size                   | 262144000              |
| innodb_log_checksums                     | ON                     |
| innodb_log_compressed_pages              | ON                     |
| innodb_log_file_size                     | 1048576000             |
| innodb_log_files_in_group                | 2                      |
| innodb_log_group_home_dir                | .\                     |
| innodb_log_write_ahead_size              | 8192                   |
| innodb_lru_scan_depth                    | 1024                   |
| innodb_max_dirty_pages_pct               | 75.000000              |
| innodb_max_dirty_pages_pct_lwm           | 0.000000               |
| innodb_max_purge_lag                     | 0                      |
| innodb_max_purge_lag_delay               | 0                      |
| innodb_max_undo_log_size                 | 1073741824             |
| innodb_monitor_disable                   |                        |
| innodb_monitor_enable                    |                        |
| innodb_monitor_reset                     |                        |
| innodb_monitor_reset_all                 |                        |
| innodb_old_blocks_pct                    | 37                     |
| innodb_old_blocks_time                   | 1000                   |
| innodb_online_alter_log_max_size         | 134217728              |
| innodb_open_files                        | 2000                   |
| innodb_optimize_fulltext_only            | OFF                    |
| innodb_page_cleaners                     | 1                      |
| innodb_page_size                         | 16384                  |
| innodb_print_all_deadlocks               | OFF                    |
| innodb_purge_batch_size                  | 300                    |
| innodb_purge_rseg_truncate_frequency     | 128                    |
| innodb_purge_threads                     | 4                      |
| innodb_random_read_ahead                 | OFF                    |
| innodb_read_ahead_threshold              | 56                     |
| innodb_read_io_threads                   | 4                      |
| innodb_read_only                         | OFF                    |
| innodb_replication_delay                 | 0                      |
| innodb_rollback_on_timeout               | OFF                    |
| innodb_rollback_segments                 | 128                    |
| innodb_sort_buffer_size                  | 1048576                |
| innodb_spin_wait_delay                   | 6                      |
| innodb_stats_auto_recalc                 | ON                     |
| innodb_stats_include_delete_marked       | OFF                    |
| innodb_stats_method                      | nulls_equal            |
| innodb_stats_on_metadata                 | OFF                    |
| innodb_stats_persistent                  | ON                     |
| innodb_stats_persistent_sample_pages     | 20                     |
| innodb_stats_sample_pages                | 8                      |
| innodb_stats_transient_sample_pages      | 8                      |
| innodb_status_output                     | OFF                    |
| innodb_status_output_locks               | OFF                    |
| innodb_strict_mode                       | ON                     |
| innodb_support_xa                        | ON                     |
| innodb_sync_array_size                   | 1                      |
| innodb_sync_spin_loops                   | 30                     |
| innodb_table_locks                       | ON                     |
| innodb_temp_data_file_path               | ibtmp1:12M:autoextend  |
| innodb_thread_concurrency                | 9                      |
| innodb_thread_sleep_delay                | 0                      |
| innodb_tmpdir                            |                        |
| innodb_undo_directory                    | .\                     |
| innodb_undo_log_truncate                 | OFF                    |
| innodb_undo_logs                         | 128                    |
| innodb_undo_tablespaces                  | 0                      |
| innodb_use_native_aio                    | ON                     |
| innodb_version                           | 5.7.19                 |
| innodb_write_io_threads                  | 4                      |
+------------------------------------------+------------------------+

15/10/2017, 17h36
Artemus24

Salut à tous.

Vous avez indiquez :

Code:

innodb_page_size | 16384

C'est la taille standard (16Kb) d'une page sous MySql.
Cela correspond à une volumétrie théorique de 64 Tb (tera byte).
Donc a priori, le problème ne vient pas de là.

Si vous êtes limité à 4 Gb (giga byte), je me demande si votre identifiant servant de clef primaire n'aurait pas été déclaré ainsi :

Code:

id integer unsigned auto_increment not null primary key,

Son amplitude va de 1 jusqu'à 4.294.967.295, soit 4Gb.

Dans ce cas là, modifiez votre clef primaire en lui attribuant comme type : "bigint".

@+
16/10/2017, 09h51
jpcubain

J'ai essayé

Hélas non, ça n'a pas fonctionné non plus... Même plantage au même moment. Merci quand même.

Salut jpcubain.

Reprenons.

1) vous devez faire une sauvegarde de votre base de données, en utilisant export de phpmyadmin.
C'est une sécurité en cas de fausse manipulation. Faites aussi le test de réinstallation, en changeant le nom de votre base de données.
Si cette étape est faite, on peut alors bidouiller dans votre base de données.

2) quel est la version exacte de votre mysql ?

3) êtes-vous sous windows ou sous linux ?

4) quel est l'exacte volumétrie de votre base de données ?
Veuillez exécuter ce script mysql :
Code:

1 2 3 4 5 6 7 8 9 10 use mysql; SELECT TABLE_NAME, DATA_LENGTH, INDEX_LENGTH, DATA_FREE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'nom_de_votre_base_de_données'; exit
5) quel est le descriptif de votre table ?
A exécuter aussi :

Code:

describe nom_de_la_base_de_données.nom_de_la_table

6) ainsi que les index :

Code:

show index from nom_de_la_table from nom_de_la_base_de_données

7) le plus important ! quel est le message d'erreur exacte que vous rencontrez ?
Et surtout dans quelle circonstance vous obtenez de message ?

8) on peut compresser les lignes de vos tables. Il faut pour cela recréer vos tables avec ce paramétre :
Code:

1 2 3 4 5 CREATE TABLE `test` (... ) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci` ROW_FORMAT=COMPRESSED;
Puis ensuite vous insérez vos lignes. D'où l'intérêt d'avoir une sauvegarde.

En gros, donnez moi un maximum d'information sur votre problème.

@+

Bonsoir,

En fait, je suis en train de migrer d'un système un peu ... ancien pour du plus moderne. Je sais, ça va faire rigoler, mais j'étais en Delphi 5 avec des tables Paradox... Mais là, en passant en Windows 10, ça commence à coincer vraiment. :)
Donc, je passe en Windows 10, Delphi 2010 Tokyo, et je migre les données sous MySQL 5.7.19.0 (la plus récente à priori). En gros, j'ai la base établissements Insee, avec de l'historique, ce qui fait plus de 15 millions d'enregistrements. Sous Paradox, j'avais divisé tout ça par département, soit une centaine de "petits" fichiers. C'est en faisant la migration vers une seule table en MySQL que ça plante lorsque la table arrive 4 194 304 Ko précisément, soit 5 702 620 lignes.
Par contre, pas de problème si je recrée mes 100 fichiers, mais je préfèrerais éviter.

J'ai tenté une autre table avec un autre schéma, et j'ai eu le même plantage à 4 194 304 Ko... ça ne doit pas être un hasard :).

ça se comporte comme si on atteignait la limite de taille de fichier de la FAT32, alors que je suis en NTFS.
Je me posais la question si il n'y avait pas un paramètre dans Windows permettant de "brider" la taille des fichiers malgré NTFS. (je suis dans une grande entreprise, on reçoit une image qu'on installe sur les micros, mais il n'y a pas moyen de savoir ce qui est paramétré dedans)...

Le schéma de la table:

Code:

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
CREATE TABLE INSEE (
 `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, 
 `Siren` VarChar(9),
 `Nic` VarChar(5),
 `ind` VarChar(1),
 `Actif` VarChar(1),
 `Siret` VarChar(14),
 `new_siren` VarChar(9),
 `new_nic` VarChar(5),
 `Dpt` VarChar(2),
 `Com` VarChar(3),
 `RS` VarChar(60),
 `RS2` VarChar(60),
 `Sigle` VarChar(30),
 `Enseigne` VarChar(60),
 `cpost` VarChar(5),
 `ville` VarChar(35),
 `NomVoie` VarChar(60),
 `LibVoie` VarChar(60),
 `Rivo` VarChar(4),
 `numvoie` Decimal,
 `numvoie2` Decimal,
 `numcomp` VarChar(4),
 `TypeVoie` VarChar(4),
 `cmpladr` VarChar(60),
 `siege` VarChar(2),
 `eff` VarChar(2),
 `nafRev2` VarChar(5),
 `Rgn` VarChar(2),
 `ardt` VarChar(1),
 `Can` VarChar(2),
 `popul` VarChar(2),
 `natetb` VarChar(2),
 `motif` VarChar(1),
 `tel_ins` VarChar(10),
 `libdist` VarChar(26),
 `BP` VarChar(10),
 `dateEve` VarChar(8),
 `Eve` VarChar(2),
 `d_cre_ins` VarChar(8),
 `d_cre_rxf` VarChar(8),
 `d_der_maj` VarChar(8),
 `d_Fin_Ent` VarChar(8),
 `d_Fin_Etb` VarChar(8),
 `TypeTran` VarChar(1),
 `Source_File` VarChar(30),
 `Source_Date` VarChar(8),
 `SOMAJ` VarChar(13),
 `cpost2` VarChar(5),
 `cedex` VarChar(35),
 `Duns` VarChar(9),
 `US_SIC` VarChar(4),
 `DUNS_HQ` VarChar(9),
 `DUNS_Ultimate` VarChar(9),
 `DUNS_Global` VarChar(9),
 `D_Duns_Upd` VarChar(8),
 `kw1` VarChar(15),
 `kw2` VarChar(15),
 `kw3` VarChar(15),
 `rivo2` VarChar(4),
 `user_id` VarChar(8),
 `country` VarChar(70),
 `eff_estab` VarChar(7),
 `cd_expl_estb` VarChar(1),
 `dpt_unit_urb_estb` VarChar(2),
 `taille_unit_urb_estb` VarChar(1),
 `num_unit_urb_estb` VarChar(5),
 `zone_emploi` VarChar(2),
 `cd_act_estab` VarChar(2),
 `cd_lieu_act_estab` VarChar(2),
 `cd_surf_estab` VarChar(1),
 `cd_mod_act_estab` VarChar(1),
 `cd_part_prod_estab` VarChar(1),
 `cd_civilite` VarChar(1),
 `cd_act_RM` VarChar(1),
 `recme` VarChar(1),
 `cd_eff_estab_cent` VarChar(10),
 `d_maj_eff` VarChar(8),
 `TypeVoieLong` VarChar(16),
 `LibelleCommune` VarChar(32),
 `cp3` VarChar(29),
 `cplAdr2` VarChar(50),
 `terr` VarChar(4),
 `Gmkt` VarChar(6),
 `GmktFut` VarChar(6),
 `RSO` VarChar(3),
 `Cd_Couv` VarChar(2),
 `d_calc_seg` VarChar(8),
 `d_deb_seg` VarChar(8),
 `d_cre_record` VarChar(8),
 `cus_acc_seg_code` VarChar(1),
 `cus_acc_sub_cla_code` VarChar(2),
 `cus_acc_sub_seg_code` VarChar(1),
 `cus_ind_sec` VarChar(2),
 `cus_ind_sub_sec` VarChar(3),
 `cus_ind_seg_code` VarChar(2),
 `OTT` VarChar(8),
 `RT` VarChar(8),
 `RSO_fut` VarChar(3),
 `Cd_Couv_Fut` VarChar(2),
 `cus_acc_seg_code_fut` VarChar(1),
 `cus_acc_sub_cla_code_fut` VarChar(2),
 `cus_acc_sub_seg_code_fut` VarChar(1),
 `cus_ind_sec_fut` VarChar(2),
 `cus_ind_sub_sec_fut` VarChar(3),
 `cus_ind_seg_code_fut` VarChar(2),
 `OTT_fut` VarChar(8),
 `RT_fut` VarChar(8),
 `d_app_fut` VarChar(8),
 `cd_cause` VarChar(2),
 `CmplAdr2` VarChar(60),
 `CmplAdr3` VarChar(60),
 `Batiment` VarChar(60),
 `Etage` VarChar(60),
 `Bureau` VarChar(60),
 `NoShipTo` VarChar(1),
 `Tbma2205` VarChar(1),
 `Tbma2206` VarChar(1),
 `Tbma2207` VarChar(1),
 `Tbma2208` VarChar(1),
 `dpt_post` VarChar(2),
 `Dpt_Adm` VarChar(2),
 `Tbma2205_Fut` VarChar(1),
 `Tbma2206_Fut` VarChar(1),
 `Tbma2207_Fut` VarChar(1),
 `Tbma2208_Fut` VarChar(1),
 `d_synch_ref` VarChar(8),
 `controle` VarChar(3),
 `qrt` VarChar(2),
 `Duns_Ultimate_Fut` VarChar(9),
 `Duns_Global_fut` VarChar(9),
 `Rs_sap` VarChar(37),
 `RS_Sfdc` VarChar(80),
 `DerAlpha` VarChar(8),
 PRIMARY KEY (id), 
 INDEX `SirenNic` (Siren,Nic),
 INDEX `DptComSiret` (Dpt,Com,Siren,Nic),
 INDEX `GmktSiret` (Gmkt,Siren,Nic),
 INDEX `GmktFutSiret` (GmktFut,Siren,Nic)
 );

la base:

+--------------+-------------+--------------+-----------+
| TABLE_NAME   | DATA_LENGTH | INDEX_LENGTH | DATA_FREE |
+--------------+-------------+--------------+-----------+
| bp_org       |   157089792 |     14204928 |   7340032 |
| gmkt_attribs |   131858432 |     18399232 |   7340032 |
| insee        |  2626682880 |   1216315392 |   4194304 |
| insee01      |    65683456 |     22085632 |   4194304 |
| insee02      |    52035584 |     16842752 |   7340032 |
| insee03      |    40484864 |     15794176 |   7340032 |
| insee04      |    31014912 |     12648448 |   7340032 |
| insee05      |    32079872 |     12648448 |   6291456 |
| insee06      |   220119040 |     64192512 |   7340032 |
| insee07      |    44679168 |     16842752 |   6291456 |
| insee08      |    29966336 |     11599872 |   5242880 |
| insee09      |    24723456 |      8454144 |   5242880 |
| insee10      |    38371328 |     14745600 |   6291456 |
| insee11      |    64634880 |     22085632 |   6291456 |
| insee12      |    44679168 |     15794176 |   7340032 |
| insee13      |   315621376 |     90505216 |   6291456 |
| insee14      |    89849856 |     29458432 |   6291456 |
| insee15      |    20512768 |      7405568 |   7340032 |
| insee16      |    45727744 |     15794176 |   6291456 |
| insee17      |    99303424 |     31555584 |   7340032 |
| insee18      |    35225600 |     12648448 |   7340032 |
| insee19      |    33128448 |     12648448 |   7340032 |
| insee20      |    58343424 |     17891328 |   4194304 |
| insee21      |    69894144 |     24182784 |   6291456 |
| insee22      |    73039872 |     26312704 |   5242880 |
| insee23      |    17367040 |      7405568 |   4194304 |
| insee24      |    62537728 |     22085632 |   4194304 |
| insee25      |    61489152 |     21037056 |   6291456 |
| insee26      |    77250560 |     26312704 |   5242880 |
| insee27      |    62537728 |     22085632 |   4194304 |
| insee28      |    46776320 |     15794176 |   5242880 |
| insee29      |    98254848 |     31555584 |   4194304 |
| insee30      |   101416960 |     30507008 |   6291456 |
| insee31      |   180207616 |     55803904 |   7340032 |
| insee32      |    32079872 |     12648448 |   4194304 |
| insee33      |   220119040 |     68386816 |   7340032 |
| insee34      |   193855488 |     58949632 |   6291456 |
| insee35      |   124518400 |     37847040 |   5242880 |
| insee36      |    26820608 |     10551296 |   5242880 |
| insee37      |    70942720 |     24182784 |   4194304 |
| insee38      |   152879104 |     50528256 |   7340032 |
| insee39      |    33128448 |     12648448 |   5242880 |
| insee40      |    62537728 |     21037056 |   7340032 |
| insee41      |    37322752 |     12648448 |   4194304 |
| insee42      |    91947008 |     29458432 |   4194304 |
| insee43      |    29966336 |     12648448 |   4194304 |
| insee44      |   153944064 |     49463296 |   5242880 |
| insee45      |    70942720 |     25231360 |   7340032 |
| insee46      |    28917760 |     11599872 |   6291456 |
| insee47      |    47824896 |     15794176 |   4194304 |
| insee48      |    14221312 |      7405568 |   4194304 |
| insee49      |    88801280 |     29458432 |   7340032 |
| insee50      |    55181312 |     19988480 |   5242880 |
| insee51      |    75153408 |     25264128 |   4194304 |
| insee52      |    20512768 |      7405568 |   6291456 |
| insee53      |    36274176 |     12648448 |   6291456 |
| insee54      |    71991296 |     25231360 |   4194304 |
| insee55      |    20512768 |      7405568 |   6291456 |
| insee56      |    91947008 |     30507008 |   5242880 |
| insee57      |    93011968 |     30507008 |   7340032 |
| insee58      |    25772032 |     10551296 |   6291456 |
| insee59      |   232734720 |     73629696 |   6291456 |
| insee60      |    79347712 |     27361280 |   6291456 |
| insee61      |    35225600 |     12648448 |   7340032 |
| insee62      |   122421248 |     38895616 |   7340032 |
| insee63      |    79347712 |     26312704 |   7340032 |
| insee64      |    99303424 |     30507008 |   5242880 |
| insee65      |    35225600 |     12648448 |   6291456 |
| insee66      |    83558400 |     26312704 |   7340032 |
| insee67      |   117161984 |     35749888 |   7340032 |
| insee68      |    73039872 |     25231360 |   6291456 |
| insee69      |   260046848 |     78888960 |   5242880 |
| insee70      |    24723456 |     10551296 |   7340032 |
| insee71      |    66748416 |     22085632 |   4194304 |
| insee72      |    53084160 |     17891328 |   7340032 |
| insee73      |    83542016 |     26312704 |   4194304 |
| insee74      |   117161984 |     36798464 |   5242880 |
| insee75      |   693108736 |    194560000 |   4194304 |
| insee76      |   114016256 |     35749888 |   4194304 |
| insee77      |   144474112 |     46268416 |   4194304 |
| insee78      |   161284096 |     50528256 |   7340032 |
| insee79      |    42582016 |     15794176 |   5242880 |
| insee80      |    50987008 |     16842752 |   4194304 |
| insee81      |    52035584 |     16842752 |   7340032 |
| insee82      |    36274176 |     12648448 |   6291456 |
| insee83      |   162332672 |     49479680 |   5242880 |
| insee84      |    89849856 |     27361280 |   4194304 |
| insee85      |    76201984 |     25264128 |   7340032 |
| insee86      |    14221312 |      6356992 |   4194304 |
| insee87      |    43630592 |     15794176 |   7340032 |
| insee88      |    41533440 |     15794176 |   7340032 |
| insee89      |    41533440 |     15794176 |   6291456 |
| insee90      |    14221312 |      6356992 |   4194304 |
| insee91      |   125566976 |     39944192 |   4194304 |
| insee92      |   230621184 |     65241088 |   7340032 |
| insee93      |   170737664 |     47382528 |   7340032 |
| insee94      |   152879104 |     44204032 |   5242880 |
| insee95      |   122421248 |     35749888 |   6291456 |
| insee96      |       16384 |        65536 |         0 |
| insee97      |   252690432 |     71548928 |   7340032 |
| insee98      |     1589248 |       475136 |   4194304 |
| insee99      |    40484864 |     14745600 |   4194304 |
| insee_files  |  1667235840 |    488636416 |   4194304 |
| tbmi1003     |   175865856 |    194117632 |   7340032 |
+--------------+-------------+--------------+-----------+

les données:

+--------------------------+---------------------+------+-----+---------+----------------+
| Field                    | Type                | Null | Key | Default | Extra          |
+--------------------------+---------------------+------+-----+---------+----------------+
| id                       | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| Siren                    | varchar(9)          | YES  | MUL | NULL    |                |
| Nic                      | varchar(5)          | YES  |     | NULL    |                |
| ind                      | varchar(1)          | YES  |     | NULL    |                |
| Actif                    | varchar(1)          | YES  |     | NULL    |                |
| Siret                    | varchar(14)         | YES  |     | NULL    |                |
| new_siren                | varchar(9)          | YES  |     | NULL    |                |
| new_nic                  | varchar(5)          | YES  |     | NULL    |                |
| Dpt                      | varchar(2)          | YES  | MUL | NULL    |                |
| Com                      | varchar(3)          | YES  |     | NULL    |                |
| RS                       | varchar(60)         | YES  |     | NULL    |                |
| RS2                      | varchar(60)         | YES  |     | NULL    |                |
| Sigle                    | varchar(30)         | YES  |     | NULL    |                |
| Enseigne                 | varchar(60)         | YES  |     | NULL    |                |
| cpost                    | varchar(5)          | YES  |     | NULL    |                |
| ville                    | varchar(35)         | YES  |     | NULL    |                |
| NomVoie                  | varchar(60)         | YES  |     | NULL    |                |
| LibVoie                  | varchar(60)         | YES  |     | NULL    |                |
| Rivo                     | varchar(4)          | YES  |     | NULL    |                |
| numvoie                  | decimal(10,0)       | YES  |     | NULL    |                |
| numvoie2                 | decimal(10,0)       | YES  |     | NULL    |                |
| numcomp                  | varchar(4)          | YES  |     | NULL    |                |
| TypeVoie                 | varchar(4)          | YES  |     | NULL    |                |
| cmpladr                  | varchar(60)         | YES  |     | NULL    |                |
| siege                    | varchar(2)          | YES  |     | NULL    |                |
| eff                      | varchar(2)          | YES  |     | NULL    |                |
| nafRev2                  | varchar(5)          | YES  |     | NULL    |                |
| Rgn                      | varchar(2)          | YES  |     | NULL    |                |
| ardt                     | varchar(1)          | YES  |     | NULL    |                |
| Can                      | varchar(2)          | YES  |     | NULL    |                |
| popul                    | varchar(2)          | YES  |     | NULL    |                |
| natetb                   | varchar(2)          | YES  |     | NULL    |                |
| motif                    | varchar(1)          | YES  |     | NULL    |                |
| tel_ins                  | varchar(10)         | YES  |     | NULL    |                |
| libdist                  | varchar(26)         | YES  |     | NULL    |                |
| BP                       | varchar(10)         | YES  |     | NULL    |                |
| dateEve                  | varchar(8)          | YES  |     | NULL    |                |
| Eve                      | varchar(2)          | YES  |     | NULL    |                |
| d_cre_ins                | varchar(8)          | YES  |     | NULL    |                |
| d_cre_rxf                | varchar(8)          | YES  |     | NULL    |                |
| d_der_maj                | varchar(8)          | YES  |     | NULL    |                |
| d_Fin_Ent                | varchar(8)          | YES  |     | NULL    |                |
| d_Fin_Etb                | varchar(8)          | YES  |     | NULL    |                |
| TypeTran                 | varchar(1)          | YES  |     | NULL    |                |
| Source_File              | varchar(30)         | YES  |     | NULL    |                |
| Source_Date              | varchar(8)          | YES  |     | NULL    |                |
| SOMAJ                    | varchar(13)         | YES  |     | NULL    |                |
| cpost2                   | varchar(5)          | YES  |     | NULL    |                |
| cedex                    | varchar(35)         | YES  |     | NULL    |                |
| Duns                     | varchar(9)          | YES  |     | NULL    |                |
| US_SIC                   | varchar(4)          | YES  |     | NULL    |                |
| DUNS_HQ                  | varchar(9)          | YES  |     | NULL    |                |
| DUNS_Ultimate            | varchar(9)          | YES  |     | NULL    |                |
| DUNS_Global              | varchar(9)          | YES  |     | NULL    |                |
| D_Duns_Upd               | varchar(8)          | YES  |     | NULL    |                |
| kw1                      | varchar(15)         | YES  |     | NULL    |                |
| kw2                      | varchar(15)         | YES  |     | NULL    |                |
| kw3                      | varchar(15)         | YES  |     | NULL    |                |
| rivo2                    | varchar(4)          | YES  |     | NULL    |                |
| user_id                  | varchar(8)          | YES  |     | NULL    |                |
| country                  | varchar(70)         | YES  |     | NULL    |                |
| eff_estab                | varchar(7)          | YES  |     | NULL    |                |
| cd_expl_estb             | varchar(1)          | YES  |     | NULL    |                |
| dpt_unit_urb_estb        | varchar(2)          | YES  |     | NULL    |                |
| taille_unit_urb_estb     | varchar(1)          | YES  |     | NULL    |                |
| num_unit_urb_estb        | varchar(5)          | YES  |     | NULL    |                |
| zone_emploi              | varchar(2)          | YES  |     | NULL    |                |
| cd_act_estab             | varchar(2)          | YES  |     | NULL    |                |
| cd_lieu_act_estab        | varchar(2)          | YES  |     | NULL    |                |
| cd_surf_estab            | varchar(1)          | YES  |     | NULL    |                |
| cd_mod_act_estab         | varchar(1)          | YES  |     | NULL    |                |
| cd_part_prod_estab       | varchar(1)          | YES  |     | NULL    |                |
| cd_civilite              | varchar(1)          | YES  |     | NULL    |                |
| cd_act_RM                | varchar(1)          | YES  |     | NULL    |                |
| recme                    | varchar(1)          | YES  |     | NULL    |                |
| cd_eff_estab_cent        | varchar(10)         | YES  |     | NULL    |                |
| d_maj_eff                | varchar(8)          | YES  |     | NULL    |                |
| TypeVoieLong             | varchar(16)         | YES  |     | NULL    |                |
| LibelleCommune           | varchar(32)         | YES  |     | NULL    |                |
| cp3                      | varchar(29)         | YES  |     | NULL    |                |
| cplAdr2                  | varchar(50)         | YES  |     | NULL    |                |
| terr                     | varchar(4)          | YES  |     | NULL    |                |
| Gmkt                     | varchar(6)          | YES  | MUL | NULL    |                |
| GmktFut                  | varchar(6)          | YES  | MUL | NULL    |                |
| RSO                      | varchar(3)          | YES  |     | NULL    |                |
| Cd_Couv                  | varchar(2)          | YES  |     | NULL    |                |
| d_calc_seg               | varchar(8)          | YES  |     | NULL    |                |
| d_deb_seg                | varchar(8)          | YES  |     | NULL    |                |
| d_cre_record             | varchar(8)          | YES  |     | NULL    |                |
| cus_acc_seg_code         | varchar(1)          | YES  |     | NULL    |                |
| cus_acc_sub_cla_code     | varchar(2)          | YES  |     | NULL    |                |
| cus_acc_sub_seg_code     | varchar(1)          | YES  |     | NULL    |                |
| cus_ind_sec              | varchar(2)          | YES  |     | NULL    |                |
| cus_ind_sub_sec          | varchar(3)          | YES  |     | NULL    |                |
| cus_ind_seg_code         | varchar(2)          | YES  |     | NULL    |                |
| OTT                      | varchar(8)          | YES  |     | NULL    |                |
| RT                       | varchar(8)          | YES  |     | NULL    |                |
| RSO_fut                  | varchar(3)          | YES  |     | NULL    |                |
| Cd_Couv_Fut              | varchar(2)          | YES  |     | NULL    |                |
| cus_acc_seg_code_fut     | varchar(1)          | YES  |     | NULL    |                |
| cus_acc_sub_cla_code_fut | varchar(2)          | YES  |     | NULL    |                |
| cus_acc_sub_seg_code_fut | varchar(1)          | YES  |     | NULL    |                |
| cus_ind_sec_fut          | varchar(2)          | YES  |     | NULL    |                |
| cus_ind_sub_sec_fut      | varchar(3)          | YES  |     | NULL    |                |
| cus_ind_seg_code_fut     | varchar(2)          | YES  |     | NULL    |                |
| OTT_fut                  | varchar(8)          | YES  |     | NULL    |                |
| RT_fut                   | varchar(8)          | YES  |     | NULL    |                |
| d_app_fut                | varchar(8)          | YES  |     | NULL    |                |
| cd_cause                 | varchar(2)          | YES  |     | NULL    |                |
| CmplAdr2                 | varchar(60)         | YES  |     | NULL    |                |
| CmplAdr3                 | varchar(60)         | YES  |     | NULL    |                |
| Batiment                 | varchar(60)         | YES  |     | NULL    |                |
| Etage                    | varchar(60)         | YES  |     | NULL    |                |
| Bureau                   | varchar(60)         | YES  |     | NULL    |                |
| NoShipTo                 | varchar(1)          | YES  |     | NULL    |                |
| Tbma2205                 | varchar(1)          | YES  |     | NULL    |                |
| Tbma2206                 | varchar(1)          | YES  |     | NULL    |                |
| Tbma2207                 | varchar(1)          | YES  |     | NULL    |                |
| Tbma2208                 | varchar(1)          | YES  |     | NULL    |                |
| dpt_post                 | varchar(2)          | YES  |     | NULL    |                |
| Dpt_Adm                  | varchar(2)          | YES  |     | NULL    |                |
| Tbma2205_Fut             | varchar(1)          | YES  |     | NULL    |                |
| Tbma2206_Fut             | varchar(1)          | YES  |     | NULL    |                |
| Tbma2207_Fut             | varchar(1)          | YES  |     | NULL    |                |
| Tbma2208_Fut             | varchar(1)          | YES  |     | NULL    |                |
| d_synch_ref              | varchar(8)          | YES  |     | NULL    |                |
| controle                 | varchar(3)          | YES  |     | NULL    |                |
| qrt                      | varchar(2)          | YES  |     | NULL    |                |
| Duns_Ultimate_Fut        | varchar(9)          | YES  |     | NULL    |                |
| Duns_Global_fut          | varchar(9)          | YES  |     | NULL    |                |
| Rs_sap                   | varchar(37)         | YES  |     | NULL    |                |
| RS_Sfdc                  | varchar(80)         | YES  |     | NULL    |                |
| DerAlpha                 | varchar(8)          | YES  |     | NULL    |                |
+--------------------------+---------------------+------+-----+---------+----------------+

et les index:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> show index  from insee  from reflocal;nom_de_la_base_de_données
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| insee |          0 | PRIMARY      |            1 | id          | A         |     5013246 |     NULL | NULL   |      | BTREE      |         |               |
| insee |          1 | SirenNic     |            1 | Siren       | A         |     3872820 |     NULL | NULL   | YES  | BTREE      |         |               |
| insee |          1 | SirenNic     |            2 | Nic         | A         |     4947860 |     NULL | NULL   | YES  | BTREE      |         |               |
| insee |          1 | DptComSiret  |            1 | Dpt         | A         |        7530 |     NULL | NULL   | YES  | BTREE      |         |               |
| insee |          1 | DptComSiret  |            2 | Com         | A         |       73457 |     NULL | NULL   | YES  | BTREE      |         |               |
| insee |          1 | DptComSiret  |            3 | Siren       | A         |     5054996 |     NULL | NULL   | YES  | BTREE      |         |               |
| insee |          1 | DptComSiret  |            4 | Nic         | A         |     5537421 |     NULL | NULL   | YES  | BTREE      |         |               |
| insee |          1 | GmktSiret    |            1 | Gmkt        | A         |       47620 |     NULL | NULL   | YES  | BTREE      |         |               |
| insee |          1 | GmktSiret    |            2 | Siren       | A         |     3569392 |     NULL | NULL   | YES  | BTREE      |         |               |
| insee |          1 | GmktSiret    |            3 | Nic         | A         |     4666191 |     NULL | NULL   | YES  | BTREE      |         |               |
| insee |          1 | GmktFutSiret |            1 | GmktFut     | A         |       44027 |     NULL | NULL   | YES  | BTREE      |         |               |
| insee |          1 | GmktFutSiret |            2 | Siren       | A         |     4754330 |     NULL | NULL   | YES  | BTREE      |         |               |
| insee |          1 | GmktFutSiret |            3 | Nic         | A         |     5183563 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Le message:

Citation:

SQL Error: the table "insee" is full

Pour ce qui est du backup, pas de soucis. J'ai toutes les données sur une autre machine. Pas de risque de casse!

Merci en tout cas pour votre aide.

JP

17/10/2017, 14h16
escartefigue

Bonjour,

Tant qu'à faire de migrer vos données dans une nouvelles DB, vous devriez en profiter pour revoir votre modélisation.
Votre table n'est pas une table d'un modèle relationnelle, elle est conçue comme un tableur excel et ne respecte aucune des formes normales Merise.
Du coup vous trimbalez une table énorme qui explique en partie vos difficultés.

Exemple parmi tant d'autre, le SIREN et l'adresse dans une meme table, c'est incohérent. Le siren est attribut de l'entreprise, tous établissements confondus, alors que l'adresse est potentiellement multiple pour chaque établissement.
Autre exemple un code civilité, qui ne concerne que les personnes physiques, dans une table ou on trouve des attributs de personnes morales

Profitez en également pour remplacer tous les varchar très courts par du char vous gagnerez également en perfs.

Bref revoyez tout votre modèle de fond en comble, il est fort probable que votre problème de volumétrie disparaitra tout seul, et vous gagnerez en fiabilité, en pérennité et en performances ;)

Salut jpcubain.

J'ai fait le test, et voici mon script :

Code:

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
SET AUTOCOMMIT = 0;
START TRANSACTION;
 
-- ======================
-- Base de Données `base`
-- ======================
 
DROP DATABASE IF EXISTS `base`;
 
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`;
 
use `base`;
 
-- =============
-- Table `insee`
-- =============
 
DROP TABLE IF EXISTS `meeeeeh`;
 
CREATE TABLE `insee`
( `id`                        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `Siren`                     Varchar(9),
  `Nic`                       Varchar(5),
  `ind`                       Varchar(1),
  `Actif`                     Varchar(1),
  `Siret`                     Varchar(14),
  `new_siren`                 Varchar(9),
  `new_nic`                   Varchar(5),
  `Dpt`                       Varchar(2),
  `Com`                       Varchar(3),
  `RS`                        Varchar(60),
  `RS2`                       Varchar(60),
  `Sigle`                     Varchar(30),
  `Enseigne`                  Varchar(60),
  `cpost`                     Varchar(5),
  `ville`                     Varchar(35),
  `NomVoie`                   Varchar(60),
  `LibVoie`                   Varchar(60),
  `Rivo`                      Varchar(4),
  `numvoie`                   Decimal       default 0,
  `numvoie2`                  Decimal       default 0,
  `numcomp`                   Varchar(4),
  `TypeVoie`                  Varchar(4),
  `cmpladr`                   Varchar(60),
  `siege`                     Varchar(2),
  `eff`                       Varchar(2),
  `nafRev2`                   Varchar(5),
  `Rgn`                       Varchar(2),
  `ardt`                      Varchar(1),
  `Can`                       Varchar(2),
  `popul`                     Varchar(2),
  `natetb`                    Varchar(2),
  `motif`                     Varchar(1),
  `tel_ins`                   Varchar(10),
  `libdist`                   Varchar(26),
  `BP`                        Varchar(10),
  `dateEve`                   Varchar(8),
  `Eve`                       Varchar(2),
  `d_cre_ins`                 Varchar(8),
  `d_cre_rxf`                 Varchar(8),
  `d_der_maj`                 Varchar(8),
  `d_Fin_Ent`                 Varchar(8),
  `d_Fin_Etb`                 Varchar(8),
  `TypeTran`                  Varchar(1),
  `Source_File`               Varchar(30),
  `Source_Date`               Varchar(8),
  `SOMAJ`                     Varchar(13),
  `cpost2`                    Varchar(5),
  `cedex`                     Varchar(35),
  `Duns`                      Varchar(9),
  `US_SIC`                    Varchar(4),
  `DUNS_HQ`                   Varchar(9),
  `DUNS_Ultimate`             Varchar(9),
  `DUNS_Global`               Varchar(9),
  `D_Duns_Upd`                Varchar(8),
  `kw1`                       Varchar(15),
  `kw2`                       Varchar(15),
  `kw3`                       Varchar(15),
  `rivo2`                     Varchar(4),
  `user_id`                   Varchar(8),
  `country`                   Varchar(70),
  `eff_estab`                 Varchar(7),
  `cd_expl_estb`              Varchar(1),
  `dpt_unit_urb_estb`         Varchar(2),
  `taille_unit_urb_estb`      Varchar(1),
  `num_unit_urb_estb`         Varchar(5),
  `zone_emploi`               Varchar(2),
  `cd_act_estab`              Varchar(2),
  `cd_lieu_act_estab`         Varchar(2),
  `cd_surf_estab`             Varchar(1),
  `cd_mod_act_estab`          Varchar(1),
  `cd_part_prod_estab`        Varchar(1),
  `cd_civilite`               Varchar(1),
  `cd_act_RM`                 Varchar(1),
  `recme`                     Varchar(1),
  `cd_eff_estab_cent`         Varchar(10),
  `d_maj_eff`                 Varchar(8),
  `TypeVoieLong`              Varchar(16),
  `LibelleCommune`            Varchar(32),
  `cp3`                       Varchar(29),
  `cplAdr2`                   Varchar(50),
  `terr`                      Varchar(4),
  `Gmkt`                      Varchar(6),
  `GmktFut`                   Varchar(6),
  `RSO`                       Varchar(3),
  `Cd_Couv`                   Varchar(2),
  `d_calc_seg`                Varchar(8),
  `d_deb_seg`                 Varchar(8),
  `d_cre_record`              Varchar(8),
  `cus_acc_seg_code`          Varchar(1),
  `cus_acc_sub_cla_code`      Varchar(2),
  `cus_acc_sub_seg_code`      Varchar(1),
  `cus_ind_sec`               Varchar(2),
  `cus_ind_sub_sec`           Varchar(3),
  `cus_ind_seg_code`          Varchar(2),
  `OTT`                       Varchar(8),
  `RT`                        Varchar(8),
  `RSO_fut`                   Varchar(3),
  `Cd_Couv_Fut`               Varchar(2),
  `cus_acc_seg_code_fut`      Varchar(1),
  `cus_acc_sub_cla_code_fut`  Varchar(2),
  `cus_acc_sub_seg_code_fut`  Varchar(1),
  `cus_ind_sec_fut`           Varchar(2),
  `cus_ind_sub_sec_fut`       Varchar(3),
  `cus_ind_seg_code_fut`      Varchar(2),
  `OTT_fut`                   Varchar(8),
  `RT_fut`                    Varchar(8),
  `d_app_fut`                 Varchar(8),
  `cd_cause`                  Varchar(2),
  `CmplAdr2`                  Varchar(60),
  `CmplAdr3`                  Varchar(60),
  `Batiment`                  Varchar(60),
  `Etage`                     Varchar(60),
  `Bureau`                    Varchar(60),
  `NoShipTo`                  Varchar(1),
  `Tbma2205`                  Varchar(1),
  `Tbma2206`                  Varchar(1),
  `Tbma2207`                  Varchar(1),
  `Tbma2208`                  Varchar(1),
  `dpt_post`                  Varchar(2),
  `Dpt_Adm`                   Varchar(2),
  `Tbma2205_Fut`              Varchar(1),
  `Tbma2206_Fut`              Varchar(1),
  `Tbma2207_Fut`              Varchar(1),
  `Tbma2208_Fut`              Varchar(1),
  `d_synch_ref`               Varchar(8),
  `controle`                  Varchar(3),
  `qrt`                       Varchar(2),
  `Duns_Ultimate_Fut`         Varchar(9),
  `Duns_Global_fut`           Varchar(9),
  `Rs_sap`                    Varchar(37),
  `RS_Sfdc`                   Varchar(80),
  `DerAlpha`                  Varchar(8),
  PRIMARY KEY (id),
  INDEX `SirenNic`      (`Siren`,`Nic`),
  INDEX `DptComSiret`   (`Dpt`,`Com`,`Siren`,`Nic`),
  INDEX `GmktSiret`     (`Gmkt`,`Siren`,`Nic`),
  INDEX `GmktFutSiret`  (`GmktFut`,`Siren`,`Nic`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
commit;
 
-- ===================
-- Procedure `remplir`
-- ===================
 
DROP PROCEDURE IF EXISTS `remplir`;
 
DELIMITER $$
CREATE PROCEDURE `remplir` ()
DETERMINISTIC
NO SQL
BEGIN
  DECLARE _ind   bigint default 0;
 
  SET _ind = 0;
 
  WHILE (_ind < 10000000000)
  DO
    insert into `insee` (`Siren`) values  ('');
 
    SET _ind = _ind + 1;
  END WHILE;
END$$
DELIMITER ;
 
commit;
 
-- =====================
-- Exécuter la procédure
-- =====================
 
call `remplir` ();
 
-- =================
-- Vidage de `insee`
-- =================
 
select * from `insee` limit 100;
 
-- ===
-- Fin
-- ===
 
COMMIT;
SET AUTOCOMMIT = 1;
exit

Je suis allé au maximum de l'espace encore disponible sur mon disque, ce qui donne :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--------------
SELECT  TABLE_NAME,
        DATA_LENGTH,
        INDEX_LENGTH,
        DATA_FREE
  FROM  information_schema.TABLES
 WHERE  TABLE_SCHEMA = 'base'
--------------
 
+------------+-------------+--------------+-----------+
| TABLE_NAME | DATA_LENGTH | INDEX_LENGTH | DATA_FREE |
+------------+-------------+--------------+-----------+
| insee      |  4572315648 |   5196742656 |   2621440 |
+------------+-------------+--------------+-----------+

--> 4.572.315.648 + 5.196.742.656 = 9.769.058.304, soit 9,098 Go !

La taille sur le disque du fichier "insee.ibd" est de 10.281.984 octets, soit en gros 9,80 Go.

Le nombre de lignes que j'ai pu inséré est de :

Code:

1
2
3
4
5
6
7
8
9
10
11
--------------
select max(id) from base.insee
--------------
 
+-----------+
| max(id)   |
+-----------+
| 175627508 |
+-----------+
 
Appuyez sur une touche pour continuer...

Soit 175 millions de lignes !

Pour le test, j'ai utilisé mon environnement de test, dont la capacité de ma partition logique est de 20 Go, et contenait déjà trois serveurs MySql.
L'espace restant, après ce test, sur ma partition logique est de 580 Ko.

La conclusion s'impose, on peut largement dépasser, pour une table donnée, les 4 Go.

A tout hasard, je donne mon fichier "my.ini". Il est fort probable que votre problème vienne de là.

Code:

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
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
# ********************* #
#                       #
#     Sphere "Bleu"     #
#                       #
# ********************* #
 
# ==================== #
#     MySql Client     #
# ==================== #
 
[client]
port   = 3306
socket = mysql
 
# ----------------------- #
#     access features     #
# ----------------------- #
 
user     = root
password = root
host     = 127.0.0.1
 
# --------------- #
#     Charset     #
# --------------- #
 
default-character-set = latin1
 
 
# ==================== #
#     MySql Server     #
# ==================== #
 
[wampmysqld]
port   = 3306
socket = mysql
 
# -------------- #
#     Master     #
# -------------- #
 
server-id = 1
skip-slave-start
 
# --------------- #
#     Charset     #
# --------------- #
 
character-set-server     = latin1
collation-server         = latin1_general_ci
character-set-filesystem = latin1
 
init-connect             = 'SET collation_connection = latin1_general_ci; SET NAMES latin1 COLLATE latin1_general_ci'
 
# ------------------- #
#     Date & Time     #
# ------------------- #
 
default-time-zone = SYSTEM
 
explicit-defaults-for-timestamp = on
 
# ------------------- #
#     Directories     #
# ------------------- #
 
basedir = f:/Wamp/bin/mysql/mysql5.7.18/
datadir = f:/Wamp/bin/mysql/mysql5.7.18/data
tmpdir  = f:/Wamp/tmp
 
# -------------------------#
#     Full Text Search     #
# -------------------------#
 
ft-max-word-len  = 10
ft-min-word-len  = 1
ft-stopword-file = ""
 
# ---------------#
#     InnoDB     #
# ---------------#
 
default-storage-engine              = InnoDB
default-tmp-storage-engine          = InnoDB
 
innodb-adaptive-hash-index          = on
 
innodb-buffer-pool-dump-now         = off
innodb-buffer-pool-dump-at-shutdown = off
innodb-buffer-pool-instances        = 2
innodb-buffer-pool-load-at-startup  = OFF
innodb-buffer-pool-size             = 128M
 
innodb-data-file-path               = ibdata1:12M:autoextend
innodb-data-home-dir                = f:/Wamp/bin/mysql/mysql5.7.18/data/ibdata/
innodb-default-row-format           = compact
innodb-doublewrite                  = 1
#skip-innodb_doublewrite
 
innodb-file-format-check            = on
innodb-file-per-table               = 1
 
innodb-flush-log-at-trx-commit      = 1
#innodb-force-recovery               = 1
 
innodb-ft-enable-stopword           = off
innodb-ft-max-token-size            = 10
innodb-ft-min-token-size            = 0
 
innodb-io-capacity                  = 2000
innodb-max-dirty-pages-pct          = 90
innodb-lock-wait-timeout            = 600
 
innodb-log-buffer-size              = 16M
innodb-log-file-size                = 20M
innodb-log-files-in-group           = 2
innodb-log-group-home-dir           = f:/Wamp/bin/mysql/mysql5.7.18/data/ibdata/
 
innodb-max-dirty-pages-pct          = 80
innodb-optimize-fulltext-only       = 1
innodb-page-size                    = 16K
innodb-purge-threads                = 10
innodb-read-io-threads              = 4
innodb-stats-on-metadata            = 0
innodb-support-xa                   = 1
innodb-thread-concurrency           = 16
innodb-undo-log-truncate            = 1
innodb-write-io-threads             = 4
 
# ---------------- #
#     Language     #
# ---------------- #
 
lc-messages   = fr_FR
lc-time-names = fr_FR
 
# -------------------#
#     Binary Log     #
# -------------------#
 
#log-bin              = f:/Wamp/logs/mysql_binary
#binlog-cache-size    = 1M
#binlog-format        = mixed
 
# --------------------#
#     General Log     #
# --------------------#
 
#general-log         = 1
#general-log-file    = f:/Wamp/logs/mysql_general.log
#log_output          = 'FILE';
 
# ------------#
#     Log     #
# ------------#
 
log-error                              = f:/Wamp/logs/mysql_error.log
log-error-verbosity                    = 3
 
log-queries-not-using-indexes          = 1
log-throttle-queries-not-using-indexes = 60
 
# ------------#
#     Max     #
# ------------#
 
max-allowed-packet     = 16M
max-connections        = 20
max-connect-errors     = 10
max-error-count        = 64
max-heap-table-size    = 1G
max-user-connections   = 0
max-sp-recursion-depth = 255
 
# --------------------#
#     Buffer-Size     #
# --------------------#
 
join-buffer-size      = 512M
read-buffer-size      = 512M
read-rnd-buffer-size  = 512M
sort-buffer-size      = 512M
 
# ---------------#
#     MyIsam     #
# ---------------#
 
bulk-insert-buffer-size   = 64M
 
key-buffer-size           = 2G
 
myisam-max-sort-file-size = 1G
myisam-recover-options    = 1
myisam-repair-threads     = 1
myisam-sort-buffer-size   = 128M
myisam-stats-method       = nulls_unequal
myisam-use-mmap           = OFF
 
# -------------------#
#     Networking     #
# -------------------#
 
back-log           = 50
 
net-buffer-length  = 8K
net-read-timeout   = 60
net-write-timeout  = 60
 
# -----------------#
#     Password     #
# -----------------#
 
default-password-lifetime      = 0
disconnect-on-expired-password = 0
 
# ---------------------------#
#     Performance-Schema     #
# ---------------------------#
 
# performance-schema                                = ON
# performance-schema-events-waits-history-size      = 20
# performance-schema-events-waits-history-long-size = 1500
 
# ----------------#
#     Pluggin     #
# ----------------#
 
early-plugin-load =
# early-plugin-load = keyring_file.dll
 
# --------------#
#     Query     #
# --------------#
 
query-alloc-block-size       = 8M
query-cache-limit            = 1G
query-cache-min-res-unit     = 4096
query-cache-size             = 512M
query-cache-type             = On
query-cache-wlock-invalidate = Off
 
# -------------#
#     Skip     #
# -------------#
 
disable-partition-engine-check
 
#skip-innodb-doublewrite
 
skip-external-locking
skip-federated
 
#skip-grant-tables
#skip-log-warnings
skip-slave-start
# skip-innodb-adaptive-hash-index
 
# --------------------#
#     Long & Slow     #
# --------------------#
 
long-query-time     = 0
 
slow-query-log      = 0
#slow-query-log-file = f:/Wamp/logs/mysql_slow.log
 
# ------------#
#     Ssl     #
# ------------#
 
ssl
ssl-ca   = f:/Wamp/bin/apache/apache2.4.27/conf/certificat/ca/ca.crt
ssl-cert = f:/Wamp/bin/apache/apache2.4.27/conf/certificat/localhost/localhost.crt
ssl-key  = f:/Wamp/bin/apache/apache2.4.27/conf/certificat/localhost/localhost.key
 
# skip-ssl
 
# --------------#
#     Table     #
# --------------#
 
table-definition-cache = 512
table-open-cache       = 512
 
# -------------------#
#     Temp Table     #
# -------------------#
 
tmp-table-size = 1G
 
# ---------------#
#     Thread     #
# ---------------#
 
thread-cache-size = 8
thread-stack      = 256K
 
# ------------------------------#
#     Timeout Configuration     #
# ------------------------------#
 
connect-timeout     = 600
interactive-timeout = 600
wait-timeout        = 600
 
# --------------------#
#     Transaction     #
# --------------------#
 
transaction-isolation = SERIALIZABLE
 
# ---------------#
#     Tuning     #
# ---------------#
 
concurrent-insert = 2
open-files-limit  = 8192
 
# secure-file-priv  = f:/wamp/tmp
secure-file-priv  = 
 
 
# =================== #
#     MySql Admin     #
# =================== #
 
[mysqldadmin]
force
 
 
# ================== #
#     MySql Dump     #
# ================== #
 
[mysqldump]
add-drop-database
add-drop-table
add-drop-trigger
 
default-character-set = utf8
 
flush-logs
flush-privileges
 
lock-all-tables
max-allowed-packet    = 16M
no-autocommit
order-by-primary
quick
routines
 
skip-comments
skip-opt
skip-set-charset
skip-tz-utc
 
triggers
 
 
# ===================== #
#     My Isam Check     #
# ===================== #
 
[myisamchk]
key-buffer-size  = 128M
sort-buffer-size = 128M
read-buffer      = 2M
write-buffer     = 2M
 
 
# ====================== #
#     MySql Hot Copy     #
# ====================== #
 
[mysqlhotcopy]
interactive-timeout
 
 
# ===================== #
#     MySql Monitor     #
# ===================== #
 
[mysql]
default-character-set = latin1
no-auto-rehash
#safe-updates
skip-reconnect
table
 
 
# ================== #
#     MySql Safe     #
# ================== #
 
[mysqld_safe]
open-files-limit = 8192
 
log-error        = f:/Wamp/logs/mysql_error.log

17/10/2017, 21h48
jpcubain

Je crois que j'ai trouvé le problème...tout bête.
C'était la version 32 bit de MySQL qui avait été installée.
J'ai tout désinstallé, remis la version 64 bits, et là j'en suis déjà presque à 5Go.
On va laisser tourner, mais je pense que dans quelques heures, ce sera bon.

Merci de vos conseils.

Cdt,
JP
17/10/2017, 22h35
SQLpro

Hé oui... 32 bits => 4 Gb d'adressage max (2^32 = 4 milliard d'octets)

A +
18/10/2017, 15h42
jpcubain

C'est bon, c'était bien le problème. La table a fini de se construire, et elle fait un peu plus de 13Go, (même si elle ne respecte pas les règles de l'art Merise).
Merci à tous
18/10/2017, 15h48
Artemus24

Salut jpcubain.

Pourquoi ne pas suivre maintenant les conseils de Escartefigue ? Votre table ressemble plutôt à un fichier Excel.
Vous devez extraire vos données de façon à respecter les formes normales !
Ensuite, si vous aviez une centaine de fichiers Excel, rien ne vous empêche de transformer votre unique table en une table partitionnée.

@+