MySQL tuning

[®on]

Op een database server (FreeBSD 5.5 stable; Dual Xeon @ 2.00GHz; 2 GB ram; MySQL 4.1.22) lijkt MySQL niet geheel gebruik te maken van het beschikbare geheugen.

SYSTEM MEMORY INFORMATION:
mem_wire: 224808960 ( 214MB) [ 10%] Wired: disabled for paging out
mem_active: + 233709568 ( 222MB) [ 11%] Active: recently referenced
mem_inactive:+ 1520402432 ( 1449MB) [ 72%] Inactive: recently not referenced
mem_cache: + 78995456 ( 75MB) [ 3%] Cached: almost avail. for allocation
mem_free: + 41496576 ( 39MB) [ 1%] Free: fully available for allocation
mem_gap_vm: + 516096 ( 0MB) [ 0%] Memory gap: UNKNOWN
-------------- ------------ ----------- ------
mem_all: = 2099929088 ( 2002MB) [100%] Total real memory managed
mem_gap_sys: + 37937152 ( 36MB) Memory gap: Kernel?!
-------------- ------------ -----------
mem_phys: = 2137866240 ( 2038MB) Total real memory available
mem_gap_hw: + 9617408 ( 9MB) Memory gap: Segment Mappings?!
-------------- ------------ -----------
mem_hw: = 2147483648 ( 2048MB) Total real memory installed

SYSTEM MEMORY SUMMARY:
mem_used: 506589184 ( 483MB) [ 23%] Logically used memory
mem_avail: + 1640894464 ( 1564MB) [ 76%] Logically available memory
-------------- ------------ ----------- ------
mem_total: = 2147483648 ( 2048MB) [100%] Logically total memoryMySQL geeft echter maar 1 GB aan beschikbaar geheugen terug:

MEMORY USAGE
Max Memory Ever Allocated : 433 M
Configured Max Per-thread Buffers : 630 M
Configured Max Global Buffers : 282 M
Configured Max Memory Limit : 912 M
Total System Memory : 1 GTotal systeem memory zou bij mijn weten 2 GB moeten zijn; op andere servers waar ook wat tweaking van MySQL heeft plaats gevonden wordt namelijk wel 2 GB total system memory weergegeven.

Zie ik iets over het hoofd binnen MySQL, of is het OS waar ik moet kijken? Googlen leverde me tot op heden weinig op, maar misschien dat iemand mij even een duw in de juiste richting kan geven.

Voor het tweaking gebruik ik overigens "MySQL Performance Tuning Primer Script (http://day32.com/MySQL/)". Tipje voor diegenen die dit handige tooltje nog niet kenden :)

[DutchTSE]

Niet heel toevallig de kernel zo ingesteld dat hij 1 GB voor het systeem zelf gebruikt, en 1 GB voor de users?

[®on]

Niet heel toevallig de kernel zo ingesteld dat hij 1 GB voor het systeem zelf gebruikt, en 1 GB voor de users?
Daar dacht ik ook al aan, maar op vergelijkbare FreeBSD installs is daar niets van terug te vinden. Op andere systemen wordt duidelijk 2 GB ram weergegeven door MySQL:

MEMORY USAGE
Max Memory Ever Allocated : 321 M
Configured Max Per-thread Buffers : 208 M
Configured Max Global Buffers : 263 M
Configured Max Memory Limit : 471 M
Total System Memory : 1.99 G

[almar]

Ron,

Ik heb deze tool op een testsysteem gezet met 4 Gb geheugen. Ik krijg Total System Memory : 6.27 G
terug, dus ik vermoed een bug in de tool.

[®on]

Okee, zou kunnen. Echter is de tool niet het probleem, MySQL hapert als gevolg van een probleem met "out of memory". Het tooltje gebruik ik met name om wat zaken bij te schaven.

Met name de table_cache lijkt me het bijschaven waard:

mysql> show variables like 'table_cache';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| table_cache | 2048 |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show status like 'open%tab%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 2048 |
| Opened_tables | 56276 |
+---------------+-------+
2 rows in set (0.00 sec)

Schroef ik de table_cache waarde verder op, dan loopt MySQL tegen de limiet van het geheugen aan. Meer geheugen bijprikken?

[almar]

Je zal denk ik wel moeten. Je zit echt aan de top. Elke tabel in de cache neemt geheugenruimte in beslag.


Wat is de uptime van mysql in dit voorbeeld?

[®on]

Enkele uren, aangezien ik wat aan het schaven ben, met name op dat table_cache niveau.

Ik laat MySQl nu even rusten, en mits geen spontane herstarts, zal ik morgen even nieuwe resultaten posten.
/etc/my.cnf:

mysql# cat /etc/my.cnf
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 8192
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 = 8

#skip-networking

server-id = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

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

[mysqlhotcopy]
interactive-timeout

[az-nzl]

het crashen van mysql zou eventueel ook kunnen komen doordat er die aan z'n max open files zit.
de table_cache mag nooit meer zijn dan de helft van de variable open_files_limit. welke uiteraard weer overeenkomt met de waarde van "descriptors" als je kijkt met limit of ulimit -n

dat het script een foute waarde geeft voor het totale geheugen heeft niets met mysql te maken. dit wordt namelijk uit de sysctl hw.realmem gehaald.

voor de foute waarde van hw.realmem, ik vermoed dat je een PAE kernel draait.

[®on]

het crashen van mysql zou eventueel ook kunnen komen doordat er die aan z'n max open files zit.
Correct, dat gebeurde zondag meerdere malen. Daarop heb ik me verder ingelezen, en kwam ook tot deze conclusie, nadat ik de mysql err file had bekeken.

dat het script een foute waarde geeft voor het totale geheugen heeft niets met mysql te maken. dit wordt namelijk uit de sysctl hw.realmem gehaald.
En die waarde moet weer terug te vinden zijn in /boot/loader.conf, right?

[az-nzl]

En die waarde moet weer terug te vinden zijn in /boot/loader.conf, right?

nee, niet elke waarde binnen sysctl is settable. hw.realmem wordt door de kernel bepaald.

voor het ophogen van je file descriptors kan je trouwens de volgende twee regels opnemen in je sysctl.conf

kern.maxfiles=655360
kern.maxfilesperproc=32768