Форум Сайтостроение Администрирование серверов

mysql оптимизация

12
[umka]
На сайте с 25.05.2008
Offline
456
[umka]
#11
zzzit:
Можно решить проблему кардинально и заменить innodb на tokudb, который оптимизирован под подобные нагрузки, много пишущие в базу, во много раз больше вытянет:
http://www.tokutek.com/tokudb-for-mysql/

Что за диковина?


http://www.tokutek.com/tokudb-for-mysql/

Error establishing a database connection
Лог в помощь!
С
На сайте с 14.04.2011
Offline
63
Сентябрь
#12

[umka]

Ахаха, вот это реклама для нового супер-пупер движка.

png toku.png
Z
На сайте с 06.09.2012
Offline
129
zzzit
#13

Он давно не новый, перконе теперь принадлежит tokutek, разработчик движка.

Сомневаюсь, что они держат на линоде свой движек для своего сайта.

http://web.archive.org/web/20150414215129/http://www.tokutek.com/tokudb-for-mysql/

Черный список врунов и обманщиков: ua-hosting.company, riaas.ru, takewyn.ru, yahoster/cadedic, Andreylab
С
На сайте с 14.04.2011
Offline
63
Сентябрь
#14

zzzit

Ну так и давали бы сразу нормальную ссылку - https://www.percona.com/software/mysql-database/percona-tokudb.

zzzit:
Он давно не новый

Как давно?

Z
На сайте с 06.09.2012
Offline
129
zzzit
#15
Сентябрь:
Как давно?

7 лет ему уже.

alexbalance
На сайте с 17.02.2012
Offline
57
alexbalance
#16
denisakajacob:
Вопрос стоит в том, есть ли смысл покупать SSD диски на сервер?

не совсем понятно зачем SSD, если у вас в MYSQL все упирается? Не думаю что это решит проблему. Лучше попробуйте

- оптимизировать таблицы

- оптимизировать скрипты по возможности

Можно попробовать переписать INSERT, UPDATE запросы с использованием PDO возможно это поможет.

Ну и самый простой вариант это выключить автосохранение )))

MySQL slow query log Call to undefined function SQL-инъекция базы данных, без
Andreyka
На сайте с 19.02.2005
Offline
822
Andreyka
#17

Для интенсивной записи давно придумали hash таблицы, которые лежат в памяти

Не стоит плодить сущности без необходимости
RAS
На сайте с 27.11.2005
Offline
126
RAS
#18

+1, надо устранять причину, а не последствия, т.е. оптимизировать процесс записи, а не "корзинку для бумаг".

Администрируем сервера, впс, вдс. Ускоряем загрузку сайтов - DLE, Word Press, Joomla, Modx... Настраиваем безопасность. Ручная чистка rootkit/malware/вирусов. (/ru/forum/867860) Разработка - shell/bash/sh/python/perl.
T
На сайте с 02.02.2015
Offline
35
Tilo
#19

Не могу понять в чем дело мускуль периодически падает, 

service mysqld status

 mysqld dead but subsys locked centos
подскажите в какую сторону копать. На серваке ТДС-ка

стоит и траф соответственно из-за этих падений мускуля сливается впустоту.

лог мускуля 


150810 02:27:01 mysqld_safe Number of processes running now: 0

150810 02:27:01 mysqld_safe mysqld restarted

150810  2:27:01 [Note] libgovernor.so not found

150810  2:27:01 [Note] /usr/libexec/mysqld (mysqld 5.5.44-cll-lve) starting as process 987 ...

150810  2:27:01 [Note] Plugin 'FEDERATED' is disabled.

150810  2:27:01 InnoDB: The InnoDB memory heap is disabled

150810  2:27:01 InnoDB: Mutexes and rw_locks use GCC atomic builtins

150810  2:27:01 InnoDB: Compressed tables use zlib 1.2.3

150810  2:27:01 InnoDB: Using Linux native AIO

150810  2:27:01 InnoDB: Initializing buffer pool, size = 128.0M

150810  2:27:01 InnoDB: Completed initialization of buffer pool

150810  2:27:01 InnoDB: highest supported file format is Barracuda.

InnoDB: The log sequence number in ibdata files does not match

InnoDB: the log sequence number in the ib_logfiles!

150810  2:27:01  InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Restoring possible half-written data pages from the doublewrite

InnoDB: buffer...

150810  2:27:01  InnoDB: Waiting for the background threads to start

150810  2:27:02 InnoDB: 5.5.44 started; log sequence number 5586665

150810  2:27:02 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306

