Wykorzystanie ~40%CPU przez Mysql

[Pitu 160]

Witam, mam pytanie czy da się zminimalizować wykorzystanie CPU po przez zapytania do bazy, na serwerze XL: http://kimsufi.pl/ na forum przy ok. 200 userów online mam właśnie 40% wykorzystania CPU przez samo Mysql (patrzyłem przez top'a) a w panelu OVH Obciążenie serwera: 100% :/

[p 3]

Mniej informacji się nie dało podać?

[Pitu 160]

Mniej informacji się nie dało podać?

 

Ok

 

To tak:

System CentOS, Apache 2, PHP 5, MySQL 5, panel Plesk, forum stoi na skrypcie vBulletin do tego vBSEO oraz kilka modów, które zapisują dane do bazy(mody mało obciążają, praktycznie w ogóle). Przy obciążeniu proca 100%, obciążenie RAM: 42%.

 

Nie wiem co jeszcze mogę podać

[crazyluki 114]

np to jaką masz konfigurację my.cnf (konfiguracja mysql), czy używasz jakiegoś cachowania i tak dalej...

[Pitu 160]

zawartość my.cnf z /etc to:

[mysqld]

set-variable=local-infile=0

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

# Default to using old password format for compatibility with mysql 3.x

# clients (those using the mysqlclient10 compatibility package).

old_passwords=1

max_allowed_packet=1G

 

[mysql.server]

user=mysql

basedir=/var/lib

 

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

[Gość N3T5kY]

Uuu to ładnie ;]

 

Odpal sobie :

 

wget http://day32.com/MySQL/tuning-primer.sh

 

chmod 755 tuning-primer.sh

 

./tuning-primer.sh

 

MySQL powinien działać od 48h

[Pitu 160]

Odpaliłem to co kazałeś i dostałem takie odpowiedzi, może da się przenieś obciążenie na RAM z CPU, sorry za takie pytania ale jestem zielony w tym:

 

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

MySQL Version 5.0.45 i686

Uptime = 25 days 22 hrs 13 min 18 sec
Avg. qps = 16
Total Questions = 36939290
Threads Connected = 4

Server has been running for over 48hrs.
It should be safe to follow 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 NOT enabled.
Current long_query_time = 10 sec.
You have 9 out of 36939322 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 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 = 0
Current threads_cached = 0
Current threads_per_sec = 6
Historic threads_per_sec = 1
Threads created per/sec are overrunning threads cached
You should raise thread_cache_size

MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 2
Historic max_used_connections = 66
The number of used connections is 66% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 195 M
Configured Max Per-thread Buffers : 268 M
Configured Max Global Buffers : 17 M
Configured Max Memory Limit : 286 M
Physical Memory : 1.95 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 128 M
Current key_buffer_size = 7 M
Key cache miss rate is 1 : 233
Key buffer fill ratio = 55.00 %
Your key_buffer_size seems to be fine

QUERY CACHE
Query cache is supported but not enabled
Perhaps you should set the query_cache_size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 132.00 K
You have had 42891 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 = 1024 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 = 64 tables
You have a total of 1423 tables
You have 64 open tables.
Current table_cache hit rate is 0%, 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 = 32 M
Of 2906531 temp tables, 2% 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 = 128 K
Current table scan ratio = 70 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 6066
Your table locking seems to be fine

[Gość N3T5kY]

Zmień w my.cnf:

 

long_query_time = 5

query_cache_size = 32M

table_cache = 512

 

restart mysqla

service mysql restart

 

I poczekaj na efekty

[Pitu 160]

ok dzięki, tylko mam pytanie treść, którą podałem wyżej mam wrzucić do my.cnf?

[crazyluki 114]

Głowa pewnie już pisze odpowiedź, ale ja też napiszę;-)

tak wrzuć te wartości, jeśli one już istnieją to po prostu zmień ich wartości na takie jak kolega wyżej zaproponował;-)

 

Pozdrawiam

[Pitu 160]

Czy:

Current table_cache value = 64 tables

 

to to samo co:

table_cache = 512

 

??

[crazyluki 114]

tak

[Pitu 160]

Dodałem tylko te linie do my.cnf, czyli plik wygląda jak poniżej, ponieważ ta treść co dałem wyżej musiała by mieć w wielu linijkach zakomentowana

 

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

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

long_query_time = 5
query_cache_size = 32M
table_cache = 512

[crazyluki 114]

teraz jeszcze

/etc/init.d/mysql restart

i zobacz czy będzie lepiej;]

[Pitu 160]

teraz jeszcze

/etc/init.d/mysql restart

i zobacz czy będzie lepiej;]

 

