Problem z wydajnością serwera dedykowanego

[xAn]

Witam serdecznie

Od pewnego czasu posiadam serwer dedykowany dzierżawiony od OVH Intel core2duo z 3 Gb ramu

Z serwera praktycznie korzysta tylko jedno forum.

Normalnie jak jest około 50 - 70 osób On-line śmiga pięknie. Natomist w miare przybywania userów - 100 -140 online forum ma zadyszkę.. Wczytyje się minutami, lub wcale i nie mam pojęcia o co chodzi.. Staram się jakoś po omacku "tuningować" my.cnf aczkolwiek z niewielkim skutkiem..

Zawartość my.cnf

# /etc/mysql/my.cnf: The global mysql configuration file.
# $Header: /var/cvsroot/gentoo-x86/dev-db/mysql/files/my.cnf-4.1,v 1.3 2006/05/05 19:51:40 chtekk Exp $

# The following options will be passed to all MySQL clients
[client]
#password					= your_password
port						= 3306
socket						= /var/run/mysqld/mysqld.sock
character-sets-dir=/usr/share/mysql/charsets
default-character-set=latin1

[php-cgi]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=latin1

[mysql]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=latin1

[mysqladmin]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=latin1

[mysqlcheck]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=latin1

[mysqldump]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=latin1

[mysqlimport]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=latin1

[mysqlshow]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=latin1

[myisamchk]
character-sets-dir=/usr/share/mysql/charsets

[myisampack]
character-sets-dir=/usr/share/mysql/charsets

# use [safe_mysqld] with mysql-3
[mysqld_safe]
err-log						= /var/log/mysql/mysql.err

# add a section [mysqld-4.1] or [mysqld-5.0] for specific configurations
[mysqld]
character-set-server		= latin1
init-connect='SET NAMES  latin1'
default-character-set		= latin1
user 						= mysql
port 						= 3306
socket 						= /var/run/mysqld/mysqld.sock
pid-file 					= /var/run/mysqld/mysqld.pid
log-error 					= /var/log/mysql/mysqld.err
basedir 					= /usr
datadir 					= /var/lib/mysql
skip-locking
key_buffer 					= 32M
max_allowed_packet 			= 16M
table_cache 				= 1024
sort_buffer_size 			= 4M
net_buffer_length 			= 8K
read_buffer_size 			= 4M
read_rnd_buffer_size 		= 4M
myisam_sort_buffer_size 	= 128M
language 					= /usr/share/mysql/english

# security:
# using "localhost" in connects uses sockets by default
skip-networking
bind-address				= 127.0.0.1

log-bin
server-id 					= 1

# point the following paths to different dedicated disks
tmpdir 						= /tmp/
#log-update 				= /path-to-dedicated-directory/hostname

# you need the debug USE flag enabled to use the following directives,
# if needed, uncomment them, start the server and issue 
# #tail -f /tmp/mysqld.sql /tmp/mysqld.trace
# this will show you *exactly* what's happening in your server;)

#log						= /tmp/mysqld.sql
#gdb
#debug						= d:t:i:o,/tmp/mysqld.trace
#one-thread

# uncomment the following directives if you are using BDB tables
#bdb_cache_size				= 4M
#bdb_max_lock				= 10000

# the following is the InnoDB configuration
# if you wish to disable innodb instead
# uncomment just the next line
skip-innodb
#
# the rest of the innodb config follows:
# don't eat too much memory, we're trying to be safe on 64Mb boxes
# you might want to bump this up a bit on boxes with more RAM
innodb_buffer_pool_size = 16M
# this is the default, increase it if you have lots of tables
innodb_additional_mem_pool_size = 2M
#
# i'd like to use /var/lib/mysql/innodb, but that is seen as a database :-(
# and upstream wants things to be under /var/lib/mysql/, so that's the route
# we have to take for the moment
#innodb_data_home_dir		= /var/lib/mysql/
#innodb_log_arch_dir		= /var/lib/mysql/
#innodb_log_group_home_dir	= /var/lib/mysql/
# you may wish to change this size to be more suitable for your system
# the max is there to avoid run-away growth on your machine
innodb_data_file_path = ibdata1:10M:autoextend:max:128M
# we keep this at around 25% of of innodb_buffer_pool_size
# sensible values range from 1MB to (1/innodb_log_files_in_group*innodb_buffer_pool_size)
innodb_log_file_size = 5M
# this is the default, increase it if you have very large transactions going on
innodb_log_buffer_size = 8M
# this is the default and won't hurt you
# you shouldn't need to tweak it
set-variable = innodb_log_files_in_group=2
# see the innodb config docs, the other options are not always safe
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet 			= 16M

