---

---

On Wed, 17 Nov 2004 23:12:05 +0100, Michael Sig Birkmose wrote:

> Jeg har btree index paa ts og objectid.

Bruger du MySQL, Oracle, Access, PostgreSQL, Interbase, MS-SQL,
DB2, ...?

Der er forskellige optimeringer for de forskellige databaser.

Jeg har jævnligt været nødt til at omskrive SQL statements, som
optimizeren bare ikke fattede at optimere. Ikke fordi det var
dårlig kode, men lige præcis den situation, som jeg bragte den i
fandt den ikke nogen optimeret løsning på.

Typisk er det, at databasen af uforklarlige årsager undlader at
bruge et index (eller flere). Det kunne også være tilfældet hos
dig. Og i dette tilfælde, så kan man tit forklare databasen
nærmere om sagen, f.eks. i Oracle med optimizer hints i
kommentarer til SQL'en.

Sådan ret generelt SQL, så er EXPLAIN din rigtigt gode ven, når
det går for langsomt. Her kan du straks se om query'en vil bruge
de index'er, som du havde i tankerne, da du skrev SQL'en.

> Men men hvis jeg nu laver et query hvor jeg vil have alle
> rapporter for et given objekt inden for en dag opstaar der
> problemmer. Pseudo query: SELECT * FROM log WHERE ts > '1dag
> siden' AND objectid=et_eller_andet

Prøv at lave et compound (sammensat!? er ikke god til IT-dansk)
index på både ts og objectid felterne, med ts som første felt.

'CREATE INDEX log_tsoi ON log (ts, objectid)' er vist syntaksen.

Du skriver, at dine index'er er btree - check hvilke typer din
database ellers kan håndtere og se om nogle af dem er
passende/bedre for denne query.

> Hvis den teori holder stik, saa er mit sporgsmaal selvfoelgelig - hvordan
> kan man designe sig ud af dette?

Svært at sige - din teori med sekventialiteten er nemlig også
database-afhængigt Men en subselect er måske hurtigere:

SELECT *
FROM (
SELECT *
FROM log
WHERE ts > '1dag siden'
)
WHERE objectid=et_eller_andet;

Her tvinger du databasen til først at select'e de rækker du ved
går hurtigt (sub-select på ts). Bagefter, så køres select'en på
objectid kun mod de rækker, som sub-select'en fandt. Det kan være
både hurtigere og langsommere - og måske er der præcist ingen
forskel overhovedet, hvis det er en rå SQL-optimizer du har dig.

Mht. designet, så start med at prøve de ting jeg nævner - måske
behøver du slet ikke røre ved det.

--
Stig - remove the 'no's to send me mail
Synchronize your bookmarks: http://syncit.goth.dk/

---

---

On Thu, 18 Nov 2004 19:56:00 +0100, Michael Sig Birkmose wrote:

> MEn generelt virker det lidt som-om at b-tree indexes maaske ikke helt er
> hvad jeg har brug for?

Når du benytter større-end og mindre-end prædikater, burde
b-træ-indekser være gode.

> Jeg stødte på begrebet bitmap indexes som lyder lidt interessant

Jeg tvivler på, at bitmap-indexes vil passe godt til de datatyper, du
søger på, men jeg har ingen erfaring med dem.

> er ikke lige stødt på nogen open source / billig db som understøtter
> det?

Pas.

--
Greetings from Troels Arvin, Copenhagen, Denmark

---

On Thu, 18 Nov 2004 19:56:00 +0100, Michael Sig Birkmose wrote:

> Efter at have læst lidt på tingenen synes jeg at kunne forstå at b-tree
> indexes ikke kan kompineres

Hmm, jeg har fortrængt for meget om MySQL til at kunne svare dig,
men det skulle ikke undre mig. En af databasens fornemmeste
opgaver er ellers netop, at kunne kombinere eksisterende
indexeringer på kryds og tværs. Men det går heller ikke altid
godt, når databasen er dyr.

> Så her har compound b-tree index hjulpet mig en del. Men problemmet er så
> når jeg skal joine tabeller, og lave mange krav så som felt1 > blah and
> felt2 > blah2 and felt3 > blah3... Her bliver det jo hurtigt ret træls at
> skulle have en masse compound indexes? Og ret ufleksibelt

Jeps. Og dyrt i drift, siden det koster performance at opdatere
en lang række index'er, hver gang du insert'er data.

> Dit forslag med subqueries hjalp faktisk ogsaa en hel del - udfra explain
> ser det ud som om det giver mysql en bedre chance for at bruge indexes.

