sqlite3 sehr langsam und rechenintensiv

Installation und Anwendung von Datenbankschnittstellen wie SQLite, PostgreSQL, MariaDB/MySQL, der DB-API 2.0 und sonstigen Datenbanksystemen.
Antworten
PNS-Richi
User
Beiträge: 68
Registriert: Donnerstag 17. Januar 2008, 01:48

Hallo,

ich habe nun fast 1.000.000 Einträge in meiner Datenbank. Bei nur einer Abfrage läuft der Rechner auf 100% und es dauert einige Sekunden.

Das Script muss jedoch pro Sekunde >100 Anfragen abarbeiten können.

Hat wer eine Idee was ich machen kann, damit das alles ein wenig schneller und icht so viel Rechenleistung benötigt?

lg Richi
Benutzeravatar
gerold
Python-Forum Veteran
Beiträge: 5555
Registriert: Samstag 28. Februar 2004, 22:04
Wohnort: Oberhofen im Inntal (Tirol)
Kontaktdaten:

PNS-Richi hat geschrieben:ich habe nun fast 1.000.000 Einträge in meiner Datenbank. Bei nur einer Abfrage läuft der Rechner auf 100% und es dauert einige Sekunden.
Hallo Richi!

Wenn SQLite bei dieser Abfrage alle Datensätze durchlaufen muss, um zu einem Ergebnis zu kommen, dann wird die Abfrage mit jedem neuen Datensatz länger brauchen.

Wenn du aber nur ein paar Datensätze aus diesen 1 Mio. Datensätzen brauchst, dann kannst du dir mit Indizes an den richtigen Stellen helfen.

Wenn du für die meisten Abfragen nur einen gefilterten Teil deiner Tabelle brauchst, dann kannst du diesen gefilterten Teil in einer temporären Tabelle zwischenspeichern.

Wenn sich nicht umgehen lässt, dass für die Abfrage alle oder sehr viele Datensätze durchlaufen werden müssen, dann kannst du dir evt. helfen, indem du eine Zusammenfassung der benötigten Werten in einer anderen Tabelle zwischenspeicherst...

Wie du siehst, kommt es auf deine Daten an, wie diese organisiert sind und welche Informationen du über deine SQL-Abfrage herausholen möchtest.

1 Mio. Datensätze ist nicht wenig, aber auch noch nicht besonders viel.

mfg
Gerold
:-)

PS: Vielleicht findest du mit ``EXPLAIN`` oder ``EXPLAIN QUERY PLAN`` heraus, ob du deine Abfrage mit Indizes beschleunigen kannst.

.
Zuletzt geändert von gerold am Freitag 19. Dezember 2008, 00:19, insgesamt 1-mal geändert.
http://halvar.at | Kleiner Bascom AVR Kurs
Wissen hat eine wunderbare Eigenschaft: Es verdoppelt sich, wenn man es teilt.
Benutzeravatar
gerold
Python-Forum Veteran
Beiträge: 5555
Registriert: Samstag 28. Februar 2004, 22:04
Wohnort: Oberhofen im Inntal (Tirol)
Kontaktdaten:

derdon hat geschrieben:SQLite wurde nicht mit dem Ziel entwickelt, möglichst schnell zu sein.
Hallo derdon!

DBMS wie MySQL, MS SQL oder PostgreSQL wurden dafür entwickelt, mehrere Clients gleichzeitig bedienen zu können. Locking, welches nicht die ganze Datenbank/Tabelle sperrt. Kontrolle der Berechtigungen, usw.

Die ganzen Megafunktionen, die dafür zuständig sind um mehrere gleichzeitige Connections mit den richtigen Rechten bedienen zu können, fallen bei SQLite weg.

SQLite wurde mit dem Ziel entwickelt, besonders ressourcenschonend, klein und schnell zu sein. Dafür kann es nicht besonders gut mit mehreren gleichzeitigen Connections umgehen.

Allerdings muss ich auch einwenden, dass die großen DBMS mehr Möglichkeiten bieten, auch riesige Datenmengen zu verwalten. Auch die Optimierungsmöglichkeiten sind vielfältiger.

