Правила     Закладки     Карма    Календарь    Журналы    Помощь    Поиск    PDA    Чат   
        СМС-ки
   
Пейджер выключен!
 
Фильтр авторов:    показать 
  скрыть
  Ответ в темуСоздание новой темыСоздание опроса

> Конфигурация MySql, Нужна помощ знатоков
Serg86  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Старик
***

Профиль
Группа: Пользователь
Сообщений: 104
Пользователь №: 42253
На форуме: 11 месяцев, 22 дня
Карма:




В общем ситуация такова: Проект работал на хостинге bget, всё вроди ничего, но со временем пришел к тому что в конфиге MySql нужно увеличить innodb_buffer_pool_size. Они такой возможности не дают. Переехал на VDS.

Долгий и мучительный переезд был, но не суть. На bget проблема была в том что запросы вытесняли друг друга из буфера и складывалась такая картина, что при первом выполнении запроса он отрабатывал за 3-5 секунд, второй раз за 0,02 -0,04.

Переехал на новый хост, увеличил innodb_buffer_pool_size, те же самые запросы выполняются за 3-5 секунд. Такое ощущение что индексы вообще не используются, хотя судя по explain всё то же самое что и на старом хосте, индексы задействованы.

Вот конфиг MySql:

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld]
port = 3306
#bind-address = 127.0.0.1
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
datadir = /var/lib/mysql
tmpdir = /tmp
#userstat = 1
#default-storage-engine = MyISAM
default-storage-engine = InnoDB
innodb_large_prefix = 1
innodb_file_format = Barracuda
innodb_file_per_table = ON

log-error = /var/log/mysql/mysql-err.log
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow-queries.log
long_query_time = 4
log_slow_filter = filesort_on_disk,tmp_table_on_disk

skip-external-locking

#character_set_server = cp1251
#character_set_client = cp1251
#skip-character-set-client-handshake

character_set_server = utf8
#character_set_client = utf8mb4
#skip-character-set-client-handshake


###
innodb_buffer_pool_size = 2048M
innodb_buffer_pool_instances = 2
###

open_files_limit = 20480
max_connections = 100
max_allowed_packet = 16M
#max_allowed_packet=2048M
key_buffer_size = 350M
#table_cache = 64
thread_cache_size = 24
sort_buffer_size = 32M
query_cache_limit = 20M
query_cache_size = 32M

myisam_sort_buffer_size = 32M
myisam-recover-options = BACKUP


[mariadb]
#aria_pagearia_pagecache_buffer_size = 8m
#aria_sort_buffer_size = 1m
#skip-pbxt
#skip-blackhole
#skip-archive
#skip-federated

[mysqldump]
quick
quote-names
max_allowed_packet = 16M

[isamchk]
key_buffer = 16M

Подскажите дилетанту что не так.

Да еще одна поправка:
На старом хосте был MySQL: 5.6.26
На ново MySQL: 10.1.10-MariaDB
Может здесь собака зарыта.

Это сообщение отредактировал Serg86 - 9.01.2016 - 16:15
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
Serg86  
 ۩  [x] Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Старик
***

Профиль
Группа: Пользователь
Сообщений: 104
Пользователь №: 42253
На форуме: 11 месяцев, 22 дня
Карма:




Вот что получил в mysqltuner
root@vm-111111111:~# ./mysqltuner.pl
-bash: ./mysqltuner.pl: No such file or directory
root@vm-111111111:~# ./mysqltuner.pl
-bash: ./mysqltuner.pl: No such file or directory
root@vm-111111111:~# cd mysqltuner
root@vm-111111111:~/mysqltuner# mysqltuner.pl
-bash: mysqltuner.pl: command not found
root@vm-111111111:~/mysqltuner# ./mysqltuner.pl
[OK] Logged in using credentials from debian maintenance account.
>> MySQLTuner 1.6.2 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 10.1.10-MariaDB-1~wheezy-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Aria +CSV +InnoDB +MRG_MyISAM +SEQUENCE
[--] Data in MyISAM tables: 247M (Tables: 57)
[--] Data in InnoDB tables: 951M (Tables: 135)
[!!] Total fragmented tables: 15

-------- Security Recommendations -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 605 basic passwords in the list.

