MYSQL 5.5.46 Server Performance Tuning Ubuntu 14.04

[Gerry]

Hallo zusammen, 

ich habe einen Ubuntu 14.04 TLS Server, es laufen einige Große Webseiten drauf die meinen MYSQL Server ziemlich in Anspruch nehmen. Jetzt wollte ich nach möglichkeiten schauen um die Performance zu verbessern weil einige 'SELECT' abfragen einfach zu lange dauern. 

Hardware:
2x Intel Xeon 5520 
30 GB Arbeitsspeicher
2x 1TB HDD Festplatten 7200

Das sind glaub ich die wichtigsten Eckdaten.

Der Server verbaucht ca. 2 GB Ram. Welche Parameter müsste ich verändern um mehr Leistung zu bekommen?

[geforceGTX]

Hi,
um ein besseren Bild von der Lage zu bekommen. Wäre es noch hilfreich, wenn du angeben könntest wie viele Datensätze dein MYSQL Datenbank Server hält. Zusätlich wäre deine Server Config hilfreich. Findest du unter

/etc/mysql/my.cnf

einfach mal posten

[Gerry]

Guten Morgen, 

hier mein Auszug aus PHPMyAdmin:

Insgesamt: 75 latin1_swedish_ci 4,811 36,297,606 5,6 GiB 1,6 GiB 7,2 GiB

Meine aktuelle MYSQL Server Config:

PHP-Code:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[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]
local-infile=0
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket        = /var/run/mysqld/mysqld.sock
port        = 3306
basedir        = /usr
datadir        = /var/lib/mysql
tmpdir        = /tmp
lc-messages-dir    = /usr/share/mysql
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        = 16M
max_allowed_packet    = 16M
thread_stack        = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit    = 1M
query_cache_size        = 16M
#
# * 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
#
# Error log - should be very few entries.
#
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!
#
# * 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/


Danke! 


[geforceGTX]

Mahlzeit,

schon besser, dass sind zwar jetzt nicht wenig Daten, aber so wild es auch nicht. Ich habe schon MYSQL instanzen gehabt, wo eine Datenbank schon 80 GB hatte. Hast du memcached als Service bei dir laufen auf dem Server?

[Gerry]

Hi,

nein, eine Memcached Instanz ist nicht auf meinem Server installiert und ich habe es auch nicht vor. Da bereits andere Caching Systeme implementiert wurden.

[geforceGTX]

Okay, ich hab dir mal eine MYSQL my.cnf geschrieben. Du kannst die mal probieren und schauen ob es besser läuft als bisher. Ich habe leider keinen Test Server da in der Version 5.5.46. Du kannst aber bevor du die Server Config veränderst noch ein update einspielen. Die aktuell freigegeben Version ist MYSQL 5.5.47 für Ubuntu 14.04 anschließend mal die neue Config testen. 

PHP-Code:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[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

innodb_buffer_pool_size=12G
innodb_additional_mem_pool_size=256M
innodb_log_buffer_size=512M
innodb_thread_concurrency=0
[mysqld]
local-infile=0
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket        = /var/run/mysqld/mysqld.sock
port        = 3306
basedir        = /usr
datadir        = /var/lib/mysql
tmpdir        = /tmp
lc-messages-dir    = /usr/share/mysql
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        = 0.0.0.0
#
# * Fine Tuning
#
key_buffer              = 128M
max_allowed_packet      = 32M
thread_stack        = 512K
thread_cache_size       = 512
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 2000
#table_cache            = 64
thread_concurrency     = 0
#
# * Query Cache Configuration
#
query_cache_limit       = 16M
query_cache_size        = 1024M
query_cache_type         = 1 
#
# * 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
#
# Error log - should be very few entries.
#
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!
#
# * 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



innodb_buffer_pool_size=12G
innodb_additional_mem_pool_size=256M
innodb_log_buffer_size=512M
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=2
[mysqldump]
quick
quote-names
max_allowed_packet      = 32M

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

[isamchk]
key_buffer              = 32M

#
# * 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/ 


[Gerry]

Hey,

ich habs jetzt mal gemacht wie du vorgeschlagen hast. MYSQL Server ist auf dem aktuellsten Stand, die Config habe ich auch bereits eingespielt. Ich meine der Server läuft deutlich besser. Auch die Ladezeiten von den Online Shops ist jetzt viel besser.

[19Kurt81]

Moin!

Ein ganz wichtiges Thema bei so vielen großen Webseiten ist meiner Meinung nach auf jeden Fall die richtige Verwendung von Zertifikaten.

Wir haben uns über globalprotec.com neulich diesbezüglich umgesehen und ich kann dir aus eigener Erfahrung sagen, dass das die Sicherheit stark erhöht und auch immer mehr User darauf großen Wert legen.

[Annital]

Sicherheit ist immer das A&O ... bzw. sollte es zumindest sein. Verhältnismäßig. Bei Online Shops aber sowieso!