Uscita anomala da ciclo while con tabella enorme

[deleted_29]

Originariamente inviato da Botola59
Ok! Il test è andato bene. Ha ciclato tutti i 13.000.000 record.

Ha iniziato alle 15:25 ed ha finito adesso 21:41 (escluso il load che avevo fatto manualmente
che ha impiegato 800 secondi).

Se ci sono ulteriori problemi, e ci saranno perchè qualche altra query fa le bizze,
mi faccio sentire. Grazie franzauker, grazie ancora e soprattutto per la pazienza

PS - Si confesso uso Microsoft ma solo perchè l'azienda ha optato per questa piattaforma.

Ahem... scusa ma che macchina usi?
Da me impiega circa 5 minuti, tutto compreso (caricamento e creazione indici!)

Hai disattivato-riattivato la creazione degli indici? E' fondamentale su macchine lente e soprattutto con dischi magnetici.

Quanto è grande il file di testo da importare? Quello di prova che ho usato io è dell'ordine di 800MB.
---
Una volta caricato il file, ti ricordo, devi scrivere la query di update.
Però, prima, metti la struttura delle altre tabelle, in modo da poter stabilire quali indici usare e, con una bella EXPLAIN, dare un'occhiata al funzionamento dell'ottimizzatore mysql

In buona sostanza è possibile, a mio parere, far tutto quello che vuoi in diciamo 30 minuti tutto compreso.

Anche meno, con qualche "trucchetto" (tipo ad esempio, perchè non codifichi in un intero i cf?)

Nel caricamento ti basta mettere un CRC32(...); nelle altre tabelle aggiungi un campo dove ce lo memorizzi brutalmente (il CRC32 del CF). Ovviamente entrambi i campi indiciati

Poi nei join metterai qualcosa tipo
... where (tabella1.crccf=tabella2.crccf) and (tabella1.cf=tabella2.cf)

la seconda parte serve per evitare le collisioni; mysql è abbastanza "intelligente" da non confrontarla, se il primo confronto non va bene.

Però sono "sottigliezze", come primissima cosa, lo ribadisco, userei una query d'update "vera", anzichè quella roba "strana" PHP

[Botola59]

Scusa il ritardo ma le procedure complete sono lunghe e i suggerimenti
che mi hai fornito sono tanti solo adesso
posso fornire dati sui tempi

Originariamente inviato da franzauker
Ahem... scusa ma che macchina usi?
Da me impiega circa 5 minuti, tutto compreso (caricamento e creazione indici!)

Il sever è un:
Microsoft windows server 2003
Dual Core AMD Opteron
Processor 865
1,80 GHZ, 8,00 GB di ram
Physical Address Extension.

Ma forse devi considerare che ad ogni ciclo deve interrogare la tabella
anagrafica (500.000 rec) e cartelle di (13.000.000) - che tu non hai nel tuo DB.
Ho visto che ogni ciclo while processa 1000 record ogni 4 secondi

Ho fatto due prove:
- Fare il load data così come è ed impiega 800 secondi (ricordo che il file testo
è di 13.00.000 rec);
- eliminando gli indici e ricsotruirli dopo il load data impiega 200
secondi per il caricamento e 600 per la ricostruzione. Quindi non c'è differenza.

Quanto è grande il file di testo da importare?

circa 2.6 GB per 13.000.000 di record

Una volta caricato il file, ti ricordo, devi scrivere la query di update.
Però, prima, metti la struttura delle altre tabelle, in modo da poter stabilire
quali indici usare e, con una bella EXPLAIN, dare un'occhiata al funzionamento
dell'ottimizzatore mysql

Dunque... forse qui ho parecchia confusione. All'interno del php ho effettuato
il load data con:

$file_IN = 'S:/Flussi/Input/appo_1_cartelle.txt';
$query_rst_schede = "
LOAD DATA LOCAL INFILE '$file_IN' INTO TABLE appo_1_cartellenew 
(@var1)
SET
carx_codut2 = substr(@var1,4,2),
carx_codconc  =  substr(@var1,6,3),
carx_idecart =  substr(@var1,9,20),
carx_impcarorig =  substr(@var1,29,13),
carx_esitnot =  substr(@var1,42,1),
carx_dtnot = substr(@var1,43,8),
carx_dtddlpr = substr(@var1,51,8),
carx_codprov = substr(@var1,59,3),
carx_codcom = substr(@var1,62,3),
carx_emisced = substr(@var1,65,7),
carx_codcontr = substr(@var1,72,10),
carx_cf = substr(@var1,82,16),
carx_tpmodcar = substr(@var1,98,2),
carx_dtprod = substr(@var1,100,8),
carx_dtesno = substr(@var1,108,8), 
carx_carifo = substr(@var1,116,1),
carx_paga = substr(@var1,117,1),
carx_cacar = substr(@var1,118,1);
";
$dati=mysqli_query($link, $query_rst_schede) or die("Errore Query - 1\n\n");
mysqli_free_result($dati); 

