V-Server - Datenbankproblem

J

jokotten

New Member
Hallo,
ich habe einen V-Server von Strato.

Auf diesem Server habe ich auch eine Domain mit einer sehr umfangreichen Datenbank, die täglich wächst. Derzeit über 140.000 Posts.

Der Aufbau der Seiten ist quälend langsam, obwohl ich bereits mit der my.cnf viel getestet habe.

Das sind die Serverdaten:

CPU Intel(R) Xeon(R) CPU E5-2680 v3 @ 2.50GHz (6 core(s))
Version Plesk Onyx v17.5.3_build1705170317.16 os_CentOS 7
OS CentOS Linux 7.3.1611 (Core)

mysql - 5.5.52-1.el7
mariadb

So sieht momentan die my.cnf aus:

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

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

# bind-address = ::
# skip_name_resolve
# local-infile=0

[mysqld]
port		= 3306
socket		= /var/lib/mysql/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 6

innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_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 = 1G
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 250M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

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

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout


!includedir /etc/my.cnf.d

Irgendwo scheint da ein Flaschenhals zu sein, den ich einfach nicht herausfinde. Kann jemand helfen?

Danke schon mal.
 
Schon daran gedacht mal Monitoring einzuführen und da zu schauen wo es bei der Datenbank Probleme gibt?
Irgendwelche Slow Queries?
Mal tuningprimer oder mysqltuner gestartet um zu sehen wo Engpässe sind?
Mit was für einer Webanwendung und mit welcher Sprache werden denn die Seiten generiert? Alles dynamisch?
 
Last edited by a moderator:
my.cnf aktualisiert und mysqltuner

Habe meine my.cnf nochmals aktualisiert, siehe weiter unten.
Es laufen ausschließlich Seiten mit WordPress auf dem Server.

Hier ein Auszug von mysqltuner

Code: 
 >>  MySQLTuner 1.6.15 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.52-MariaDB
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 1G (Tables: 319)
[--] Data in InnoDB tables: 635M (Tables: 640)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[!!] User 'pp_sb_db@localhost' has no password set.
[!!] There is no basic password file list!

-------- CVE Security Recommendations --------------------------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 36s (30K q [850.667 qps], 47 conn, TX: 118M, RX: 2M)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Physical Memory     : 12.0G
[--] Max MySQL memory    : 2.6G
[--] Other process memory: 2.6G
[--] Total buffers: 1.8G global + 5.5M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 1.8G (15.06% of installed RAM)
[OK] Maximum possible memory usage: 2.6G (21.54% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (33/30K)
[OK] Highest usage of available connections: 4% (7/151)
[OK] Aborted connections: 2.13%  (1/47)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 14K sorts)
[OK] No joins without indexes
[!!] Temporary tables created on disk: 65% (112 on disk / 172 total)
[OK] Table cache hit rate: 130% (112 open / 86 opened)
[OK] Open file limit used: 14% (144/1K)
[OK] Table locks acquired immediately: 100% (30K immediate / 30K locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 6 thread(s).
[--] Using default value is good enough for your version (5.5.52-MariaDB)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 23.1% (124M used / 536M cache)
[OK] Key buffer size / total MyISAM indexes: 512.0M/262.3M
[OK] Read Key buffer hit rate: 99.4% (4M cached / 25K reads)
[!!] Write Key buffer hit rate: 3.8% (106 cached / 102 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 97.6% (1K cached / 40 reads)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 900.0M/635.6M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 1.30% (748 used/ 57599 total)
[OK] InnoDB Read buffer efficiency: 95.91% (17532 hits/ 18279 total)
[!!] InnoDB Write Log efficiency: 0% (2 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 2 writes)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses


die my.cnf

Code: 
[mysqld]

innodb_file_per_table=1

datadir=/var/lib/mysql

open_files_limit                = 2000
table_open_cache                = 8192
table_definition_cache          = 4096
max_allowed_packet              = 256M
key_buffer_size                 = 512M
join_buffer_size 		= 256M
bulk_insert_buffer_size         = 64M
join_buffer_size                = 512K
sort_buffer_size                = 4M
read_buffer_size                = 256K
read_rnd_buffer_size            = 512K
max_heap_table_size             = 512M
tmp_table_size                  = 256M
query_cache_type                = 0
query_cache_size                = 0
long_query_time                 = 0.5

innodb_flush_method = O_DIRECT
# innodb_log_files_in_group = 2
innodb_buffer_pool_size = 700M
# innodb_additional_mem_pool_size = 20M
innodb_buffer_pool_instances = 1
innodb_log_file_size = 68M
innodb_flush_log_at_trx_commit = 1


connect_timeout=2

collation_server=utf8_unicode_ci
character_set_server=utf8

# delayed_insert_timeout=40

bind-address = ::
skip_name_resolve
local-infile=0

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
open_files_limit = 1000

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

Wenn ich den innodb_buffer_pool_size erhöhe, wird alles noch langsamer. So geht es einigermaßen, aber der CPU-Load ist sehr hoch.

Eigentlich passt das meiner Meinung nach nicht zur Serverausstattung.
 
Einige Zeit laufen lassen (>24 Stunden) und dann noch mal mysqltuner drüber laufen lassen.
Bezüglich der hohen CPU-Last solltest du prüfen, was diese erzeugt. Kommt es wirklich von der Datenbank?
 
Habe die my.cnf nochmals etwas angepasst. Dadurch ist zwar die CPU-Last etwas nach unten gegangen, dafür brauchen die Seiten jetzt noch länger zum laden.

Code: 
[mysqld]

innodb_file_per_table=1

datadir=/var/lib/mysql

open_files_limit                = 2000
table_open_cache                = 8192
table_definition_cache          = 4096
max_allowed_packet              = 256M
key_buffer_size                 = 512M
join_buffer_size 		= 1G
bulk_insert_buffer_size         = 64M
join_buffer_size                = 512K
sort_buffer_size                = 4M
read_buffer_size                = 256K
read_rnd_buffer_size            = 512K
max_heap_table_size             = 512M
tmp_table_size                  = 256M
query_cache_type                = 0
query_cache_size                = 0
long_query_time                 = 0.5
thread_cache_size = 4
performance_schema = OFF

innodb_flush_method = O_DIRECT
# innodb_log_files_in_group = 2
innodb_buffer_pool_size = 1680M
# innodb_additional_mem_pool_size = 20M
# innodb_buffer_pool_instances = 1
innodb_log_file_size = 347M
innodb_flush_log_at_trx_commit = 0


# connect_timeout=2

collation_server=utf8_unicode_ci
character_set_server=utf8

delayed_insert_timeout=40

bind-address = ::
skip_name_resolve
local-infile=0

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
open_files_limit = 1000

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

Den innodb_log_file_size habe ich absichtlich nicht höher gemacht, sonst schläft der Server ganz ein.

Hier die aktuelle ausgabe von mysqltuner:

Code: 
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.52-MariaDB
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ----------------------------------------------                                   --------------------
[--] Log file: /var/log/mariadb/mariadb.log(164K)
[OK] Log file /var/log/mariadb/mariadb.log exists
[OK] Log file /var/log/mariadb/mariadb.log is readable.
[OK] Log file /var/log/mariadb/mariadb.log is not empty
[OK] Log file /var/log/mariadb/mariadb.log is smaller than 32 Mb
[!!] /var/log/mariadb/mariadb.log contains 153 warning(s).
[!!] /var/log/mariadb/mariadb.log contains 60 error(s).
[--] 72 start(s) detected in /var/log/mariadb/mariadb.log
[--] 1) 170603 19:03:17 [Note] /usr/libexec/mysqld: ready for connections.
[--] 2) 170603 19:01:24 [Note] /usr/libexec/mysqld: ready for connections.
[--] 3) 170603 18:59:39 [Note] /usr/libexec/mysqld: ready for connections.
[--] 4) 170603 18:54:52 [Note] /usr/libexec/mysqld: ready for connections.
[--] 5) 170603 18:14:22 [Note] /usr/libexec/mysqld: ready for connections.
[--] 6) 170603 16:59:37 [Note] /usr/libexec/mysqld: ready for connections.
[--] 7) 170603 16:47:36 [Note] /usr/libexec/mysqld: ready for connections.
[--] 8) 170603 16:41:33 [Note] /usr/libexec/mysqld: ready for connections.
[--] 9) 170603 16:25:59 [Note] /usr/libexec/mysqld: ready for connections.
[--] 10) 170603 16:20:57 [Note] /usr/libexec/mysqld: ready for connections.
[--] 87 shutdown(s) detected in /var/log/mariadb/mariadb.log
[--] 1) 170603 19:03:15 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 2) 170603 19:01:22 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 3) 170603 18:59:37 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 4) 170603 18:54:43 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 5) 170603 18:13:47 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 6) 170603 16:59:35 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 7) 170603 16:47:31 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 8) 170603 16:41:31 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 9) 170603 16:25:57 [Note] /usr/libexec/mysqld: Shutdown complete
[--] 10) 170603 16:20:55 [Note] /usr/libexec/mysqld: Shutdown complete