Godt at høre, men det lyder som om du stadigvæk er langt fra
målet?

> MEn generelt virker det lidt som-om at b-tree indexes maaske ikke helt er
> hvad jeg har brug for? Jeg stødte på begrebet bitmap indexes som lyder
> lidt interessant, men er ikke lige stødt på nogen open source / billig db
> som understøtter det?

Jeg checkede lige Postgres 7.4, men her var kun B-tree, R-tree,
GiST & Hash. Men Oracle har bitmap og den er nok egentligt
også mere velegnet til din halvtunge applikation end MySQL.

Du kan frit downloade CD'ere via oracle.com, hvis du vil prøve
den af. Mht. licensen, så er priserne blevet knapt så ublu de senere
år..

Kig også på Firebird/Interbase, den er ganske kapabel & gratis.

--
Stig - remove 'nospam.' to send me mail
Synchronize your bookmarks: http://syncit.goth.dk/

---

---

On Thu, 18 Nov 2004 23:15:18 +0100, Michael Sig Birkmose wrote:

> Nogle af de felter hvor der er yderlige prædikater
> vil have rimeligt få mulige værdier. Og der forstod jeg at bitmap index
> skulle være gode?

Det er absolut ikke utænkeligt, idet bitmap-indekser jo er en slags
kompakte "afkrydsningstabeller" for hver enkelt, gemt værdi: Er værdien
X? Er værdien Y? Er værdien Z? ...

Jeg kan ikke på stående fod (eller snarere: siddende ...) finde noget
open source DBMS, der tilbyder bitmap indekser.

Understøtter MySQL hash indekser? - I så fald kunne du også overveje
dem til visse af dine kolonner. I lærebogsmæssig forstand er hash
indekser gode til lighedsforespørgsler (i modsætning til b-træer, der
særligt er gode til ulighedsforespørgsler); om det holder i praksis må
komme an på en prøve med det aktuelle DBMS og de konkrete data.

--
Greetings from Troels Arvin, Copenhagen, Denmark

---

Troels Arvin wrote:

> I lærebogsmæssig forstand er hash
> indekser gode til lighedsforespørgsler (i modsætning til b-træer, der
> særligt er gode til ulighedsforespørgsler);

Sådan er det også i praksis, forudsat der er en god balance mellem
hashalgoritmen og nøgleværdierne.

--
Med venlig hilsen
Stig Johansen

---

---

On Thu, 18 Nov 2004 23:23:52 +0100, Michael Sig Birkmose wrote:

> > Du kan frit downloade CD'ere via oracle.com
> Hmm jeg kan se noget med en eller anden given pris per "named user" -
> hvordan skal det lige fortolkes?

Det er per database bruger. Så hvis din applikation kan nøjes med
at bruge een database-login til alt database-arbejdet og
ikke behøver sikkerhed i mere end ét niveau, så er den hjemme.

Med mindre, selvfølgelig, at Oracle minimum vil sælge 5 named users
per installation eller sådan noget..

> F.eks. hvis man har en web applikation
> der bruger db'en er det så én named user