[mysql]
# uncomment the next directive if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer 					= 64M
sort_buffer_size 			= 64M
read_buffer 				= 16M
write_buffer 				= 16M

[myisamchk]
key_buffer 					= 64M
sort_buffer_size 			= 64M
read_buffer 				= 16M
write_buffer 				= 16M

[mysqlhotcopy]
interactive-timeout

Teraz pytanie moje do Was.. Czy oby nie jest to przecholowany tuning?

Ewentualnie gdzie leżą błędy lub co jeszcze można zmienić aby poprawić działanie forum? Dodam iż od strony skryptu wszystko zostało poprawnie pooptymalizowane. Indexy w bazie danych gdzie sie dało tez dodane zostały..

Jezeli znacie jakies strony PL gdzie mógłbym poczytać cos w tym temacie to również poprosiłbym o linki

Z góry dziękuje za wszelką pomoc..

[krzycho]

z konfigiem mysql nie ma co przesadzac... ten co napisales to tak mysle ok. 1,5-2gB powinien miec na sam mysql

jezeli zwalnia to mozesz albo inwestowac w lepszy serwer, albo zastanowic sie co nie tak ze skryptem... mialem sytuacje ze na sama baze 2*xeon quad 2.0ghz(8 rdzeni po 2ghz)+4gB ram+dysk SAS 10k komputer zdychal...

Ciebie prawdopodobnie interesuja pewnie linijki:

sort_buffer_size = 4M

myisam_sort_buffer_size = 128M

nie piszesz z jakiego engeenu korzystasz, jakie wystepuja slow query

zwykle problem powoduja zapytania ktore powoduja wytworzenie duzego bufora(zwykle przy sortowaniu), ktory nie miesci sie w pamieci(wedlug ustawien), zaczyna zapisywac na dysku, pare query rownoleglych i predkosc zapisu na dysk pada i zaczyna sie lawinowe zwalnianie

najlepiej zoptymalizuj skrypt, chyba ze masz kase zeby inwestowac w coraz lepsze serwery(polecalbym cos z duza iloscia ram i paroma dyskami sas 15k w raid0)

[Maximus Marius]

Dokładanie serwerów ma tę wadę że przyrost mocy jest co najwyżej liniowy , a przy nie zoptymalizowanych zapytaniach złożoność problemu jest co najmniej N^2 , wiec jak by nie kombinować lepiej optymalizować niż stawiac nowe serwery.

Tak samo tuningowanie nie pomoże jezeli zapytanie bedzie nie optymalne wiec na poczatek tak jak napisał krzycho obejrzyj sobie "slow query"

namierz te które wykonuja sie za długo , sprawdz plan zapytań, ocen czy da sie coz z tym zrobić

[shad]

Oj brakuje bardzo ważnego parametru, dopisz i pozmieniaj parę linijek (podaje tak na oko przy założeniu ze komp ma 3gb) dokładne wartości musisz ustalić eksperymentalnie. A z doświadczenia po prostu tabelki tymczasowe nie są tworzone w pamięci tylko na dysku i komp przymiera.

key_buffer = 64M

key_buffer_size=64M

max_allowed_packet = 32M

table_cache = 512K

sort_buffer_size = 16M

read_buffer_size = 16M

read_rnd_buffer_size = 16M

thread_cache_size = 1024

query_cache_size = 128M

tmp_table_size=312M

myisam_sort_buffer_size = 64M

[xAn]

Ok dziękuję za wszystkie odpowiedzi