-------- Storage Engine Statistics ---------------------------------------------                                   --------------------
[--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY +MRG_MYIS                                   AM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 1G (Tables: 319)
[--] Data in InnoDB tables: 619M (Tables: 640)
[OK] Total fragmented tables: 0

-------- Security Recommendations ----------------------------------------------                                   --------------------
[OK] There are no anonymous accounts for any database users
[!!] User 'pp_sb_db@localhost' has no password set.
[!!] There is no basic password file list!

-------- CVE Security Recommendations ------------------------------------------                                   --------------------
[--] Skipped due to --cvefile option undefined
[!!] failed to execute: SELECT IFNULL(SUM(INDEX_LENGTH),0) FROM information_sche                                   ma.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema') AND ENGINE = 'MyISAM'                                   ;
[!!] FAIL Execute SQL / return code: 256
[!!] failed to execute: SELECT IFNULL(SUM(INDEX_LENGTH),0) FROM information_sche                                   ma.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema') AND ENGINE = 'Aria';
[!!] FAIL Execute SQL / return code: 256
Argument "" isn't numeric in numeric eq (==) at ./mysqltuner.pl line 2420 (#1)
    (W numeric) The indicated string was fed as an argument to an operator
    that expected a numeric value instead.  If you're fortunate the message
    will identify which operator was so unfortunate.

Argument "" isn't numeric in numeric eq (==) at ./mysqltuner.pl line 2428 (#1)

-------- Performance Metrics ---------------------------------------------------                                   --------------------
[--] Up for: 1h 31m 3s (10M q [1K qps], 3K conn, TX: 25G, RX: 809M)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Physical Memory     : 12.0G
[--] Max MySQL memory    : 3.3G
[--] Other process memory: 3.6G
[--] Total buffers: 2.5G global + 5.5M per thread (151 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 2.6G (21.77% of installed RAM)
[OK] Maximum possible memory usage: 3.3G (27.89% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (1K/10M)
[OK] Highest usage of available connections: 9% (15/151)
[OK] Aborted connections: 0.03%  (1/3578)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (16 temp sorts / 5M sorts)
[!!] Joins performed without indexes: 360
[!!] Temporary tables created on disk: 84% (19K on disk / 22K total)
[OK] Thread cache hit rate: 96% (124 created / 3K connections)
[OK] Table cache hit rate: 105% (377 open / 356 opened)
[OK] Open file limit used: 32% (330/1K)
[OK] Table locks acquired immediately: 99% (10M immediate / 10M locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[!!] failed to execute: SHOW DATABASES
[!!] FAIL Execute SQL / return code: 256
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is enabled.
[--] Thread Pool Size: 6 thread(s).
[--] Using default value is good enough for your version (5.5.52-MariaDB)

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 33.0% (177M used / 536M cache)
[!!] Cannot calculate MyISAM index size - re-run script as root user

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 1.6G/619.4M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (41.3095238095238 %): 347.0M * 2/1.6G should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 99.95% (7763959 hits/ 7767640 total)
[!!] InnoDB Write Log efficiency: 77.84% (3225 hits/ 4143 total)
[OK] InnoDB log waits: 0.00% (0 waits / 918 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is enabled.
[OK] Aria pagecache size / total Aria indexes: 128.0M/1B
[OK] Aria pagecache hit rate: 99.8% (2M cached / 5K reads)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- RocksDB Metrics ---------------------------------------------------------------------------
[--] RocksDB is disabled.

-------- Spider Metrics ----------------------------------------------------------------------------
[--] Spider is disabled.

-------- Connect Metrics ---------------------------------------------------------------------------
[--] Connect is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/log/mariadb/mariadb.log file
    Control error line(s) into /var/log/mariadb/mariadb.log file
    Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Performance shouldn't be activated for MySQL and MariaDB 5.5 and lower version
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
    join_buffer_size (> 512.0K, or always use indexes with joins)
    performance_schema = OFF disable PFS
    innodb_log_file_size * innodb_log_files_in_group should be equal to 1/4 of buffer pool size (=840M) if possible.
 
Bevor Du weiterdoktorst, migriere bitte erstmal die MyISAM Tabellen zu InnoDB und bringe MySQL/MariaDB auf die aktuellste Version (5.7/10.2)

Danach übernimmst Du folgende my.cnf (Pfade und mit # beginnende Zeilen bitte selbst anpassen) und legst diese Verzeichnisse an:
Code: 
mkdir -p /data/db/mysql_tmpdir
mkdir -p /data/db/mysql_secure
chmod 0750 /data/db/mysql_tmpdir
chmod 0750 /data/db/mysql_secure
chown mysql:mysql /data/db/mysql_tmpdir
chown mysql:mysql /data/db/mysql_secure
my.cnf
Code: 
[client]
port                            = 3306
socket                          = /tmp/mysql.sock

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

[mysqld]
user                            = mysql
port                            = 3306
socket                          = /tmp/mysql.sock
#bind-address                    = 127.0.0.1
basedir                         = /usr/local
datadir                         = /data/db/mysql
tmpdir                          = /data/db/mysql_tmpdir
slave-load-tmpdir               = /data/db/mysql_tmpdir
secure-file-priv                = /data/db/mysql_secure
log-bin                         = /data/db/mysql/mysql-bin
log-output                      = TABLE
master-info-repository          = TABLE
relay-log-info-repository       = TABLE
relay-log-recovery              = 1
general-log                     = 0
general-log-file                = /data/db/mysql/general.log
slow-query-log                  = 0
slow-query-log-file             = /data/db/mysql/slow-query.log
default_authentication_plugin   = mysql_native_password
default_password_lifetime       = 0
#server-id                       = 1
sync_binlog                     = 1
sync_relay_log                  = 1
binlog_cache_size               = 16M
expire_logs_days                = 30
#enforce-gtid-consistency        = 1
#gtid-mode                       = ON
max_connections                 = 501
safe-user-create                = 1
#lower_case_table_names          = 1
explicit-defaults-for-timestamp = 1
myisam-recover-options          = FORCE,BACKUP
open_files_limit                = 32768
table_open_cache                = 8192
table_definition_cache          = 4096
max_allowed_packet              = 64M
key_buffer_size                 = 256M
myisam_sort_buffer_size         = 16M
bulk_insert_buffer_size         = 64M
join_buffer_size                = 512K
sort_buffer_size                = 4M
read_buffer_size                = 256K
read_rnd_buffer_size            = 512K
max_heap_table_size             = 256M
tmp_table_size                  = 256M
query_cache_type                = 0
query_cache_size                = 0
long_query_time                 = 0.05
innodb_thread_concurrency       = 8
innodb_buffer_pool_size         = 2G
innodb_buffer_pool_dump_pct     = 100
innodb_data_home_dir            = /data/db/mysql
innodb_log_group_home_dir       = /data/db/mysql
#innodb_data_file_path           = ibdata1:1G;ibdata2:1G;ibdata3:128M:autoextend
#innodb_temp_data_file_path      = ibtmp1:128M:autoextend
innodb_flush_method             = O_DIRECT
innodb_log_file_size            = 256M
innodb_log_buffer_size          = 16M
innodb_flush_log_at_timeout     = 2
innodb_flush_log_at_trx_commit  = 2
innodb_disable_sort_file_cache  = 1
innodb_write_io_threads         = 4
innodb_read_io_threads          = 8
innodb_autoinc_lock_mode        = 2
innodb_max_dirty_pages_pct      = 0
skip-symbolic-links
skip-name-resolve

[mysqldump]
max_allowed_packet              = 256M
quote_names
quick

Nach dem MySQL/MariaDB restart bitte einmal folgende Befehle abarbeiten:
Code: 
mysql_upgrade --force
mysqlcheck --defaults-file=/etc/mysql/my.cnf --check --all-in-1 --extended --all-databases -uroot -p
mysqlcheck --defaults-file=/etc/mysql/my.cnf --check --all-in-1 --check-upgrade --all-databases -uroot -p
mysqlcheck --defaults-file=/etc/mysql/my.cnf --check --all-in-1 --auto-repair --use-frm --all-databases -uroot -p
mysqlcheck --defaults-file=/etc/mysql/my.cnf --optimize --all-in-1 --all-databases -uroot -p
# MySQL/MariaDB restarten
mysqlcheck --defaults-file=/etc/mysql/my.cnf --analyze --all-in-1 --all-databases -uroot -p

Bitte circa 48 Stunden so laufen lassen und dann nochmals mysqltuner.pl posten, danke.
 
Beim MariaDB-Update bekomme ich laufend Fehlermeldungen:
5.6.36-2.el6.x86_64
file /usr/include/mysql/sql_state.h conflicts between attempted installs of MariaDB-devel.................

Wahrscheinlich liegt der Fehler vor meinem Monitor???
 
So weit war ich auch schon. Leider funktionierte Plesk danach gar nicht mehr.
Werde den Server neu aufsetzen, dieses mal mit Ubuntu. Da kenne ich mich besser aus.
 
Habe jetzt ein 3 Wochen altes Serverbackup eingespielt, noch mit der 12er Plesk-Version. Habe ngix installiert und jetzt läuft alles wie geschmiert.

Trotzdem habe ich aus der ganzen Aktion so einiges gelernt und viel Schlaf versäumt :)

Danke für die Tipps.
 
You must log in or register to reply here.
Back
Top