Ja, altid mindst en. Sommetider to eller flere. F.eks. kan der
være en db-bruger (named user) til de alm. webapp brugere og en
anden db-bruger (named user#2), som bruges når man går ind under
http:.../webapp/admin/

Sidstnævnte har så udvidede beføjelser og kan f.eks. insert'e nye
rækker i (webapp-)users-tabellen og andet sjov.

> eller er være bruger af web applikationen en named user?

Normalt ikke, fordi de brugere er webapp-brugere og antageligt i
webapp'ens brugerliste. Men for at komme til brugerlisten, så skal
webapp'en logge på databasen og hertil skal bruges en "named
user".

> > Kig også på Firebird/Interbase, den er ganske kapabel & gratis.
> Og generelt virker den lidt langsomere med den samme mængde data og de
> samme indexes og de samme queries?

Det tror jeg gerne. MySQL er temmelig hurtig i mange henseender,
men til gengæld kan den ikke så meget som de andre.

> Jeg kan sikkert skrue noget mere og få bedre performance - har
> faktisk ikke prøvet at se med subqueries - vil lige prøve det i
> morgen.

Go' idé. Men giv også PostgreSQL en chance. Du kan nemt tune den
ved at tilrette postgresql.conf til dit formål (og alle options
er veldokumenterede!). 'fsync' og 'wal_sync_method' kan der
pilles ved til dit formål - de får databasen til at flyve under
de rigtige omstændigheder på bekostning af lidt datasikkerhed.
Men jeg forstod, at du får data nok, hele tiden

--
Stig - remove 'nospam.' to send me mail
Synchronize your bookmarks: http://syncit.goth.dk/

---

2004-11-17 Michael Sig Birkmose wrote
>
> Jeg arbejder paa et projekt hvor jeg indsaetter positions rapporter for
> bevaegende objekter i en database, i stoerrelsesordnene 2-3 millioner per
> dag.

Ud over de guldkorn Stig giver så vil jeg lige bidrage med lidt
praktisk erfaring.

Hvis dit projekt tager en uge at skrive/udvikle, og det handler
om at behandle data der efterfølgende akumuleres over længere tid,
så kan det let vise sig værdifuldt at bruge et par timer på at
kode en dummy-data-generator, og så lade den fylde databasen op
natten over.

Næste morgen har du en _masse_ data du kan afprøve dine teorier på.

Det kan jo også tænkes at du, med et års syntetiske data, kommer
til den erkendelse at der er noget fundamentalt galt med dit design.

Ovenstående "prøv det" metode kan langt fra kaldes datalogisk korrekt,
men i praktis kan den være fiks nok. (og real world kunder er som oftest
ret ligeglade med datalogi alligevel


/Morten - Som har testet de underliggende databaser i Tele2ADSL
med 50 millioner syntetiske kunder. Marketing var
henrykte....

---

---

On Wed, 17 Nov 2004 23:12:05 +0100, Michael Sig Birkmose wrote:

> Jeg har btree index paa ts og objectid.

Forøvrigt.. Du har vel fået kørt ANALYZE kommandoen på passende
tidspunkter, ikk'?

Uden analyze, så kan mange SQL-databaser ikke anvende index'erne
ordentligt og nogle "ser" f.eks. slet ikke et nyt index, før man
har kørt en analyze.

Det samme gælder ved større ændringer i datamængden, siden
optimizeren typisk tager udgangspunkt i data indsamlet af
analyze. Så hvis gamle analyze-data indikerer, at der er 80
rækker i tabellen, så beslutter optimizeren måske at en
sekventiel scan af tabellen er hurtigere end at gå via et index.

Problemet kommer så, når du tilsætter 2.000.000 rækker til din
tabel og kører din query uden først at have kørt en ny analyze.
Optimzeren ser stadigvæk kun 80 rækker og vælger selvfølgelig en
sekventiel scan igen. Her bliver der så tid til kaffe/cola og en
smøg, mens der scannes

--
Stig - remove the 'no's to send me mail
Synchronize your bookmarks: http://syncit.goth.dk/

---

> Forøvrigt.. Du har vel fået kørt ANALYZE kommandoen på passende
> tidspunkter, ikk'?

Jep :(
Jeg har nu opsamlet omkring 3.000.000 rækker. Har kørt en analyze table på
den tabel som jeg laver et query på.

Følgende query returnerer ca 950 rækker, og tog 12 minutter at eksekvere(!)


select * from tbllog l WHERE l.lat > 56.3103 and l.lat < 56.4686 AND l.lon
> 11.2053 and l.lon < 11.3218

En explain på ovenstående query giver følgende info:

[select_type] => SIMPLE
[table] => l
[type] => range
[possible_keys] => tblshippos_idx3
[key] => tblshippos_idx3
[key_len] => 9
[ref] =>
[rows] => 78659
[Extra] => Using where

Index tblshippos_idx3 er et compound index på lat og lon.

Jeg kan ikke forstå hvorfor det skal tage så langt tid? Og der er jo fint
nok et index?
Og hvorfor siger explain at der er 78659 rækker? Burde indexet ikke fortælle
med det samme at der er 950 rækker, og så vil man kunne gå direkte ind og
finde dem på disken.

Et andet query:

select * from tbllog l WHERE l.lat > 54.2691 and l.lat < 54.5683 AND l.lon >
11.9761 and l.lon < 12.2933

Returner ca 170.000 rækker, men tog "kun" 60 sekunder (stadig alt for lang
tid efter min mening?)

Explain på denne query giver:

[select_type] => SIMPLE
[table] => l
[type] => ALL
[possible_keys] => tblshippos_idx3
[key] =>
[key_len] =>
[ref] =>
[rows] => 3506473
[Extra] => Using where

Her ser det ud til at der bliver lavet en fuld table scan? Og hvorfor tag
den så kortere tid end ovenstående query?


Endelig prøvede jeg at lave en select * from tbllog

3515370 - på ca. 4 minutter.

Nogen ide om hvorfor jeg får denne underlige performance?

Det skal lige siges at der ikke bliver indsat nogle rækker i db'en mens jeg
laver mine queries