Page 1 of 1
langsamer und schneller mysql-server
Posted: 2005-07-27 21:28
by 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
Re: langsamer und schneller mysql-server
Posted: 2005-07-28 09:37
by Joe User
Wieviel RAM hat die Kiste und wie sieht die my.cnf aus?
Re: langsamer und schneller mysql-server
Posted: 2005-07-28 17:29
by 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
Re: langsamer und schneller mysql-server
Posted: 2005-07-28 18:05
by 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
Re: langsamer und schneller mysql-server
Posted: 2005-07-28 18:18
by synoo
Danke für die antwort,
wie sieht das mit der my.cnf aus, ist die richtig eingestellt oder habt Ihr Verbesserungsvorschläge.
Slow-Logs
Posted: 2005-07-29 00:40
by kawfy
:idea: aktiviere das Slow-Log und analysiere die Queries, die im Log landen. (dja-vu?:) )
Re: langsamer und schneller mysql-server
Posted: 2005-07-29 09:37
by 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