dopodichè - sempre nello stesso script - ho caricato la query che ho postato
per prima:

$query_rst_schede = "SELECT * FROM appo_1_cartellenew ";
$dati=mysqli_query($link1, $query_rst_schede, MYSQLI_USE_RESULT) or die("Errore Query - 1");
$conta = mysqli_num_rows($dati);
while($array=mysqli_fetch_array($dati))    {
.....join anagrafica
.....join cartelle
... se ok - UPDATE
... se NO ok - INSERT
} 

.. e dove dici "...Però, prima, metti la struttura delle altre tabelle, in modo da
poter stabilire quali indici usare e, con una bella EXPLAIN, dare un'occhiata al funzionamento
dell'ottimizzatore mysql" che non capisco cosa devo fare.

Queste le struttura delle altre tabelle estratta con explain ma non so come
come dare l'occhiata all'ottimizzatore mysql.


Query EXPLAIN SELECT IDana FROM anagrafica WHERE ana_codut2 = 'C0' AND ana_cf = 'XXXXXXXXXXXXXXXX', Wed May 18 09:33:40 2011 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE anagrafica const prova,ana_cf,Index_4 prova 22 const,const 1 Using index

Query EXPLAIN SELECT IDCart FROM cartelle WHERE car_codprov = '015' AND car_codcom = '130' AND car_emisced = 'XXXXXXX', Wed May 18 09:36:21 2011 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE cartelle ref prova prova 25 const,const,const 1 Using where

Ahh... e già che ci siamo... volendo puoi codificare il CF in un intero, sicchè da avere un join (circa) 3 volte più veloce.
Vabbè una volta raggiunto il risultato vediamo come velocizzarlo
...Anche meno, con qualche "trucchetto" (tipo ad esempio, perchè non codifichi in un intero i cf?)

Stavo cercando qualcosa su internet perchè non so come fare. Qualche ling da suggerire?

Però sono "sottigliezze", come primissima cosa, lo ribadisco, userei una query d'update "vera", anzichè quella roba "strana" PHP

Anche qui non ho capito. Forse intendi che posso effettuare tutto il ciclo
anzichè in php sotto mysql?

[deleted_29]

Originariamente inviato da Botola59
...
dopodichè - sempre nello stesso script - ho caricato la query che ho postato
.. e dove dici "...Però, prima, metti la struttura delle altre tabelle, in modo da
poter stabilire quali indici usare e, con una bella EXPLAIN, dare un'occhiata al funzionamento
dell'ottimizzatore mysql" che non capisco cosa devo fare.

Queste le struttura delle altre tabelle estratta con explain ma non so come
come dare l'occhiata all'ottimizzatore mysql.
...

Anche qui non ho capito. Forse intendi che posso effettuare tutto il ciclo
anzichè in php sotto mysql?

Sto uscendo ora, di corsa.

la versione breve è: proprio così.
Continuo a non capire la necessità di fare i "cicloni" in PHP, invece di far fare la "querona" a mysql
---
Per la struttura delle tabelle vale il discorso già fatto:
mysqldump -uroot -ppassword nomedb nometabella -e "filtraquellochevuoi"

Perchè senza sapere come son fatte, le tabelle, e che indici hanno, e che selettività hanno questi ultimi (magari poi vediamo come stimarli) è difficile fare un piano che abbia "un senso"
---

Stavo cercando qualcosa su internet perchè non so come fare. Qualche ling da suggerire?

Bhè, questo non ti basta?

Te l'ho già scritto sopra, in sintesi, vabbè poi lo vedremo con più calma, serve per velocizzare le join, che ora non usi, quindi guadagneresti poco
-----
Indi per cui, passo successivo,

dumpa tutte le tabelle (compresa quella di importazione) e postale qui.

Ovviamente "tagliane via" tutto tranne una piccola parte (che solo le prime 20 righe)
Ciao torno oggi pomeriggio