Celowo nie napisałem jakiego używam skryptu forum by ograniczyć odpowiedzi typu zmień skrypt itp. Korzystam z bardzo mocno modyfikowanej wersji php by przemo. Nad skryptem pracuje cały czas bardzo doświadczony człowiek więc myślę że co mógł to tam zrobił.

Co do slow query przyznam że jestem raczej początkującym (czytaj marnym adminem) szukam czytam ucze się - aczkolwiek na chwilkę obecną nie wiem jak to sprawdzić ani tym bardziej przeanalizować

A z doświadczenia po prostu tabelki tymczasowe nie są tworzone w pamięci tylko na dysku i komp przymiera.

Też tak mi się wydaję..

[NieW]

zainteresuj się poniższymi skryptami:

https://mysqltuner.com/mysqltuner.pl

https://www.day32.com/MySQL/tuning-primer.sh

... ale tak jak sugerowali przedpiści - przede wszystkim analiza slow query

[xAn]

mysqltuner powiedział mi

General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries

Wczesniejsze porady zastosowałem bez większych rezultatów..

wracając do mysqltuner

Reduce your overall MySQL memory footprint for system stability

Nie za bardzo wiem co to oznacza.. Mógłbym prosić o jakąś podpowiedź ??

Co do slow query log tak jak pisałem nie bardzo wiem jak to sprawdzić i jakie wnioski wyciągnać

[xAn]

prosze a oto wynik drugiego testu

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10 sec.
You have 0 out of 249445 that take longer than 10 sec. to complete
Your long_query_time may be too high, I typically set this under 5 sec.

BINARY UPDATE LOG
The binary update log is enabled
The expire_logs_days is not set.
The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER																			  LOGS commands are run manually
Setting expire_logs_days will allow you to remove old binary logs automatically
See https://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html

WORKER THREADS
Current thread_cache_size = 1024
Current threads_cached = 12
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 1
Historic max_used_connections = 9
The number of used connections is 9% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating

MEMORY USAGE
Max Memory Ever Allocated : 653 M
Configured Max Per-thread Buffers : 4 G
Configured Max Global Buffers : 218 M
Configured Max Memory Limit : 4 G
Physical Memory : 2.93 G

Max memory limit exceeds 90% of physical memory

KEY BUFFER
Current MyISAM index space = 82 M
Current key_buffer_size = 64 M
Key cache miss rate is 1 : 5478
Key buffer fill ratio = 60.00 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is enabled
Current query_cache_size = 128 M
Current query_cache_used = 4 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 3.79 %
Current query_cache_min_res_unit = 4 K
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 16 M
Current read_rnd_buffer_size = 15 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 132.00 K
You have had 6 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 = 1134 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
You currently have open more than 75% of your open_files_limit
You should set a higher value for open_files_limit in my.cnf

TABLE CACHE
Current table_cache value = 512 tables
You have a total of 1238 tables
You have 512 open tables.
Current table_cache hit rate is 39%, while 100% of your table cache is in use
You should probably increase your table_cache

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 312 M
Of 32156 temp tables, 23% 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 = 15 M
Current table scan ratio = 490 : 1
read_buffer_size is over 8 MB there is probably no need for such a large read_bu																			 ffer

TABLE LOCKING
Current Lock Wait ratio = 1 : 476
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'
If you have a high concurrency of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.

jakieś porady ??

[Maximus Marius]

do poczytania https://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

https://www.ducea.com/2006/11/06/identifyin...l-slow-queries/

w sumie to chodzi o to aby sie zapytania zapisały do pliku aby można było je przeanalizować

ustaw sobie long_query_time = 1 i powtórz testy

mozesz tez zainstalowac sobie mtop na serwerze i na żywo ogladac jakie zapytania sie wykonują DLUGOOOOOOOO

[relons]

Sprawdziłeś w "top" czy to na pewno MySQL jest wąskim gardłem? Jeżeli Twoją dystrybucją jest OVH Release 2 to problem leży najprawdopodobniej w bardzo niewydajnej konfiguracji apache, który działa w trybie CGI. Radzę zmienić system, bo żeby zainstalować w tej dystrybucji apache z mod_php z paczki gentoo zamiast wersjo OVH trzeba mieć trochę pojęcia o systemie Gentoo.

