Optimalisering av uttrekk

["Pål G. Larsson"]

Sitter med en relativt stor flat-fildatabase - 4,5 millioner records.

Enkle uttrekk er raske (5s -15s) i Mysql, men når jeg skal ha mer
komplekse data ut tar det tid (timer).
Databasen innholder bl.a. et ID-felt og et kode-felt. Det er omtrent 1
mill ulike ID'er og ulike 50 koder. Jeg ønsker å se hvilke andre koder
ID'er som har en spesiell kode, f.eks. 7.

Dette har jeg løst med
'create table p7id select id as p7id where kode=7'
og så
'create table data_p7mer select * from p7id,data where p7id=id'

Med et neste hundre tusen records p7id tar dette timer. Forslag til
optimalisering?

Pål

[Odd Skjaeveland]

Pål G. Larsson:
[...]

> Databasen innholder bl.a. et ID-felt og et kode-felt. Det er omtrent 1
> mill ulike ID'er og ulike 50 koder. Jeg ønsker å se hvilke andre koder
> ID'er som har en spesiell kode, f.eks. 7.
>
> Dette har jeg løst med
> 'create table p7id select id as p7id where kode=7'
> og så
> 'create table data_p7mer select * from p7id,data where p7id=id'
>
> Med et neste hundre tusen records p7id tar dette timer. Forslag til
> optimalisering?

Det er uklart for meg hva det betyr at du "ønsker å se hvilke andre
koder ID'er som har en spesiell kode, f.eks 7."

Andre koder enn hva?
Første tanke var at du ville ha ID'er for andre koder enn f.eks 7, men
det rimer ikke med løsningen du beskriver.

Har du prøvd løsningen du beskriver? Jeg er i så fall interessert i
hva Mysql sier til første av dine to statements.

Du utfører massiv skriving av data til disk samtidig som formålet
angivelig kun er "å se". Det er kanskje ikke urimelig at det tar tid
å skrive hundretuseinvis av dataposter som hver kanskje har vesentlig
mer informasjon enn id-feltet du angivelig er interessert i.

Jeg forstår høyst sannsynlig ikke hva du prøver å oppnå, kan du være
mer presis og konkret?

--
Odd Skjaeveland odd.p...@hamso.no

["Pål G. Larsson"]

Odd Skjaeveland skrev:

> Pål G. Larsson:
> [...]
>> Databasen innholder bl.a. et ID-felt og et kode-felt. Det er omtrent 1
>> mill ulike ID'er og ulike 50 koder. Jeg ønsker å se hvilke andre koder
>> ID'er som har en spesiell kode, f.eks. 7.
>>
>> Dette har jeg løst med
>> 'create table p7id select id as p7id where kode=7'
>> og så
>> 'create table data_p7mer select * from p7id,data where p7id=id'
>>
>> Med et neste hundre tusen records p7id tar dette timer. Forslag til
>> optimalisering?
>
>
> Det er uklart for meg hva det betyr at du "ønsker å se hvilke andre
> koder ID'er som har en spesiell kode, f.eks 7."
>
> Andre koder enn hva?
> Første tanke var at du ville ha ID'er for andre koder enn f.eks 7, men
> det rimer ikke med løsningen du beskriver.

Hvis vi tenker oss bare 2 felt id og kode, kan databasen se slik ut

1 7
2 9
3 9
1 7
1 9
2 7
4 12

Her har ID 1 kode 7 noen ganger, men også kode 9. ID har ikke 7, bare 9.
Jeg vil vite hvilke andre koder de har som har kode 7. Dvs kode 1 har 9
i tillegg, 2 har 9 i tillegg mens 3 og 4 ikke skal regnes med da de ikke
har 7.

>
> Har du prøvd løsningen du beskriver? Jeg er i så fall interessert i
> hva Mysql sier til første av dine to statements.
>

create table p7id select id as p7id FROM DATA where kode=7
- rettet, fremdeles etter hodet.

> Du utfører massiv skriving av data til disk samtidig som formålet
> angivelig kun er "å se". Det er kanskje ikke urimelig at det tar tid
> å skrive hundretuseinvis av dataposter som hver kanskje har vesentlig
> mer informasjon enn id-feltet du angivelig er interessert i.

