MySQL richtig konfigurieren

MySQL, PostgreSQL, SQLite
User avatar
Joe User
Project Manager
Project Manager
Posts: 11138
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL richtig konfigurieren

Post by Joe User » 2010-08-03 22:03

Updated to MySQL 5.1 and fixed some Bugs
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

2Papeete
Posts: 18
Joined: 2011-01-09 23:17

Re: MySQL richtig konfigurieren

Post by 2Papeete » 2011-01-09 23:30

Hallo,

ich habe ein Problem und zwar läuft eine unserer Internetseiten seit ein paar Tagen extrem langsam. Erster Verdacht, der Server, aber dieser ist leistungsstark und kaum ausgelastet, alle anderen Internetseiten laufen auch schnell und gut. Nach längerer Recherche ist wohl klar, dass es an der MySQL-Datenbankkonfiguration liegt, die nicht wirklich gut ist, da die Strato Standardkonfiguration dort greift. Ich habe in verschiedenen Foren schon Optimierungsmöglichkeiten gefunden, was man wie einstellen sollte und dies auch getan, glaube aber, dass das noch nicht optimal ist. Ich schreibe hier mal die Server- und MySQL Daten und hoffe, dass mir jemand weiterhelfen kann, wo man noch drehen kann und muss.

AMD Dual-Core AMD Opteron(tm) Processor 1214 HE
4 GB RAM

Servervariablen und -einstellungen
Variable Wert für diese Sitzung / Globaler Wert

Code: Select all

auto increment increment    1
auto increment offset    1
automatic sp privileges    ON
back log    50
basedir    /usr/
binlog cache size    1.048.576
bulk insert buffer size    8.388.608
character set client    utf8
(Globaler Wert)    latin1
character set connection    utf8
(Globaler Wert)    latin1
character set database    latin1
character set filesystem    binary
character set results    utf8
(Globaler Wert)    latin1
character set server    latin1
character set system    utf8
character sets dir    /usr/share/mysql/charsets/
collation connection    utf8_general_ci
(Globaler Wert)    latin1_swedish_ci
collation database    latin1_swedish_ci
collation server    latin1_swedish_ci
completion type    0
concurrent insert    1
connect timeout    5
datadir    /var/lib/mysql/
date format    %Y-%m-%d
datetime format    %Y-%m-%d %H:%i:%s
default week format    0
delay key write    ON
delayed insert limit    100
delayed insert timeout    300
delayed queue size    1.000
div precision increment    4
keep files on create    OFF
engine condition pushdown    OFF
expire logs days    0
flush    OFF
flush time    0
ft boolean syntax    + -><()~*:""&|
ft max word len    84
ft min word len    4
ft query expansion limit    20
ft stopword file    (built-in)
group concat max len    1.024
have archive    NO
have bdb    NO
have blackhole engine    NO
have compress    YES
have crypt    YES
have csv    NO
have dynamic loading    YES
have example engine    NO
have federated engine    NO
have geometry    YES
have innodb    YES
have isam    NO
have merge engine    YES
have ndbcluster    NO
have openssl    NO
have ssl    NO
have query cache    YES
have raid    NO
have rtree keys    YES
have symlink    YES
hostname    h1603473
init connect    
init file    
init slave    
innodb additional mem pool size    1.048.576
innodb autoextend increment    8
innodb buffer pool awe mem mb    0
innodb buffer pool size    8.388.608
innodb checksums    ON
innodb commit concurrency    0
innodb concurrency tickets    500
innodb data file path    ibdata1:10M:autoextend
innodb data home dir    
innodb doublewrite    ON
innodb fast shutdown    1
innodb file io threads    4
innodb file per table    OFF
innodb flush log at trx commit    1
innodb flush method    
innodb force recovery    0
innodb lock wait timeout    50
innodb locks unsafe for binlog    OFF
innodb log arch dir    
innodb log archive    OFF
innodb log buffer size    1.048.576
innodb log file size    5.242.880
innodb log files in group    2
innodb log group home dir    ./
innodb max dirty pages pct    90
innodb max purge lag    0
innodb mirrored log groups    1
innodb open files    300
innodb rollback on timeout    OFF
innodb support xa    ON
innodb sync spin loops    20
innodb table locks    ON
innodb thread concurrency    8
innodb thread sleep delay    10.000
interactive timeout    28.800
join buffer size    2.097.152
key buffer size    16.777.216
key cache age threshold    300
key cache block size    1.024
key cache division limit    100
language    /usr/share/mysql/english/
large files support    ON
large page size    0
large pages    OFF
lc time names    en_US
license    GPL
local infile    OFF
locked in memory    OFF
log    OFF
log bin    OFF
log bin trust function creators    OFF
log error    
log queries not using indexes    OFF
log slave updates    OFF
log slow queries    OFF
log warnings    1
long query time    10
low priority updates    OFF
lower case file system    OFF
lower case table names    0
max allowed packet    33.554.432
max binlog cache size    18.446.744.073.709.551.616
max binlog size    1.073.741.824
max connect errors    10
max connections    1.000
max delayed threads    20
max error count    64
max heap table size    67.108.864
max insert delayed threads    20
max join size    18.446.744.073.709.551.616
max length for sort data    1.024
max prepared stmt count    16.382
max relay log size    0
max seeks for key    18.446.744.073.709.551.616
max sort length    1.024
max sp recursion depth    0
max tmp tables    32
max user connections    0
max write lock count    18.446.744.073.709.551.616
multi range count    256
myisam data pointer size    6
myisam max sort file size    9.223.372.036.854.775.808
myisam recover options    OFF
myisam repair threads    1
myisam sort buffer size    67.108.864
myisam stats method    nulls_unequal
net buffer length    8.192
net read timeout    30
net retry count    10
net write timeout    60
new    OFF
old passwords    OFF
open files limit    1.024
optimizer prune level    1
optimizer search depth    62
pid file    /var/lib/mysql/mysqld.pid
port    3.306
preload buffer size    32.768
profiling    OFF
profiling history size    15
protocol version    10
query alloc block size    8.192
query cache limit    16.777.216
query cache min res unit    4.096
query cache size    134.217.728
query cache type    ON
query cache wlock invalidate    OFF
query prealloc size    8.192
range alloc block size    2.048
read buffer size    2.097.152
read only    OFF
read rnd buffer size    8.388.608
relay log purge    ON
relay log space limit    0
rpl recovery rank    0
secure auth    OFF
secure file priv    
server id    1
skip external locking    ON
skip networking    OFF
skip show database    OFF
slave compressed protocol    OFF
slave load tmpdir    /var/lib/mysql/.tmp/
slave net timeout    3.600
slave skip errors    OFF
slave transaction retries    10
slow launch time    2
socket    /var/lib/mysql/mysql.sock
sort buffer size    2.097.152
sql big selects    ON
sql mode    
sql notes    ON
sql warnings    OFF
ssl ca    
ssl capath    
ssl cert    
ssl cipher    
ssl key    
storage engine    MyISAM
sync binlog    0
sync frm    ON
system time zone    CEST
table cache    1.024
table lock wait timeout    50
table type    MyISAM
thread cache size    24
thread stack    262.144
time format    %H:%i:%s
time zone    SYSTEM
timed mutexes    OFF
tmp table size    67.108.864
tmpdir    /var/lib/mysql/.tmp/
transaction alloc block size    8.192
transaction prealloc size    4.096
tx isolation    REPEATABLE-READ
updatable views with limit    YES
version    5.0.51a
version comment    SUSE MySQL RPM
version compile machine    x86_64
version compile os    suse-linux-gnu
wait timeout    28.800


