MySQL Sicherheit und Performance

G

GoMoPa

Grünschnabel
Hallo,
wir haben einen dedizierten Server mit 6GB Ram und 2xXEON 2.3 GHZ.

Wir haben zur Zeit massive Probleme, dass der Datenbankserver abstürzt bzw. überlastet ist, sehr viele Firmen versuchen unseren Content zu replizieren bzw. versuchen Massenanfragen an unseren Server.

Ich habe aus Sicherheit-/Performancegründen bereits den Proxy/Opensource Firewall GreenSQL davor geschaltet, leider stürzt dieser Dienst auch in regelmässigen Abständen mit der Fehlermeldung:

Warning: mysql_connect() [function.mysql-connect]: Lost connection to
MySQL server at 'reading initial communication packet', system error:
111 in ...functions.lib.php
on line 252
SQL Error:Lost connection to MySQL server at 'reading initial
communication packet', system error: 111

Hat jemand Erfahrungen mit MySQL-Sicherheit und Performancenoptimierung, erkennt jemand Redudanzen in nachfolgender My.cnf? Für Tipps und Hilfe wäre ich dankbar.

Code:
[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1


#max_connections=1000
#query_cache_size=128M
#thread_cache_size=8

#max_connections=1000

ft_min_word_len=3

#key_buffer_size = 64M
#thread_cache_size = 40
#query_cache_size = 16M
#query_cache_limit = 16M
#thread_concurrency = 3
#tmp_table_size = 8M
#sort_buffer_size = 1M
#read_buffer_size = 1M
#read_rnd_buffer_size = 1M
#myisam_sort_buffer_size = 128M
#thread_cache_size = 32

join_buffer_size=1M
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=1M
table_cache=256M
#max_allowed_packet=4M
key_buffer=256M
key_buffer_size=256M
thread_cache=256M
thread_concurrency=2
thread_cache_size=40
thread_stack=128K
concurrent_insert=2
query_cache_limit=256M
query_cache_size=4M
query_cache_type=1
skip-bdb
#skip-innodb
interactive_timeout=120
max_connections = 500
max_user_connections = 500

wait_timeout= 30
connect_timeout= 30
long_query_time = 2
# Log Querys
log-slow-queries=/var/log/mysql/mysql-slow.log

#key_buffer = 16M
#net_buffer_length = 8K

#sort_buffer_size = 128K
#myisam_sort_buffer_size = 256K
#join_buffer_size = 2M

#query_cache_size = 4M
#thread_cache = 32
#table_cache = 1024
#max_allowed_packet = 256K

#max_connections = 60
#low_priority_updates = 1
#long_query_time = 2
#[mysqld]
#query_cache_limit       = 1M
#query_cache_size        = 32M
#query_cache_type        = 1
#thread_concurrency     = 4
#thread_cache           = 64

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
 
Also diese Konfiguration ist vermutlich für den Server völlig daneben - da kann sicher einiges optimiert werden.

Lass mal das mysqltuner-Script drüber laufen.

Poste auch mal ein paar weitere Infos zu dem System (was genau drauf läuft, IO-Werte, Load, ...)
 
TuningPrimer Ergebnis:

[root@ded1461 bin]# tuning-primer.sh
mysqld is alive

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

MySQL Version 5.0.45-log x86_64

Uptime = 0 days 16 hrs 10 min 48 sec
Avg. qps = 37
Total Questions = 2161627
Threads Connected = 2

Warning: Server has not been running for at least 48hrs.
It may not be safe to use 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 enabled.
Current long_query_time = 2 sec.
You have 2705 out of 2161656 that take longer than 2 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 = 40
Current threads_cached = 36
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 500
Current threads_connected = 5
Historic max_used_connections = 87
The number of used connections is 17% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 627 M
Configured Max Per-thread Buffers : 2.00 G
Configured Max Global Buffers : 270 M
Configured Max Memory Limit : 2.27 G
Physical Memory : 5.81 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
175851 * 1024 / 268435456 * 100
Current MyISAM index space = 598 M
Current key_buffer_size = 256 M
Key cache miss rate is 1 : 512
Key buffer free ratio = 0 %
You could increase key_buffer_size
It is safe to raise this up to 1/4 of total system memory;
assuming this is a dedicated database server.

QUERY CACHE
Query cache is enabled
Current query_cache_size = 4 M
Current query_cache_used = 3 M
Current query_cache_limit = 256 M
Current Query cache Memory fill ratio = 83.54 %
Current query_cache_min_res_unit = 4 K
However, 115955 queries have been removed from the query cache due to lack of memory
Perhaps you should raise query_cache_size
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 1 M
Current read_rnd_buffer_size = 1020 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 1.00 M
You have had 3 queries where a join could not use an index properly
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 = 65535 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_cache value = 32512 tables
You have a total of 810 tables
You have 1195 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 64409 temp tables, 10% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 1020 K
Current table scan ratio = 3557 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 36
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'

Top Auszug:

top - 10:40:29 up 41 days, 7 min, 1 user, load average: 2.00, 2.99, 3.01
Tasks: 211 total, 4 running, 207 sleeping, 0 stopped, 0 zombie
Cpu(s): 16.7%us, 5.5%sy, 0.0%ni, 76.4%id, 1.3%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 6093956k total, 5633340k used, 460616k free, 224476k buffers
Swap: 2096472k total, 136k used, 2096336k free, 1994828k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
13894 mysql 15 0 689m 276m 4752 S 190 4.6 323:59.81 mysqld
10021 apache 22 0 299m 11m 2700 R 100 0.2 0:04.11 httpd
9402 apache 15 0 366m 25m 4080 S 35 0.4 0:05.22 httpd
9463 apache 15 0 365m 25m 4012 R 22 0.4 0:00.78 httpd
10004 apache 15 0 360m 19m 3992 S 8 0.3 0:00.44 httpd
10011 apache 15 0 365m 24m 4000 S 8 0.4 0:00.25 httpd
4772 greensql 15 0 56712 2972 1656 S 6 0.0 3:16.33 greensql-fw
1 root 15 0 10348 688 572 S 0 0.0 0:09.20 init
2 root RT -5 0 0 0 S 0 0.0 0:05.10 migration/0
3 root 34 19 0 0 0 S 0 0.0 0:00.01 ksoftirqd/0

Aus Sicherheits-/Performance Gründen wurde bereits der/die Proxy/Opensource Firewall GreenSQL vorgeschalten.

Apache 2 mit PHP5 ist installiert und es läuft Plesk 9.1.

PHP 5.2.9 (cli) (built: Mar 11 2009 08:22:06)
Copyright (c) 1997-2009 The PHP Group
Zend Engine v2.2.0, Copyright (c) 1998-2009 Zend Technologies
with Zend Extension Manager v1.2.2, Copyright (c) 2003-2007, by Zend Technologies
with Zend Optimizer v3.3.3, Copyright (c) 1998-2007, by Zend Technologies


Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 247623
Server version: 5.0.45-log Source distribution

[root@ded1461 bin]# httpd -v
Server version: Apache/2.2.3
Server built: Nov 12 2008 07:09:03


[root@ded1461 bin]# uname -a
Linux ded1461 2.6.18-92.el5 #1 SMP Tue Apr 29 13:16:15 EDT 2008 x86_64 x86_64 x86_64 GNU/Linux


Ich hoffe das reicht als Information.
 
Ein paar mehr Infos zum System wären nicht schlecht (was wird gehostet, ...)

Ansätze zum Optimieren hast Du ja schon aus dem Script - ein erster Ansatz wäre vermutlich erst mal eine my-huge.cnf oder my-large.cnf zu verwenden und von dort aus weiterzumachen.

Dann generell auch das Datenbank- und Query-Design analysieren, evtl. über php-Beschleuniger nachdenken, persistente SQL-Verbindungen, ...
 
Hallo,
wir verwenden das Woltlab Burning Board und viele Thirdpart Softwarelösungen und Eigenentwicklungen die mit Joins etc arbeiten. Wir haben mehr als 2000 Gleichzeitig online.

Die Frage ist einfach, was & wie ich dort optimieren kann - ich bin in Sachen MySQL Datenbankkonfiguration eher unerfahren.

Kannst du die obige My.cnf gemäß Tuning Primer anpassen?

Also das Betriebssystem ist: Redhat Enterprise Linux 5

Die Serverausstattung laut Plesk wie folgt:

CPU GenuineIntel, Intel(R) Xeon(R)CPU E5410 @ 2.33GHz
Version Parallels Plesk Panel v9.0.1_build90090127.18 os_RedHat el5
OS Linux 2.6.18-92.el5
Key number PLSK.00830197.0003
System Uptime: 41 day(s) 00:26

Memory Usage
Total

Used

Free

Shared

Buffer

Cached
Usage
5.81 GB

5.53 GB

285.65 MB

0 B

238.75 MB

1.83 GB

63.65%

Swap Usage
Total

Used

Free
Usage
2.00 GB

136.00 KB

2.00 GB




Festplatten sind 2x160 GB im RAID-Verbund.
 
DB-Optimierung geht leider nicht "einfach so" - das ist ein langwieriger Prozess, wo man sich an das Optimum herantastet - welches überall anders ist.

Für's erste - Caches hochdrehen, da scheint bei euch noch einiges an Reserven vorhanden zu sein. Dann mal ein Query-Log anschalten und schauen, was so reinkommt, ob indices korrekt sind, Slow-Queries analysieren (und immer wieder die div. Analyser-Scripte drüber laufen lassen. Auch mal die Status-Seite von z.B. phpMyAdmin anschauen, da sind auch einige Dinge zu finden).

Dieses SQLGreen würde ich rauswerfen - schneller wird's meist durch ein Einsatz eher nicht, meist ist das Gegenteil der Fall. Und entsprechende Sicherheitsgewinne bringt das meist auch nicht, wenn man eine gepflegte und supportete Applikation benutzt. Wie das bei WBB aussieht kann ich konkret nicht sagen.
 
Mir wäre schon viel geholfen, wenn die My.cnf einigermaßen vernüpftig wäre und er mehr in den Cache legen würde, die SWAP ist 2 GB und eigentlich bis auf 163k immer frei, dort könnte man das Auslagern und würde Performance und Ressourcen gewinnen.
 
Öhm, sei froh, daß der SWAP leer ist - alles andere wäre mehr als kontraproduktiv.

Such einfach mal im System nach der my-huge.cnf / my-large.cnf - vermutlich unter /usr/share/doc/mysql-server* zu finden.

Empfehlenswert als Buch dazu ist übrigens "High Performance MySQL: Optimierung, Datensicherung & Lastverteilung"
 
Ich habe die Huge gefunden und werde diese mal verwenden und schauen was passiert.

Gibt es noch Optimierungsmöglichkeiten für den Webserver?
 
Sicher - aber was soll ich da sagen?

Aus den bisherigen Infos lässt sich da recht wenig orakeln... Setzt ihr irgendwelche Beschleuniger, Cache-Systeme, Pre-Generatoren, ... ein? Ist die Serverkonfig entsprechend optmiert? Ist das OS an sich optimiert?

Ernsthaft: Hausaufgaben muss man auch selbst machen.
 
Hallo,
die gesamte Administration wurde von ehemaligen Webmastern gemacht - die Spaß hatten ihren Unfug zu treiben. Ich baue das ganze jetzt Stück für Stück auf.

PHP verwendet nur die ganzen Zend-Geschichten und es läuft modsec und modevansive für die Sicherheit und eben GreenSQL.

Ich dachte schon an mod_cache für den Apache2 oder den Umstieg auf litespeed.


die my-huge.cnf scheint mir laut tuning-primer aber schlechter zu sein, als meine eigene My.cnf
 
Hast Du auch schon http://rackerhacker.com/mysqltuner/ ausprobiert?

Die Tuning-Scripte geben nur Hinweise - wie die zu bewerten sind musst Du entscheiden. Das hängt auch vom jeweiligen System, den Workflows, dem Userverhalten, ... ab.

(wir haben z.B. hier auf einem System mit 4GB Speicher Caches in der Gesamtgröße von ca. 6GB definiert - weil aber die Verteilung der Abfragen und die Art der Cache-Nutzung "passend" ist funktioniert das problemlos ohne Swap-Nutzung)

DB-Administration ist nicht zu unrecht ein komplexes Thema...
 
Zuletzt bearbeitet:
Jupp Datenbankadministration ist ein sehr komplexes Thema und nein MySQL Tuner habe ich noch nicht probiert, aber jetzt:

Code:
[root@ded1461 ~]# ./mysqltuner.pl
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = "de_DE.UTF-8@euro",
        LC_ALL = "de_DE.UTF-8@euro",
        LANG = "en_US.UTF-8"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").

 >>  MySQLTuner 1.0.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

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.45-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 602)
[--] Data in InnoDB tables: 3M (Tables: 190)
[--] Data in MEMORY tables: 0B (Tables: 1)
[!!] Total fragmented tables: 51