PS ci sarebbe tutto un "mondo" di ottimizzazione del server mysql, ma lasciamolo proprio per ultimo...

[Botola59]

cancellato !!

[Botola59]

Innanzitutto qui di seguito i dump:

-- MySQL dump 10.11
--
-- Host: localhost Database: xxxxxxx
-- ------------------------------------------------------
-- Server version 5.0.45-community-nt
--
-- Table structure for table `appo_1_cartelle_filtronew`
--

DROP TABLE IF EXISTS `appo_1_cartelle_filtronew`;
CREATE TABLE `appo_1_cartelle_filtronew` (
`id` int(16) unsigned NOT NULL auto_increment,
`carx_codut2` char(2) default NULL,
`carx_cf` char(16) default NULL,
`carx_cdoute` char(2) default NULL,
`carx_codprov` char(3) default NULL,
`carx_codcom` char(3) default NULL,
`carx_carte` char(7) default NULL,
`carx_codconc` char(3) default NULL,
`carx_aaemiss` char(4) default NULL,
`carx_numident` char(8) default NULL,
`carx_id` char(2) default NULL,
`carx_progr` char(3) default NULL,
`carx_esifiltro` char(2) default NULL,
`carx_impscax` char(13) default NULL,
`carx_numfermo` char(6) default NULL,
`carx_dtfermo` char(8) default NULL,
PRIMARY KEY (`id`),
KEY `icodprov` (`carx_codprov`),
KEY `icodcom` (`carx_codcom`),
KEY `iemisced` (`carx_carte`)
) ENGINE=MyISAM AUTO_INCREMENT=1133962 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `appo_1_cartelle_filtronew`
--
-- WHERE: true LIMIT 5