150810  2:27:02 [Note]   - '0.0.0.0' resolves to '0.0.0.0';

150810  2:27:02 [Note] Server socket created on IP: '0.0.0.0'.

150810  2:27:02 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it

150810  2:27:02 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure

150810  2:27:02 [Note] Event Scheduler: Loaded 0 events

150810  2:27:02 [Note] /usr/libexec/mysqld: ready for connections.

Version: '5.5.44-cll-lve'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL) by Atomicorp

150810 02:27:50 mysqld_safe Number of processes running now: 0

150810 02:27:50 mysqld_safe mysqld restarted

150810  2:27:50 [Note] libgovernor.so not found

150810  2:27:50 [Note] /usr/libexec/mysqld (mysqld 5.5.44-cll-lve) starting as process 1019 ...

150810  2:27:50 [Note] Plugin 'FEDERATED' is disabled.

150810  2:27:50 InnoDB: The InnoDB memory heap is disabled

150810  2:27:50 InnoDB: Mutexes and rw_locks use GCC atomic builtins

150810  2:27:50 InnoDB: Compressed tables use zlib 1.2.3

150810  2:27:50 InnoDB: Using Linux native AIO

150810  2:27:50 InnoDB: Initializing buffer pool, size = 128.0M

150810  2:27:50 InnoDB: Completed initialization of buffer pool

150810  2:27:50 InnoDB: highest supported file format is Barracuda.

InnoDB: The log sequence number in ibdata files does not match

InnoDB: the log sequence number in the ib_logfiles!

150810  2:27:50  InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Restoring possible half-written data pages from the doublewrite

InnoDB: buffer...

150810 02:27:51 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

150811 01:14:36 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

150811  1:14:36 [Note] libgovernor.so not found

150811  1:14:36 [Note] /usr/libexec/mysqld (mysqld 5.5.44-cll-lve) starting as process 11614 ...

150811  1:14:36 [Note] Plugin 'FEDERATED' is disabled.

150811  1:14:36 InnoDB: The InnoDB memory heap is disabled

150811  1:14:36 InnoDB: Mutexes and rw_locks use GCC atomic builtins

150811  1:14:36 InnoDB: Compressed tables use zlib 1.2.3

150811  1:14:36 InnoDB: Using Linux native AIO

150811  1:14:36 InnoDB: Initializing buffer pool, size = 128.0M

150811  1:14:36 InnoDB: Completed initialization of buffer pool

150811  1:14:36 InnoDB: highest supported file format is Barracuda.

InnoDB: The log sequence number in ibdata files does not match

InnoDB: the log sequence number in the ib_logfiles!

150811  1:14:36  InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Restoring possible half-written data pages from the doublewrite

InnoDB: buffer...

150811  1:14:36  InnoDB: Waiting for the background threads to start

150811  1:14:37 InnoDB: 5.5.44 started; log sequence number 5586665

150811  1:14:37 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306

150811  1:14:37 [Note]   - '0.0.0.0' resolves to '0.0.0.0';

150811  1:14:37 [Note] Server socket created on IP: '0.0.0.0'.

150811  1:14:37 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it

150811  1:14:37 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure

150811  1:14:37 [Note] Event Scheduler: Loaded 0 events

150811  1:14:37 [Note] /usr/libexec/mysqld: ready for connections.

Version: '5.5.44-cll-lve'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL) by Atomicorp
baas
На сайте с 17.09.2012
Offline
171
baas
#20
Tilo:
Не могу понять в чем дело мускуль периодически падает, 
service mysqld status

 mysqld dead but subsys locked centos
подскажите в какую сторону копать. На серваке ТДС-ка
стоит и траф соответственно из-за этих падений мускуля сливается впустоту.
лог мускуля 

150810 02:27:01 mysqld_safe Number of processes running now: 0

150810 02:27:01 mysqld_safe mysqld restarted

150810  2:27:01 [Note] libgovernor.so not found

150810  2:27:01 [Note] /usr/libexec/mysqld (mysqld 5.5.44-cll-lve) starting as process 987 ...

150810  2:27:01 [Note] Plugin 'FEDERATED' is disabled.

150810  2:27:01 InnoDB: The InnoDB memory heap is disabled

150810  2:27:01 InnoDB: Mutexes and rw_locks use GCC atomic builtins

150810  2:27:01 InnoDB: Compressed tables use zlib 1.2.3

150810  2:27:01 InnoDB: Using Linux native AIO

150810  2:27:01 InnoDB: Initializing buffer pool, size = 128.0M

150810  2:27:01 InnoDB: Completed initialization of buffer pool

