Nach Serverwechsel DB langsam

MySQL, PostgreSQL, SQLite
razy
Posts: 12
Joined: 2008-01-16 20:14

Nach Serverwechsel DB langsam

Post by razy » 2008-01-16 20:24

Hi,

ich hab mein Server gewechelt vom VServer zum echten Server. Nunja ich HOFFE das es die Richtige Entscheidung war und ich nur eine dumme konfig habe, weil mit den VServer war meine MySQL Datenbank wesentlich schneller!

Also hier einmal die Eckdaten:
2x Tabellen mit je 800 MB langsam steigend je 2.5 Millionen Einträge. Ich habe recht viele indexe genommen nach dem Motto "Mehr bringt mehr" . Hab InnoDB dafür verwendet. Alle anderen Tabellen sind MyIsam (?)
Server: 1 GB Ram
Nun die 2 Tabellen sind recht hoch beansprucht. Hauptsächlich SELECT´s.

Ich habe hier in Forum einiges Versuch aber mittlerweile hab ich angst das nochmal zu versuch überhaupt die my.cnf zu öffnen. Das letzte mal hab ich mir damit Plesk zerschossen (Tabellen waren einfach weg) ... und hab mein Server neu aufgesetzt...

Ich benutze recht einfache Querys ohne JOIN oder sonstwas... Hier ein Beispiel:

SELECT bla FROM blaaaaa WHERE range = 4 AND num = 5 AND rows = 3 AND session = "dsdfklsdpok!" AND blaaa = 34 LIMIT 1

Es läuft darauf hinauß das ich wissen will ob ein einmaligen Eintrag vorhanden ist. Alle Felder sind indexe. Sooo warum dauert die Abfrage so lang (1-3 sek bis ich die Seite sehe) :)

my.cnf = Standard dinge, nur innodb teile hab ich aktiviert. Sonst nix verändert.

Roger Wilco
Administrator
Administrator
Posts: 5924
Joined: 2004-05-23 12:53

Re: Nach Serverwechsel DB langsam

Post by Roger Wilco » 2008-01-16 20:55

Was sagt dein Slow Query Log? Was sagt EXPLAIN?
http://dev.mysql.com/doc/refman/5.0/en/query-speed.html schon gelesen?
http://www.rootforum.org/forum/view ... 23&t=36343 schon gelesen?

User avatar
Joe User
Project Manager
Project Manager
Posts: 11137
Joined: 2003-02-27 01:00
Location: Hamburg

Re: Nach Serverwechsel DB langsam

Post by Joe User » 2008-01-16 20:58

Zuviele Indexe sind kontraproduktiv und können die Abfragen verlangsamen. Gehe Deine Queries mit EXPLAIN durch und entferne nutzlose Indexe. Zudem hält MySQL Indexe möglichst im Speicher, was bei nutzlosen Indexen zu Lasten des gesamten Systems geht. Apropos Speicher: Auch InnoDB ist sehr speicherfreudig, was bei 1,6GB Daten plus Overhaed und nur 1GB RAM auch nach Hinten losgehen kann. Bei den Datenmengen und dem vergleichsweise geringem Speicher kommen dann auch sehr viele HDD-Zugriffe zusammen, wodurch das gesamte System zusätzlich belastet wird.
Kurz: Du benötigst allein für MySQL mindestens 2GB RAM und mindestens eine dedizierte HDD. Zudem würde ich einen Core2Duo/Xeon mit 2000MHz bis 2400MHz Taktfrequenz empfehlen. Aber auch auf solchen Maschinen ist das Optimieren der Queries das wichtigste und effektivste Tuning...
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

razy
Posts: 12
Joined: 2008-01-16 20:14

Re: Nach Serverwechsel DB langsam

Post by razy » 2008-01-16 21:02

Hi, ja wo finde ich die Log datei? EXPLAIN sagt sowas:

id , select_type, table , type , possible_keys , key , key_len , ref, rows , Extra
1 ,SIMPLE , blaaa ,ref ,myitemid,itemid,sessid,num ,sessid, 14 ,const ,1 ,Using where

Ich weiß nicht ... kann damit nicht so viel anfangen.. ich mein er sagt mir doch nur was in Query doch eh ersichtlich ist?

Hier mal den Query:
SELECT id FROM blaaa WHERE num="10" AND myitemid="133" AND itemid="510" AND preis="3000" AND sessid="6362b|09a|10" LIMIT 1

Wo finde ich den die log? Naja neuer Server geht jetz wohl nicht mehr wegen 2 Jahre Laufzeit... :-/

Roger Wilco
Administrator
Administrator
Posts: 5924
Joined: 2004-05-23 12:53