INSERT INTO `appo_1_cartelle_filtronew` VALUES (1,'RT',':10/05/2011','','','',''
,'','','','','','','','',''),(2,'B1','aaaaaaaaaaaa aaaa','B1','54','510','1AA6319
','077','2011','00037834','49','000','00','0000000 546221','ELATER','01010001'),(
3,'B1','bbbbbbbbbbbbbbbb','B1','54','510','1AA5215 ','077','2011','00000438','39'
,'000','00','0000002306228','ELATER','01010001'),( 4,'B1','cccccccccccccccc','B1'
,'54','510','0CA5037','077','2010','00274945','27' ,'000','00','0000000899309','E
LATER','01010001'),(5,'B1','dddddddddddddddd','B1' ,'54','510','0CA4546','077','2
010','00212259','23','000','00','0000000565369','E LATER','01010001');
-- Dump completed on 2011-05-19 10:50:02


-- MySQL dump 10.11
--
-- Host: localhost Database: xxxxxxx
-- ------------------------------------------------------
-- Server version 5.0.45-community-nt
--
-- Table structure for table `cartelle`
--

DROP TABLE IF EXISTS `cartelle`;
CREATE TABLE `cartelle` (
`IDCart` int(16) NOT NULL auto_increment,
`IDana` int(16) NOT NULL default '0',
`car_codconc` varchar(3) NOT NULL default '-',
`car_codute` varchar(2) NOT NULL default '-',
`car_idecart` varchar(20) NOT NULL default '-',
`car_impcar` decimal(13,2) NOT NULL default '0.00',
`car_esitnot` varchar(1) NOT NULL default '-',
`ca

[Botola59]

Innanzitutto qui di seguito i dump:

-- MySQL dump 10.11
--
-- Host: localhost Database: xxxxxxx
-- ------------------------------------------------------
-- Server version 5.0.45-community-nt
--
-- Table structure for table `appo_1_cartelle_filtronew`
--

DROP TABLE IF EXISTS `appo_1_cartelle_filtronew`;
CREATE TABLE `appo_1_cartelle_filtronew` (
`id` int(16) unsigned NOT NULL auto_increment,
`carx_codut2` char(2) default NULL,
`carx_cf` char(16) default NULL,
`carx_cdoute` char(2) default NULL,
`carx_codprov` char(3) default NULL,
`carx_codcom` char(3) default NULL,
`carx_carte` char(7) default NULL,
`carx_codconc` char(3) default NULL,
`carx_aaemiss` char(4) default NULL,
`carx_numident` char(8) default NULL,
`carx_id` char(2) default NULL,
`carx_progr` char(3) default NULL,
`carx_esifiltro` char(2) default NULL,
`carx_impscax` char(13) default NULL,
`carx_numfermo` char(6) default NULL,
`carx_dtfermo` char(8) default NULL,
PRIMARY KEY (`id`),
KEY `icodprov` (`carx_codprov`),
KEY `icodcom` (`carx_codcom`),
KEY `iemisced` (`carx_carte`)
) ENGINE=MyISAM AUTO_INCREMENT=1133962 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `appo_1_cartelle_filtronew`
--
-- WHERE: true LIMIT 5

INSERT INTO `appo_1_cartelle_filtronew` VALUES (1,'RT',':10/05/2011','','','',''
,'','','','','','','','',''),(2,'B1','aaaaaaaaaaaa aaaa','B1','54','510','1AA6319
','077','2011','00037834','49','000','00','0000000 546221','ELATER','01010001'),(
3,'B1','bbbbbbbbbbbbbbbb','B1','54','510','1AA5215 ','077','2011','00000438','39'
,'000','00','0000002306228','ELATER','01010001'),( 4,'B1','cccccccccccccccc','B1'
,'54','510','0CA5037','077','2010','00274945','27' ,'000','00','0000000899309','E
LATER','01010001'),(5,'B1','dddddddddddddddd','B1' ,'54','510','0CA4546','077','2
010','00212259','23','000','00','0000000565369','E LATER','01010001');
-- Dump completed on 2011-05-19 10:50:02





-- MySQL dump 10.11
--
-- Host: localhost Database: xxxxxxx
-- ------------------------------------------------------
-- Server version 5.0.45-community-nt
--
-- Table structure for table `cartelle`
--

DROP TABLE IF EXISTS `cartelle`;
CREATE TABLE `cartelle` (
`IDCart` int(16) NOT NULL auto_increment,
`IDana` int(16) NOT NULL default '0',
`car_codconc` varchar(3) NOT NULL default '-',
`car_codute` varchar(2) NOT NULL default '-',
`car_idecart` varchar(20) NOT NULL default '-',
`car_impcar` decimal(13,2) NOT NULL default '0.00',
`car_esitnot` varchar(1) NOT NULL default '-',
`car_d

[Botola59]

Caro franzauker sto sbattendo la testa per mostrarti i dump.
Ogni volta che clicco anteprima per vedere il testo incollato
rimane bloccato . Evidentemente c'è qualche carattere
che gli da fastidio. Provo a fare a pezzetti.

[Botola59]

Questo è cartelle
C...... T...... `cartelle` (
`IDCart` int(16) NOT NULL auto_increment,
`IDana` int(16) NOT NULL default '0',
`car_codconc` varchar(3) NOT NULL default '-',
`car_codute` varchar(2) NOT NULL default '-',
`car_idecart` varchar(20) NOT NULL default '-',
`car_impcar` decimal(13,2) NOT NULL default '0.00',
`car_esitnot` varchar(1) NOT NULL default '-',
`car_dtnot` date NOT NULL default '0000-00-00',
`car_dtddlpr` date NOT NULL default '0000-00-00',
`car_codprov` varchar(3) NOT NULL default '-',
`car_codcom` varchar(3) NOT NULL default '-',
`car_emisced` varchar(7) NOT NULL default '-',
`car_codcontr` varchar(10) NOT NULL default '-',
`car_cf` varchar(16) NOT NULL default '-',
`car_tpmodcar` varchar(2) NOT NULL default '-',
`car_dtprod` date NOT NULL default '0000-00-00',
`car_dtesno` date NOT NULL default '0000-00-00',
`car_carifo` varchar(1) NOT NULL default '-',
`car_paga` varchar(1) NOT NULL default '-',
`car_cacar` varchar(1) NOT NULL default '-',
`car_dtins` date NOT NULL default '0000-00-00',
`car_dtmod` date NOT NULL default '0000-00-00',
`car_host` varchar(1) NOT NULL default '0',
PRIMARY KEY (`IDCart`),
UNIQUE KEY `prova` (`car_codprov`,`car_codcom`,`car_emisced`,`car_ide cart`),
KEY `IDAna` (`IDana`),
KEY `car_idecart` (`car_idecart`)
) ENGINE=InnoDB AUTO_INCREMENT=17372633 DEFAULT CHARSET=latin1;

I...... INTO `cartelle` VALUES (1,1,'022','C0','00000000000000000000','1179660.68
','P','1996-06-10','0000-00-00','015','130','5023758','0728466764','aaaaaaaaaa aa
aaaa','','2007-11-13','1996-09-20','',' ','C','0000-00-00','2011-05-14','1'),(2,
2,'022','C0','02220050026001326000','1382502.19',' I','2006-03-30','2006-03-31','
015','130','5EA5133','0080461092','bbbbbbbbbbbbbbb b','1','2008-01-27','2006-03-2
3','N',' ',' ','0000-00-00','2011-05-14','1'),(3,2,'022','C0','02220040036256112
000','239.72','E','2005-09-17','2005-05-31','015','130','4MA1747','0080461092','
cccccccccccccccc','1','2008-01-27','2005-08-18','N',' ',' ','0000-00-00','2011-0
5-14','1'),(4,3,'022','C0','00000000000000000000','1 18.78',' ','0000-00-00','000
0-00-00','015','130','4023982','0728404019','dddddddddd dddddd','','2007-11-13','
1997-06-04','',' ','C','0000-00-00','2011-05-14','1'),(5,3,'022','C0','000000000
00000000000','319910.46','P','1998-10-09','0000-00-00','015','130','8032670','07
28404019','eeeeeeeeeeeeeeee','','2007-11-13','1998-10-29','',' ','C','0000-00-00
','2011-05-14','1');
(ho tolto le parole CREATE e INSERT in quei punti - tantevolte il server le
interpreta come intrusi!)

[Santino83_02]

perchè non incolli tutto il dump in un file e poi alleghi il file?

[Botola59]

Ultima tabella
C.... T..... `cartelle_filtro` (
`IDCarfil` int(16) NOT NULL auto_increment,
`IDCart` int(16) NOT NULL default '0',
`carf_cf` varchar(16) NOT NULL default '-',
`carf_cdoute` varchar(2) NOT NULL default '-',
`carf_codprov` varchar(3) NOT NULL default '-',
`carf_codcom` varchar(3) NOT NULL default '-',
`carf_carte` varchar(7) NOT NULL default '-',
`carf_carminist` varchar(26) default '-',
`carf_codconc` varchar(3) NOT NULL default '-',
`carf_aaemiss` varchar(4) NOT NULL default '-',
`carf_numident` varchar(8) NOT NULL default '-',
`carf_id` varchar(2) NOT NULL default '-',
`carf_progr` varchar(3) NOT NULL default '-',
`carf_esifiltro` varchar(2) NOT NULL default '-',
`carf_impscad` decimal(13,2) NOT NULL default '0.00',
`carf_nososp` decimal(13,2) NOT NULL default '0.00',
`carf_riscuotibile` decimal(13,2) NOT NULL default '0.00',
`carf_numfermo` varchar(6) NOT NULL default '-',
`carf_dtfermo` date NOT NULL default '0000-00-00',
`carf_spostorico` varchar(1) default NULL,
PRIMARY KEY (`IDCarfil`),
UNIQUE KEY `prova` (`carf_codprov`,`carf_codcom`,`carf_carte`,`carf_c odconc`,`
carf_aaemiss`,`carf_numident`,`carf_id`,`carf_prog r`),
KEY `IDCarte` (`IDCart`)
) ENGINE=MyISAM AUTO_INCREMENT=11182789 DEFAULT CHARSET=latin1;

I.... INTO `cartelle_filtro` VALUES (1,1,'aaaaaaaaaaaaaaaa','C0','015','130','5
023758','','022','1995','30001278','51','000','09' ,'1179490.25','0.00','1179490.
25','ELATER','0001-01-01',''),(2,2,'bbbbbbbbbbbbbbbb','C0','015','130',' 5EA5133'
,'','022','2005','00260013','26','000','00','13825 02.19','0.00','1382502.19','EL
ATER','0001-01-01',''),(3,3,'cccccccccccccccc','C0','015','130',' 4MA1747','','02
2','2004','00362561','12','000','02','239.72','239 .72','0.00','ELATER','0001-01-
01',''),(4,6,'dddddddddddddddd','C0','015','130',' 9068674','','022','1999','4000
1160','45','000','26','674.31','0.00','674.31','EL ATER','0001-01-01',''),(5,8,'e
eeeeeeeeeeeeeee','C0','015','523','9000149','','02 2','1999','40001221','34','000
','26','530.10','0.00','530.10','ELATER','0001-01-01','');