Habe ich in meiner SQL Conf zu viel verstellt?

MySQL, PostgreSQL, SQLite
derheiko
Posts: 2
Joined: 2013-05-12 12:53

Habe ich in meiner SQL Conf zu viel verstellt?

Post by derheiko » 2013-05-12 13:06

Hallo,

ich versuche mich mal so kurz wie möglich zu fassen. Ich habe einen Root Server XXL bei Hosteurope mit 48 GB Ram, 2 Cores (1 x AMD 12-Cores) und 4 × 1.000 GB im RAID 10.

Auf Plesk habe ich verzichtet und habe nach Anleitung ISPConfig installiert.

Auf dem Server läuft "nur" ein XTCommerce Shop mit einer SQL Datenbank. Die Datenbank ist rund 1 GB groß und hat immer eine hohe Auslastung. Nun bin ich eurer Anleitung aus diesem Thread gefolgt: viewtopic.php?f=103&t=36343

Es ist schon deutlich besser geworden, aber dennoch gibt es momente an denen Minutenlang nichts mehr geht, weil der Mysqlthread auf alles Cores voll ausgelastet ist.

Ich wäre euch unheimlich dankbar, wen ihr euch mal meine SQL Settings anschauen können und sagen würdet was ich evtl. falsch gemacht habe. Ich glaube, das ich zu viel verstellt habe ](*,)

Code: Select all

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0



[mysqld]
#
# * Basic Settings
#

#
# * IMPORTANT
#   If you make changes to these settings and your system uses apparmor, you may
#   also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
#

user            = mysql
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1
#
# * Fine Tuning
#
key_buffer              = 256M
max_allowed_packet      = 32M
thread_stack            = 192K
thread_cache_size       = 80
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 500
table_cache            = 1024
thread_concurrency     = 12
#
query_cache_limit       = 2M
query_cache_size        = 128M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1

log_error                = /var/log/mysql/error.log

# Here you can see queries with especially long duration
#log_slow_queries       = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#

#innodb_thread_concurrency       = 8
#innodb_buffer_pool_size         = 2G
#innodb_additional_mem_pool_size = 16M
#innodb_data_home_dir            = /var/lib/mysql
#innodb_log_group_home_dir       = /var/lib/mysql
#innodb_data_file_path           = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_flush_method             = O_DIRECT
#innodb_log_file_size            = 128M
#innodb_log_buffer_size          = 16M
#innodb_log_files_in_group       = 2
#innodb_flush_log_at_trx_commit  = 2
#innodb_max_dirty_pages_pct      = 90
#innodb_file_per_table           = 1
#innodb_purge_threads            = 1
#innodb_strict_mode              = 1
#innodb_old_blocks_time          = 1000
#innodb_stats_on_metadata        = 0
#innodb_thread_concurrency=2




# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem


[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/



So habe ich das Problem, das ich im htop den Prozess /usr/sbin/mysqld/ rund 30 mal habe, ohne das er Cpu nutzt. Nur 0.8 MEM%

Ich bin noch ein Anfänger im Servergebiet und danke vorab für eure Hilfe ;)

Gruß Heiko

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

Re: Habe ich in meiner SQL Conf zu viel verstellt?

Post by Joe User » 2013-05-12 16:58

Bitte die Ausgaben von mysqltuner.pl und tuning-primer.sh nachliefern, danke.
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.

derheiko
Posts: 2
Joined: 2013-05-12 12:53

Re: Habe ich in meiner SQL Conf zu viel verstellt?

Post by derheiko » 2013-05-12 19:22

Danke für die rasche Antwort und Sorry :)

Code: Select all

