mysql Optimierung

MySQL, PostgreSQL, SQLite
insomnia
Posts: 30
Joined: 2003-10-30 12:53

mysql Optimierung

Post by insomnia » 2003-12-15 07:05

Hallo,

ein Kunde hat einen Server, der tgl. zur Hauptstoßzeit aufgrund vieler Datenbankabfragen den A**** hoch reißt.

Meine Frage: Kann man an der MySql conf etwas optimieren? Oder muss eher nen schnellerer Rechner her?

Code: Select all

# Example mysql config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# a important part and systems up to 128M very MySQL is used together with
# other programs (like a web server)
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# One can in this file use all long options that the program supports.
# If you want to know which options a program support, run the program
# with --help option.

# 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]
port		= 3306
socket		= /var/lib/mysql/mysql.sock
skip-locking
set-variable	= key_buffer=16M
set-variable	= max_allowed_packet=1M
set-variable	= table_cache=64
set-variable	= sort_buffer=512K
set-variable	= net_buffer_length=8K
set-variable	= myisam_sort_buffer_size=8M
set-variable    = max_connections=200
log-bin
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
#set-variable	= bdb_cache_size=4M
#set-variable	= 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
#set-variable = innodb_buffer_pool_size=16M
#set-variable = innodb_additional_mem_pool_size=2M
# Set .._log_file_size to 25 % of buffer pool size
#set-variable = innodb_log_file_size=5M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#set-variable = innodb_lock_wait_timeout=50

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

[mysqldump]
quick
set-variable	= max_allowed_packet=16M

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

[isamchk]
set-variable	= key_buffer=20M
set-variable	= sort_buffer=20M
set-variable	= read_buffer=2M
set-variable	= write_buffer=2M

[myisamchk]
set-variable	= key_buffer=20M
set-variable	= sort_buffer=20M
set-variable	= read_buffer=2M
set-variable	= write_buffer=2M

[mysqlhotcopy]
interactive-timeout
Last edited by insomnia on 2003-12-15 09:27, edited 1 time in total.

jtb
Posts: 599
Joined: 2002-08-18 16:41
Location: Darmstadt

Re: mysql Optimierung

Post by jtb » 2003-12-15 09:26

such mal im Forum. Zu diesem Thema gab es einige Threads..
Ansonsten log-bin raus :wink:
slow-query-log wäre auch sinnvoll..

oxygen
Posts: 2138
Joined: 2002-12-15 00:10
Location: Bergheim

Re: mysql Optimierung

Post by oxygen » 2003-12-15 12:35

Query Cache aktivieren und Binary Log deaktivieren sind die aussichtsreichsten Ansatzpunkte... ansonsten heißt es Scripts optimieren. Mit den Buffern lässt sich noch feintuning betrieben, das ist aber recht schwierig.

flo
Posts: 2223
Joined: 2002-07-28 13:02
Location: Berlin

Re: mysql Optimierung

Post by flo » 2003-12-15 20:27

bei umfangreichen Tabellen oder großen Joins helfen auch noch zusätzliche Schlüssel.

flo.

jtb
Posts: 599
Joined: 2002-08-18 16:41
Location: Darmstadt

Re: mysql Optimierung

Post by jtb » 2003-12-16 11:18

flo wrote:bei umfangreichen Tabellen oder großen Joins helfen auch noch zusätzliche Schlüssel.
das gehört schon zu einem guten Design der Datenbank :wink:

flo
Posts: 2223
Joined: 2002-07-28 13:02
Location: Berlin

Re: mysql Optimierung

Post by flo » 2003-12-17 09:55

ich sags ja bloß ... wird leider oft vergessen und bringt sehr viel Performance.

*sorry* ;-)

flo.

odysseus
Posts: 115
Joined: 2003-02-07 10:21

Re: mysql Optimierung

Post by odysseus » 2003-12-18 17:04

Auf MySQL 4.0.17 updaten, falls noch nicht geschehen.
Log-bin rausnehmen, indem man ein # davor setzt.
Außerdem den Query Cache aktivieren und auf z.B. 12 MB setzen:

Code: Select all

# The MySQL server
[mysqld]
port		= 3306
socket		= /var/lib/mysql/mysql.sock
skip-locking
set-variable	= key_buffer=16M
set-variable	= max_allowed_packet=1M
set-variable	= table_cache=64
set-variable	= sort_buffer=512K
set-variable	= net_buffer_length=8K
set-variable	= myisam_sort_buffer_size=8M
set-variable   = query_cache_size=12M
set-variable   = max_connections=200
#log-bin
server-id	= 1