langsamer und schneller mysql-server

[synoo]

Hallo,
habe mehrere Millionen daten und normallerweise geht die suche sehr schnell. 400.000 ergebnisse in 2-3 sekunden und die CPU und Mem Power geht auch hoch, was ja gut ist. Jetzt kommt es häufiger vor das die suche sehr lange dauert und die CPU-Power steigt nicht höher alls 3%. Nun weiß ich nicht wieso die datenbank abbremsen.

Habe mysql 4.1.12

Danke in voraus

[Joe User]

Wieviel RAM hat die Kiste und wie sieht die my.cnf aus?

[synoo]

Der server hat 1 Giga Arbeitsspeicher.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
port = 3306
set-variable = key_buffer_size = 384M
set-variable = sort_buffer_size = 12M
set-variable = read_buffer_size = 12M
set-variable = max_allowed_packet = 2M
set-variable = max_connections = 2000
set-variable = thread_cache = 1024
set-variable = table_cache = 6048
set-variable = query_cache_type = 1
set-variable = query_cache_size = 12M
set-variable = query_cache_limit = 20M

[mysqldump]
quick
max_allowed_packet = 48M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer_size = 256M
sort_buffer_size = 256M
read_buffer_size = 4M
write_buffer_size = 4M

[outofbound]

a) Speicher (swappt)
b) Platte zu langsam
c) Optimizer kaputt
d) DB- Statistiken im Eimer (Löcher im phys. Datenbestand).
e) Race Conditions
f) Deadlocks

Genauer gehts leider net.

Gruss,

Out

[synoo]

Danke für die antwort,

wie sieht das mit der my.cnf aus, ist die richtig eingestellt oder habt Ihr Verbesserungsvorschläge.

[kawfy]

:idea: aktiviere das Slow-Log und analysiere die Queries, die im Log landen. (dja-vu?:) )

[Joe User]

Versuche es mal folgender my.cnf (Pfade anpassen!):

Code: Select all

[client]
port                  = 3306
socket                = /var/run/mysqld/mysqld.sock

[mysqld_safe]
err-log               = /var/log/mysql/mysql.err

[mysqld]
user                  = mysql
pid-file              = /var/run/mysqld/mysqld.pid
socket                = /var/run/mysqld/mysqld.sock
log-error             = /var/log/mysql/mysqld.err
basedir               = /usr
datadir               = /var/lib/mysql
tmpdir                = /tmp
language              = /usr/share/mysql/english
bind-address          = 127.0.0.1
port                  = 3306
server-id             = 1
skip-locking
skip-networking
skip-name-resolve
skip-external-locking
local-infile = 0
key_buffer_size = 64M
sort_buffer_size = 4M
read_buffer_size = 1M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
max_allowed_packet = 16M
net_buffer_length = 8K
thread_stack = 128K
thread_cache = 8
table_cache = 256
query_cache_type = 1
query_cache_size = 16M
query_cache_limit = 1M
thread_concurrency = 2
skip-innodb
innodb_buffer_pool_size = 32M
innodb_additional_mem_pool_size = 4M
innodb_data_home_dir = /var/lib/mysql/
innodb_log_arch_dir = /var/lib/mysql/
innodb_log_group_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend:max:128M
innodb_log_file_size = 8M
innodb_log_buffer_size = 8M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
#bdb_cache_size = 4M
#bdb_max_lock = 10000

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
#no-auto-rehash
#safe-updates

[isamchk]
key_buffer_size = 64M
sort_buffer_size = 4M
read_buffer_size = 1M
write_buffer_size = 1M

[myisamchk]
key_buffer_size = 64M
sort_buffer_size = 4M
read_buffer_size = 1M
write_buffer_size = 1M

[mysqlhotcopy]
interactive-timeout