root@r***:/home/heiko# ./mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.69-0ubuntu0.10.04.1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 249M (Tables: 160)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 7

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 4h 35m 55s (111M q [589.236 qps], 158K conn, TX: 98B, RX: 15B)
[--] Reads / Writes: 93% / 7%
[--] Total buffers: 410.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 815.8M (1% of installed RAM)
[OK] Slow queries: 0% (621/111M)
[OK] Highest usage of available connections: 70% (107/151)
[OK] Key buffer size / total MyISAM indexes: 256.0M/73.7M
[OK] Key buffer hit rate: 100.0% (16B cached / 25K reads)
[OK] Query cache efficiency: 92.7% (102M cached / 110M selects)
[!!] Query cache prunes per day: 1250685
[OK] Sorts requiring temporary tables: 8% (41K temp sorts / 514K sorts)
[!!] Temporary tables created on disk: 27% (156K on disk / 561K total)
[OK] Thread cache hit rate: 99% (163 created / 158K connections)
[OK] Table cache hit rate: 56% (737 open / 1K opened)
[OK] Open file limit used: 41% (920/2K)
[OK] Table locks acquired immediately: 99% (12M immediate / 12M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    query_cache_size (> 128M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)


Beim zweiten erhalte ich eine Frage, bei der ich nicht so recht weiß was ich beantworten soll.

Code: Select all

root@******:/home/heiko# ./tuning-primer.sh

Using login values from ~/.my.cnf
- INITIAL LOGIN ATTEMPT FAILED -
Testing for stored webmin passwords:
 None Found
Could not auto detect login info!
Found potential sockets: /var/run/mysqld/mysqld.sock
Using: /var/run/mysqld/mysqld.sock
Would you like to provide a different socket?: [y/N]


Danke dir vorab für deine geduldige Hilfe am Sonntag ;)

Heiko

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

Re: Habe ich in meiner SQL Conf zu viel verstellt?

Post by Joe User » 2013-05-12 20:09

Lege bitte das Verzeichnis /var/tmp/mysql an:

Code: Select all

mkdir /var/tmp/mysql
chmod 0750 /var/tmp/mysql
chown mysql:mysql /var/tmp/mysql

Dann bitte Deine my.cnf für den Fall der Fälle sichern und durch diese ersetzen:

Code: Select all

[client]
port                            = 3306
max_allowed_packet              = 64M

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

[mysqld]
user                            = mysql
port                            = 3306
bind-address                    = 127.0.0.1
socket                          = /var/run/mysqld/mysqld.sock
basedir                         = /usr
datadir                         = /var/lib/mysql
tmpdir                          = /var/tmp/mysql
secure-file-priv                = /var/tmp/mysql
log-bin                         = /var/lib/mysql/mysql-bin
log_error                       = /var/log/mysql/error.log
server-id                       = 1
back_log                        = 500
sync_binlog                     = 1
binlog_cache_size               = 4M
binlog_stmt_cache_size          = 4M
max_binlog_size                 = 500M
binlog-format                   = MIXED
expire_logs_days                = 30
slow-query-log                  = 1
slow-query-log-file             = /var/log/mysql/slow-query.log
safe-user-create                = 1
delay-key-write                 = ALL
myisam-recover                  = FORCE,BACKUP
key_buffer_size                 = 256M
max_allowed_packet              = 64M
max_heap_table_size             = 64M
tmp_table_size                  = 64M
thread_stack                    = 192K
table_cache                     = 2048
table_open_cache                = 2048
table_definition_cache          = 2048
query_cache_type                = 1
query_cache_size                = 128M
query_cache_limit               = 4M
query_cache_min_res_unit        = 2K
thread_cache_size               = 150
max_connections                 = 200
long_query_time                 = 1
local-infile                    = 0
log-warnings                    = 2
log-queries-not-using-indexes
skip-external-locking
skip-symbolic-links
skip-innodb

[mysqldump]
max_allowed_packet              = 64M
quote_names
quick

[isamchk]
key_buffer_size                 = 256M

[myisamchk]
key_buffer_size                 = 256M

[mysqlhotcopy]
interactive_timeout

Danach MySQL neu starten und nach 24-48 Stunden nochmal die Ausgaben von mysqltuner/tuning-primer posten. Bei tuning-primer dann bitte die richtigen Zugangsdaten für den MySQL-root angeben.
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.