Tweak my DB

jluerken
Posts: 58
Joined: 2003-03-10 10:53

Tweak my DB

Post by jluerken »

Sooo, ich hoffe ich finde hier ein paar Linux/MySQL Gurus die mir helfen können meinen MySQLd ein wenig anzupassen. Der Speicherverbrauch scheint mir ein wenig zu gross momentan und die DB ist ein wenig langsam.

Ich hoffe hier kann mir jemand sagen woran ich drehen kann, aber erstmal ein paar Infos:

Top Ausgabe 136m VIRT und 46m RES. Ein wenig hoch wie ich finde oder?

Code: Select all

24468 mysql     15   0  136m  46m 4896 S  0.3  1.2  16:26.85 mysqld


Tuning Primer. Nicht ganz 48 Stunden aber das sollte auch reichen
s15219942:/home # ./tuning-primer.sh

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

MySQL Version 4.1.10a i686

Uptime = 1 days 16 hrs 39 min 47 sec
Avg. qps = 4
Total Questions = 710444
Threads Connected = 6

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/server-system-variables.html

SLOW QUERIES

Current long_query_time = 10 sec.
You have 0 out of 710456 that take longer than 10 sec. to complete
The slow query log is NOT enabled.
Your long_query_time may be too high, I typically set this under 5 sec.

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

MAX CONNECTIONS

Current max_connections = 300
Current threads_connected = 6
Historic max_used_connections = 20
The number of used connections is 6% 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 : 98 M
Configured Max Per-thread Buffers : 952 M
Configured Max Global Buffers : 34 M
Configured Max Memory Limit : 987 M
Total System Memory : 22.47 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 46 M
Current key_buffer_size = 16 M
Key cache miss rate is 1 : 3207
Key buffer fill ratio = 74.00 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 16 M
Current query_cache_used = 15 M
Current query_cach_limit = 1 M
Current Query cache fill ratio = 96.97 %
However, 31403 queries have been removed from the query cache due to lack of memory
Perhaps you should raise query_cache_size
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 512 K
Current record/read_rnd_buffer_size = 1020 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 512.00 K
You have had 7151 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 = 1510 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 = 512 tables
You have a total of 515 tables
You have 450 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 31 M
Current tmp_table_size = 32 M
Of 54759 temp tables, 42% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables

Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.


TABLE SCANS
Current read_buffer_size = 1020 K
Current table scan ratio = 1117 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 4787
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'


Meine my.cnf

Code: Select all

# The MySQL server
[mysqld]
set-variable=local-infile=0
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
skip-name-resolve
key_buffer = 16MB
max_allowed_packet = 1M
table_cache = 512
join_buffer_size = 512K
sort_buffer_size = 512K
net_buffer_length = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
myisam_sort_buffer_size = 8M
max_connections = 300
max_heap_table_size = 32M


Ich sehe gerade das sich mein Apache auch ne Menge Speicher reserviert. Meiner Meinung nach auch viel zu viel:

Code: Select all

 6058 wwwrun    15   0 51844  26m 6448 S  0.0  0.7   0:12.77 httpd2-prefork                                                         
 7552 wwwrun    16   0 51864  26m 6424 S  0.0  0.7   0:23.14 httpd2-prefork                                                         
14047 wwwrun    16   0 52956  26m 5620 S  0.0  0.7   0:07.77 httpd2-prefork                                                         
14167 wwwrun    17   0 52888  26m 5612 S  0.0  0.7   0:08.27 httpd2-prefork                                                         
14209 wwwrun    16   0 51140  25m 6144 S  0.0  0.6   0:06.79 httpd2-prefork                                                         
17999 wwwrun    16   0 53128  26m 5600 S  0.0  0.7   0:03.17 httpd2-prefork                                                         
18399 wwwrun    16   0 51588  25m 5588 S  0.0  0.6   0:01.30 httpd2-prefork                                                         
18401 wwwrun    16   0 51580  25m 5592 S  0.0  0.6   0:01.67 httpd2-prefork                                                         
18402 wwwrun    16   0 45692  19m 5340 S  0.0  0.5   0:00.14 httpd2-prefork                                                         
18403 wwwrun    16   0 49644  23m 5444 S  0.0  0.6   0:00.41 httpd2-prefork                                                         
18425 wwwrun    16   0 48136  21m 5464 S  0.0  0.6   0:00.23 httpd2-prefork


Ich bitte um Vorschläge!
Top

jluerken
Posts: 58
Joined: 2003-03-10 10:53

Re: Tweak my DB

Post by jluerken »

Hier mal die Module die mein Apache lädt.
Passt vielleicht nicht ganz in dieses Forum aber passt zum Beitrag und ich möchte die Mods bitten den Beitrag deshalb hier zu lassen!