Vielen Dank schon mal!
edit matzewe01: Die Konfigurationsdaten in code Tags gepackt.
Last edited by Anonymous on 2011-01-10 00:50, edited 1 time in total.

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

Re: MySQL richtig konfigurieren

Post by Joe User » 2011-01-10 00:31

max_connections ist schonmal definitiv viel zu hoch und sollte bei 4GB RAM und einem LAMP-System keinesfalls über 50 und idealerweise zwischen 10 und 30 liegen.

Ansonsten bitte mysqltuner.pl und tuning-primer.sh laufen lassen und die Ausgaben hier posten.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

2Papeete
Posts: 18
Joined: 2011-01-09 23:17

Re: MySQL richtig konfigurieren

Post by 2Papeete » 2011-01-10 16:54

Vielen Dank schon mal!

Habe mir tuning-primer.sh runtergeladen und nachgeschaut, wie man es verwendet. Habe mich als root eingeloggt und das ganze in das Verzeichnis usr/bin geladen. Jetzt weiß ich nicht, wie ich mir das ganze anzeigen lassen kann.

Ein anderes Problem ist, dass auf einmal ein Teil unserer Internetseiten wegen einem Datenbankfehler nicht mehr funktioniert, habe wahrscheinlich versehentlich etwas falsch eingestellt.

Hier der Fehlercode, vielleicht weiß ja jemand Rat *hoff'*

Code: Select all

400 - JT-E-9: Query Error -> DB function failed with error number 1
Can't create/write to file '/var/lib/mysql/.tmp/#sql_b76_0.MYI' (Errcode: 24) SQL=SELECT (SELECT count(cmtid) FROM jos_joomgallery_comments WHERE cmtpic=id) AS cmtcount, p.id AS picid, p.catid, p.imgthumbname, p.imgfilename, p.imgdate, p.imgtitle,p.imgauthor, p.owner, p.imgtext, p.imgcounter, p.imgvotes, (p.imgvotesum/p.imgvotes) AS vote,c.name AS cattitle,c.catpath as catpath, co.cmttext, co.cmtdate, co.userid, co.cmtid FROM jos_joomgallery_comments AS co LEFT JOIN jos_joomgallery AS p ON p.id = co.cmtpic LEFT JOIN jos_joomgallery_catg AS c ON c.cid = p.catid WHERE c.published = '1' AND c.access <= 24 AND p.published = 1 AND p.approved = 1 AND co.userid = '64' AND co.published = 1 AND co.approved = 1 ORDER BY co.cmtdate DESC,cmtcount DESC LIMIT 0,9
SQL =