mfg
Gerold
:-)

PS: Das hier dürfte es besser erklären: http://www.sqlite.org/whentouse.html

.
http://halvar.at | Kleiner Bascom AVR Kurs
Wissen hat eine wunderbare Eigenschaft: Es verdoppelt sich, wenn man es teilt.
PNS-Richi
User
Beiträge: 68
Registriert: Donnerstag 17. Januar 2008, 01:48

Hallo,

Also im Grunde gibt es 2 Tabellen:

kat:
id, kat_name

blacklist:
id, kat_id, domain

Derzeit habe ich aber nur 1 Tabelle in verwendung und diese beinhaltet nur die Domains. Demnach ist der Verwaltungsaufwand eh gering.

Ich werde morgen das Komplex ausbauen und auch Indexing setzen. So wie ein paar Funktionen schreiben, damit sqlite etwas entlastet wird.

Ich würde gerne Wissen ob sqlite am ende ist für sein Einsatzgebiet, oder ob es mit >1.000.000 Datensätze zurecht kommt und ich mich einfach mit den Optionen spielen muss, sowie mich um das Indexing kümmern muss.

Wie gesagt im Endeffekt ist es eine sehr einfache Datenbank, mit sehr vielen Einträgen.

lg Richi
Benutzeravatar
gerold
Python-Forum Veteran
Beiträge: 5555
Registriert: Samstag 28. Februar 2004, 22:04
Wohnort: Oberhofen im Inntal (Tirol)
Kontaktdaten:

PNS-Richi hat geschrieben:ob es mit >1.000.000 Datensätze zurecht kommt und ich mich einfach mit den Optionen spielen muss, sowie mich um das Indexing kümmern muss
Hallo Richi!

Eine Datenbank ohne Indizes ist wie ein Bremsklotz.
http://www.sqlite.org/optoverview.html#like_opt

Du könntest auch versuchen, die Domäne umgeformt abzuspeichern. Denn um einen Index für LIKE nutzen zu können, darfst du keinen Platzhalter an erster Stelle benutzen.

Z.B. könntest du die Domäne "www.halvar.at" umgekehrt in die Datenbank speichern: "at.halvar.www"
Ich könnte mir vorstellen, dass du damit einen Index besser ausnutzen kannst. Damit kann auch eine Abfrage dieser Art einen Index nutzen:

Code: Alles auswählen

SELECT id, kat_id FROM blacklist WHERE domain LIKE 'at.halvar.%'
Achte auch darauf, dass die Domäne nur kleingeschrieben in die DB geschrieben wird. Vielleicht kannst du mit ``PRAGMA case_sensitive_like=ON;`` dann noch etwas mehr Geschwindigkeit raus holen. Das musst du aber ausprobieren. Ich weiß nicht, ob du damit wirklich etwas verbesserst.

mfg
Gerold
:-)
http://halvar.at | Kleiner Bascom AVR Kurs
Wissen hat eine wunderbare Eigenschaft: Es verdoppelt sich, wenn man es teilt.
Benutzeravatar
gerold
Python-Forum Veteran
Beiträge: 5555
Registriert: Samstag 28. Februar 2004, 22:04
Wohnort: Oberhofen im Inntal (Tirol)
Kontaktdaten:

Hallo Richi!

Es gibt dann noch etwas, was schneller als eine DB ist, wenn du einen eindeutigen Schlüssel (ohne Platzhalter) für die Suche verwenden kannst --> **shelve**

Aber die Einschränkung ist, dass du wirklich nur nach kompletten und eindeutigen Schlüsseln suchen kannst. Shelve ist wie ein Dictionary, welches die Daten auch auf der Festplatte hält. Shelve habe ich unter Anderem hier http://www.python-forum.de/topic-6157.html erklärt.

Die Kombination aus shelve und sqlite bringt dir wahscheinlich ziemlichen Speed. Zur groben Vorfilterung shelve und wenn shelve etwas gefunden hat, wird in der Datenbank der Rest an Informationen ausgelesen.

