Mysql Optimieren

falko2
Posts: 9
Joined: 2007-07-24 22:24

Mysql Optimieren

Post by falko2 »

Hallo, ich habe einen Server mit 2x2,2 Ghz und 2 Gb Ram, darauf läuft ein Optimerter WebServer (Apache 2)
Auf dem Server läuft ein Browsergame, die Datenbsnk für das Game ist im Moment 150 Mb gross und pro Sekunde werden über 10000 Datenbankzugriffe getätigt laut phpmyadmin und am Tag sind es knapp 1,5 Millarden zugriffe auf die DB, desweiteren ist mir aufgefallen das nur 2 Prozesse vom Mysql-Deamon laufen und das finde ich etwas wenig. ich hoffe mir kann hier irgendwie geholfen wetrden?

Danke schon mal im vorraus
Top

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

Re: Mysql Optimieren

Post by Joe User »

Wenn der MySQLd mindestens 48 Stunden gelaufen ist, lässt Du das tuning-primer.sh von http://www.day32.com/MySQL/ auf die Datenbank los und postest die Ausgabe, sowie Deine my.cnf (ohne Kommentare).
Top

falko2
Posts: 9
Joined: 2007-07-24 22:24

Re: Mysql Optimieren

Post by falko2 »

also die my.cnf sieht im moment bei mir so aus:

[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 2M
table_cache = 1024
tmp_table_size = 32M
sort_buffer_size = 2M
net_buffer_length = 8K
read_buffer_size = 1M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 64M
max_connections = 2500
thread_cache = 64
thread_concurrency = 4
skip-networking
server-id = 1

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

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[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

Habe nur das gepostet was auch nicht auskommentiert ist
Top

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

Re: Mysql Optimieren

Post by Joe User »

Pfade bitte selbst anpassen:

Code: Select all

[client]
character-sets-dir    = /usr/share/mysql/charsets
default-character-set = latin1
port                  = 3306
socket                = /var/run/mysqld/mysqld.sock

[mysql]
character-sets-dir    = /usr/share/mysql/charsets
default-character-set = latin1
prompt = u@h [d]>_
no-auto-rehash

[mysqladmin]
character-sets-dir    = /usr/share/mysql/charsets
default-character-set = latin1

[mysqlcheck]
character-sets-dir    = /usr/share/mysql/charsets
default-character-set = latin1

[mysqldump]
character-sets-dir    = /usr/share/mysql/charsets
default-character-set = latin1
max_allowed_packet    = 32M
quote-names
quick

[mysqlimport]
character-sets-dir    = /usr/share/mysql/charsets
default-character-set = latin1

[mysqlshow]
character-sets-dir    = /usr/share/mysql/charsets
default-character-set = latin1

[isamchk]
character-sets-dir    = /usr/share/mysql/charsets
key_buffer_size       = 256M

[myisamchk]
character-sets-dir    = /usr/share/mysql/charsets
key_buffer_size       = 256M

[myisampack]
character-sets-dir    = /usr/share/mysql/charsets

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

[mysqld]
character-sets-dir    = /usr/share/mysql/charsets
character-set-server  = latin1
default-character-set = latin1
user                  = mysql
port                  = 3306
bind-address          = 127.0.0.1
socket                = /var/run/mysqld/mysqld.sock
pid-file              = /var/run/mysqld/mysqld.pid
log-error             = /var/log/mysql/mysqld.err
basedir               = /usr
datadir               = /var/lib/mysql
tmpdir                = /var/tmp
language              = /usr/share/mysql/english
#master-host           = <hostname>
#master-user           = <username>
#master-password       = <password>
#master-port           = 3306
log-bin               = mysql-bin
server-id             = 1
back_log              = 50
sync_binlog           = 1
binlog_cache_size     = 1M
max_binlog_size       = 100M
slave_compressed_protocol = 1
delay-key-write       = ALL
skip-locking
skip-networking
skip-name-resolve
skip-external-locking
skip-show-database
safe-user-create      = 1
key_buffer_size       = 384M
myisam_repair_threads = 1
max_heap_table_size   = 128M
max_allowed_packet    = 32M
tmp_table_size        = 128M
table_cache           = 1024
query_cache_type      = 1
query_cache_size      = 64M
query_cache_limit     = 2M
thread_concurrency    = 16
thread_cache_size     = 32
max_connections       = 2500
max_connect_errors    = 10
local-infile          = 0
log_warnings          = 0
long_query_time       = 4
log_slow_queries
log_queries_not_using_indexes
#log_slave_updates
log_long_format
myisam_recover
skip-innodb
innodb_thread_concurrency = 8
innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 16M
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:2000M;ibdata2:10M:autoextend
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

[mysqlhotcopy]
interactive-timeout
Nach 48 Stunden das tuning-timer.sh laufen lassen und die Ausgabe posten.
Top

falko2
Posts: 9
Joined: 2007-07-24 22:24

Re: Mysql Optimieren

Post by falko2 »

so habe die my.cnf auf den server geladen und musste noch dazu das bin-log ausschalten und innodb auch, denn sonst wäre plesk nicht mehr gelaufen und der mysql starte bei mir nicht wenn bin-log aktiv ist. Jetzt heisst es wohl 48 stunden warten und dann mal sehen was die tuning-primer.sh sagt
Top

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

Re: Mysql Optimieren

Post by Joe User »

falko2 wrote:und innodb auch,
InnoDB war bereits per skip-innodb deaktiviert ;)
Top

falko2
Posts: 9
Joined: 2007-07-24 22:24

Re: Mysql Optimieren

Post by falko2 »

das hat plesk aber nicht ekzeptiert und dadurch musste ich alles auskommentieren
Top

falko2
Posts: 9
Joined: 2007-07-24 22:24

Re: Mysql Optimieren

Post by falko2 »

also habe jetzt das tuning-primer.sh durchlaufen lassen und da ist einiges rot gewesen, aber wie man das auswertet weis ich nicht.
Hier das ergebniss:

-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -

MySQL Version 4.1.10a-log x86_64

Uptime = 1 days 15 hrs 45 min 0 sec
Avg. qps = 7863
Total Questions = 1125313764
Threads Connected = 2

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/4.1/en/ ... ables.html

SLOW QUERIES
Current long_query_time = 4 sec.
You have 967959 out of 1125314127 that take longer than 4 sec. to complete
The slow query log is enabled.
Your long_query_time seems to be fine

WORKER THREADS
Current thread_cache_size = 32
Current threads_cached = 30
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 2500
Current threads_connected = 3
Historic max_used_connections = 200
The number of used connections is 8% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating

MEMORY USAGE
Max Memory Ever Allocated : 995 M
Configured Max Per-thread Buffers : 6 G
Configured Max Global Buffers : 458 M
Configured Max Memory Limit : 7 G
Total System Memory : 5.68 G

Max memory limit exceeds 85% of total system memory

KEY BUFFER
Current MyISAM index space = 47 M
Current key_buffer_size = 384 M
Key cache miss rate is 1 : 31368
Key buffer fill ratio = 6.00 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is enabled
Current query_cache_size = 64 M
Current query_cache_used = 4 M
Current query_cach_limit = 2 M
Current Query cache fill ratio = 7.74 %
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current record/read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 132.00 K
You have had 361 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 12510 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_cache value = 1024 tables
You have a total of 525 tables
You have 786 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 127 M
Current tmp_table_size = 128 M
Of 7074 temp tables, 0% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 2838 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 1
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
Top

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

Re: Mysql Optimieren

Post by Joe User »

OK, hier die optimierte my.cnf:

Code: Select all

[client]
character-sets-dir    = /usr/share/mysql/charsets
default-character-set = latin1
port                  = 3306
socket                = /var/run/mysqld/mysqld.sock

[mysql]
character-sets-dir    = /usr/share/mysql/charsets
default-character-set = latin1
prompt = u@h [d]>_
no-auto-rehash

[mysqladmin]
character-sets-dir    = /usr/share/mysql/charsets
default-character-set = latin1

[mysqlcheck]
character-sets-dir    = /usr/share/mysql/charsets
default-character-set = latin1

[mysqldump]
character-sets-dir    = /usr/share/mysql/charsets
default-character-set = latin1
max_allowed_packet    = 32M
quote-names
quick

[mysqlimport]
character-sets-dir    = /usr/share/mysql/charsets
default-character-set = latin1

[mysqlshow]
character-sets-dir    = /usr/share/mysql/charsets
default-character-set = latin1

[isamchk]
character-sets-dir    = /usr/share/mysql/charsets
key_buffer_size       = 256M

[myisamchk]
character-sets-dir    = /usr/share/mysql/charsets
key_buffer_size       = 256M

[myisampack]
character-sets-dir    = /usr/share/mysql/charsets

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

[mysqld]
character-sets-dir    = /usr/share/mysql/charsets
character-set-server  = latin1
default-character-set = latin1
user                  = mysql
port                  = 3306
bind-address          = 127.0.0.1
socket                = /var/run/mysqld/mysqld.sock
pid-file              = /var/run/mysqld/mysqld.pid
log-error             = /var/log/mysql/mysqld.err
basedir               = /usr
datadir               = /var/lib/mysql
tmpdir                = /var/tmp
language              = /usr/share/mysql/english
#master-host           = <hostname>
#master-user           = <username>
#master-password       = <password>
#master-port           = 3306
#log-bin               = mysql-bin
server-id             = 1
#back_log              = 50
#sync_binlog           = 1
#binlog_cache_size     = 1M
#max_binlog_size       = 100M
slave_compressed_protocol = 1
delay-key-write       = ALL
skip-locking
skip-networking
skip-name-resolve
skip-external-locking
skip-show-database
safe-user-create      = 1
key_buffer_size       = 64M
myisam_repair_threads = 1
max_heap_table_size   = 64M
max_allowed_packet    = 32M
tmp_table_size        = 64M
table_cache           = 1024
query_cache_type      = 1
query_cache_size      = 32M
query_cache_limit     = 2M
thread_concurrency    = 16
thread_cache_size     = 32
max_connections       = 250
max_connect_errors    = 10
local-infile          = 0
log_warnings          = 0
long_query_time       = 4
#log_slow_queries
#log_queries_not_using_indexes
#log_slave_updates
log_long_format
myisam_recover
skip-innodb

[mysqlhotcopy]
interactive-timeout 
Mehr lässt sich per my.cnf nicht tunen. Jetzt hilft nur noch das Optimieren/Minimieren der SQL-Queries im Browsergame, sofern erlaubt.
Top

falko2
Posts: 9
Joined: 2007-07-24 22:24

Re: Mysql Optimieren

Post by falko2 »

also erlaubt ist es von mir auf jedenfall, da ich es selbst programmiert habe. das problem dabei ist nur das dieses game sehr viele querys und updates hat und es kaum möglich ist diese weg zu lassen. Das einzige was mich noch wundert ist, dass wenn ich top eingebe, dann wird mir nur 1 mysqld angezeigt und auf dem alten server waren es bis zu 30 stück, woran kann dies liegen?
Top

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

Re: Mysql Optimieren

Post by Joe User »

Drücke innerhalb von top mal <SHIFT><h> ;)
Top