SELECT (SELECT count(cmtid) FROM jos_joomgallery_comments WHERE cmtpic=id) AS cmtcount,
             p.id AS picid, p.catid, p.imgthumbname, p.imgfilename,
             p.imgdate, p.imgtitle,p.imgauthor, p.owner, p.imgtext, p.imgcounter, p.imgvotes,
             (p.imgvotesum/p.imgvotes) AS vote,c.name AS cattitle,c.catpath as catpath,
             co.cmttext, co.cmtdate, co.userid, co.cmtid
             FROM jos_joomgallery_comments AS co
             LEFT JOIN jos_joomgallery AS p ON p.id = co.cmtpic
             LEFT JOIN jos_joomgallery_catg AS c ON c.cid = p.catid
             WHERE c.published = '1'
             AND c.access <= 24
             AND p.published = 1
             AND p.approved = 1
             AND co.userid = '64'
             AND co.published = 1
             AND co.approved = 1 ORDER BY co.cmtdate DESC,cmtcount DESC LIMIT 0,9

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

Re: MySQL richtig konfigurieren

Post by Joe User » 2011-01-10 17:06

Code: Select all

[root@devzero:~] # perror 24
Too many open files

Das bedeutet, dass das Open-Files-Limit (siehe Deine my.cnf) für Deinen MySQL-Server erschöpft ist. Poste bitte Deine my.cnf und die Ausgabe von tuning-primer.sh. Letzterem verpasst Du ein `chmod 0755` und startest es dann wie jedes andere Script/Programm auch, indem Du es einfach Aufrufst.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

2Papeete
Posts: 18
Joined: 2011-01-09 23:17

Re: MySQL richtig konfigurieren

Post by 2Papeete » 2011-01-10 23:44

Die my.cnf sagt folgendes:

Code: Select all

# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as 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.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--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]
set-variable=local-infile=0
port      = 3306
socket      = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
# log-bin=mysql-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id   = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# 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
#bdb_cache_size = 4M
#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
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

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

[mysqldump]
quick
max_allowed_packet = 16M

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

[isamchk]
key_buffer = 20M
max_connections = 1000
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

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysqld_multi.log
# user       = multi_admin
# password   = secret

# If you want to use mysqld_multi uncomment 1 or more mysqld sections
# below or add your own ones.

# WARNING
# --------
# If you uncomment mysqld1 than make absolutely sure, that database mysql,
# configured above, is not started.  This may result in corrupted data!
# [mysqld1]
# port       = 3306
# datadir    = /var/lib/mysql
# pid-file   = /var/lib/mysql/mysqld.pid
# socket     = /var/lib/mysql/mysql.sock
# user       = mysql

# [mysqld2]
# port       = 3307
# datadir    = /var/lib/mysql-databases/mysqld2
# pid-file   = /var/lib/mysql-databases/mysqld2/mysql.pid
# socket     = /var/lib/mysql-databases/mysqld2/mysql.sock
# user       = mysql

# [mysqld3]
# port       = 3308
# datadir    = /var/lib/mysql-databases/mysqld3
# pid-file   = /var/lib/mysql-databases/mysqld3/mysql.pid
# socket     = /var/lib/mysql-databases/mysqld3/mysql.sock
# user       = mysql

# [mysqld6]
# port       = 3309
# datadir    = /var/lib/mysql-databases/mysqld6
# pid-file   = /var/lib/mysql-databases/mysqld6/mysql.pid
# socket     = /var/lib/mysql-databases/mysqld6/mysql.sock
# user       = mysql



Wie ich das tuning-primer.sh Script starte, stehe ich auf dem Schlauch. Ich habe die Rechte 755 vergeben, dann versuche ich es aufzurufen im Firefox, in dem ich "/usr/bin/tuning-primer.sh" anhänge, dann findet er nix.

2Papeete
Posts: 18
Joined: 2011-01-09 23:17

Re: MySQL richtig konfigurieren

Post by 2Papeete » 2011-01-10 23:59

Console? Noch nie gemacht sowas. Ich greife entweder mit Plesk oder mit dem FTP-Programm (Filezilla) auf den Server zu. Wie mache ich das dann?

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

Re: MySQL richtig konfigurieren

Post by Joe User » 2011-01-11 00:13

Schicke mir bitte per PM die IP-Adresse und das root-Passwort für den Server, dann schaue ich mir das morgen nachmittag mal unverbindlich an.