Du kannst auch mal **anydbm** ausprobieren. Wahrscheinlich ist das für deine einfache Vorfilterung noch schneller als **shelve**.

http://docs.python.org/library/persistence.html

mfg
Gerold
:-)
http://halvar.at | Kleiner Bascom AVR Kurs
Wissen hat eine wunderbare Eigenschaft: Es verdoppelt sich, wenn man es teilt.
PNS-Richi
User
Beiträge: 68
Registriert: Donnerstag 17. Januar 2008, 01:48

Hallo,

eine Anfrage die z.b. http://test.blub.com/ heißt wird schon von Python in blub.com umgewandelt. Also der Hostname wird ausgelesen. Das heißt das Derzeit keine suche mit LIKE von nöten ist. Dazu ist das Domain Feld so "eingestellt" das der Inhalt einmalig ist und sqlite sollte das automatisch "Indexen", oder irre ich mich?

Tut mir leid wenn meine Wortwahl heute etwas seltsam ist, aber ich habe tierisch Migräne :?

Ich werd jedenfalls das mit Indexing noch mal Prüfen und mich etwas einlesen.

Danke für deine Hilfe :)
Benutzeravatar
gerold
Python-Forum Veteran
Beiträge: 5555
Registriert: Samstag 28. Februar 2004, 22:04
Wohnort: Oberhofen im Inntal (Tirol)
Kontaktdaten:

Hallo Richi!

Code: Alles auswählen

#!/usr/bin/env python
# -*- coding: iso-8859-15 -*-

import os
import sys
import sqlite3

FILENAME = "database.sdb3"


def main():
    # nur zum Testen: DB löschen
    if os.path.isfile(FILENAME):
        os.remove(FILENAME)
    
    # Connection
    conn = sqlite3.connect(FILENAME)
    
    # Tabellen erstellen
    sql = """
    CREATE TABLE IF NOT EXISTS cat (
      id INTEGER PRIMARY KEY,
      name TEXT
    );
    CREATE TABLE IF NOT EXISTS blacklist (
      id INTEGER PRIMARY KEY,
      cat_id INTEGER,
      domain TEXT
    );
    """
    conn.executescript(sql)
    conn.commit()
    
    # Kategorientabelle befüllen
    sql = """
    INSERT INTO cat (id, name) VALUES (?, ?)
    """
    categories = [
        (1, u"Kategorie 1"),
        (2, u"Kategorie 2")
    ]
    conn.executemany(sql, categories)
    conn.commit()
    
    # Blacklisttabelle befüllen
    domains = [
        (1, u"halvar.at"),
        (2, u"uuu.at"),
        (1, u"bbb.at"),
        (2, u"ccc.de"),
        (1, u"musik.at"),
        (1, u"wahl.at"),
        (1, u"nerven.com"),
        (2, u"marius.com"),
        (1, u"python.org"),
    ]
    sql = """
    INSERT INTO blacklist (cat_id, domain) VALUES (?, ?)
    """
    conn.executemany(sql, domains)
    conn.commit()
    
    # Tabellen indizieren (WICHTIG)
    sql = """
    CREATE UNIQUE INDEX ix_cat_name ON cat (name);
    CREATE INDEX ix_blacklist_cat_id ON blacklist(cat_id);
    CREATE UNIQUE INDEX ix_blacklist_domain ON blacklist(domain);
    """
    conn.executescript(sql)
    conn.commit()
    
    # Einzelnen Datensatz abfragen
    cur = conn.cursor()
    sql = """
    SELECT 
      cat.name, blacklist.domain 
    FROM
      blacklist INNER JOIN cat ON blacklist.cat_id = cat.id
    WHERE
      blacklist.domain = ?
    """
    cur.execute(sql, [u"python.org"])
    row = cur.fetchone()
    cur.close()
    if row:
        print list(row)
        print
    
    # Mehrere Datensätze abfragen
    cur = conn.cursor()
    sql = """
    SELECT 
      cat.name, blacklist.domain 
    FROM
      blacklist INNER JOIN cat ON blacklist.cat_id = cat.id
    WHERE
      blacklist.domain LIKE '%.com'
    """
    cur.execute(sql)
    for row in cur:
        print list(row)
    cur.close()
    
    # Connection schließen
    conn.close()
    