# as listed in APACHE_MODULES (/etc/sysconfig/apache2)

Code: Select all

LoadModule rewrite_module                 /usr/lib/apache2-prefork/mod_rewrite.so
LoadModule perl_module                    /usr/lib/apache2/mod_perl.so
LoadModule access_module                  /usr/lib/apache2-prefork/mod_access.so
LoadModule actions_module                 /usr/lib/apache2-prefork/mod_actions.so
LoadModule alias_module                   /usr/lib/apache2-prefork/mod_alias.so
LoadModule auth_module                    /usr/lib/apache2-prefork/mod_auth.so
LoadModule auth_dbm_module                /usr/lib/apache2-prefork/mod_auth_dbm.so
LoadModule autoindex_module               /usr/lib/apache2-prefork/mod_autoindex.so
LoadModule cgi_module                     /usr/lib/apache2-prefork/mod_cgi.so
LoadModule dir_module                     /usr/lib/apache2-prefork/mod_dir.so
LoadModule env_module                     /usr/lib/apache2-prefork/mod_env.so
LoadModule expires_module                 /usr/lib/apache2-prefork/mod_expires.so
LoadModule include_module                 /usr/lib/apache2-prefork/mod_include.so
LoadModule log_config_module              /usr/lib/apache2-prefork/mod_log_config.so
LoadModule mime_module                    /usr/lib/apache2-prefork/mod_mime.so
LoadModule negotiation_module             /usr/lib/apache2-prefork/mod_negotiation.so
LoadModule setenvif_module                /usr/lib/apache2-prefork/mod_setenvif.so
LoadModule ssl_module                     /usr/lib/apache2-prefork/mod_ssl.so
LoadModule userdir_module                 /usr/lib/apache2-prefork/mod_userdir.so
LoadModule php4_module                    /usr/lib/apache2-prefork/libphp4.so
LoadModule python_module                  /usr/lib/apache2/mod_python.so
LoadModule suexec_module                  /usr/lib/apache2-prefork/mod_suexec.so


Muss man den ganzen Käse laden? Ich meine python nutze ich nicht, kann ich den Kram dann nicht z.b. weglassen oder braucht das System das für was anderes irgendwo?

Hier ein Auszug aus der vom Hoster angelegten sogenannten TUNING Config:

Code: Select all

# prefork MPM
<IfModule prefork.c>
        # number of server processes to start
        StartServers         5
        # minimum number of server processes which are kept spare
        MinSpareServers      5
        # maximum number of server processes which are kept spare
        MaxSpareServers     10
        # highest possible MaxClients setting for the lifetime of the Apache process.
        ServerLimit        150
        # maximum number of server processes allowed to start
        MaxClients         150
        # maximum number of requests a server process serves
        MaxRequestsPerChild  300
</IfModule>

# worker MPM
<IfModule worker.c>
        # initial number of server processes to start
        StartServers         2
        # minimum number of worker threads which are kept spare
        MinSpareThreads     25
        # maximum number of worker threads which are kept spare
        MaxSpareThreads     75
        # maximum number of simultaneous client connections
        MaxClients         150
        # constant number of worker threads in each server process
        ThreadsPerChild     25
        # maximum number of requests a server process serves
        MaxRequestsPerChild  0
</IfModule>

# leader MPM
<IfModule leader.c>
        # initial number of server processes to start
        StartServers         2
        # minimum number of worker threads which are kept spare
        MinSpareThreads     25
        # maximum number of worker threads which are kept spare
        MaxSpareThreads     75
        # maximum number of simultaneous client connections
        MaxClients         150
        # constant number of worker threads in each server process
        ThreadsPerChild     25
        # maximum number of requests a server process serves
        MaxRequestsPerChild  0
</IfModule>

# perchild MPM
<IfModule perchild.c>
        # constant number of server processes
        NumServers           5
        # initial number of worker threads in each server process
        StartThreads         5
        # minimum number of worker threads which are kept spare
        MinSpareThreads      5
        # maximum number of worker threads which are kept spare
        MaxSpareThreads     10
        # maximum number of worker threads in each server process
        MaxThreadsPerChild  20
        # maximum number of connections per server process
        MaxRequestsPerChild  0

        AcceptMutex fcntl
</IfModule>

# metux MPM
<IfModule metuxmpm.c>
        # initial number of worker threads in each server process
        StartThreads          5
        # minimum number of worker threads which are kept spare
        MinSpareThreads       5
        # maximum number of worker threads which are kept spare
        MaxSpareThreads      10
        # maximum number of connections per server process
        MaxRequestsPerChild   0

        Multiplexer     "wwwrun"        "www"

</IfModule>


#
# KeepAlive: Whether or not to allow persistent connections (more than
# one request per connection). Set to "Off" to deactivate.
#
KeepAlive On