Die Standpauke zum Grundlagenwissen und Co erspare ich uns in diesem Thread.
Last edited by Joe User on 2011-01-11 00:15, edited 1 time in total.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

2Papeete
Posts: 18
Joined: 2011-01-09 23:17

Re: MySQL richtig konfigurieren

Post by 2Papeete » 2011-01-11 00:48

Die sitzt auch so. Komme mir grade richtig dämlich vor. Habe inzwischen im Netz geforstet und mir Putty runtergelden und den Befehl ausgeführt. Daher hier nun zuerst die aktuelle my.cnf:

Code: Select all

# Example MySQL config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as 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.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the "--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]
set-variable=local-infile=0
port      = 3306
socket      = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

# Don't listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the "enable-named-pipe" option) will render mysqld useless!
#
#skip-networking

# Replication Master Server (default)
# binary logging is required for replication
# log-bin=mysql-bin

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id   = 1

# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
#    the syntax is:
#
#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,
#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
#
#    where you replace <host>, <user>, <password> by quoted strings and
#    <port> by the master's port number (3306 by default).
#
#    Example:
#
#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
#    MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
#    start replication for the first time (even unsuccessfully, for example
#    if you mistyped the password in master-password and the slave fails to
#    connect), the slave will create a master.info file, and any later
#    change in this file to the variables' values below will be ignored and
#    overridden by the content of the master.info file, unless you shutdown
#    the slave server, delete master.info and restart the slaver server.
#    For that reason, you may want to leave the lines below untouched
#    (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 - 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id       = 2
#
# The replication master for this slave - required
#master-host     =   <hostname>
#
# The username the slave will use for authentication when connecting
# to the master - required
#master-user     =   <username>
#
# The password the slave will authenticate with when connecting to
# the master - required
#master-password =   <password>
#
# The port the master is listening on.
# optional - defaults to 3306
#master-port     =  <port>
#
# binary logging - not required for slaves, but recommended
#log-bin=mysql-bin

# 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
#bdb_cache_size = 4M
#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
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

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

[mysqldump]
quick
max_allowed_packet = 16M

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

[isamchk]
key_buffer = 20M
max_connections = 24
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

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysqld_multi.log
# user       = multi_admin
# password   = secret

# If you want to use mysqld_multi uncomment 1 or more mysqld sections
# below or add your own ones.

# WARNING
# --------
# If you uncomment mysqld1 than make absolutely sure, that database mysql,
# configured above, is not started.  This may result in corrupted data!
# [mysqld1]
# port       = 3306
# datadir    = /var/lib/mysql
# pid-file   = /var/lib/mysql/mysqld.pid
# socket     = /var/lib/mysql/mysql.sock
# user       = mysql

# [mysqld2]
# port       = 3307
# datadir    = /var/lib/mysql-databases/mysqld2
# pid-file   = /var/lib/mysql-databases/mysqld2/mysql.pid
# socket     = /var/lib/mysql-databases/mysqld2/mysql.sock
# user       = mysql

# [mysqld3]
# port       = 3308
# datadir    = /var/lib/mysql-databases/mysqld3
# pid-file   = /var/lib/mysql-databases/mysqld3/mysql.pid
# socket     = /var/lib/mysql-databases/mysqld3/mysql.sock
# user       = mysql

# [mysqld6]
# port       = 3309
# datadir    = /var/lib/mysql-databases/mysqld6
# pid-file   = /var/lib/mysql-databases/mysqld6/mysql.pid
# socket     = /var/lib/mysql-databases/mysqld6/mysql.sock
# user       = mysql


und die tuning-primer.sh Auswertung:

Code: Select all

Status:
=====================================
110111  0:32:55 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 6 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 85291, signal count 85276
Mutex spin waits 0, rounds 1580939, OS waits 78938
RW-shared spins 12573, OS waits 6286; RW-excl spins 76, OS waits 55
------------
TRANSACTIONS
------------
Trx id counter 0 2075521
Purge done for trx's n:o < 0 2075519 undo n:o < 0 0
History list length 4
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 2934, OS thread id 1164712272
MySQL thread id 8960825, query id 571900185 localhost admin
SHOW /*!50000 ENGINE */ INNODB STATUS
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
1215 OS file reads, 218936 OS file writes, 198969 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 17393, used cells 1128, node heap has 3 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 47339822
Log flushed up to   0 47339822
Last checkpoint at  0 47339822
0 pending log writes, 0 pending chkp writes
183830 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 22707936; in additional pool allocated 1048576
Buffer pool size   512
Free buffers       1
Database pages     508
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 1426, created 1419, written 33728
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 2934, id 1153796432, state: waiting for server activity
Number of rows inserted 11153, updated 163715, deleted 9833, read 6474570
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