150810  2:27:01 InnoDB: highest supported file format is Barracuda.

InnoDB: The log sequence number in ibdata files does not match

InnoDB: the log sequence number in the ib_logfiles!

150810  2:27:01  InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Restoring possible half-written data pages from the doublewrite

InnoDB: buffer...

150810  2:27:01  InnoDB: Waiting for the background threads to start

150810  2:27:02 InnoDB: 5.5.44 started; log sequence number 5586665

150810  2:27:02 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306

150810  2:27:02 [Note]   - '0.0.0.0' resolves to '0.0.0.0';

150810  2:27:02 [Note] Server socket created on IP: '0.0.0.0'.

150810  2:27:02 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it

150810  2:27:02 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure

150810  2:27:02 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure

150810  2:27:02 [Note] Event Scheduler: Loaded 0 events

150810  2:27:02 [Note] /usr/libexec/mysqld: ready for connections.

Version: '5.5.44-cll-lve'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL) by Atomicorp

150810 02:27:50 mysqld_safe Number of processes running now: 0

150810 02:27:50 mysqld_safe mysqld restarted

150810  2:27:50 [Note] libgovernor.so not found

150810  2:27:50 [Note] /usr/libexec/mysqld (mysqld 5.5.44-cll-lve) starting as process 1019 ...

150810  2:27:50 [Note] Plugin 'FEDERATED' is disabled.

150810  2:27:50 InnoDB: The InnoDB memory heap is disabled

150810  2:27:50 InnoDB: Mutexes and rw_locks use GCC atomic builtins

150810  2:27:50 InnoDB: Compressed tables use zlib 1.2.3

150810  2:27:50 InnoDB: Using Linux native AIO

150810  2:27:50 InnoDB: Initializing buffer pool, size = 128.0M

150810  2:27:50 InnoDB: Completed initialization of buffer pool

150810  2:27:50 InnoDB: highest supported file format is Barracuda.

InnoDB: The log sequence number in ibdata files does not match

InnoDB: the log sequence number in the ib_logfiles!

150810  2:27:50  InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Restoring possible half-written data pages from the doublewrite

InnoDB: buffer...

150810 02:27:51 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

150811 01:14:36 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

150811  1:14:36 [Note] libgovernor.so not found

150811  1:14:36 [Note] /usr/libexec/mysqld (mysqld 5.5.44-cll-lve) starting as process 11614 ...

150811  1:14:36 [Note] Plugin 'FEDERATED' is disabled.

150811  1:14:36 InnoDB: The InnoDB memory heap is disabled

150811  1:14:36 InnoDB: Mutexes and rw_locks use GCC atomic builtins

150811  1:14:36 InnoDB: Compressed tables use zlib 1.2.3

150811  1:14:36 InnoDB: Using Linux native AIO

150811  1:14:36 InnoDB: Initializing buffer pool, size = 128.0M

150811  1:14:36 InnoDB: Completed initialization of buffer pool

150811  1:14:36 InnoDB: highest supported file format is Barracuda.

InnoDB: The log sequence number in ibdata files does not match

InnoDB: the log sequence number in the ib_logfiles!

150811  1:14:36  InnoDB: Database was not shut down normally!

InnoDB: Starting crash recovery.

InnoDB: Reading tablespace information from the .ibd files...

InnoDB: Restoring possible half-written data pages from the doublewrite

InnoDB: buffer...

150811  1:14:36  InnoDB: Waiting for the background threads to start

150811  1:14:37 InnoDB: 5.5.44 started; log sequence number 5586665

150811  1:14:37 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306

150811  1:14:37 [Note]   - '0.0.0.0' resolves to '0.0.0.0';

150811  1:14:37 [Note] Server socket created on IP: '0.0.0.0'.

150811  1:14:37 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it

150811  1:14:37 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure

150811  1:14:37 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure

150811  1:14:37 [Note] Event Scheduler: Loaded 0 events

150811  1:14:37 [Note] /usr/libexec/mysqld: ready for connections.

Version: '5.5.44-cll-lve'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Server (GPL) by Atomicorp

Мускул просит вас please run mysql_upgrade to create it

Попробуйте так.

# /etc/init.d/mysqld stop

# mysqld_safe --skip-grant-tables &

# mysql_upgrade

# /etc/init.d/mysqld stop

# /etc/init.d/mysqld start

Настройка BSD систем. (https://www.fryaha.ru) Знание сила, незнание Рабочая сила!
12

Авторизуйтесь или зарегистрируйтесь, чтобы оставить комментарий