falko2
Posts: 9
Joined: 2007-07-24 22:24

Re: Mysql Optimieren

Post by falko2 »

dann sagt er mir unknown command
Top

hornox
Posts: 139
Joined: 2005-09-22 23:09

Re: Mysql Optimieren

Post by hornox »

Probier mal ein

Code: Select all

top -H
, das sollte das gewünschte bewirken.
Top

falko2
Posts: 9
Joined: 2007-07-24 22:24

Re: Mysql Optimieren

Post by falko2 »

also da gibt er mir aus:

top: procps version 3.2.5
usage: top -hv | -bcisS -d delay -n iterations
und halt u für user und so weiter, aber was soll ich damit anfangen?
Top

kase
RSAC
Posts: 1041
Joined: 2002-10-14 22:56

Re: Mysql Optimieren

Post by kase »

MySQL ist threaded, du siehst deswegen nur 1 Prozess.

Wie viele Threads laufen siehst du mit:

Code: Select all

# pstree | grep mysql
     |-mysqld_safe-+-logger
     |             `-mysqld---3*[{mysqld}]
Top

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

Re: Mysql Optimieren

Post by Joe User »

Dein MySQLd verwendet einen Prozess mit mehreren Threads. Standardmässig zeigen top und ps nur Prozesse und keine Threads an (siehe Manpage). Auf einigen Systemen lässt sich die Anzeige der Threads mit obigen Optionen aktivieren, auf einigen aber auch nicht. Deines gehört wohl zu den Letzteren...
Top

falko2
Posts: 9
Joined: 2007-07-24 22:24

Re: Mysql Optimieren

Post by falko2 »

also wenn ich pstree | grep mysql eintippe, dann zeigt er mir nur

|-mysqld_safe---mysqld

also ich habe jetzt die hauptscripte etwas umgeschrieben und jetzt läuft es vorerst wieder schneller, aber mal sehen wie es morgen aussieht.
Ich danke auf jedenfall für eure Hilfe
Top

lord_pinhead
RSAC
Posts: 830
Joined: 2004-04-26 15:57

Re: Mysql Optimieren

Post by lord_pinhead »

Etwas offtopic, aber einfacher wäre es top mit htop zu ersetzen, sieht man alles besser finde ich.
Top

twins
Posts: 13
Joined: 2008-02-12 15:05

Re: Mysql Optimieren

Post by twins »

Ich habe mal eine Frage, bei mir (vserver mit Debian 3.1, MySQL5) funktioniert das Script leider nicht.

Code: Select all

cd /usr/local/mysql/bin
wget http://www.day32.com/MySQL/tuning-primer.sh
chmod 755 tuning-primer.sh
tuning-primer.sh

- INITIAL LOGIN ATTEMPT FAILED -

Testing Stored  for passwords:-RETRY LOGIN ATTEMPT FAILD -

Could not auto detect login info!

Dou you have your login handy ? [y/N] : y
User: web1
password:
Would you like me to create a /.my.cnf file for you? [y/N] : y
- FINAL LOGIN ATTEMPT FAILD -

Unable to log into soket : /tmp/mysql.sock

Habe es auch mit root probiert und die Passwört sind 100%ig richtig, wieso funktioniert das trotzdem nicht?
Top