Jeg skal etterhvert trekke ut distribusjoner over tid (år) og gruppere
informasjonen på andre elementer, bare jeg har "ryddet" i data. Derfor
er jeg avhengig av å skrive nye tabeller. Har vurdert om jeg skulle
gjøre jobber per år allerede i første omgang, da jeg tror at det tar ned
tidsbruk 10 ganger e.l. (4 årganger)
Har allerede laget endel "småuttrekk" til tekstfiler hvor kravet bare er
at det går inn i Excel (<65536 linjer)

>
> Jeg forstår høyst sannsynlig ikke hva du prøver å oppnå, kan du være
> mer presis og konkret?

Det dreier seg altså om 4 års data, grovt sett med 4 typer
informasjon:ID kode, hvorfor og kvantitet
Data har felter med ulik grad av finkornethet, så det er betydelig
redundans i data, men problemet er nok mer krysskoblingen av millioner
av datapunkter som i mitt utgangspunkt kommer fra en og samme flate fil.

Er forresten ferdig med 2 av 3 koder, men forventer at jeg må gjøre
flere "oppryddingsuttrekk".

Er det mye hjelp i å slå av indexering?
Er det andre Mysql-db-motorer som håndterer mine data bedre?

>

["Pål G. Larsson"]

Nytt forsøk

> create table p7id select id as p7id FROM DATA where kode=7

create table p7id select distinct id as vp7id FROM DATA where kode=7

Trenger hver ID bare én gang naturligvis. Jeg har gjort det riktig -
glemte det her.

Pål

[Odd Skjaeveland]

Pål G. Larsson:

> create table p7id select distinct id as vp7id FROM DATA where kode=7

Har Mysql nok minne til rådighet? Swapper den mens 'create table
data_p7mer select * from p7id,data where p7id=id' kjører?

Kan du finne ut om det er ekstrahering av data eller skriving til disk
som er den overordnede tidstyven?

Hvor lang tid tar f.eks en modifisert utgave av 'create table

data_p7mer select * from p7id,data where p7id=id'

SELECT count(*) FROM p7id,data WHERE p7id=id

Og ikke minst: stemmer antallet med det du forventer deg? Hvis
antallet er større enn SELECT count(*) FROM data, har du et eller
annet kartesisk produkt på gang, og det kan jo fort bli krevende
nok...

Gjør det noen forskjell om du kjører denne

SELECT count(*) FROM data
WHERE id IN (SELECT DISTINCT id FROM data WHERE kode=7)

Opprett indeks på id og på kode (i tabellen 'data') hvis indeksene
ikke alt finnes, og prøv igjen.

Går
SELECT * FROM data
WHERE id IN (SELECT DISTINCT id FROM data WHERE kode=7)

bedre eller verre enn

SELECT * FROM p7id,data WHERE p7id=id

Gjør det noen forskjell om du bruker en self join på tabellen 'data'
i stedet for å først generere tabellen 'p7id' og deretter gjøre en
join mellom 'data' og 'p7id'? (Du må evt fintenke filteret)

Med hensyn til indekser, regner jeg med at create table fører til en
ikke-indeksert tabell, og slik bør det være her. (Du kan selvsagt
indeksere den ferdige tabellen med tanke på spørringer mot den.) Min
kommentar var mer rettet mot at kildetabellen 'data' trolig bør være
indeksert slik som angitt ovenfor, men hvis det er skriving til disk
som tar mesteparten av tiden, er det ikke sikkert det hjelper
allverden.

--
Odd Skjaeveland odd.p...@hamso.no

["Pål G. Larsson"]

Takk for utfyllende svar

Har startet noen tester nå, så jeg kommer tilbake med mer resultater.
HAr i mellomtiden oppdaget at jeg har "råte" i disken, så jeg kommer nok
til å sette opp hele maskinen på nytt med nye disker.

Her er litt
select kode,count(*) from data_p18mer group by kode;