[krzycho]

nie kolujcie czlowieka... php-cgi jak jest odpalane to tez kwestia bezpieczenstwa, ale i tak system upcha to w swap, zreszta wiele rownoleglych php-cgi jest w jednym obszarze pamieci ze wzgledu na glowna czesc- organizacja pamieci, wiec nie jest to az tak szkodliwe, zalezy od polityki admina/servera... zwlaszcza ze z tego co pisze autor jest 1 skrypt, wiec ile procesow: 10 fast cgi? do tego 8 apache? jak by nie szlo w fast cgi to by apache troche przypecznialo

moja rada:

1. wez konfig domyslny od php, tam sa pod rozne servery i napisane ile ramu... wez najlepiej ten medium jak tam jeszcze cos oprocz tego chodzi, przesadzajac z konfigiem mozna jeszcze pogorszyc

2. jeden z krokow, w zwyklym top i tak pewnie zobaczysz ze albo prawie nie obciazony server, albo obciazony journalingiem(opoziony zapis), albo uwalony procesem systemowym(poprostu zapis na dysk), jak masz freebsd to masz narzedzie gstat(statystyki dyskow), w linuxie nie znam odpowiednika

2a. wrzuc tak jak mowil Maximus Marius linijke:

long_query_time = 1

albo nawet na 2-3, bo 1 to moze za duzo wrzucic i powstanie balagan... chyba ze testowy server gdzie nie ma duzo uderzen

2b. mozesz uzyc tak jak pisal Maximus Marius: mtop lub ja uzywalem mysql Administrator, laczysz sie jako root i wtedy masz co aktualnie jest w polaczeniach wykonywane, jak uwala to i tak bedzie widac

3. jak juz zlokalizujesz query pewnie sie okaze ze leci

SELECT [...] JOIN [JOIN [...]] ORDER BY [...]

ewentualnie jeszcze jakis bezsensowny LIMIT 0, jakas liczba dosc mala(w kazdym razie znacznie mniejsza od ilosci rekordow)

przekonasz sie ze to powoduje ze baza powiedzmy 12mB, limit 16mB i niby to nie uwala...

ale jest tam JOIN ktory z tych paru tabel po pare mB zbija to kilkukrotnie na powiedzmy 20-36mB(dysk moze tak do 70-80mB/s zapisac), wiec 2 query przechodza, jak sie trzecie pojawia to lawinowo zwalnia bo sie zapycha...

4. jak juz znasz winowajce(tak zwykle sie wyciaga powiedzmy 10 ostatnich postow, w sposob wyciagnij, sortuj wedlug daty i na koniec wypluj pare nedznych rekordow po masie operacji)... to zeby nie rezygnowac z funkcjonalnosci to do tego zapytania dodaj zmyslnie WHERE albo do WHERE dopisz jakies zmyslne AND...

troche zawile, ale mam nadzjeje ze jakies wnioski z tego wyciagniesz...

a tak podsumowujac: piszesz ze to jakies forum- moze bys napisal jakie(skrypt), moze pomysl na zmiana na cos komercyjnego(skoro masz na server to powinno i znalezc sie na skrypt)

i wcale nie jest powiedziane ze komercyjny skrypt odrazu rozwiazuje wszelkie problemy- klopoty wlasnie mialem na platnym skrypcie do ogloszen...

edycja:

-nie dopisalem krok 2a lub 2b

-jezeli widzisz ze sobie nie radzisz to poszukaj kogos kto sie zna, bo na 99% nie ma sensu inwestowac w lepszy server, kwestia jak to wyeliminujesz/zoptymalizujesz

-zawsze mozna zrobic extremalne cachowanie(albo przynajmniej tego co powoduje slow query) i wtedy byle jaki server moze przetrzymac tysiace uderzen...

[NieW]

Reduce your overall MySQL memory footprint for system stability

Nie za bardzo wiem co to oznacza.. Mógłbym prosić o jakąś podpowiedź ??

przydzieliłeś "za dużo" pamięci dla mysql - zerknij w źródło skryptu