-------- CVE Security Recommendations -------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -------------------------------------------------
[--] Up for: 8m 12s (3M q [6K qps], 334 conn, TX: 12B, RX: 278M)
[--] Reads / Writes: 99% / 1%
[--] Binary logging is disabled
[--] Total buffers: 2.5G global + 32.9M per thread (151 max threads)
[OK] Maximum reached memory usage: 3.1G (38.96% of installed RAM)
[!!] Maximum possible memory usage: 7.4G (92.40% of installed RAM)
[OK] Slow queries: 0% (75/3M)
[OK] Highest usage of available connections: 11% (18/151)
[OK] Aborted connections: 0.00% (0/334)
[OK] Query cache efficiency: 23.4% (1M cached / 4M selects)
[!!] Query cache prunes per day: 281592000
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 3K sorts)
[OK] Temporary tables created on disk: 2% (9 on disk / 409 total)
[OK] Thread cache hit rate: 94% (18 created / 334 connections)
[OK] Table cache hit rate: 105% (210 open / 199 opened)
[OK] Open file limit used: 0% (77/20K)
[OK] Table locks acquired immediately: 100% (3M immediate / 3M locks)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 19.5% (71M used / 367M cache)
[OK] Key buffer size / total MyISAM indexes: 350.0M/55.4M
[OK] Read Key buffer hit rate: 98.8% (181K cached / 2K reads)
[!!] Write Key buffer hit rate: 0.0% (1 cached / 1 writes)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 2.0G/951.1M
[!!] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 22.34% (29285 used/ 131071 total)
[OK] InnoDB Read buffer efficiency: 99.99% (314885196 hits/ 314908885 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 915 writes)

-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
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
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
query_cache_size (> 32M)
innodb_buffer_pool_instances(=2)
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
VELIK505  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Здесь живет
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 1515
Пользователь №: 25307
На форуме: 5 лет, 11 месяцев, 26 дней
Карма: 44




Так. У тебя таблицы какие innodb только?
сколько оперативной памяти на VPS и скоко ядер?
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
Serg86  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Старик
***

Профиль
Группа: Пользователь
Сообщений: 104
Пользователь №: 42253
На форуме: 11 месяцев, 22 дня
Карма:




8 ядер 8 гигов
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
VELIK505  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Здесь живет
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 1515
Пользователь №: 25307
На форуме: 5 лет, 11 месяцев, 26 дней
Карма: 44




query_cache_type = 1
query_cache_limit = 32M
query_cache_size = 512M
добавь в секцию mysqld
и обязательно:
innodb_flush_log_at_trx_commit = 1
и
innodb_thread_concurrency = 16
max_heap_table_size=256M
tmp_table_size=512M
table_cache = 512
table_open_cache = 1024
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
Serg86  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Старик
***

Профиль
Группа: Пользователь
Сообщений: 104
Пользователь №: 42253
На форуме: 11 месяцев, 22 дня
Карма:




Цитата
У тебя таблицы какие innodb только?

Да
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
Serg86  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Старик
***

Профиль
Группа: Пользователь
Сообщений: 104
Пользователь №: 42253
На форуме: 11 месяцев, 22 дня
Карма:




Цитата
query_cache_type = 1
query_cache_limit = 32M
query_cache_size = 512M
добавь в секцию mysqld
и обязательно:
innodb_flush_log_at_trx_commit = 1
и
innodb_thread_concurrency = 16
max_heap_table_size=256M
tmp_table_size=512M
table_cache = 512
table_open_cache = 1024

Сейчас всё попробую, можно подробнее в чем суть проблемы?
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
Serg86  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Старик
***

Профиль
Группа: Пользователь
Сообщений: 104
Пользователь №: 42253
На форуме: 11 месяцев, 22 дня
Карма:




Цитата
query_cache_type = 1
query_cache_limit = 32M
query_cache_size = 512M
добавь в секцию mysqld
и обязательно:
innodb_flush_log_at_trx_commit = 1
и
innodb_thread_concurrency = 16
max_heap_table_size=256M
tmp_table_size=512M
table_cache = 512
table_open_cache = 1024

Это всё в секцию ,mysqld??
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
VELIK505  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Здесь живет
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 1515
Пользователь №: 25307
На форуме: 5 лет, 11 месяцев, 26 дней
Карма: 44




