MySQL Very High CPU Usage - Please Help

[m1l4n]

I migrate my sites to new server.

1GB Memory
1 Core
30GB SSD Disk
2TB Transfer

But MySQL usage on CPU is very high.



My proces usage ... I don't know why My SQL use hig CPU resurces, can anyone help me? I am read all topic-s on cPanel forum but I can't find answer on my question



My MySQL config file

 

[thinkbot]

run mysqltuner.pl and copy here result

 

[cPanelMichael]

Hello

You can use the "mysqladmin processlist;" command to get a better idea of which databases are utilizing MySQL. The following thread is useful for obtaining MySQL statistics:

mysqlmymonlite.sh server stats gathering tool for cPanel Server

Thank you.

 

[m1l4n]

MySQL Tuner

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.32-cll
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 3M (Tables: 47)
[--] Data in InnoDB tables: 44M (Tables: 65)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 8

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 23h 23m 14s (1M q [13.320 qps], 32K conn, TX: 1B, RX: 74M)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 184.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 589.8M (58% of installed RAM)
[OK] Slow queries: 0% (79/1M)
[OK] Highest usage of available connections: 13% (21/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/689.0K
[OK] Key buffer hit rate: 97.4% (52K cached / 1K reads)
[OK] Query cache efficiency: 56.0% (569K cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 7K sorts)
[!!] Temporary tables created on disk: 33% (1K on disk / 5K total)
[OK] Thread cache hit rate: 99% (280 created / 32K connections)
[!!] Table cache hit rate: 4% (64 open / 1K opened)
[OK] Open file limit used: 0% (38/10K)
[OK] Table locks acquired immediately: 100% (459K immediate / 459K locks)
[OK] InnoDB data size / buffer pool: 44.0M/128.0M

 

[m1l4n]

Can anyone sugest to me MySQ: configuration?

 

[thinkbot]

There is not much that can be optimized here, since you got not much queries,
you can increase table_cache=1000

The best thing you can do is add slow queries tracking

slow_query_log=1
slow_query_log_file=mysql-slow.log
long_query_time=0.1
log-queries-not-using-indexes



and restart

after a while review mysql-slow.log for queries running slow, not utilizing indexes and using temp tables on disk - those can be optimized

cd /root
wget http://percona.com/get/pt-query-digest
chmod +x pt-query-digest
./pt-query-digest /var/lib/mysql/mysql-slow.log > slow.txt

Review of slow queries will be in slow.txt

 

[m1l4n]

I do all steps but nothing change
Do you have any other sugestion?

this is output of slow.txt

/http://pastebin.com/xMU6uNWf

 

[Kevinye]

I haven't install the MySQL yet.I don't know why

 

[m1l4n]

I can't understan what you want to say?

 

[thinkbot]

Queries:

pitajfm_cms

1. SELECT * FROM odgovori WHERE uid = 7954 AND pid = 86013
table odgovori, make sure index for uid and pid exists, if not add it in phpmyadmin

2. SELECT id FROM brojac_posjeta WHERE ko = '185.36.84.113' AND kome = '8074'
table brojac_posjeta, make sure index for ko and kome

3. SELECT id FROM brojac_posjeta WHERE kome = '9464'
table brojac_posjeta, same as above, need kome index

4. SELECT * FROM notifikacije WHERE kome = 8443 AND vrsta = 'odgovor' ORDER BY datum DESC LIMIT 5
notifikacije, add index kome

5. SELECT * FROM pitanja WHERE uid = 8443 AND status = '1' ORDER BY id DESC LIMIT 0,25\G
table pitanja, index on uid

6. SELECT `id` FROM `lajkovi` WHERE `oid` = 63566 AND `ko` = 8443\G
table lajkovi, index on oid and ko


Based on top results
top - 18:06:18 up 2 days, 21:54, 1 user, load average: 0.85, 0.84, 0.97
Tasks: 86 total, 2 running, 84 sleeping, 0 stopped, 0 zombie
Cpu(s): 15.7%us, 0.7%sy, 0.0%ni, 83.6%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st

Your server is mostly idle

 

[m1l4n]

How I can fix that?

 

[thinkbot]

Add indexes in phpmyadmin for those columns

 

[m1l4n]

Thanks, now is better

Server load 0.15 (1 CPU)