if __name__ == "__main__":
    main()
mfg
Gerold
:-)
http://halvar.at | Kleiner Bascom AVR Kurs
Wissen hat eine wunderbare Eigenschaft: Es verdoppelt sich, wenn man es teilt.
PNS-Richi
User
Beiträge: 68
Registriert: Donnerstag 17. Januar 2008, 01:48

Vielen Dank, damit hast du mir sqlite3 viel näher gebracht.

lg Richi
Benutzeravatar
gerold
Python-Forum Veteran
Beiträge: 5555
Registriert: Samstag 28. Februar 2004, 22:04
Wohnort: Oberhofen im Inntal (Tirol)
Kontaktdaten:

Hallo Richi!

Wenn du sehr schnell herausfinden musst, ob eine Domäne in einer Liste ist, dann bist du mit *anydbm* wahrscheinlich schneller. Bitte probiere beide Varianten (sqlite und anydbm) mit deinen Daten aus und berichte uns hier, wie schnell die Varianten sind.

Code: Alles auswählen

#!/usr/bin/env python
# -*- coding: iso-8859-15 -*-

import os
import anydbm

CATEGORIES_FILENAME = "categories.anydbm"
BLACKLIST_FILENAME = "blacklist.anydbm"


def main():
    # nur zum Testen: DBs löschen
    if os.path.isfile(CATEGORIES_FILENAME):
        os.remove(CATEGORIES_FILENAME)
    if os.path.isfile(BLACKLIST_FILENAME):
        os.remove(BLACKLIST_FILENAME)
    
    # Datenbanken öffnen
    categories = anydbm.open(CATEGORIES_FILENAME, flag = "c")
    blacklist = anydbm.open(BLACKLIST_FILENAME, flag = "c")
    
    # Kategorien befüllen
    new_categories = {
        "1": "Kategorie 1",
        "2": "Kategorie 2",
    }
    categories.update(new_categories)
    
    # Blacklist befüllen
    new_domains = {
        "halvar.at": "1",
        "uuu.at": "1",
        "bbb.at": "2",
        "ccc.de": "1",
        "musik.at": "2",
        "wahl.at": "1",
        "nerven.com": "2",
        "marius.com": "1",
        "python.org": "2",
    }
    blacklist.update(new_domains)
    
    # Nachfragen ob es die Domäne in der Blacklist gibt
    print "'python.org'? ", blacklist.has_key("python.org")
    
    # Kategorie von python.org herausfinden
    print "Kategorie von 'python.org': ", categories[blacklist["python.org"]]
    
    # Datenbanken schließen
    categories.close()
    blacklist.close()

if __name__ == "__main__":
    main()
Wie bereits geschrieben, ist anydbm für einfache Schlüsselvergleiche (wahrscheinlich) schneller als sqlite. Aber sobald du keinen ganzen Schlüssel mehr hast, mit dem du die Daten aus anydbm herausholen kannst, bist du gezwungen, alle Datensätze zu durchlaufen. Ab dem Moment ist sqlite mit Indizes (wahrscheinlich) schneller.

mfg
Gerold
:-)
http://halvar.at | Kleiner Bascom AVR Kurs
Wissen hat eine wunderbare Eigenschaft: Es verdoppelt sich, wenn man es teilt.
PNS-Richi
User
Beiträge: 68
Registriert: Donnerstag 17. Januar 2008, 01:48

Hallo,

das werde ich machen so bald ich mehr Zeit habe. Also im laufe der nächsten Wochen. Ich wollte sowieso mehrere Datenbank Backends unterstützen.

Dazu habe ich nun ein Dictionary das als Temporäre Whitelist dient und Domains die 1 Stunde lang nicht aufgerufen wurde wieder entfernt. Somit gibt es eine limitierte Whitelist und die sqlite Datenbank wird entlasstet. Wobei es auch ohne dem sehr zügig läuft, auch die CPU ist nur gering belastet.
Antworten