Цитата (Serg86 @ 9.01.2016 - 13:02)
Сейчас всё попробую, можно подробнее в чем суть проблемы?

Настройки mysql-сервера малый эффект дают по сути.
Раз у тебя запросы выполняються по 3-5 секунд. Это уже корявости твоих запросов! Даже sql noindex 0.5сек считаеться долго!
Всё правильно сначала 3-5 сек на выполнение. А потом уже последующие разы 0.02сек это уже отдача результата из кеша (оперативной памяти, если не было в запросе update delet и тд). В первую очередь надо оптимизировать запросы.
Ну и так же проверь все таблицы чтобы не было битых индексов. проверь анализируй и оптимизируй сами таблицы
mysqlcheck -u root -pROOT_PASSWORD --auto-repair --optimize --all-databases

а потом
поставь mytop и посмотри они все тяжелые (плохие) запросы будут жёлтыми. В идеале если в коде всё ровно ничего не должна выдавать!
Да в секцию mysqld вносить. После чего перезагрузить mysql сервер. /etc/init.d/mysql restart
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
Serg86  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Старик
***

Профиль
Группа: Пользователь
Сообщений: 104
Пользователь №: 42253
На форуме: 11 месяцев, 22 дня
Карма:




Цитата
mysqlcheck -u root -pROOT_PASSWORD --auto-repair --optimize --all-databases


root@vm-111111:~# mysqlcheck -u root -p 22222222 --auto-repair --optimize --all
mysqlcheck: ambiguous option '--all' (all-databases, all-in-1)

Чет не нравится ему в команде
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
Serg86  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Старик
***

Профиль
Группа: Пользователь
Сообщений: 104
Пользователь №: 42253
На форуме: 11 месяцев, 22 дня
Карма:




После внесения изменений в настройки MySql падает всё время, думал памяти не хватает, изменил тариф, не помогло. Еще пару дней назад всё работало, иногда с небольшими тормозами, всё началось после того как я не заметил что у меня место на диске кончилось, на основном, сам MySql на отдельном работает. Диск увеличил, перезагрузился и пошло поехало.
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
Serg86  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Старик
***

Профиль
Группа: Пользователь
Сообщений: 104
Пользователь №: 42253
На форуме: 11 месяцев, 22 дня
Карма:




db_sh.sait_counter_ip
note : Table does not support optimize, doing recreate + analyze instead
status : OK

Запустил, получил такое сообщение ко всем таблицам
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
VELIK505  
Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Здесь живет
******

Профиль
Группа: Форумчанин
Завсегдатай форума
Сообщений: 1515
Пользователь №: 25307
На форуме: 5 лет, 11 месяцев, 26 дней
Карма: 44




всё ок. просто innodb не поддерживает optimize. Они помойму не дефрагментируются.
Ну а дальше уже смотри лог тяжелых запросов и оптимизируй их.
PMПисьмо на e-mail пользователю
    1   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
Serg86  
 ۩  Дата
Цитировать сообщение

Пользователя сейчас нет на форуме



Старик
***

Профиль
Группа: Пользователь
Сообщений: 104
Пользователь №: 42253
На форуме: 11 месяцев, 22 дня
Карма:




Цитата
всё ок. просто innodb не поддерживает optimize. Они помойму не дефрагментируются.
Ну а дальше уже смотри лог тяжелых запросов и оптимизируй их.
Спасибо за помощ. +1
PMПисьмо на e-mail пользователю
    0   Для быстрого поиска похожих сообщений выделите 1-2 слова в тексте и нажмите сюда Для быстрой цитаты из этого сообщения выделите текст и нажмите сюда
  Быстрый ответ
Информация о Госте
Введите Ваше имя
Кнопки кодов
Для вставки цитаты, выделите нужный текст и
НАЖМИТЕ СЮДА
Введите сообщение
Смайлики
:huh:  :o  ;) 
:P  :D  :lol: 
B)  :rolleyes:  <_< 
:)  :angry:  :( 
:unsure:  :blink:  :ph34r: 
     
Показать всё

Опции сообщения  Включить смайлики?
 Включить подпись?
 
1 Пользователей читают эту тему (1 Гостей и 0 Скрытых Пользователей)
0 Пользователей:

Опции темы Ответ в темуСоздание новой темыСоздание опроса