#
# MaxKeepAliveRequests: The maximum number of requests to allow
# during a persistent connection. Set to 0 to allow an unlimited amount.
# We recommend you leave this number high, for maximum performance.
#
MaxKeepAliveRequests 100

#
# KeepAliveTimeout: Number of seconds to wait for the next request from the
# same client on the same connection.
#
KeepAliveTimeout 10


#
# EnableMMAP: Control whether memory-mapping is used to deliver
# files (assuming that the underlying OS supports it).
# The default is on; turn this off if you serve from NFS-mounted
# filesystems.  On some systems, turning it off (regardless of
# filesystem) can improve performance; for details, please see
# http://httpd.apache.org/docs-2.0/mod/core.html#enablemmap
#
#EnableMMAP off

#
# EnableSendfile: Control whether the sendfile kernel support is
# used  to deliver files (assuming that the OS supports it).
# The default is on; turn this off if you serve from NFS-mounted
# filesystems.  Please see
# http://httpd.apache.org/docs-2.0/mod/core.html#enablesendfile
#
#EnableSendfile off
Top

jluerken
Posts: 58
Joined: 2003-03-10 10:53

Re: Tweak my DB

Post by jluerken »

matzewe01 wrote:
jluerken wrote:Hier mal die Module die mein Apache lädt.


Wenn du einzelne Module nicht benötigst, schmeiss diese raus.
/etc/sysconfig/apache2

Wenn da aber ein Plesk frauf läuft, solltest Du ggf. vorsicht walten lassen.

Gruss Matthias


Ja da läuft Plesk auch wenn ich den Service momentan runtergefahren habe.
Plesk ist ja ganz nett für die Grundeinstellungen und für Anfänger wie mich.
Je mehr ich jedoch lerne desto lieber arbeite ich ohne :D
Top

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

Re: Tweak my DB

Post by Joe User »

Versuche es mal mit dieser my.cnf, 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
log-bin               = mysql-bin
server-id             = 1
back_log              = 50
sync_binlog           = 1
binlog_cache_size     = 1M
max_binlog_size       = 100M
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      = 64M
query_cache_limit     = 2M
thread_concurrency    = 8
thread_cache_size     = 16
max_connections       = 24
max_connect_errors    = 10
local-infile          = 0
log_warnings          = 0
long_query_time       = 4
log_slow_queries
log_queries_not_using_indexes
log_long_format
myisam_recover
skip-innodb

[mysqlhotcopy]
interactive-timeout
Top

jluerken
Posts: 58
Joined: 2003-03-10 10:53

Re: Tweak my DB

Post by jluerken »

Danke Joe User,

leider funktioniert der Connect externer Hosts nun gar nicht mehr:

Code: Select all

Verbindung fehlgeschlagen!
Threaded Query Error, Ort: 17
Nachricht: Can't connect to MySQL server on 'IP' (111) (2003)


Hat das was mit dem BIND zu tun?
Top

jluerken
Posts: 58
Joined: 2003-03-10 10:53

Re: Tweak my DB

Post by jluerken »

Habs hinbekommen Joe User, danke!

Ich müsste den Memory Verbrauch des Apache2 und MySQLd noch etwas senken. Aktuell sieht das immer noch so aus

Code: Select all

 3712 wwwrun    16   0 51792  25m 5596 S  0.0  0.6   0:04.35 httpd2-prefork                                                         
 7924 wwwrun    16   0 51684  25m 5592 S  0.0  0.6   0:03.29 httpd2-prefork                                                         
 7976 wwwrun    15   0 48192  22m 5808 S  0.0  0.6   0:01.36 httpd2-prefork                                                         
 7978 wwwrun    16   0 51572  25m 5592 S  0.0  0.6   0:01.19 httpd2-prefork                                                         
 8062 wwwrun    15   0 48136  21m 5464 S  0.0  0.6   0:00.63 httpd2-prefork                                                         
 8072 wwwrun    16   0 48144  21m 5460 S  0.0  0.6   0:00.43 httpd2-prefork                                                         
 8073 wwwrun    15   0 40596  12m 4016 S  0.0  0.3   0:00.02 httpd2-prefork                                                         
 8074 wwwrun    15   0 48128  21m 5464 S  0.0  0.6   0:00.23 httpd2-prefork                                                         
 8077 wwwrun    16   0 38640 8488 1448 S  0.0  0.2   0:00.00 httpd2-prefork                                                         
 8078 wwwrun    15   0 48128  21m 5464 S  0.0  0.6   0:00.63 httpd2-prefork     


Code: Select all

7880 mysql     15   0  139m  56m 3460 S  0.0  1.4   0:11.40 mysqld


Ne Idee was ich da noch dran drehen kann?
Top