Re: Nach Serverwechsel DB langsam

Post by Roger Wilco » 2008-01-16 21:25

Joe User wrote:Kurz: Du benötigst allein für MySQL mindestens 2GB RAM und mindestens eine dedizierte HDD.

Naja, das würde ich so nicht unterschreiben. Mehr Speicher ist bei Datenbanken nie schlecht, aber in diesem Fall glaube ich nicht, dass es ausschließlich daran liegt. Zumal er meint, die Datenbank sei auf einem V(!)-Server schon besser gelaufen. Da ist eher die Konfiguration im Eimer.

Razy wrote:Wo finde ich den die log?

http://dev.mysql.com/doc/refman/5.0/en/ ... y-log.html

razy
Posts: 12
Joined: 2008-01-16 20:14

Re: Nach Serverwechsel DB langsam

Post by razy » 2008-01-16 21:56

Naja ich hab den Query der langsam läuft ja bereits lokalisiert :-) Da brauch ich die log nicht mehr, es ist einfach die InnoDB Tabelle die ein bisschen lahmt. Nun ich werde wohl versuchen bessere Querys hinzubekommen ... Ansonsten muss ich meine gewohnte strategie anweden und die Tabellen aufteilen in mehrere Tabellen ... Nur eigentlich hat InnoDB doch mit mehren million datensätze kaum Probleme bzw. immer Logarithmischen Aufwand. Will ungern die Config ändern, wie gesagt, beim letzten mal ging Plesk dabei flöten und bin nicht so der Linux gott das ich wüsste wie man das neu installiert ^^

braindead
Posts: 250
Joined: 2002-10-22 09:49
Location: vorm Rechner

Re: Nach Serverwechsel DB langsam

Post by braindead » 2008-01-16 22:53

Um die Configänderung wirst du aber wahrscheinlich nicht herumkommen wenn du ordentliche Performance willst. Um das aufteilen auf mehrere Tabellen bringt dir nicht weil du dadurch nicht weniger Daten hast. Warum geht Plesk flöten wenn du eine MySql Config änderst?

razy
Posts: 12
Joined: 2008-01-16 20:14

Re: Nach Serverwechsel DB langsam

Post by razy » 2008-01-16 22:59

Frag mich net ;) Plesk benutzt wohl auch InnoDB und dann kann die MySQL Datenbank die Tabellen von Plesk nicht mehr erkennen... So fehler wie "misc.frm couln´t find" etc. Vielleicht liegt es auch daran das ich MySQL immer restartet habe ... jedenfalls kam ich recht schnell zu den Punkt als nix mehr ging.

P.S Kritik am Forum : Der Speicher Button! Warum sollte ich ein Beitrag speichern wollen? Der verwirrt mich eher immer weil der "standard" sowas nicht hat ...

User avatar
Joe User
Project Manager
Project Manager
Posts: 11137
Joined: 2003-02-27 01:00
Location: Hamburg

Re: Nach Serverwechsel DB langsam

Post by Joe User » 2008-01-16 23:09

<OT>
Razy wrote:P.S Kritik am Forum : Der Speicher Button! Warum sollte ich ein Beitrag speichern wollen? Der verwirrt mich eher immer weil der "standard" sowas nicht hat ...

Doch, phpBB3-Vanilla kommt mit dem OOTB aktivierten "Drafts" Feature.
</OT>
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

braindead
Posts: 250
Joined: 2002-10-22 09:49
Location: vorm Rechner

Re: Nach Serverwechsel DB langsam

Post by braindead » 2008-01-17 01:58

Razy wrote:So fehler wie "misc.frm couln´t find" etc. Vielleicht liegt es auch daran das ich MySQL immer restartet habe ... jedenfalls kam ich recht schnell zu den Punkt als nix mehr ging.


Dann hast du wahrscheinlich was an den Pfaden geändert. Sonst kann ich mir nicht erklären warum er die nicht mehr finden sollte. Das einzige kritische an my.cnf Änderungen sind Änderungen an der Größe der innodb Logfiles. Da muss man halt wissen was man macht. Post doch mal dein aktuelle Config hier.

razy
Posts: 12
Joined: 2008-01-16 20:14

Re: Nach Serverwechsel DB langsam

Post by razy » 2008-01-17 16:47

Na ich hab jetz die "slowquery" log gefunden und mal geschaut woran es liegt ca. 3 Querys die immer über 3 sekunden gebraucht haben. Und bereits eine Lösung gefunden für 2 querys wie ich die Tabelle entlasten kann. Ich muss nurnoch 1 query optimieren und das teil läuft wieder schnell ... Hab das tuning.sh Skript drüber laufen lassen und scheint fast alles in grünen Bereich zu sein. Hier trotzdem mal die Config file , denke aber das ich jetz zufrieden bin:

Code: Select all

# The following options will be passed to all MySQL clients
[client]
#password   = your_password
port      = 3306
socket      = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
set-variable=local-infile=0
port      = 3306
socket      = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 4M
max_allowed_packet = 1M
table_cache = 1000
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
long_query_time = 2
thread_cache_size = 128k
max_connections = 50
query_cache_size = 1024k
low_priority_updates=1

log-slow-queries

skip-networking
bind-address = 127.0.0.1

server-id   = 1

# Point the following paths to different dedicated disks
#tmpdir      = /tmp/      
#log-update    = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 10000

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 128M
innodb_additional_mem_pool_size = 4M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

# The safe_mysqld script
[safe_mysqld]
err-log=/var/lib/mysql/mysqld.log

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout


Der letzte Query den ich Optimieren muss:
SELECT * FROM `blaaaa` WHERE myitemid=13794 ORDER BY id DESC LIMIT 20;
# User@Host: aaa[aaa] @ localhost []
# Query_time: 4 Lock_time: 0 Rows_sent: 12 Rows_examined: 3727

Vielleicht sollte ich eine eigene Catch Tabelle für die häufigsten abfragen machen?

braindead
Posts: 250
Joined: 2002-10-22 09:49
Location: vorm Rechner

Re: Nach Serverwechsel DB langsam

Post by braindead » 2008-01-17 19:02

Wenn deine 2 Haupttabellen InnoDB sind würde ich vielleicht die folgenden Paramter mal überdenken:

Code: Select all

innodb_buffer_pool_size = 128M


Hier kannst du wenn es deine Speicherauslastung zulässt auf 256 MB gehen oder sogar mehr. Für einen reinen MySQL Server bis zu 80% des Speichers, aber lies mal folgeden Artikel.

Code: Select all

innodb_log_file_size = 5M


Deine Log File ist doch sehr klein und sollte vergrößert werden (bei zu kleinen Logs muss der Server die Logs zu oft neu anlegen). Hierbei ist allerdings Vorsicht geboten. Bei falschem Vorgehen ist danach Innodb nicht mehr verfügbar. Richtig wäre:

- MySQL runterfahren
- config ändern
- die alten logs sicher und danach löschen
- MySQL neu starten und prüfen ob die Logs neu angelegt wurden.

Code: Select all

innodb_log_buffer_size = 8M


Hier reichen 4 MB aus wenn du nicht gerade Blobs in deiner DB hast

Code: Select all

query_cache_size = 1024k


Wenn du viele Reads und wenig Updates hast dürfen es hier ruhig auch ein paar MB mehr sein (da kannst du aber auf tuning-primer vertrauen)

Code: Select all

nnodb_flush_method=O_DIRECT


Umgeht den Cache des OS und verhindert damit doppeltes Buffern (MySQL und OS Cache). Ist meistens unter Linux die bessere Wahl.

Die Slow Query Time würde ich auf 1 Sek stellen weil alles was länger als 1 Sek braucht ist nach meiner Meinung fragwürdig (und die Ausnahmen kann man ja ignorieren).

razy
Posts: 12
Joined: 2008-01-16 20:14

Re: Nach Serverwechsel DB langsam

Post by razy » 2008-01-17 20:00

Wozu brauch man den die log files? Kann man das nicht komplett ausschalten?

Andere Frage: Ich hab kein lynx deshalb nehm ich wget als cronjob aufruf - jetz hab ich in root verzeichnis ganz viele files wegen den wget --- ^^ Kann man das irgendwie verhindern?

braindead
Posts: 250
Joined: 2002-10-22 09:49
Location: vorm Rechner

Re: Nach Serverwechsel DB langsam

Post by braindead » 2008-01-17 21:12

Du brauchst die Logfiles um im Notfall die Datenbank vor oder zurück rollen zu können. Wenn du also mal etwas ausversehen geändert hast kannst du damit einen rollback bis zu einem gewissen Zeitpunkt machen. Du solltest sie also besser nicht ausschalten ;)

Razy wrote:Andere Frage: Ich hab kein lynx deshalb nehm ich wget als cronjob aufruf - jetz hab ich in root verzeichnis ganz viele files wegen den wget --- ^^ Kann man das irgendwie verhindern?


Warum rufst du denn wget auf? Was machst du mit den Daten? Verhindern kannst du das indem du nach dem wget aufruf oder davor die File die er runterlädt löschst.