Optimizacija VPS servera - MySQL preopterecenje CPU

Hosting i serveri
1

Uzeo sam VPS kod digital ocean, sljedecih konfiguracija:

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

Prebaciosam neke stranice sa shared hostinga na ovaj VPS i desio se problem, naime MySQL mi preopterecuje CPU, evo neke slike

MySQL config fajl

dodao sam jos u my.cnf sljedece

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

i promjenio table cache u 1000

Evo i output MySQL Tuner-a

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

Isto kada pokusavam da odradim repair ili optimize tabele ili baze (na samo jednoj stranici, na ostalima moze) javlja mi se sljedeca greska:

The storage engine for the table doesn't support repair in MySQL

Bio bih zahvalan na bilo kakvoj pomoci :slight_smile:

2

Daj probaj uraditi ovo kao root (obavezno) i javi mi:

/etc/init.d/ntpd stop
date -s "`date`"
/etc/init.d/ntpd start

Kada vidiš da je ogroman load opet, daj pukni sljedeću komandu i kopiraj nam ovdje rezultate:

SHOW FULL PROCESSLIST;
3

-bash: SHOW: command not found

CentOS 32 bita mi je sistem

4 
SHOW FULL PROCESSLIST;

radiš u mysql console.

Jesi odradio ostalo?

5

Evo uspio sam :smile:

mysql> SHOW FULL PROCESSLIST;
+------+--------------+-----------------+--------------+----------------+------+--------------------+-----------------------+
| Id   | User         | Host            | db           | Command        | Time | State              | Info                  |
+------+--------------+-----------------+--------------+----------------+------+--------------------+-----------------------+
|   78 | eximstats    | localhost       | eximstats    | Sleep          |    1 |                    | NULL                  |
| 1766 | DELAYED      | localhost       | eximstats    | Delayed insert |    1 | Waiting for INSERT |                       |
| 1852 | DELAYED      | localhost       | eximstats    | Delayed insert |    3 | Waiting for INSERT |                       |
| 1979 | DELAYED      | localhost       | eximstats    | Delayed insert |    5 | Waiting for INSERT |                       |
| 2675 | leechprotect | localhost       | leechprotect | Sleep          | 5182 |                    | NULL                  |
| 3117 | root         | localhost:47235 | NULL         | Query          |    0 | NULL               | SHOW FULL PROCESSLIST |
+------+--------------+-----------------+--------------+----------------+------+--------------------+-----------------------+
6 rows in set (0.00 sec)

Evo i ntp:

root@server1 [~]# /etc/init.d/ntpd stop
Shutting down ntpd:                                        [  OK  ]
root@server1 [~]# date -s "`date`"
Fri Nov 22 03:34:37 EST 2013
root@server1 [~]# /etc/init.d/ntpd start
Starting ntpd:                                             [  OK  ]
6

Kakav je load nakon svega? Ima li pomaka?

U konzoli uradi sljedeće i javi rezultate:

mysqladmin kill PROCESSID

Zamijeni PROCESSID s ID-em leechprotect procesa (u postu iznad to bi bilo 2675, ali ne mora nužno biti kada budeš upisivao komandu pa provjeri).

7

Pa onako cini mi se da ima malo poboljsanja :smile:

Ubio sam taj proces i evo sada rezultata:

mysql> SHOW FULL PROCESSLIST;
+-------+-----------+-----------------+-----------+----------------+------+--------------------+-----------------------+
| Id    | User      | Host            | db        | Command        | Time | State              | Info                  |
+-------+-----------+-----------------+-----------+----------------+------+--------------------+-----------------------+
|    78 | eximstats | localhost       | eximstats | Sleep          |   18 |                    | NULL                  |
| 11648 | DELAYED   | localhost       | eximstats | Delayed insert |   18 | Waiting for INSERT |                       |
| 11675 | DELAYED   | localhost       | eximstats | Delayed insert |   20 | Waiting for INSERT |                       |
| 11735 | DELAYED   | localhost       | eximstats | Delayed insert |  291 | Waiting for INSERT |                       |
| 14281 | root      | localhost:46732 | NULL      | Query          |    0 | NULL               | SHOW FULL PROCESSLIST |
+-------+-----------+-----------------+-----------+----------------+------+--------------------+-----------------------+
5 rows in set (0.00 sec)

Sada je onako manje oterecenje ali opet je izvan normalnog, znaci opet de previse :smile:

Imas li jos nesto sto bih morao uraditi?

8

MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief format along with some basic performance suggestions.

Osim toga, ništa mi više ne pada na pamet.

9

Pa evo postavio sam gore izvjestaj sa MySQL Tunera, mozes li protumaciti nekako?

10

Ne pada mi više ništa na pamet. Probaj izguglati.

Čitao sam davno da innodb jede RAMe kao lud, a ti opet imaš problema sa CPU pa nisam htio ništa govoriti u vezi toga.

11

Aha, ok hvala u svakom slucaju :smile:

A ima li mozda baza i optimizacija skripte neke veze, posto imam jedan custom made CMS i mozda nije njegova baza dobro optimizovana?

12

Naravno. Probaj ga obrisati, zatim restartirati apache i instaliraj neki CMS za koji znaš da radi (npr Wordpress) čisto da testiraš. Pa ako je sve normalno, vrati taj custom CMS i usporedi rezultate.

13

Ok, probacu nesto nemam sada vremena pa sutra :smile:

Mada imam jos stranica na WHM-u ako ga samo suspendujem hoc eli to biti dovoljno da uporedim rezultate, ali na ostalima nemam nista posjete :smile:

i kada mi je na ovom slaba posjeta CPU se manje optereti, a slabo mi ide php i mysql tako da ne znam hocu li skontati u cemu je problem, ali dobro snaci cu se nekako :smile:

14

@tony svaka cast na savjetima.

Samo da te pitam koja je tvoja preporuka koji OS uzeti ako je RAM 2 GB (na nekoj od ranijih tema smo diskutovali).

Lp

15

CentOS ili Ubuntu će ti biti dovoljni.

1 Like
16

MariaDB je rjesenje za tebe, puno manje resursa treba, a performanse su daleko bolje od obicnog mysql servera…

Sto se tice linuxa, centos vise nije sto je bio, kasne jako sa libraryima, Ubuntu/Debian je bolja solucija…

17

za optimizaciju resursa bi preporučio i neki PHP opcode cacher poput Xcache-a ili nešto slično.

…to zna dosta smanjiti opterećenje na mysql-u/apache-u

što ti je rekao slow-query-log?

18

Evo slow jquery

http://pastebin.com/xMU6uNWf

A da li bih sada morao server reisntalirati da bih instalirao MariaDB i dali bih ove sadasne baze mogao prilagoditi MariaDB? Da li bi sve funkcionisalo kakotreba?

19

Htio bih da pohvalim korisnika @1one pomogao mi je dosta, smanjio mi je load, hvala vidi se da covjek zna sta radi :slight_smile: Preporucio bih svakome kome treba optimizacija servera ili usluga toga tipa da ga kontaktiraju.