Aug 312014
 

Um einen möglichst hohen Performance Gewinn unter MySQL zu erzielen, sollte die Größe der Buffer Pools mindestens die Größe der Datenmenge umfassen. So funktioniert MySQL wie eine in-Memory-Datenbank. Für die Default Storage Engine InnoDB ist dafür der Parameter innodb_buffer_pool_size verantwortlich. Mit dem Tool mysqltuner kann man noch Hinweise bekommen, wie man noch weitere Parameter anpassen sollte.

Ein Beispiel für eine solche Ausgabe mit mysqltuner sieht folgendermaßen aus:

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 0h 17m 42s (3M q [42.492 qps], 150K conn, TX: 12B, RX: 304M)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 1.5G global + 18.6M per thread (150 max threads)
[OK] Maximum possible memory usage: 4.3G (27% of installed RAM)
[OK] Slow queries: 0% (10K/3M)
[OK] Highest usage of available connections: 55% (83/150)
[OK] Key buffer size / total MyISAM indexes: 16.0M/97.0K
[OK] Key buffer hit rate: 99.1% (1K cached / 17 reads)
[!!] Query cache efficiency: 6.8% (30K cached / 443K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 13K sorts)
[OK] Temporary tables created on disk: 3% (14K on disk / 412K total)
[OK] Thread cache hit rate: 87% (19K created / 150K connections)
[OK] Table cache hit rate: 27% (584 open / 2K opened)
[OK] Open file limit used: 0% (71/41K)
[OK] Table locks acquired immediately: 100% (112K immediate / 112K locks)
[OK] InnoDB data size / buffer pool: 652.3M/1.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
Variables to adjust:
    query_cache_limit (> 256M, or use smaller result sets)

Anzeige:


In diesem Beispiel kann man sehen, dass die Datenmenge ohne Probleme in den Speicher passt. Ein maximaler Speicherverbrauch von 4,3 GB ist für heutige Verhältnisse schon als klein zu betrachten. Ich habe schon in meinem Artikel über die SSL-Connections beschrieben, dass man die Default-Konfiguration nicht ändern sollte und statt dessen im Verzeichnis /etc/mysql/conf.d eine weitere Konfigurationsdatei anlegen sollte. Hier können die angepassten Werte z.B. in die Datei /etc/mysql/conf.d/003-performance.cnf eingetragen werden.

Huge Pages

Einen weiteren Performance-Schub kann man erreichen, in dem der Buffer Pool Huge Pages benutzt. In meinem Artikel Huge Pages unter Linux für die JVM nutzen beschreibe ich, wie man grundlegend Huge Pages unter Linux aufsetzt. Sind die Huge Pages eingerichtet, muss der User mysql in die Gruppe eingetragen werden, die auf die Huge Pages zugreifen darf. In meinem Artikel ist das die Gruppe hugetlb. Das kann mit folgendem Befehl durchgeführt werden:

usermod -G hugetlb mysql

Damit MySQL die Huge Pages auch benutzt, muss in die Section mysqld der Parameter large-pages ergänzt werden. Danach muss MySQL nur noch neu gestartet werden:

service mysql restart

Referenz:

http://dev.mysql.com/doc/refman/5.5/en/large-page-support.html

Aug 242014
 

MySQL lässt den verschlüsselten Zugriff mit SSL zu. Dazu sollte das passende Zertifikat im Ordner /etc/ssl hinterlegt werden.

Konfiguration

Unter Ubuntu oder Debian empfiehlt es sich, die vom Paketverwalter mitgelieferte Datei /etc/mysql/my.cnf unangetastet zu lassen. Stattdessen können im Verzeichnis /etc/mysql/conf.d beliebig viele Dateien mit Endung .cnf ergänzt werden. Für die verschlüsselte Connection legen wir daher folgende Datei in /etc/mysql/conf.d/001-ssl.cnf an:

[client]
ssl-ca      = /etc/ssl/certs/ca-certificates.crt
 
[mysqld]
ssl
ssl-ca      = /etc/ssl/certs/ca-certificates.crt
ssl-cert    = /etc/ssl/localhost-cert.pem
ssl-key     = /etc/ssl/localhost-key.pem
ssl-cipher  = DHE-RSA-AES256-SHA:DHE-RSA-AES128-SHA:AES256-SHA:AES128-SHA

Anzeige:


Das Zertifikat für Client und Server darf nicht mit SHA256 signiert werden, ansonsten erscheint folgende Fehlermeldung, wenn man sich mit dem mysql-Client verbinden will:

mysql -p
Enter password:
ERROR 2026 (HY000): SSL connection error: ASN: bad other signature confirmation

Es gibt auch einen vermeintlichen Bug unter Ubuntu 12.04 LTS. Ein dort generierter SSL-Private Key kann nicht von MySQL eingelesen werden. Mit der Anweisung kann der Schlüssel jedoch umgewandelt werden:

openssl rsa -in /etc/ssl/localhost-key.pem  -out /etc/ssl/localhost-key.pem 

Neustart

Die Datei des Private Key darf nur für Root und der Gruppe ssl-cert lesbar sein. Daher muss der MySQL-User mysql auch in die Gruppe ssl-cert eingetragen werden. Nach diesen Eintragungen muss MySQL mit

usermod -G ssl-cert mysql
service mysql restart

neu gestartet werden.


Anzeige:


Testen der verschlüsselten Verbindung

Jetzt kann überprüft werden, ob sich ein Client mit SSL verbindet. Dazu benutzt man einfach das Terminal-Programm mysql und verbindet sich mit beliebigen Benutzerdaten mit einer beliebigen Datenbank. Als nächstes gibt man nur:

status

ein und erhält folgende Ausgabe:

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.5.38, for debian-linux-gnu (x86_64) using readline 6.2

Connection id:          5000
Current database:
Current user:           root@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.5.38-0ubuntu0.12.04.1 (Ubuntu)
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    latin1
Conn.  characterset:    latin1
UNIX socket:            /run/mysqld/mysqld.sock
Uptime:                 7 days 5 hours 48 min 30 sec

Threads: 21  Questions: 321745  Slow queries: 0  Opens: 1197  Flush tables: 1  Open tables: 413  Queries per second avg: 0.514

Die Zeile mit „SSL“ deutet auf den verwendeten Verschlüsselungsalgorithmus hin.

Verschlüsselte JDBC-Verbindung

Damit die typischen Application Server auch eine verschlüsselte Verbindung zu MySQL aufbauen, ist nur ein weiterer Connection Parameter notwendig: useSSL=true. Ein kompletter Connection String könnte also so aussehen:

jdbc:mysql://localhost:3306/database?autoReconnect=true&characterEncoding=utf8&useSSL=true

Referenz:

http://dev.mysql.com/doc/refman/5.5/en/ssl-connections.html