/usr/bin/tuning-primer.sh: line 440: bc: command not found
Current innodb_buffer_pool_size =  M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
/usr/bin/tuning-primer.sh: line 1321: bc: command not found
/usr/bin/tuning-primer.sh: line 1322: bc: command not found
/usr/bin/tuning-primer.sh: line 1346: bc: command not found
/usr/bin/tuning-primer.sh: line 1349: bc: command not found
/usr/bin/tuning-primer.sh: line 1350: bc: command not found
/usr/bin/tuning-primer.sh: line 1352: bc: command not found
/usr/bin/tuning-primer.sh: line 1354: [: -gt: unary operator expected
/usr/bin/tuning-primer.sh: line 459: [: max_memoryHR: integer expression expected
/usr/bin/tuning-primer.sh: line 465: [: max_memoryHR: integer expression expected
/usr/bin/tuning-primer.sh: line 471: [: max_memoryHR: integer expression expected
/usr/bin/tuning-primer.sh: line 478: export: `=max_memoryHR': not a valid identifier
Max Memory Ever Allocated :  bytes
/usr/bin/tuning-primer.sh: line 459: [: per_thread_buffersHR: integer expression expected
/usr/bin/tuning-primer.sh: line 465: [: per_thread_buffersHR: integer expression expected
/usr/bin/tuning-primer.sh: line 471: [: per_thread_buffersHR: integer expression expected
/usr/bin/tuning-primer.sh: line 478: export: `=per_thread_buffersHR': not a valid identifier
Configured Max Per-thread Buffers :  bytes
/usr/bin/tuning-primer.sh: line 459: [: global_buffersHR: integer expression expected
/usr/bin/tuning-primer.sh: line 465: [: global_buffersHR: integer expression expected
/usr/bin/tuning-primer.sh: line 471: [: global_buffersHR: integer expression expected
/usr/bin/tuning-primer.sh: line 478: export: `=global_buffersHR': not a valid identifier
Configured Max Global Buffers :  bytes
/usr/bin/tuning-primer.sh: line 459: [: total_memoryHR: integer expression expected
/usr/bin/tuning-primer.sh: line 465: [: total_memoryHR: integer expression expected
/usr/bin/tuning-primer.sh: line 471: [: total_memoryHR: integer expression expected
/usr/bin/tuning-primer.sh: line 478: export: `=total_memoryHR': not a valid identifier
Configured Max Memory Limit :  bytes
/usr/bin/tuning-primer.sh: line 440: bc: command not found
Physical Memory :  G
Max memory limit seem to be within acceptable norms

KEY BUFFER
ERROR 1018 (HY000) at line 1: Can't read dir of '.' (errno: 24)
/usr/bin/tuning-primer.sh: line 754: bc: command not found
/usr/bin/tuning-primer.sh: line 755: bc: command not found
/usr/bin/tuning-primer.sh: line 440: bc: command not found
Current MyISAM index space =  M
/usr/bin/tuning-primer.sh: line 440: bc: command not found
Current key_buffer_size =  M
Key cache miss rate is 1 : 1477
Key buffer free ratio =  %
/usr/bin/tuning-primer.sh: line 792: [: -le: unary operator expected
/usr/bin/tuning-primer.sh: line 796: [: -le: unary operator expected
Your key_buffer_size seems to be fine

QUERY CACHE
/usr/bin/tuning-primer.sh: line 827: bc: command not found
/usr/bin/tuning-primer.sh: line 828: bc: command not found
Query cache is enabled
/usr/bin/tuning-primer.sh: line 440: bc: command not found
Current query_cache_size =  M
/usr/bin/tuning-primer.sh: line 440: bc: command not found
Current query_cache_used =  M
/usr/bin/tuning-primer.sh: line 440: bc: command not found
Current query_cache_limit =  M
Current Query cache Memory fill ratio =  %
/usr/bin/tuning-primer.sh: line 440: bc: command not found
Current query_cache_min_res_unit =  K
/usr/bin/tuning-primer.sh: line 845: bc: command not found
/usr/bin/tuning-primer.sh: line 846: bc: command not found
/usr/bin/tuning-primer.sh: line 847: [: -gt: unary operator expected
/usr/bin/tuning-primer.sh: line 854: [: -le: unary operator expected
/usr/bin/tuning-primer.sh: line 858: [: -ge: unary operator expected
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
/usr/bin/tuning-primer.sh: line 440: bc: command not found
Current sort_buffer_size =  M
/usr/bin/tuning-primer.sh: line 440: bc: command not found
Current read_rnd_buffer_size =  M
Sort buffer seems to be fine

JOINS
/usr/bin/tuning-primer.sh: line 440: bc: command not found
Current join_buffer_size =  M
You have had 2657389 queries where a join could not use an index properly
You have had 944 joins without keys that check for key usage after each row
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 = 1024 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
You currently have open more than 75% of your open_files_limit
You should set a higher value for open_files_limit in my.cnf

TABLE CACHE
ERROR 1018 (HY000) at line 1: Can't read dir of '.' (errno: 24)
Current table_cache value = 1024 tables
You have a total of 937 tables
You have 658 open tables.
The table_cache value seems to be fine

TEMP TABLES
/usr/bin/tuning-primer.sh: line 440: bc: command not found
Current max_heap_table_size =  M
/usr/bin/tuning-primer.sh: line 440: bc: command not found
Current tmp_table_size =  M
Of 19901820 temp tables, 45% were created on disk
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
/usr/bin/tuning-primer.sh: line 440: bc: command not found
Current read_buffer_size =  M
Current table scan ratio = 1689 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 260
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'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.

2Papeete
Posts: 18
Joined: 2011-01-09 23:17

Re: MySQL richtig konfigurieren

Post by 2Papeete » 2011-01-11 17:04

Wie mache ich das? Soll wohl mit dem Code "apt-get install bc" funktionieren, nur wie gehe ich konkret vor?

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

Re: MySQL richtig konfigurieren

Post by Joe User » 2011-01-11 17:39

Auf Grund Deiner my.cnf gehe ich davon aus, das Du kein Debian als OS hast, weshalb Dir "apt-get" nicht zur Verfügung stehen wird. Welches OS ist denn installiert?
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

2Papeete
Posts: 18
Joined: 2011-01-09 23:17

Re: MySQL richtig konfigurieren

Post by 2Papeete » 2011-01-11 23:12

Linux 2.6.25.18-0.2-default mit Plesk 9.

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

Re: MySQL richtig konfigurieren

Post by Joe User » 2011-01-11 23:27

Laut Google ist es der openSUSE 11.0 Standard-Kernel, somit installierst Du bc mit:

Code: Select all

zypper install bc

Danach führst Du tuning-primer.sh bitte nochmal aus und postest die Ausgabe.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

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

Re: MySQL richtig konfigurieren

Post by Joe User » 2011-01-11 23:35

Letzteres zumindest nicht in diesem Thread, sonst wirds zu unübersichtlich und hilft dem Betroffenen momentan leider auch nicht.

Wenn Plesk nicht wäre, würde ich ihm das notwendige Update auch abnehmen, denn er ist ja mitlerweile lernwillig und hat die Rüge wohl verstanden.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

2Papeete
Posts: 18
Joined: 2011-01-09 23:17

Re: MySQL richtig konfigurieren

Post by 2Papeete » 2011-01-12 15:46

Habe noch mal geforstet und es ist tatsächlich "openSUSE 11.0 + Plesk 9.0 x64" installiert.

Ihr habt natürlich Recht, dass einiges an Basiswissen fehlt und ich möchte mich noch mal entschuldigen, dass ich dadurch für euch selbstverständliche Sachen nachfrage. Ich will auch gar keine umfassende Basiswissenseinführung oder so.

Mein Problem ist halt im Moment, dass die MySQL-Datenbank so falsch konfiguriert ist, dass bei den auf unserem Server geladenen Internetseiten teilweise die Fehlermeldung "Could not connect to MySQL" kommt und Sie dadurch nicht mehr richtig funktionieren.

bc ist nun installiert, hier das neue tuning-primer.sh Ergebis:

Code: Select all

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

MySQL Version 5.0.51a x86_64

Uptime = 592 days 23 hrs 16 min 59 sec
Avg. qps = 11
Total Questions = 572194158
Threads Connected = 1

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10 sec.
You have 38060 out of 572194179 that take longer than 10 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html

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

MAX CONNECTIONS
Current max_connections = 1000
Current threads_connected = 1
Historic max_used_connections = 157
The number of used connections is 15% of the configured maximum.
Your max_connections variable seems to be fine.

INNODB STATUS
ERROR 1018 (HY000) at line 1: Can't read dir of '.' (errno: 24)
ERROR 1018 (HY000) at line 1: Can't read dir of '.' (errno: 24)
Cannot parse InnoDB stats prior to 5.0.x
*************************** 1. row ***************************
Status:
=====================================
110112 15:36:03 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 38 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 85332, signal count 85317
Mutex spin waits 0, rounds 1581200, OS waits 78951
RW-shared spins 12629, OS waits 6314; RW-excl spins 76, OS waits 55
------------
TRANSACTIONS
------------
Trx id counter 0 2076822
Purge done for trx's n:o < 0 2076793 undo n:o < 0 0
History list length 12
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 2934, OS thread id 1177758032
MySQL thread id 8967426, query id 572194266 localhost admin
SHOW /*!50000 ENGINE */ INNODB STATUS
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
1215 OS file reads, 219423 OS file writes, 199349 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 17393, used cells 1137, node heap has 3 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 47405022
Log flushed up to   0 47405022
Last checkpoint at  0 47405022
0 pending log writes, 0 pending chkp writes
184132 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 22707944; in additional pool allocated 1048576
Buffer pool size   512
Free buffers       1
Database pages     508
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 1426, created 1419, written 33892
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 2934, id 1153796432, state: waiting for server activity
Number of rows inserted 11193, updated 163926, deleted 9853, read 6478368
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

Current innodb_buffer_pool_size = 8 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 2.33 G
Configured Max Per-thread Buffers : 13.91 G
Configured Max Global Buffers : 154 M
Configured Max Memory Limit : 14.06 G
Physical Memory : 3.84 G

nMax memory limit exceeds 90% of physical memory

KEY BUFFER
ERROR 1018 (HY000) at line 1: Can't read dir of '.' (errno: 24)
Current MyISAM index space = 11 M
Current key_buffer_size = 16 M
Key cache miss rate is 1 : 1478
Key buffer free ratio = 49 %
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 = 128 M
Current query_cache_used = 36 M
Current query_cache_limit = 16 M
Current Query cache Memory fill ratio = 28.67 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 8 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 2.00 M
You have had 2659910 queries where a join could not use an index properly
You have had 944 joins without keys that check for key usage after each row
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 = 1024 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
You currently have open more than 75% of your open_files_limit
You should set a higher value for open_files_limit in my.cnf

TABLE CACHE
ERROR 1018 (HY000) at line 1: Can't read dir of '.' (errno: 24)
Current table_cache value = 1024 tables
You have a total of 937 tables
You have 658 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 64 M
Current tmp_table_size = 64 M
Of 19915760 temp tables, 45% were created on disk
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 = 2 M
Current table scan ratio = 1690 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 260
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'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.

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

Re: MySQL richtig konfigurieren

Post by Joe User » 2011-01-12 16:54

Mache bitte ein Backup der my.cnf und von allen Datenbanken!
Danach überschreibst Du die jetzige my.cnf mit folgendem Inhalt:

Code: Select all

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

[mysql]
prompt                          = \u@\h [\d]>\_
no_auto_rehash

[mysqldump]
max_allowed_packet              = 32M
quote_names
quick

[isamchk]
key_buffer_size                 = 256M

[myisamchk]
key_buffer_size                 = 256M

[mysqld_safe]
err_log                         = /var/lib/mysql/mysqld.log

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

[mysqld]
user                            = mysql
port                            = 3306
bind-address                    = 127.0.0.1
socket                          = /var/lib/mysql/mysql.sock
log_error                       = /var/lib/mysql/mysqld.log
log-bin                         = /var/lib/mysql/mysql-bin
relay-log                       = /var/lib/mysql/relay.log
relay-log-index                 = /var/lib/mysql/relay.index
relay-log-info-file             = /var/lib/mysql/relay.info
master-info-file                = /var/lib/mysql/master.info
#master-host                     = <hostname>
#master-user                     = <username>
#master-password                 = <password>
#master-port                     = 3306
#auto_increment_increment        = 10
#auto_increment_offset           = 1
server-id                       = 1
back_log                        = 50
sync_binlog                     = 1
binlog_cache_size               = 1M
max_binlog_size                 = 100M
binlog-format                   = MIXED
expire_logs_days                = 7
slow-query-log                  = 1
slow-query-log-file             = /var/lib/mysql/slow-query.log
slave_compressed_protocol       = 1
safe-user-create                = 1
delay-key-write                 = ALL
myisam-recover                  = FORCE,BACKUP
key_buffer_size                 = 32M
join_buffer_size                = 2M
sort_buffer_size                = 2M
read_buffer_size                = 2M
read_rnd_buffer_size            = 8M
myisam_sort_buffer_size         = 64M
max_allowed_packet              = 32M
max_heap_table_size             = 64M
tmp_table_size                  = 64M
open-files-limit                = 4096
table_cache                     = 1024
query_cache_type                = 1
query_cache_size                = 64M
query_cache_limit               = 16M
thread_concurrency              = 8
thread_cache_size               = 24
max_connections                 = 32
ft_max_word_len                 = 20
ft_min_word_len                 = 3
long_query_time                 = 2
local-infile                    = 0
log-warnings                    = 2
log-slave-updates
log-queries-not-using-indexes
skip-external-locking
skip-locking
skip-innodb
skip-bdb

[mysqlhotcopy]
interactive_timeout

[mysqld_multi]
mysqld                          = /usr/bin/mysqld_safe
mysqladmin                      = /usr/bin/mysqladmin
log                             = /var/log/mysqld_multi.log

Nun kannst Du den MySQL-Server restarten und sofern ich keinen Flüchtigkeitsfehler gemacht habe, sollte der MySQL normal starten und in ein paar Tagen etwas besser laufen als bisher. Wunder bewirkt die neue Konfiguration aber nicht, weil die Webanwendungen extrem schlecht programmiert sind (fehlende INDEXe und zu viele TEXT/BLOB-Felder) und dadurch die eigentlichen Probleme verursachen.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

2Papeete
Posts: 18
Joined: 2011-01-09 23:17

Re: MySQL richtig konfigurieren

Post by 2Papeete » 2011-01-14 18:02

Habe ich gemacht, leider ohne Erfolg. Im Gegenteil :(

Es kommt weiterhin diese Fehlermeldung:
Database Error: Unable to connect to the database:Could not connect to MySQL

Und ich kann mich weder in Plesk, noch in PHPMyAdmin einloggen...

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

Re: MySQL richtig konfigurieren

Post by Joe User » 2011-01-14 18:11

Wie sieht tuning-primer.sh jetzt aus? mysqltuner.pl bitte zum Vergleich ebenfalls laufen lassen.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

2Papeete
Posts: 18
Joined: 2011-01-09 23:17

Re: MySQL richtig konfigurieren

Post by 2Papeete » 2011-01-14 18:16

Gehlt leider nicht, Ergebnis ist folgendes:

The mysqld process is not running or it is installed in a custom location.
If you are sure mysqld is running, execute script in "prompt" mode or set
the socket= variable at the top of this script

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

Re: MySQL richtig konfigurieren

Post by Joe User » 2011-01-14 18:40

Bitte beide Ausgaben posten:

Code: Select all

ps auxf | grep -i mysql

tail -n 20 /var/lib/mysql/mysqld.log
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

2Papeete
Posts: 18
Joined: 2011-01-09 23:17

Re: MySQL richtig konfigurieren

Post by 2Papeete » 2011-01-14 20:48

Einmal:
root 10074 0.0 0.0 4284 768 pts/0 S+ 20:45 0:00 \_ grep -i mysql

Beim anderen passiert leider gar nichts.

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

Re: MySQL richtig konfigurieren

Post by Joe User » 2011-01-14 21:12

Der MySQL-Server läuft also gar nicht, dann starten wir mal:

Code: Select all

/etc/init.d/mysql start
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

2Papeete
Posts: 18
Joined: 2011-01-09 23:17

Re: MySQL richtig konfigurieren

Post by 2Papeete » 2011-01-14 21:39

Kommt leider nur das:

Starting service MySQL warning: /var/lib/mysql/mysql.sock didn't appear within 30 seconds

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

Re: MySQL richtig konfigurieren

Post by Joe User » 2011-01-14 23:21

Code: Select all

ls -alh /var/{lib,log}/mysql/
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.

2Papeete
Posts: 18
Joined: 2011-01-09 23:17

Re: MySQL richtig konfigurieren

Post by 2Papeete » 2011-01-15 04:28

Erhalte ich dieses Ergebnis:

Code: Select all

ls: cannot access /var/log/mysql: No such file or directory
/var/lib/mysql:
total 29M
drwxr-xr-x 15 mysql mysql 4.0K Jan 13 15:45 .
drwxr-xr-x 33 root  root  4.0K May 29  2009 ..
drwx------  2 mysql mysql 4.0K May 29  2009 .protected
drwxr-xr-x  2 mysql mysql 4.0K Jan 10 05:02 .tmp
drwx------  2 mysql mysql  24K May 29  2009 atmail
drwx------  2 mysql mysql 4.0K Oct  7  2009 cms
drwx------  2 mysql mysql 4.0K Jun 17  2009 cpg
drwx------  2 mysql mysql  12K Sep 24  2009 dportal
drwx------  2 mysql mysql 4.0K Oct 31 19:27 gcms
drwx------  2 mysql mysql 4.0K Dec 26  2009 horde
-rw-rw----  1 mysql mysql 5.0M Jan 13 13:25 ib_logfile0
-rw-rw----  1 mysql mysql 5.0M Jan 13 13:04 ib_logfile1
-rw-rw----  1 mysql mysql  18M Jan 13 13:25 ibdata1
drwx------  2 mysql mysql  16K Aug 22 17:35 jcms
-rw-r--r--  1 root  root  6.2K Jan  8 19:02 my.cnf
drwx------  2 mysql mysql 4.0K May 29  2009 mysql
-rw-r--r--  1 mysql mysql    7 May 29  2009 mysql_upgrade_info
-rw-rw----  1 mysql mysql  435 Jan 14 21:57 mysqld.log
-rw-rw----  1 mysql mysql  28K Jan 13 13:25 mysqld.log-20090530
-rw-rw----  1 mysql mysql  153 Jan 13 15:45 mysqld.log-20110113.bz2
drwx------  2 mysql mysql 8.0K Nov 25 03:27 pcms
drwx------  2 mysql mysql 4.0K May 29  2009 phpmyadmin_vgsoZaaYu9tc
drwx------  2 mysql mysql 8.0K May 29  2009 psa
Last edited by 2Papeete on 2011-01-15 04:29, edited 1 time in total.