High Load mysql server

Server NGINX
1

Hello brothers, uncles all the master …:slight_smile:

Please help all of you.
I was again in trouble ya its very high cpu load, but the beginning does
not problematic, it is good to use nginx now … uh … keep up the cpu
load specifications for vps
RAM = 768M
SWAP = 1567M
CPU = 2

This config my.cnf

[mysqld]
#skip-name-resolve
safe-show-database
#old_passwords
back_log = 50
skip-innodb
max_connections = 500
key_buffer_size = 1536M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 5000
thread_cache_size = 384
wait_timeout = 20
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
net_buffer_length = 16384
max_connect_errors = 100000
thread_concurrency = 4
concurrent_insert = 2
table_lock_wait_timeout = 30
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 3M
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
range_alloc_block_size = 4096
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
max_write_lock_count = 8

[mysqld_safe]
nice = -5
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 64M

[myisamchk]
key_buffer_size = 144M
sort_buffer_size = 16M
read_buffer_size = 16M
write_buffer_size = 16M

for cpu load images like this …
http://video.eurekapage.com/files/photos/1319808563e83d46_o.jpg

Please help the master …

Posted at Nginx Forum:

2

Hello,

key_buffer_size = 1536M

owh. i’d suggest you to try this tool:
http://mysqltuner.pl/mysqltuner.pl

Cheers,

Vladimir

3

Кирюшкин Владимир Wrote:

Vladimir

Thx for help, i was run that config but still load my CPU is high…
this is after installation :

-------- General Statistics

[–] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.77
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics

[–] Status: -Archive +BDB -Federated -InnoDB -ISAM -NDBCluster
[–] Data in MyISAM tables: 3M (Tables: 6)
[!!] BDB is enabled but isn’t being used
[!!] Total fragmented tables: 4

-------- Security Recommendations

[OK] All database users have passwords assigned

-------- Performance Metrics

[–] Up for: 4h 9m 43s (5M q [378.161 qps], 1M conn, TX: 15B, RX: 259M)
[–] Reads / Writes: 85% / 15%
[–] Total buffers: 1.6G global + 5.0M per thread (500 max threads)
[!!] Maximum possible memory usage: 4.1G (543% of installed RAM)
[OK] Slow queries: 0% (0/5M)
[OK] Highest usage of available connections: 2% (12/500)
[OK] Key buffer size / total MyISAM indexes: 1.5G/945.0K
[OK] Key buffer hit rate: 99.8% (419K cached / 1K reads)
[OK] Query cache efficiency: 33.9% (667K cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 346K sorts)
[OK] Temporary tables created on disk: 0% (23 on disk / 5K total)
[OK] Thread cache hit rate: 99% (12 created / 1M connections)
[OK] Table cache hit rate: 39% (37 open / 94 opened)
[OK] Open file limit used: 0% (59/10K)
[OK] Table locks acquired immediately: 96% (1M immediate / 1M locks)

-------- Recommendations

General recommendations:
Add skip-bdb to MySQL configuration to disable BDB
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

So, what i`ve must to do again.?

Posted at Nginx Forum:

4

Hello,

So, what i`ve must to do again.?

you should do this:

General recommendations:
Add skip-bdb to MySQL configuration to disable BDB
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries

also you should lower key_buffer_size to e.g. 64M (recommendation =
“Reduce your overall MySQL memory footprint for system stability”)

Cheers,

Vladimir

5

On 28 October 2011 20:11, MyName [email protected] wrote:

MySQL started within last 24 hours - recommendations may be
inaccurate
…
So, what i`ve must to do again.?
Wait until it can give you reliable recommendations

6

maybe you shoud use some cache server in front of MySQL:)

MyName [email protected] wrote:

myisam_sort_buffer_size = 64M
net_buffer_length = 16384
query_alloc_block_size = 65536
[mysqldump]
http://video.eurekapage.com/files/photos/1319808563e83d46_o.jpg

Please help the master …

Posted at Nginx Forum:
High Load mysql server

nginx mailing list
[email protected]
nginx Info Page

–
Sent from my Android phone with K-9 Mail. Please excuse my brevity.

7

awsome… thx and respect to Thinke365, Nginx U. and Кирюшкин
Владимир … my cpu`s load is now normal again…

Posted at Nginx Forum: