Тупит админка

Технические вопросы по UTM 5.0
Ответить
Аватара пользователя
ZeM
Сообщения: 371
Зарегистрирован: Чт фев 17, 2011 08:38

Тупит админка

Сообщение ZeM »

Коллеги дайте совет. Перенес SQL на другую машину

Код: Выделить всё

Linux SQL1 3.16.0-0.bpo.4-amd64 #1 SMP Debian 3.16.7-ckt2-1~bpo70+1 (2014-12-08) x86_64 GNU/Linux
Personalities : [raid10]
md0 : active raid10 sdd2[3] sdb2[1] sdc2[2] sda2[0]
      3891141632 blocks super 1.2 512K chunks 2 near-copies [4/4] [UUUU]

md1 : active raid10 sdb1[1] sdc1[2] sdd1[3] sda1[0]
      15612928 blocks super 1.2 512K chunks 2 near-copies [4/4] [UUUU]

unused devices&#58; <none>
и есть проблема с админкой она тупит.

Код: Выделить всё

| Id  | User    | Host               | db  | Command | Time | State | Info             |
+-----+---------+--------------------+-----+---------+------+-------+------------------+
| 438 | billing | 172.28.100.100&#58;53388 | UTM | Sleep   | 0    |       |                  |
| 439 | billing | 172.28.100.100&#58;53389 | UTM | Sleep   | 0    |       |                  |
| 440 | billing | 172.28.100.100&#58;53390 | UTM | Sleep   | 0    |       |                  |
| 441 | billing | 172.28.100.100&#58;53391 | UTM | Sleep   | 0    |       |                  |
| 442 | billing | 172.28.100.100&#58;53392 | UTM | Sleep   | 0    |       |                  |
| 443 | billing | 172.28.100.100&#58;53393 | UTM | Sleep   | 0    |       |                  |
| 444 | billing | 172.28.100.100&#58;53394 | UTM | Query   | 0    |       | COMMIT           |
| 445 | billing | 172.28.100.100&#58;53395 | UTM | Sleep   | 0    |       |                  |
| 446 | billing | 172.28.100.100&#58;53396 | UTM | Sleep   | 20   |       |                  |
| 447 | billing | 172.28.100.100&#58;53397 | UTM | Sleep   | 20   |       |                  |
| 448 | billing | 172.28.100.100&#58;53398 | UTM | Sleep   | 20   |       |                  |
| 449 | billing | 172.28.100.100&#58;53399 | UTM | Sleep   | 21   |       |                  |
| 450 | billing | 172.28.100.100&#58;53400 | UTM | Sleep   | 19   |       |                  |
| 451 | billing | 172.28.100.100&#58;53401 | UTM | Sleep   | 21   |       |                  |
| 452 | billing | 172.28.100.100&#58;53402 | UTM | Sleep   | 0    |       |                  |
| 453 | root    | localhost          |     | Query   | 0    |       | show processlist |
Видим загрузку винтов...

Код: Выделить всё

Device&#58;         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00     0,00    0,00   19,00     0,00  1024,00   107,79     0,01    0,63    0,00    0,63   0,42   0,80
sdc               0,00    17,00    0,00   69,00     0,00  1304,00    37,80     1,02   14,90    0,00   14,90  12,70  87,60
sdb               0,00     0,00    0,00   20,00     0,00  1040,00   104,00     0,01    0,60    0,00    0,60   0,40   0,80
sdd               0,00    17,00    0,00   69,00     0,00  1304,00    37,80     1,07   15,54    0,00   15,54  11,77  81,20
sde               0,00     0,00    0,00    0,00     0,00     0,00     0,00     0,00    0,00    0,00    0,00   0,00   0,00
md1               0,00     0,00    0,00    0,00     0,00     0,00     0,00     0,00    0,00    0,00    0,00   0,00   0,00
md0               0,00     0,00    0,00   80,00     0,00  2600,00    65,00     0,00    0,00    0,00    0,00   0,00   0,00

avg-cpu&#58;  %user   %nice %system %iowait  %steal   %idle
           0,50    0,00    0,25   20,05    0,00   79,20

Device&#58;         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
sda               0,00    19,00    0,00   82,00     0,00  2844,00    69,37     0,57    6,93    0,00    6,93   6,15  50,40
sdc               0,00     5,00    0,00   65,00     0,00  2496,00    76,80     0,64    9,78    0,00    9,78   7,57  49,20
sdb               0,00    19,00    0,00   81,00     0,00  2828,00    69,83     0,50    6,17    0,00    6,17   5,48  44,40
sdd               0,00     5,00    0,00   65,00     0,00  2496,00    76,80     0,43    6,58    0,00    6,58   4,80  31,20
sde               0,00     0,00    0,00    0,00     0,00     0,00     0,00     0,00    0,00    0,00    0,00   0,00   0,00
md1               0,00     0,00    0,00    0,00     0,00     0,00     0,00     0,00    0,00    0,00    0,00   0,00   0,00
md0               0,00     0,00    0,00  121,00     0,00  5068,00    83,77     0,00    0,00    0,00    0,00   0,00   0,00

Код: Выделить всё

 >>  MySQLTuner 1.4.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http&#58;//mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
&#91;OK&#93; Logged in using credentials from debian maintenance account.
&#91;OK&#93; Currently running supported MySQL version 5.5.41-0+wheezy1
&#91;OK&#93; Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
&#91;--&#93; Status&#58; +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
&#91;--&#93; Data in MyISAM tables&#58; 56G &#40;Tables&#58; 349&#41;
&#91;--&#93; Data in InnoDB tables&#58; 702M &#40;Tables&#58; 134&#41;
&#91;--&#93; Data in PERFORMANCE_SCHEMA tables&#58; 0B &#40;Tables&#58; 17&#41;
&#91;!!&#93; Total fragmented tables&#58; 15

-------- Security Recommendations  -------------------------------------------
&#91;OK&#93; All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
&#91;--&#93; Up for&#58; 30m 21s &#40;110K q &#91;60.678 qps&#93;, 474 conn, TX&#58; 50M, RX&#58; 611M&#41;
&#91;--&#93; Reads / Writes&#58; 26% / 74%
&#91;--&#93; Total buffers&#58; 6.5G global + 2.7M per thread &#40;151 max threads&#41;
&#91;!!&#93; Maximum possible memory usage&#58; 6.9G &#40;87% of installed RAM&#41;
&#91;OK&#93; Slow queries&#58; 0% &#40;0/110K&#41;
&#91;OK&#93; Highest usage of available connections&#58; 11% &#40;17/151&#41;
&#91;OK&#93; Key buffer size / total MyISAM indexes&#58; 16.0M/62.9G
&#91;OK&#93; Key buffer hit rate&#58; 100.0% &#40;192K cached / 68 reads&#41;
&#91;OK&#93; Query cache efficiency&#58; 63.8% &#40;29K cached / 46K selects&#41;
&#91;OK&#93; Query cache prunes per day&#58; 0
&#91;OK&#93; Sorts requiring temporary tables&#58; 0% &#40;0 temp sorts / 2K sorts&#41;
&#91;OK&#93; Temporary tables created on disk&#58; 23% &#40;62 on disk / 261 total&#41;
&#91;OK&#93; Thread cache hit rate&#58; 94% &#40;24 created / 474 connections&#41;
&#91;!!&#93; Table cache hit rate&#58; 18% &#40;528 open / 2K opened&#41;
&#91;OK&#93; Open file limit used&#58; 0% &#40;746/100K&#41;
&#91;OK&#93; Table locks acquired immediately&#58; 100% &#40;63K immediate / 63K locks&#41;
&#91;OK&#93; InnoDB buffer pool / data size&#58; 6.0G/702.7M
&#91;OK&#93; InnoDB log waits&#58; 0
-------- Recommendations -----------------------------------------------------
General recommendations&#58;
    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
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64&#58; http&#58;//bit.ly/1mi7c4C
Variables to adjust&#58;
    table_open_cache &#40;> 50000&#41;
Тюнинга особо нет. Проблема возникает когда запускаю utm5_radius.
Без него utm_core и utm5_dhcp работает нормально. Загрузки нету по iostat.
В момент когда запущен биллинг.
hdparm -t /dev/md1

/dev/md1:
Timing buffered disk reads: 496 MB in 3.00 seconds = 165.12 MB/sec

Когда биллинг вырублен

hdparm -t /dev/md1

/dev/md1:
Timing buffered disk reads: 1112 MB in 3.00 seconds = 381.92 MB/sec
Может кто присоветует как потюнить?
Вернуться к началу
Аватара пользователя
ZeM
Сообщения: 371
Зарегистрирован: Чт фев 17, 2011 08:38

Сообщение ZeM »

Когда COMMIT то админка клина ловит..

Код: Выделить всё

Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 RADIUS_Module&#58; &#91;5&#93;&#58; account ID <1142>  was sent
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 DBConnection_mysql&#58; <0x2f131c0> SQL query&#58; INSERT INTO discount_transactions_iptraffic_all&#40;id,account_id,discount,discount_with_tax,service_id,slink_id,discount_date,discount_date_hour,discount_date_day,discount_date_month,t_class,base_cost,ipid,bytes,discount_period_id&#41; VALUES &#40;'429494591','1142','0','0','125','4926','1423075305','1423072800','1422997200','1422738000','200','0','174326909','2694582','58'&#41;
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 DBConnection_mysql&#58; <0x2f131c0> SQL query takes 0.000 sec
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 DBConnection_mysql&#58; <0x2f131c0> SQL query&#58; COMMIT
Feb 04 21&#58;41&#58;45 ?Debug &#58; 7906b700 DBConnection_mysql&#58; <0x7f6a7400f940> SQL query takes 0.033 sec
Feb 04 21&#58;41&#58;45 ?Debug &#58; 7906b700 DBConnectionPool&#58; DBConnectionManager pool &#91;Default&#93;&#58; connection is pushed back
Feb 04 21&#58;41&#58;45 ?Debug &#58; 7906b700 StreamConnection&#58; Got message ID 0x1107
Feb 04 21&#58;41&#58;45 ?Debug &#58; 7906b700 DBConnectionPool&#58; DBConnectioManager pool &#91;Default&#93;&#58; connection is popped
Feb 04 21&#58;41&#58;45 ?Debug &#58; 7906b700 DBConnection_mysql&#58; <0x2f06200> SQL query&#58; UPDATE dhs_sessions_log SET  account_id='41', flags='0', slink_id='8886', last_update_date='1423075305', Acct_Status_Type='3', Acct_Input_Packets ='29260', Acct_Input_Octets ='2727040', Acct_Input_Gigawords ='0', Acct_Output_Packets ='39018', Acct_Output_Octets ='45954271', Acct_Output_Gigawords ='0', Acct_Terminate_Cause='0', Acct_Session_Time ='1893' WHERE id='14893032'
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 DBConnection_mysql&#58; <0x2f131c0> SQL query takes 0.031 sec
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 DBConnectionPool&#58; DBConnectionManager pool &#91;Default&#93;&#58; connection is pushed back
Feb 04 21&#58;41&#58;45 ?Debug &#58; 7a27d700 DynaShapePlugin&#58; SUM tclass_id&#58; 100 downloaded&#58; 47382156
Feb 04 21&#58;41&#58;45 ?Debug &#58; 7a27d700 DynaShapePlugin&#58; slink_id&#58; 7600 total downloaded&#58; 47382156 direction&#58; 1
Feb 04 21&#58;41&#58;45 ?Debug &#58; 7a27d700 DynaShapePlugin&#58; calculated border 0 limit 50000
Feb 04 21&#58;41&#58;45 ?Debug &#58; 7a27d700 DynaShapePlugin&#58; set_curr_limit&#58; BW_NONE
Feb 04 21&#58;41&#58;45 ?Debug &#58; 7a27d700 DynaShapePlugin&#58; slink_id=7600 BW limit unchanged. Done.
Feb 04 21&#58;41&#58;45 ?Debug &#58; 7a37e700 DynaShapePlugin&#58; SUM tclass_id&#58; 100 downloaded&#58; 5475443202
Feb 04 21&#58;41&#58;45 ?Debug &#58; 7a37e700 DynaShapePlugin&#58; slink_id&#58; 8464 total downloaded&#58; 5475443202 direction&#58; 1
Feb 04 21&#58;41&#58;45 ?Debug &#58; 7a37e700 DynaShapePlugin&#58; calculated border 0 limit 50000
Feb 04 21&#58;41&#58;45 ?Debug &#58; 7a37e700 DynaShapePlugin&#58; set_curr_limit&#58; BW_NONE
Feb 04 21&#58;41&#58;45 ?Debug &#58; 7a37e700 DynaShapePlugin&#58; slink_id=8464 BW limit unchanged. Done.
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 DBConnectionPool&#58; DBConnectioManager pool &#91;Default&#93;&#58; connection is popped
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 TransactionQueueManager&#58; push&#58; using default priority queue
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 TransactionQueueManager&#58; push&#58; transaction ptr &#91;0x3895480&#93; handler #0 ptr &#91;0x7f6a742d71d0&#93;
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 TransactionHandlerImpl&#58; push&#58; transaction ptr &#91;03895480&#93; &#40;size 1, Events &#91; 95 &#93;&#41;
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 DBConnection_mysql&#58; <0x2effa20> SQL query&#58; BEGIN
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 DBConnection_mysql&#58; <0x2effa20> SQL query takes 0.000 sec
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 DBConnection_mysql&#58; <0x2effa20> SQL SELECT query&#58; SELECT discounted,qnt,downed_as_prepaid FROM downloaded WHERE is_deleted=0 AND downloaded_id='1951' AND tclass_id='100'
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 DBConnection_mysql&#58; <0x2effa20> SQL SELECT query&#58; 1 rows in 0.000 sec
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 DBConnection_mysql&#58; <0x2effa20> SQL query&#58; UPDATE downloaded SET qnt='2192298062', discounted='0', downed_as_prepaid='0' WHERE downloaded_id='1951' AND tclass_id='100'
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 DBConnection_mysql&#58; <0x2effa20> SQL query takes 0.000 sec
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 DBConnection_mysql&#58; <0x2effa20> SQL query&#58; UPDATE dtagg_iptraffic SET discounted = discounted + '0', discounted_without_tax = discounted_without_tax + '0', bytes = bytes + '26961395' WHERE is_closed = '0' AND slink_id = '5904' AND tclass = '100' AND base_cost = '0'
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 DBConnection_mysql&#58; <0x2effa20> SQL query takes 0.000 sec
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 DBACharge&#58; charge 0 for link 5904 account 1557
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 DBConnection_mysql&#58; <0x2effa20> SQL query&#58; UPDATE accounts SET balance='0' WHERE id='1557'
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 DBConnection_mysql&#58; <0x2effa20> SQL query takes 0.000 sec
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 DBConnection_mysql&#58; <0x2effa20> SQL query&#58; INSERT INTO discount_transactions_all&#40;account_id,incoming_rest,outgoing_rest,discount,discount_with_tax,service_id,service_type,slink_id,discount_date,charge_type,discount_period_id&#41; VALUES &#40;'1557','0','0','0','0','125','3','5904','1423075305','3','58'&#41;
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 DBConnection_mysql&#58; <0x2effa20> SQL query takes 0.000 sec
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 StreamConnection&#58; Sending message ID 0x2102
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 RADIUS_Module&#58; &#91;5&#93;&#58; account ID <1557>  was sent
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 DBConnection_mysql&#58; <0x2effa20> SQL query&#58; INSERT INTO discount_transactions_iptraffic_all&#40;id,account_id,discount,discount_with_tax,service_id,slink_id,discount_date,discount_date_hour,discount_date_day,discount_date_month,t_class,base_cost,ipid,bytes,discount_period_id&#41; VALUES &#40;'429494592','1557','0','0','125','5904','1423075305','1423072800','1422997200','1422738000','100','0','174326055','26961395','58'&#41;
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 DBConnection_mysql&#58; <0x2effa20> SQL query takes 0.000 sec
Feb 04 21&#58;41&#58;45 ?Debug &#58; 79c77700 DBConnection_mysql&#58; <0x2effa20> SQL query&#58; COMMIT
Feb 04 21&#58;41&#58;45 ?Debug &#58; 7906b700 DBConnection_mysql&#58; <0x2f06200> SQL query takes 0.033 sec
Feb 04 21&#58;41&#58;45 ?Debug &#58; 7906b700 DBConnectionPool&#58; DBConnectionManager pool &#91;Default&#93;&#58; connection is pushed back
Feb 04 21&#58;41&#58;45 ?Debug &#58; 7906b700 StreamConnection&#58; Got message ID 0x1107
P.S.
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
Вернуться к началу
banec
Сообщения: 269
Зарегистрирован: Вт сен 11, 2007 09:06

Сообщение banec »

Была похожая ситуация, но на тестовом серваке на Винде.
сильно не разбирался - поставил на Linux :) и всё пошло.
Вернуться к началу
Аватара пользователя
ZeM
Сообщения: 371
Зарегистрирован: Чт фев 17, 2011 08:38

Сообщение ZeM »

Ну поставил это на более мощном железе. Более менее все потупило минут 15 и пошло без проблем.
Вопрос такой кто подскажет в mysqltuner
только это предупреждение [!!] Total fragmented tables: 134
Как побороть?
Это не помогает Run OPTIMIZE TABLE to defragment tables for better performance.

Еще момент.
Системные пользователи которых нет их в свое время удалили и такая ошибка

Код: Выделить всё

Feb 05 00&#58;24&#58;08  ERROR &#58; fbfb700 RfwPlugin&#58; shaping for service_id <135> not set
Feb 05 00&#58;24&#58;56  ERROR &#58; ebeb700 DBAUsers&#58; system account ID -6 not found
Feb 05 00&#58;24&#58;56  ERROR &#58; ebeb700 DBAExistingError&#58; system account not found
Feb 05 00&#58;24&#58;56  Warn  &#58; ebeb700 RPCServer@X.X.X.X&#58; __rpcf_get_sys_user&#58; DBAExistingError&#58; system account not found
Feb 05 00&#58;25&#58;00  ERROR &#58; ebeb700 DBAUsers&#58; system account ID -10 not found
Feb 05 00&#58;25&#58;00  ERROR &#58; ebeb700 DBAExistingError&#58; system account not found
Feb 05 00&#58;25&#58;00  Warn  &#58; ebeb700 RPCServer@X.X.X.X&#58; __rpcf_get_sys_user&#58; DBAExistingError&#58; system account not found
Feb 05 00&#58;25&#58;56  ERROR &#58; ebeb700 DBAUsers&#58; system account ID -6 not found
Feb 05 00&#58;25&#58;56  ERROR &#58; ebeb700 DBAExistingError&#58; system account not found
Feb 05 00&#58;25&#58;56  Warn  &#58; ebeb700 RPCServer@X.X.X.X&#58; __rpcf_get_sys_user&#58; DBAExistingError&#58; system account not found
Feb 05 00&#58;26&#58;28  ERROR &#58; ebeb700 DBAUsers&#58; system account ID -10 not found
Feb 05 00&#58;26&#58;28  ERROR &#58; ebeb700 DBAExistingError&#58; system account not found
Вернуться к началу
Ответить

Вернуться в «UTM 5.0»