Datenbank für Forum optimieren

MySQL, PostgreSQL, SQLite
sittichforum
Posts: 2
Joined: 2017-12-07 21:16

Datenbank für Forum optimieren

Post by sittichforum » 2017-12-07 21:39

hallo,

unser forum mit über 500k beiträgen und über 5000 besuchern täglich bekommt in letzter zeit immer mehr traffic und dadurch ist der seitenaufbau deutlich langsamer geworden. ich würde gern die datenbank optimieren aber mir fehlen die genauen kenntnisse, will nichts falsch machen ^^

könnt ihr mir vllt dabei helfen? würde mich sehr freuen! :heart_eyes:

im prinzip brauche ich eine konfiguration die den server richtig ausreizt, damit das forum schön luft nach oben hat.

server os: jessie
ram: 8gb
cpu: Intel i5

Code: Select all

~# mysql -V
mysql  Ver 14.14 Distrib 5.7.20, for Linux (x86_64) using  EditLine wrapper
mysqltuner:

Code: Select all

~# perl mysqltuner.pl
 >>  MySQLTuner 1.7.4 - 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] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.7.20
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/log/mysql/error.log(1M)
[OK] Log file /var/log/mysql/error.log exists
[OK] Log file /var/log/mysql/error.log is readable.
[OK] Log file /var/log/mysql/error.log is not empty
[OK] Log file /var/log/mysql/error.log is smaller than 32 Mb
[!!] /var/log/mysql/error.log contains 619 warning(s).
[!!] /var/log/mysql/error.log contains 562 error(s).
[--] 72 start(s) detected in /var/log/mysql/error.log
[--] 1) 2017-11-16T00:17:24.592339Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 2) 2017-11-15T23:17:58.075048Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 3) 2017-10-17T09:08:55.707784Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 4) 2017-10-09T18:43:14.141431Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 5) 2017-10-08T23:23:05.119302Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 6) 2017-09-12T13:46:21.215293Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 7) 2017-09-12T10:38:47.233195Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 8) 2017-07-22T03:10:32.147957Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 9) 2017-07-21T20:26:13.037431Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 10) 2017-06-14T14:39:05.148994Z 0 [Note] /usr/sbin/mysqld: ready for connections.
[--] 259 shutdown(s) detected in /var/log/mysql/error.log
[--] 1) 2017-11-16T00:17:23.481714Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 2) 2017-11-15T23:22:43.594827Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 3) 2017-10-17T09:08:37.155839Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 4) 2017-10-09T18:43:13.084421Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 5) 2017-10-08T23:23:01.955150Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 6) 2017-09-12T13:46:20.098384Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 7) 2017-09-12T10:38:45.015629Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 8) 2017-07-22T03:10:30.730677Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 9) 2017-07-21T20:25:36.025579Z 0 [Note] /usr/sbin/mysqld: Shutdown complete
[--] 10) 2017-06-14T14:39:01.419650Z 0 [Note] /usr/sbin/mysqld: Shutdown complete

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in InnoDB tables: 73M (Tables: 410)
[--] Data in MEMORY tables: 3M (Tables: 6)
[--] Data in MyISAM tables: 3G (Tables: 284)
[OK] Total fragmented tables: 0

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 612 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 21d 20h 13m 51s (212M q [112.364 qps], 16M conn, TX: 6624G, RX: 42G)
[--] Reads / Writes: 94% / 6%
[--] Binary logging is disabled
[--] Physical Memory     : 7.7G
[--] Max MySQL memory    : 3.7G
[--] Other process memory: 965.1M
[--] Total buffers: 656.0M global + 6.2M per thread (500 max threads)
[--] P_S Max memory usage: 0B
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 768.5M (9.70% of installed RAM)
[OK] Maximum possible memory usage: 3.7G (47.72% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (0/212M)
[OK] Highest usage of available connections: 3% (18/500)
[OK] Aborted connections: 0.00%  (29/16193085)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (6K temp sorts / 12M sorts)
[!!] Joins performed without indexes: 1535928
[OK] Temporary tables created on disk: 4% (137K on disk / 2M total)
[OK] Thread cache hit rate: 99% (108K created / 16M connections)
[!!] Table cache hit rate: 1% (2K open / 159K opened)
[OK] Open file limit used: 38% (1K/5K)
[OK] Table locks acquired immediately: 99% (228M immediate / 229M locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is disabled.
[--] Memory used by P_S: 0B
[--] Sys schema is installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 55.0% (147M used / 268M cache)
[OK] Key buffer size / total MyISAM indexes: 256.0M/756.9M
[OK] Read Key buffer hit rate: 99.9% (6B cached / 3M reads)
[!!] Write Key buffer hit rate: 60.4% (8M cached / 5M writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 128.0M/73.0M
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 48.0M * 2/128.0M should be equal 25%
[OK] InnoDB buffer pool instances: 1
[--] Number of InnoDB Buffer Pool Chunk : 1 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (7230702958 hits/ 7230709397 total)
[!!] InnoDB Write Log efficiency: 51.63% (512589 hits/ 992905 total)
[OK] InnoDB log waits: 0.00% (0 waits / 480316 writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- 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/mysql/error.log file
    Control error line(s) into /var/log/mysql/error.log file
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Adjust your join queries to always utilize indexes
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (5000) variable
    should be greater than table_open_cache (2245)
    Performance should be activated for better diagnostics
    Read this before changing innodb_log_file_size and/or innodb_log_files_in_group: http://bit.ly/2wgkDvS
Variables to adjust:
    join_buffer_size (> 512.0K, or always use indexes with joins)
    table_open_cache (> 2245)
    performance_schema = ON enable PFS
    innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
mysqld.conf:

Code: Select all

#
# 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.

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

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * 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_size         = 256M
myisam_sort_buffer_size = 16M
bulk_insert_buffer_size = 64M
max_allowed_packet      = 64M
thread_stack            = 1M
thread_cache_size       = 8
join_buffer_size        = 512k
sort_buffer_size        = 4M
read_buffer_size        = 256k
read_rnd_buffer_size    = 512k
open_files_limit        = 32768
max_heap_table_size     = 256M
tmp_table_size          = 256M
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
max_connections         = 500
table_open_cache        = 8192
table_definition_cache  = 4096
innodb_flush_log_at_trx_commit = 2
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 0
query_cache_size        = 0
#
# * 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 = 0.5
#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
mpm_event.conf

Code: Select all

<IfModule mpm_event_module>
        ServerLimit               40
        StartServers               5
        MinSpareThreads          100
        MaxSpareThreads          250
        ThreadLimit               64
        ThreadsPerChild           25
        MaxRequestWorkers       1000
        MaxConnectionsPerChild  1000
</IfModule>
liebe grüße annita

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

Re: Datenbank für Forum optimieren

Post by Joe User » 2017-12-08 03:50

Als Erstes bitte alle Tabellen nach InnoDB migrieren.
Als Zweites die Apps überarbeiten und die JOINs ohne INDEX beseitigen.

Danach schauen wir weiter.
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.

ddm3ve
Moderator
Moderator
Posts: 1187
Joined: 2011-07-04 10:56

Re: Datenbank für Forum optimieren

Post by ddm3ve » 2017-12-08 10:28

Hi,

welche Forensoftware kommt den zum Einsatz.
02:32:12 21.12.2012 und dann sind Deine Probleme alle unwichtig.

sittichforum
Posts: 2
Joined: 2017-12-07 21:16

Re: Datenbank für Forum optimieren

Post by sittichforum » 2017-12-08 11:22

Als Erstes bitte alle Tabellen nach InnoDB migrieren.
kann es da nicht zu problemen mit der forensoftware kommen?
Als Zweites die Apps überarbeiten und die JOINs ohne INDEX beseitigen.
keine ahnung was damit gemeint ist. habe eben eine stunde gegoogelt aber komm nicht weiter...
welche Forensoftware kommt den zum Einsatz.
noch eine alte. vbulletin3.8.10

mit der optimierung von mysql habe ich mich nie beschäftigt und es ist schwer jetzt innerhalb von tagen/stunden an eine lösung zu kommen. ich habe keine zeit mich jetzt wochenlang da einzuarbeiten da das problem wirklich akut ist und meine user schon rumheuln. ':(

daher hoffe ich auf eine schnelle lösung.

User avatar
rudelgurke
Posts: 405
Joined: 2008-03-12 05:36

Re: Datenbank für Forum optimieren

Post by rudelgurke » 2017-12-08 15:43

Hi,

falls ihr wirklich noch dieses recht betagte Vbulletin im Einsatz habt, hier vielleicht mal aktualisieren. Wenn ich mich recht erinnere läuft Vbulletin 3.8.x nicht mit PHP Versionen die noch offiziell unterstützt werden.
Zum DB Problem, hier auf InnoDB wie schon empfohlen, einige Tabellen wie POST und THREAD solltet ihr nicht migrieren oder die Suche hängt mitunter gewaltig.
Zusätzlich solltet ihr noch prüfen ob irgendwelche Addons installiert sind. Dort sind mitunter mal "Helden des Codings" am Werk, wo alles super läuft in der Testinstanz mit 10 gleichzeitigen Zugriffen, dann aber nach oben recht bescheiden skaliert.
Und natürlich die Einstellungen noch prüfen, Beschränkungen für minimal Anzahl von Zeichen für eine Suche, Wartezeit zwischen 2 Nachrichten usw. usw.

Was zusätzlich noch geholfen hat - eher allgemein - war der Einsatz eines Cache vor dem jeweiligen Webserver wie Nginx oder Varnish.