Tak zrobiłem, w moim przypadku restart leci po przez:

/etc/init.d/mysqld restart

 

Mam jeszcze jedno pytanie czy jest jakaś komenda, która wywali mi w konsoli ustawienia mysql, chodzi mi o to czy na pewno zmiany się zapisały

[crazyluki 114]

nano /etc/mysql/my.cnf i sobie zobacz;-)

[Pitu 160]

ok, dzięki, tylko ścieżka inna bo ja nie mam nawet folderu /etc/mysql

[lukaschemp 27]

Możesz sprawdzić logując się na MySQL'a np.:

 

mysql -u root -p

 

i wpisz:

 

show variables;

[is_wm 287]

ok, dzięki, tylko ścieżka inna bo ja nie mam nawet folderu /etc/mysql

 

A /etc/my.ini?

 

Daj znać później czy zmiany w konfigu pomogły.

[Pitu 160]

Ok dzięki wszystkim, jak tylko na forum będzie zbliżona ilość osób online sprawdzę zużycie CPU i dam znać jak to wygląda.

[Pitu 160]

Jednak problem występuje dalej, z top'a:

23418 mysql 20 0 139m 43m 4068 S 57 2.2 1331:06 mysqld

 

57- CPU %

2.2- RAM %

 

 

Z panelu OVH:

Obciążenie serwera: 100 %

Obciążenie RAM: 35 %

Obciążenie SWAP: 4 %

 

Oczywiście forum chodzi wolniej niż powinno :/

[is_wm 287]

Wiec pewnie pozostaje optymalizacja zapytan, dodanie indexow itp. A jesli i to nie pomoze, to czas pomyslec nad zmiana maszyny/dyskow.

[Pitu 160]

OK, jak będę przedłużał dzierżawę serwera to wezmę 2XL zobaczymy jak na nim będzie.

[Jabuszko 0]

Uptime = 4 days 6 hrs 30 min 40 sec

Avg. qps = 30

Total Questions = 11295240

Threads Connected = 1

 

Server has been running for over 48hrs.

It should be safe to follow these recommendations

 

To find out more information on how each of these

runtime variables effects performance visit:

http://dev.mysql.com...-variables.html

Visit http://www.mysql.com...e/advisors.html

for info about MySQL's Enterprise Monitoring and Advisory Service

 

SLOW QUERIES

The slow query log is NOT enabled.

Current long_query_time = 10 sec.

You have 0 out of 11295261 that take longer than 10 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...e-recovery.html

 

WORKER THREADS

Current thread_cache_size = 8

Current threads_cached = 7

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 = 101

The number of used connections is 101% of the configured maximum.

You should raise max_connections

 

INNODB STATUS

Current InnoDB index space = 0 bytes

Current InnoDB data space = 0 bytes

Current InnoDB buffer pool free = 65 %

Current innodb_buffer_pool_size = 8 M

Depending on how much space your innodb indexes take up it may be safe

to increase this value to up to 2 / 3 of total system memory

 

MEMORY USAGE

Max Memory Ever Allocated : 307 M

Configured Max Per-thread Buffers : 262 M

Configured Max Global Buffers : 42 M

Configured Max Memory Limit : 304 M

Physical Memory : 3.83 G

Max memory limit seem to be within acceptable norms

 

KEY BUFFER

Current MyISAM index space = 2 M

Current key_buffer_size = 16 M

Key cache miss rate is 1 : 2441

Key buffer free ratio = 81 %

Your key_buffer_size seems to be fine

 

QUERY CACHE

Query cache is enabled

Current query_cache_size = 16 M

Current query_cache_used = 5 M

Current query_cache_limit = 1 M

Current Query cache Memory fill ratio = 32.22 %

Current query_cache_min_res_unit = 4 K

MySQL won't cache query results that are larger than query_cache_limit in size

 

SORT OPERATIONS

Current sort_buffer_size = 2 M

Current read_rnd_buffer_size = 256 K

Sort buffer seems to be fine

 

JOINS

Current join_buffer_size = 132.00 K

You have had 1 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 = 1024 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 = 64 tables

You have a total of 73 tables

You have 64 open tables.

Current table_cache hit rate is 2%

, 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 = 32 M

Of 4077 temp tables, 11% 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 = 128 K

Current table scan ratio = 307 : 1

read_buffer_size seems to be fine

 

TABLE LOCKING

Current Lock Wait ratio = 1 : 285

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

 

 

 

 

 

co powinienem zmienic?? :>

[Gamsiu 1]

http://www.google.pl/search?q=tuning+primer&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:pl:official&client=firefox-a