Data_p18mer er en database med alle IDer og all dets informasjon for de
som har kode 18 minst 1 gang. Her er resultatet:
+--------------------+----------+
| kode | count(*) |
+--------------------+----------+
| | 1494491 |
| 10 | 54 |
| 11 | 1 |
| 12 | 200 |
| 13 | 3 |
| 14 | 9 |
| 15 | 1 |
| 16 | 34647 |
| 17 | 15 |
| 18 | 1209125 |
| 19 | 3 |
| 2 | 57 |
| 22 | 5633 |
| 27 | 23 |
| 28 | 7 |
| 29 | 1 |
| 3 | 1 |
| 31 | 8529 |
| 33 | 2 |
| 36 | 79919 |
| 38 | 224 |
| 39 | 201 |
| 4 | 1 |
| 41 | 1 |
| 5 | 35 |
| 6 | 12 |
| 611 | 1 |
| 7 | 370834 |
| 71 | 1 |
| 8 | 8 |
| 9 | 28893 |
| 90 | 42 |
| 99 | 1202 |
+--------------------+----------+
33 rows in set (5.73 sec)

Jeg synes dette er imponerende raskt, og det står i sterk kontrast til
de mange timene det tar å lage f.eks data_p18mer.

Har sjekket 'top' som ikke gir inntrykk av bruk av swap. Nedenfor ser du
alle filene. 'data' innholder alle data p* innholder distict id. 'data*'
er alle record av gitt kode. De med forlengelsen 'mer' innholder alle
andre koder i tillegg for de samme id'er.

12 9230 2007-12-20 22:11 data.frm
331884 339509372 2007-12-17 22:51 data.MYD
4 1024 2007-12-20 22:11 data.MYI
12 9230 2007-12-25 15:31 data_p18.frm
12 9262 2007-12-27 11:41data_p18mer.frm
254492 260335052 2007-12-27 18:48 data_p18mer.MYD
4 1024 2007-12-27 18:48 data_p18mer.MYI
91500 93596684 2007-12-25 15:31 data_p18.MYD
4 1024 2007-12-25 15:31 data_p18.MYI
12 9262 2007-12-26 16:05 data_p36mer.frm
39528 40431200 2007-12-26 18:13 data_p36mer.MYD
4 1024 2007-12-26 18:13 data_p36mer.MYI
12 9230 2007-12-25 15:31 data_p7.frm
12 9262 2007-12-26 09:56 data_p7mer.frm
206956 211708936 2007-12-26 21:42 data_p7mer.MYD
4 1024 2007-12-26 21:42 data_p7mer.MYI
75644 77374272 2007-12-25 15:31 data_p7.MYD
4 1024 2007-12-25 15:31 data_p7.MYI
4 65 2007-11-12 20:30 db.opt
12 8562 2007-12-26 16:01 p18id.frm
444 448014 2007-12-26 16:01 p18id.MYD
4 1024 2007-12-26 16:01 p18id.MYI
12 8562 2007-12-26 16:01 p36id.frm
68 65205 2007-12-26 16:01 p36id.MYD
4 1024 2007-12-26 16:01 p36id.MYI
12 8562 2007-12-25 21:17 p7id.frm
476 481572 2007-12-25 21:17 p7id.MYD
4 1024 2007-12-25 21:17 p7id.MYI

Hadde håpet at det å sette argument fra minste fil først skulle gjøre at
den filen (iallefall index-info) ble lest inn i RAM hvoretter det ble en
rask beregning.

Skal sjekke litt utover kvelden og litt i morgen.

Hilsen

Pål

Odd Skjaeveland skrev:

["Pål G. Larsson"]

>
> SELECT count(*) FROM data
> WHERE id IN (SELECT DISTINCT id FROM data WHERE kode=7)

Kuttet den etter ca. 18 timer. Har nå kjørt med tillegget
YEAR(dato)=2004. Går fremdeles etter 2,5 timer. Kommer til å bryte det
nå for å serve maskinen.

Hvis systemet bruker indexene burde jobben vært gjort på under ett
minutt?? Det er noe som jeg gjør grunnleggende feil. Ser ut som det
søkes igjennom hele filen for hver ID.

Pål