-------- Performance Metrics -------------------------------------------------
[--] Up for: 9m 33s (47K q [82.613 qps], 5K conn, TX: 231M, RX: 27M)
[--] Reads / Writes: 70% / 30%
[--] Total buffers: 314.0M global + 12.2M per thread (250 max threads)
[OK] Maximum possible memory usage: 3.3G (56% of installed RAM)
[OK] Slow queries: 0% (51/47K)
[OK] Highest usage of available connections: 14% (37/250)
[OK] Key buffer size / total MyISAM indexes: 256.0M/602.2M
[OK] Key buffer hit rate: 99.8% (30M cached / 62K reads)
[OK] Query cache efficiency: 78.1% (24K cached / 30K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 6% (131 temp sorts / 1K sorts)
[OK] Temporary tables created on disk: 11% (177 on disk / 1K total)
[OK] Thread cache hit rate: 99% (37 created / 5K connections)
[OK] Table cache hit rate: 95% (155 open / 163 opened)
[OK] Open file limit used: 10% (245/2K)
[OK] Table locks acquired immediately: 97% (13K immediate / 13K locks)
[OK] InnoDB data size / buffer pool: 3.6M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate

Wir haben extra den stärksten Server genommen den wir bei unserem ISP bestellen konnten, Konfiguration wie oben aufgeführt.

Wir haben wie gesagt sehr viel Content und im Schnitt zwischen 1000-2500 User gleichzeitig online, im Monat kommen wir auf mehr als 500.000 Besucher.
.
.
.
EDIT (autom. Beitragszusammenführung) :
.

Folgende Apache Konfiguration verwenden wir:
Code:
LoadModule evasive20_module   /usr/lib64/httpd/modules/mod_evasive20.so

<IfModule mod_evasive20.c>
    DOSHashTableSize    3097
    DOSPageCount        2
    DOSSiteCount        50
    DOSPageInterval     1
    DOSSiteInterval     1
    DOSBlockingPeriod   10
</IfModule>


 KeepAlive On
 KeepAliveTimeout 4
 MaxKeepAliveRequests 500
ServerLimit 60
 MaxClients 60
 StartServers 30
 MinSpareServers 30
 MaxSpareServers 30
 MaxRequestsPerChild 1000
 ServerTokens Prod
 ServerSignature On
 HostnameLookups Off
 
Zuletzt bearbeitet:

Ähnliche Themen

JBidWatcher: Problem bei loading Auctions in Verbindung mit mySQL

Akonadi startet nicht mehr

Bilfe bei 1064 - You have an error in your SQL syntax; check the manuel that correspo

MySQL Problem

Windows clients können nicht mehr auf lange laufendes System zugreifen

Zurück
Oben