Magento MySQL hanging

Stephan
September 17, 2018
200 views
3 votes
6 Answers

I have searched the internet in many places to find some answers to problems in the database of a Magento installation.

The website is not available at such a moment (lasting most of the time about 15 minutes and resolves itself), not frontend and not backend.
I have tried various tuningscripts for mysql, setting indexing to manual, moving the DB to a dedicated server, etc. The logs of the Ubuntu OS nor the Magento logs show any unusual error message. Fail2Ban is installed against DDOS.

The mysql process is OR using 100+ percent OR using 0.0 percent (!!!) but the whole server seems te freeze. Sometimes even my typed characted with Putty/SSH are not shown by the server until it restores itself.

The dedicated DB-server has 32Gb RAM, mysql uses about 25G.

Top of ToP:
840 mysql 20 0 25,640g 0,011t 6632 S 26,2 34,2 270:08.31 mysqld

Settings my.cnf:

[mysqld]
key_buffer              = 512M
max_allowed_packet      = 64M
thread_stack            = 512K
thread_cache_size       = 512
sort_buffer_size        = 24M
read_buffer_size        = 8M
read_rnd_buffer_size    = 24M
join_buffer_size        = 128M
max_connections         = 1024
concurrent_insert       = 2
connect_timeout         = 5
table_open_cache        = 12288
tmp_table_size          = 512M
max_heap_table_size     = 512M
bulk_insert_buffer_size = 512M
open-files-limit        = 8192
open-files              = 1024
query_cache_type=1
query_cache_limit       = 4M
query_cache_size        = 512M
slow_query_log_file     = /var/log/mysql/slow.log
slow_query_log          = 0
long_query_time         = 0.2
expire_logs_days        = 10
max_binlog_size         = 1024M
binlog_cache_size       = 32K
innodb_thread_concurrency       = 16
innodb_commit_concurrency       = 2
innodb_buffer_pool_size = 16G
innodb_log_file_size    = 512M
innodb_additional_mem_pool_size = 8M
#innodb_data_file_path   = ibdata1:1024M:autoextend
innodb_file_per_table
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size  = 2G

Output of Show full processlist:

| Id    | User            | Host                            | db              | Command | Time | State              | Info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
++
| 76636 | juwelierwebshop | ...:40316 | juwelierwebshop | Query   |  328 | query end          | INSERT INTO abctools_merkgevuld (sku) VALUES ('JUPA22655')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| 76638 | juwelierwebshop | ...:40344 | juwelierwebshop | Sleep   |  328 |                    | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| 76642 | juwelierwebshop | ...:40484 | juwelierwebshop | Query   |   54 | query end          | SELECT `main_table`.* FROM `bolconnect_items_be` AS `main_table` WHERE (product_id = '62622')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| 76695 | juwelierwebshop | ...:54112 | juwelierwebshop | Sleep   |  603 |                    | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
| 76696 | juwelierwebshop | ...:54126 | juwelierwebshop | Query   |   54 | update             | INSERT INTO `bolconnect_offerlist` (`ean`, `prijs`, `leverancier`, `sellerid`, `sellerrating`, `avdesc`, `koopbox`, `updated_at`) VALUES ('8718834545421', '619', 'abc', '1121928', '8.8', '5 - 7 dagen', '1', '2018-09-07 12:10:10')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| 77039 | juwelierwebshop | ...:40290 | juwelierwebshop | Query   |   54 | Sending data       | INSERT INTO `sales_flat_order_grid` (`entity_id`, `status`, `store_id`, `customer_id`, `base_grand_total`, `base_total_paid`, `grand_total`, `total_paid`, `increment_id`, `base_currency_code`, `order_currency_code`, `store_name`, `created_at`, `updated_at`, `billing_name`, `shipping_name`) SELECT `main_table`.`entity_id`, `main_table`.`status`, `main_table`.`store_id`, `main_table`.`customer_id`, `main_table`.`base_grand_total`, `main_table`.`base_total_paid`, `main_table`.`grand_total`, `main_table`.`total_paid`, `main_table`.`increment_id`, `main_table`.`base_currency_code`, `main_table`.`order_currency_code`, `main_table`.`store_name`, `main_table`.`created_at`, `main_table`.`updated_at`, CONCAT(IFNULL(table_billing_name.firstname, ''), ' ', IFNULL(table_billing_name.lastname, '')) AS `billing_name`, CONCAT(IFNULL(table_shipping_name.firstname, ''), ' ', IFNULL(table_shipping_name.lastname, '')) AS `shipping_name` FROM `sales_flat_order` AS `main_table`
 LEFT JOIN `sales_flat_order_address` AS `table_billing_name` ON `main_table`.`billing_address_id`=`table_billing_name`.`entity_id`
 LEFT JOIN `sales_flat_order_address` AS `table_shipping_name` ON `main_table`.`shipping_address_id`=`table_shipping_name`.`entity_id` WHERE (main_table.entity_id IN('27894')) ON DUPLICATE KEY UPDATE `entity_id` = VALUES(`entity_id`), `status` = VALUES(`status`), `store_id` = VALUES(`store_id`), `customer_id` = VALUES(`customer_id`), `base_grand_total` = VALUES(`base_grand_total`), `base_total_paid` = VALUES(`base_total_paid`), `grand_total` = VALUES(`grand_total`), `total_paid` = VALUES(`total_paid`), `increment_id` = VALUES(`increment_id`), `base_currency_code` = VALUES(`base_currency_code`), `order_currency_code` = VALUES(`order_currency_code`), `store_name` = VALUES(`store_name`), `created_at` = VALUES(`created_at`), `updated_at` = VALUES(`updated_at`), `billing_name` = VALUES(`billing_name`), `shipping_name` = VALUES(`shipping_name`) |
| 77041 | juwelierwebshop | ...:40578 | juwelierwebshop | Query   |   54 | statistics         | SELECT `catalog_product_entity`.* FROM `catalog_product_entity` WHERE (entity_id ='45702')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
| 77042 | juwelierwebshop | ...:40766 | juwelierwebshop | Query   |   54 | Sending data       | SELECT COUNT(DISTINCT e.entity_id) FROM `catalog_product_flat_2` AS `e`
 INNER JOIN `report_viewed_product_index` AS `idx_table` ON (idx_table.product_id=e.entity_id) AND (idx_table.visitor_id = '')
 INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=2 AND cat_index.category_id = '2'
 LEFT JOIN `core_store` AS `store_index` ON store_index.store_id = idx_table.store_id
 LEFT JOIN `core_store_group` AS `store_group_index` ON store_index.group_id = store_group_index.group_id
 LEFT JOIN `catalog_category_product_index` AS `store_cat_index` ON store_cat_index.product_id = e.entity_id AND store_cat_index.store_id = idx_table.store_id AND store_cat_index.category_id=store_group_index.root_category_id WHERE (cat_index.visibility IN(3, 2, 4) OR store_cat_index.visibility IN(3, 2, 4))                                                                                                                                                                             |
| 77043 | juwelierwebshop | ...:40930 | juwelierwebshop | Query   |   54 | statistics         | SELECT `catalog_category_entity`.* FROM `catalog_category_entity` WHERE (entity_id ='37')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
| 77047 | juwelierwebshop | ...:42478 | juwelierwebshop | Query   |   53 | Sending data       | SELECT COUNT(DISTINCT e.entity_id) FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = '96') AND (`at_status`.`store_id` = 0)
 INNER JOIN `catalog_product_entity_int` AS `at_visibility` ON (`at_visibility`.`entity_id` = `e`.`entity_id`) AND (`at_visibility`.`attribute_id` = '102') AND (`at_visibility`.`store_id` = 0)
 INNER JOIN `catalog_product_entity_varchar` AS `at_name` ON (`at_name`.`entity_id` = `e`.`entity_id`) AND (`at_name`.`attribute_id` = '71') AND (`at_name`.`store_id` = 0) WHERE (at_name.value LIKE '%BUDDHA TO BUDDHA 811 SIGNIFY CORD BRACELET BROWN%')                                                                                                                                                                                                                                                                                                                                             |
| 77050 | juwelierwebshop | ...:43538 | juwelierwebshop | Query   |   54 | updating           | UPDATE `sales_flat_quote_address` SET `quote_id` = '62660', `created_at` = '2018-09-02 19:38:03', `updated_at` = '2018-09-07 12:10:10', `customer_id` = '22445', `save_in_address_book` = '0', `customer_address_id` = '45277', `address_type` = 'billing', `email` = '[email protected]', `prefix` = NULL, `firstname` = 'Pieter', `middlename` = NULL, `lastname` = 'Slits', `suffix` = NULL, `company` = NULL, `street` = 'Steurgatn92', `city` = 'Gorinchem', `region` = 'Zuid-Holland', `region_id` = NULL, `postcode` = '4208BL', `country_id` = 'NL', `telephone` = '0621263156', `fax` = NULL, `same_as_billing` = '0', `free_shipping` = '0', `collect_shipping_rates` = '0', `shipping_method` = NULL, `shipping_description` = NULL, `weight` = '0', `subtotal` = '0', `base_subtotal` = '0', `subtotal_with_discount` = '0', `base_subtotal_with_discount` = '0', `tax_amount` = '0', `base_tax_amount` = '0', `shipping_amount` = '0', `base_shipping_amount` = '0', `shipping_tax_amount` = '0', `base_shipping_tax_amount` = '0', `discount_amount` = '0', `base_discount_amount` = '0', `grand_total` = '0', `base_grand_total` = '0', `customer_notes` = NULL, `applied_taxes` = 'a:0:{}', `discount_description` = NULL, `shipping_discount_amount` = NULL, `base_shipping_discount_amount` = NULL, `subtotal_incl_tax` = '0', `base_subtotal_total_incl_tax` = NULL, `hidden_tax_amount` = NULL, `base_hidden_tax_amount` = NULL, `shipping_hidden_tax_amount` = NULL, `base_shipping_hidden_tax_amnt` = NULL, `shipping_incl_tax` = '0', `base_shipping_incl_tax` = '0', `vat_id` = NULL, `vat_is_valid` = NULL, `vat_request_id` = NULL, `vat_request_date` = NULL, `vat_request_success` = NULL, `gift_message_id` = NULL, `kp_id` = NULL, `payment_fee` = '0', `base_payment_fee` = '0', `payment_fee_tax` = '0', `base_payment_fee_tax` = '0' WHERE (address_id='121757') |
| 77052 | juwelierwebshop | ...:43840 | juwelierwebshop | Query   |   54 | init               | commit                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| 77053 | juwelierwebshop | ...:44590 | juwelierwebshop | Query   |  204 | removing tmp table | SELECT `customer_address_entity_varchar`.`entity_id`, `customer_address_entity_varchar`.`attribute_id`, `customer_address_entity_varchar`.`value` FROM `customer_address_entity_varchar` WHERE (entity_type_id =2) AND (entity_id IN (45277)) AND (attribute_id IN ('26', '24', '27', '32', '20', '22', '21', '30', '19', '28', '23', '31', '36', '39', '38')) UNION ALL SELECT `customer_address_entity_int`.`entity_id`, `customer_address_entity_int`.`attribute_id`, `customer_address_entity_int`.`value` FROM `customer_address_entity_int` WHERE (entity_type_id =2) AND (entity_id IN (45277)) AND (attribute_id IN ('29', '37', '40')) UNION ALL SELECT `customer_address_entity_text`.`entity_id`, `customer_address_entity_text`.`attribute_id`, `customer_address_entity_text`.`value` FROM `customer_address_entity_text` WHERE (entity_type_id =2) AND (entity_id IN (45277)) AND (attribute_id IN ('25'))                                                                                                                                         |
| 77055 | juwelierwebshop | ...:44704 | juwelierwebshop | Query   |  203 | Opening tables     | SELECT `customer_address_entity_varchar`.`entity_id`, `customer_address_entity_varchar`.`attribute_id`, `customer_address_entity_varchar`.`value` FROM `customer_address_entity_varchar` WHERE (entity_type_id =2) AND (entity_id IN (45277)) AND (attribute_id IN ('26', '24', '27', '32', '20', '22', '21', '30', '19', '28', '23', '31', '36', '39', '38')) UNION ALL SELECT `customer_address_entity_int`.`entity_id`, `customer_address_entity_int`.`attribute_id`, `customer_address_entity_int`.`value` FROM `customer_address_entity_int` WHERE (entity_type_id =2) AND (entity_id IN (45277)) AND (attribute_id IN ('29', '37', '40')) UNION ALL SELECT `customer_address_entity_text`.`entity_id`, `customer_address_entity_text`.`attribute_id`, `customer_address_entity_text`.`value` FROM `customer_address_entity_text` WHERE (entity_type_id =2) AND (entity_id IN (45277)) AND (attribute_id IN ('25'))                                                                                                                                         |
| 77056 | juwelierwebshop | ...:44950 | juwelierwebshop | Query   |  199 | Opening tables     | SELECT `customer_address_entity_varchar`.`entity_id`, `customer_address_entity_varchar`.`attribute_id`, `customer_address_entity_varchar`.`value` FROM `customer_address_entity_varchar` WHERE (entity_type_id =2) AND (entity_id IN (45277)) AND (attribute_id IN ('26', '24', '27', '32', '20', '22', '21', '30', '19', '28', '23', '31', '36', '39', '38')) UNION ALL SELECT `customer_address_entity_int`.`entity_id`, `customer_address_entity_int`.`attribute_id`, `customer_address_entity_int`.`value` FROM `customer_address_entity_int` WHERE (entity_type_id =2) AND (entity_id IN (45277)) AND (attribute_id IN ('29', '37', '40')) UNION ALL SELECT `customer_address_entity_text`.`entity_id`, `customer_address_entity_text`.`attribute_id`, `customer_address_entity_text`.`value` FROM `customer_address_entity_text` WHERE (entity_type_id =2) AND (entity_id IN (45277)) AND (attribute_id IN ('25'))                                                                                                                                         |
| 77058 | juwelierwebshop | ...:45508 | juwelierwebshop | Query   |   54 | Sending data       | SELECT `catalog_product_website`.`website_id` FROM `catalog_product_website` WHERE (product_id = 16722)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
| 77063 | juwelierwebshop | ...:45836 | juwelierwebshop | Query   |   54 | update             | INSERT INTO `sales_flat_quote_address` (`quote_id`, `created_at`, `updated_at`, `customer_id`, `address_type`, `city`, `region`, `region_id`, `postcode`, `country_id`, `same_as_billing`, `collect_shipping_rates`, `shipping_method`, `shipping_description`, `shipping_amount`, `base_shipping_amount`) VALUES ('65679', '2018-09-07 12:10:10', '2018-09-07 12:10:10', NULL, 'shipping', NULL, NULL, '0', NULL, 'NL', '1', '1', 'flatrate_flatrate', NULL, '0', '0')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
| 77067 | juwelierwebshop | ...:46232 | juwelierwebshop | Query   |  229 | update             | INSERT INTO `sales_flat_quote` (`store_id`, `created_at`, `updated_at`, `store_to_base_rate`, `store_to_quote_rate`, `base_currency_code`, `store_currency_code`, `quote_currency_code`, `remote_ip`, `global_currency_code`, `base_to_global_rate`, `base_to_quote_rate`, `is_changed`, `allow_alerts`) VALUES ('2', '2018-09-07 12:07:15', '2018-09-07 12:07:15', '1', '1', 'EUR', 'EUR', 'EUR', '81.169.242.132', 'EUR', '1', '1', '1', '1')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| 77069 | juwelierwebshop | ...:46678 | juwelierwebshop | Query   |  222 | query end          | SELECT `t_d`.`entity_id`, `t_d`.`attribute_id`, `t_d`.`value` AS `default_value`, `t_s`.`value` AS `store_value`, IF(t_s.value_id IS NULL, t_d.value, t_s.value) AS `value` FROM `catalog_category_entity_varchar` AS `t_d`
 LEFT JOIN `catalog_category_entity_varchar` AS `t_s` ON t_s.attribute_id = t_d.attribute_id AND t_s.entity_id = t_d.entity_id AND t_s.store_id = 2 WHERE (t_d.entity_type_id = 3) AND (t_d.entity_id IN (260)) AND (t_d.attribute_id IN ('41')) AND (t_d.store_id = 0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| 77074 | juwelierwebshop | ...:46984 | juwelierwebshop | Query   |  216 | query end          | SELECT `e`.*, IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) AS `is_active` FROM `catalog_category_entity` AS `e`
 INNER JOIN `catalog_category_entity_int` AS `at_is_active_default` ON (`at_is_active_default`.`entity_id` = `e`.`entity_id`) AND (`at_is_active_default`.`attribute_id` = '42') AND `at_is_active_default`.`store_id` = 0
 LEFT JOIN `catalog_category_entity_int` AS `at_is_active` ON (`at_is_active`.`entity_id` = `e`.`entity_id`) AND (`at_is_active`.`attribute_id` = '42') AND (`at_is_active`.`store_id` = 2) WHERE (`e`.`entity_type_id` = '3') AND (`e`.`entity_id` IN('365')) AND (IF(at_is_active.value_id > 0, at_is_active.value, at_is_active_default.value) = '1')                                                                                                                                                                                                                                                                                                                             |
| 77076 | juwelierwebshop | ...:47082 | juwelierwebshop | Query   |  214 | query end          | SELECT `t_d`.`entity_id`, `t_d`.`attribute_id`, `t_d`.`value` AS `default_value`, `t_s`.`value` AS `store_value`, IF(t_s.value_id IS NULL, t_d.value, t_s.value) AS `value` FROM `catalog_category_entity_varchar` AS `t_d`
 LEFT JOIN `catalog_category_entity_varchar` AS `t_s` ON t_s.attribute_id = t_d.attribute_id AND t_s.entity_id = t_d.entity_id AND t_s.store_id = 2 WHERE (t_d.entity_type_id = 3) AND (t_d.entity_id IN (187)) AND (t_d.attribute_id IN ('41')) AND (t_d.store_id = 0)

.. and more

Output ulimit -a:

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 1030957
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 1030957
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

Answers

Chosen as BEST ANSWER

As long as my post is getting too long, i will post the additional info here...

SHOW GLOBAL STATUS:

Aborted_clients
146
Aborted_connects
286
Binlog_cache_disk_use
0
Binlog_cache_use
0
Binlog_stmt_cache_disk_use
0
Binlog_stmt_cache_use
0
Bytes_received
262381827589
Bytes_sent
934591758226
Com_admin_commands
13
Com_assign_to_keycache
0
Com_alter_db
0
Com_alter_db_upgrade
0
Com_alter_event
0
Com_alter_function
0
Com_alter_procedure
0
Com_alter_server
0
Com_alter_table
0
Com_alter_tablespace
0
Com_alter_user
0
Com_analyze
0
Com_begin
21257645
Com_binlog
0
Com_call_procedure
0
Com_change_db
303
Com_change_master
0
Com_check
0
Com_checksum
0
Com_commit
21257476
Com_create_db
0
Com_create_event
0
Com_create_function
0
Com_create_index
0
Com_create_procedure
0
Com_create_server
0
Com_create_table
1981
Com_create_trigger
0
Com_create_udf
0
Com_create_user
0
Com_create_view
0
Com_dealloc_sql
0
Com_delete
8394541
Com_delete_multi
63886
Com_do
0
Com_drop_db
0
Com_drop_event
0
Com_drop_function
0
Com_drop_index
0
Com_drop_procedure
0
Com_drop_server
0
Com_drop_table
1980
Com_drop_trigger
0
Com_drop_user
0
Com_drop_view
0
Com_empty_query
0
Com_execute_sql
0
Com_flush
11
Com_get_diagnostics
0
Com_grant
0
Com_ha_close
0
Com_ha_open
0
Com_ha_read
0
Com_help
0
Com_insert
11568107
Com_insert_select
1143891
Com_install_plugin
0
Com_kill
0
Com_load
0
Com_lock_tables
0
Com_optimize
0
Com_preload_keys
0
Com_prepare_sql
0
Com_purge
0
Com_purge_before_date
0
Com_release_savepoint
0
Com_rename_table
0
Com_rename_user
0
Com_repair
0
Com_replace
12
Com_replace_select
0
Com_reset
0
Com_resignal
0
Com_revoke
0
Com_revoke_all
0
Com_rollback
151
Com_rollback_to_savepoint
0
Com_savepoint
0
Com_select
450132043
Com_set_option
2690585
Com_signal
0
Com_show_binlog_events
0
Com_show_binlogs
10
Com_show_charsets
0
Com_show_collations
0
Com_show_create_db
0
Com_show_create_event
0
Com_show_create_func
0
Com_show_create_proc
0
Com_show_create_table
38
Com_show_create_trigger
0
Com_show_databases
0
Variable_name
Value

Com_show_engine_logs
0
Com_show_engine_mutex
0
Com_show_engine_status
0
Com_show_events
0
Com_show_errors
0
Com_show_fields
18447
Com_show_function_code
0
Com_show_function_status
0
Com_show_grants
17
Com_show_keys
9599
Com_show_master_status
26
Com_show_open_tables
0
Com_show_plugins
3
Com_show_privileges
0
Com_show_procedure_code
0
Com_show_procedure_status
0
Com_show_processlist
0
Com_show_profile
0
Com_show_profiles
0
Com_show_relaylog_events
0
Com_show_slave_hosts
0
Com_show_slave_status
26
Com_show_status
3
Com_show_storage_engines
1
Com_show_table_status
20700
Com_show_tables
236
Com_show_triggers
0
Com_show_variables
193
Com_show_warnings
56103
Com_slave_start
0
Com_slave_stop
0
Com_stmt_close
0
Com_stmt_execute
0
Com_stmt_fetch
0
Com_stmt_prepare
0
Com_stmt_reprepare
0
Com_stmt_reset
0
Com_stmt_send_long_data
0
Com_truncate
379
Com_uninstall_plugin
0
Com_unlock_tables
0
Com_update
13681401
Com_update_multi
38290135
Com_xa_commit
0
Com_xa_end
0
Com_xa_prepare
0
Com_xa_recover
0
Com_xa_rollback
0
Com_xa_start
0
Compression
OFF
Connection_errors_accept
0
Connection_errors_internal
0
Connection_errors_max_connections
0
Connection_errors_peer_address
0
Connection_errors_select
0
Connection_errors_tcpwrap
0
Connections
1754922
Created_tmp_disk_tables
32299788
Created_tmp_files
2827
Created_tmp_tables
67858347
Delayed_errors
0
Delayed_insert_threads
0
Delayed_writes
0
Flush_commands
1
Handler_commit
537556955
Handler_delete
21203603
Handler_discover
0
Handler_external_lock
2080617356
Handler_mrr_init
0
Handler_prepare
0
Handler_read_first
5701366
Handler_read_key
26262529583
Handler_read_last
31617
Handler_read_next
109928062206
Handler_read_prev
212093135
Handler_read_rnd
6690959918
Handler_read_rnd_next
26130974483
Handler_rollback
99880
Handler_savepoint
0
Handler_savepoint_rollback
0
Handler_update
80365845
Handler_write
3262327514
Innodb_buffer_pool_dump_status
not started
Innodb_buffer_pool_load_status
not started
Innodb_buffer_pool_pages_data
384476
Innodb_buffer_pool_bytes_data
6299254784
Innodb_buffer_pool_pages_dirty
95
Innodb_buffer_pool_bytes_dirty
1556480
Innodb_buffer_pool_pages_flushed
16849536
Innodb_buffer_pool_pages_free
617389
Innodb_buffer_pool_pages_misc
46707
Innodb_buffer_pool_pages_total
1048572
Innodb_buffer_pool_read_ahead_rnd
0
Innodb_buffer_pool_read_ahead
26706
Innodb_buffer_pool_read_ahead_evicted
0
Innodb_buffer_pool_read_requests
235232077708
Innodb_buffer_pool_reads
157875
Innodb_buffer_pool_wait_free
0
Innodb_buffer_pool_write_requests
523402314
Innodb_data_fsyncs
8277097
Variable_name
Value

Innodb_data_pending_fsyncs
0
Innodb_data_pending_reads
0
Innodb_data_pending_writes
0
Innodb_data_read
3187642368
Innodb_data_reads
195006
Innodb_data_writes
46743559
Innodb_data_written
669383931392
Innodb_dblwr_pages_written
16849536
Innodb_dblwr_writes
2819209
Innodb_have_atomic_builtins
ON
Innodb_log_waits
0
Innodb_log_write_requests
231729452
Innodb_log_writes
26991919
Innodb_os_log_fsyncs
1177300
Innodb_os_log_pending_fsyncs
0
Innodb_os_log_pending_writes
0
Innodb_os_log_written
117223886848
Innodb_page_size
16384
Innodb_pages_created
244392
Innodb_pages_read
194427
Innodb_pages_written
16849536
Innodb_row_lock_current_waits
0
Innodb_row_lock_time
19483692
Innodb_row_lock_time_avg
2345
Innodb_row_lock_time_max
51715
Innodb_row_lock_waits
8306
Innodb_rows_deleted
20957957
Innodb_rows_inserted
27091885
Innodb_rows_read
156431546126
Innodb_rows_updated
25463060
Innodb_num_open_files
573
Innodb_truncated_status_writes
0
Innodb_available_undo_logs
128
Key_blocks_not_flushed
0
Key_blocks_unused
247176
Key_blocks_used
181543
Key_read_requests
478108001
Key_reads
171224
Key_write_requests
42044666
Key_writes
32000663
Last_query_cost
0.000000
Last_query_partial_plans
0
Max_used_connections
108
Not_flushed_delayed_rows
0
Open_files
132
Open_streams
0
Open_table_definitions
695
Open_tables
2157
Opened_files
129209548
Opened_table_definitions
5035
Opened_tables
98173
Performance_schema_accounts_lost
0
Performance_schema_cond_classes_lost
0
Performance_schema_cond_instances_lost
0
Performance_schema_digest_lost
0
Performance_schema_file_classes_lost
0
Performance_schema_file_handles_lost
0
Performance_schema_file_instances_lost
0
Performance_schema_hosts_lost
0
Performance_schema_locker_lost
0
Performance_schema_mutex_classes_lost
0
Performance_schema_mutex_instances_lost
0
Performance_schema_rwlock_classes_lost
0
Performance_schema_rwlock_instances_lost
0
Performance_schema_session_connect_attrs_lost
0
Performance_schema_socket_classes_lost
0
Performance_schema_socket_instances_lost
0
Performance_schema_stage_classes_lost
0
Performance_schema_statement_classes_lost
0
Performance_schema_table_handles_lost
0
Performance_schema_table_instances_lost
0
Performance_schema_thread_classes_lost
0
Performance_schema_thread_instances_lost
0
Performance_schema_users_lost
0
Prepared_stmt_count
0
Qcache_free_blocks
1
Qcache_free_memory
536852816
Qcache_hits
0
Qcache_inserts
0
Qcache_lowmem_prunes
0
Qcache_not_cached
450105606
Qcache_queries_in_cache
0
Qcache_total_blocks
1
Queries
570344429
Questions
570344415
Select_full_join
336026
Select_full_range_join
1914512
Select_range
21082218
Select_range_check
0
Select_scan
37881116
Slave_heartbeat_period
Slave_last_heartbeat
Slave_open_temp_tables
0
Slave_received_heartbeats
Slave_retried_transactions
Slave_running
OFF
Slow_launch_threads
0
Slow_queries
359667
Sort_merge_passes
1690
Sort_range
24277510
Variable_name
Value

Sort_rows
2017426115
Sort_scan
66464668
Ssl_accept_renegotiates
0
Ssl_accepts
0
Ssl_callback_cache_hits
0
Ssl_cipher
Ssl_cipher_list
Ssl_client_connects
0
Ssl_connect_renegotiates
0
Ssl_ctx_verify_depth
0
Ssl_ctx_verify_mode
0
Ssl_default_timeout
0
Ssl_finished_accepts
0
Ssl_finished_connects
0
Ssl_server_not_after
Ssl_server_not_before
Ssl_session_cache_hits
0
Ssl_session_cache_misses
0
Ssl_session_cache_mode
NONE
Ssl_session_cache_overflows
0
Ssl_session_cache_size
0
Ssl_session_cache_timeouts
0
Ssl_sessions_reused
0
Ssl_used_session_cache_entries
0
Ssl_verify_depth
0
Ssl_verify_mode
0
Ssl_version
Table_locks_immediate
1022116993
Table_locks_waited
155
Table_open_cache_hits
1022210351
Table_open_cache_misses
2690
Table_open_cache_overflows
0
Tc_log_max_pages_used
0
Tc_log_page_size
0
Tc_log_page_waits
0
Threads_cached
103
Threads_connected
5
Threads_created
108
Threads_running
1
Uptime
929242
Uptime_since_flush_status
929242

(Edit)

- VladimirSamsonov
- September 17, 2018 at 11:15 am
- 0 votes
0
Just in general, when catalog is larger then 50k
1. Enable Flat catalog
2. Set indexer modes to manual and setup a nightly
  cron for them.
Login or Signup to reply.

- WilsonHauck
- September 20, 2018 at 8:04 pm
- 0 votes
0
Rate Per Second=RPS
Suggestions to consider for your my.cnf [mysqld] section
```
thread_cache_size=100  # from 512 see refman 5.7 for CAP of 100 suggestions to avoid OOM
innodb_log_buffer_size=512M  # from 2G - BUFFER should NOT be > file size
innodb_log_file_size=2G  # from 512M for a reasonable relationship to BUFFER
```
the following 4 are per CONNECTION values and you are OVER the TOP
```
sort_buffer_size=4M  # from 24M  default is 2M
read_buffer_size=256K  # from 8M  default is 128K to reduce handler_read_next RPS
read_rnd_buffer_size=512K  # from 24M  default is 256K to reduce handler_read_rnd_next RPS
join_buffer_size=256K  # from 128M default is 128K  for row pointers
```
Please view my profile, Network profile for contact info including my Skype ID and get in touch.
Login or Signup to reply.

SHOW GLOBAL VARIABLES:

auto_increment_increment
1
auto_increment_offset
1
autocommit
ON
automatic_sp_privileges
ON
back_log
254
basedir
/usr
big_tables
OFF
bind_address
*
binlog_cache_size
32768
binlog_checksum
CRC32
binlog_direct_non_transactional_updates
OFF
binlog_format
STATEMENT
binlog_max_flush_queue_time
0
binlog_order_commits
ON
binlog_row_image
FULL
binlog_rows_query_log_events
OFF
binlog_stmt_cache_size
32768
bulk_insert_buffer_size
536870912
character_set_client
latin1
character_set_connection
latin1
character_set_database
latin1
character_set_filesystem
binary
character_set_results
latin1
character_set_server
latin1
character_set_system
utf8
character_sets_dir
/usr/share/mysql/charsets/
collation_connection
latin1_swedish_ci
collation_database
latin1_swedish_ci
collation_server
latin1_swedish_ci
completion_type
NO_CHAIN
concurrent_insert
ALWAYS
connect_timeout
5
core_file
OFF
datadir
/var/lib/mysql/
date_format
%Y-%m-%d
datetime_format
%Y-%m-%d %H:%i:%s
default_storage_engine
InnoDB
default_tmp_storage_engine
InnoDB
default_week_format
0
delay_key_write
ON
delayed_insert_limit
100
delayed_insert_timeout
300
delayed_queue_size
1000
disconnect_on_expired_password
ON
div_precision_increment
4
end_markers_in_json
OFF
enforce_gtid_consistency
OFF
eq_range_index_dive_limit
10
event_scheduler
OFF
expire_logs_days
10
explicit_defaults_for_timestamp
OFF
flush
OFF
flush_time
0
foreign_key_checks
ON
ft_boolean_syntax
+ -><()~*:""&|
ft_max_word_len
84
ft_min_word_len
4
ft_query_expansion_limit
20
ft_stopword_file
(built-in)
general_log
OFF
general_log_file
/var/lib/mysql/xxx.log
group_concat_max_len
1024
gtid_executed
gtid_mode
OFF
gtid_owned
gtid_purged
have_compress
YES
have_crypt
YES
have_dynamic_loading
YES
have_geometry
YES
have_openssl
DISABLED
have_profiling
YES
have_query_cache
YES
have_rtree_keys
YES
have_ssl
DISABLED
have_symlink
YES
host_cache_size
654
hostname
h2776883.stratoserver.net
ignore_builtin_innodb
OFF
ignore_db_dirs
init_connect
init_file
init_slave
innodb_adaptive_flushing
ON
innodb_adaptive_flushing_lwm
10
innodb_adaptive_hash_index
ON
innodb_adaptive_max_sleep_delay
150000
innodb_additional_mem_pool_size
8388608
innodb_api_bk_commit_interval
5
innodb_api_disable_rowlock
OFF
innodb_api_enable_binlog
OFF
innodb_api_enable_mdl
OFF
innodb_api_trx_level
0
innodb_autoextend_increment
64
innodb_autoinc_lock_mode
1
innodb_buffer_pool_dump_at_shutdown
OFF
innodb_buffer_pool_dump_now
OFF
innodb_buffer_pool_filename
ib_buffer_pool
innodb_buffer_pool_instances
8
innodb_buffer_pool_load_abort
OFF
Variable_name
Value

innodb_buffer_pool_load_at_startup
OFF
innodb_buffer_pool_load_now
OFF
innodb_buffer_pool_size
17179869184
innodb_change_buffer_max_size
25
innodb_change_buffering
all
innodb_checksum_algorithm
innodb
innodb_checksums
ON
innodb_cmp_per_index_enabled
OFF
innodb_commit_concurrency
0
innodb_compression_failure_threshold_pct
5
innodb_compression_level
6
innodb_compression_pad_pct_max
50
innodb_concurrency_tickets
5000
innodb_data_file_path
ibdata1:12M:autoextend
innodb_data_home_dir
innodb_disable_sort_file_cache
OFF
innodb_doublewrite
ON
innodb_fast_shutdown
1
innodb_file_format
Antelope
innodb_file_format_check
ON
innodb_file_format_max
Antelope
innodb_file_per_table
ON
innodb_flush_log_at_timeout
1
innodb_flush_log_at_trx_commit
2
innodb_flush_method
innodb_flush_neighbors
1
innodb_flushing_avg_loops
30
innodb_force_load_corrupted
OFF
innodb_force_recovery
0
innodb_ft_aux_table
innodb_ft_cache_size
8000000
innodb_ft_enable_diag_print
OFF
innodb_ft_enable_stopword
ON
innodb_ft_max_token_size
84
innodb_ft_min_token_size
3
innodb_ft_num_word_optimize
2000
innodb_ft_result_cache_limit
2000000000
innodb_ft_server_stopword_table
innodb_ft_sort_pll_degree
2
innodb_ft_total_cache_size
640000000
innodb_ft_user_stopword_table
innodb_io_capacity
200
innodb_io_capacity_max
2000
innodb_large_prefix
OFF
innodb_lock_wait_timeout
50
innodb_locks_unsafe_for_binlog
OFF
innodb_log_buffer_size
2147483648
innodb_log_compressed_pages
ON
innodb_log_file_size
536870912
innodb_log_files_in_group
2
innodb_log_group_home_dir
./
innodb_lru_scan_depth
1024
innodb_max_dirty_pages_pct
75
innodb_max_dirty_pages_pct_lwm
0
innodb_max_purge_lag
0
innodb_max_purge_lag_delay
0
innodb_mirrored_log_groups
1
innodb_monitor_disable
innodb_monitor_enable
innodb_monitor_reset
innodb_monitor_reset_all
innodb_old_blocks_pct
37
innodb_old_blocks_time
1000
innodb_online_alter_log_max_size
134217728
innodb_open_files
12288
innodb_optimize_fulltext_only
OFF
innodb_page_size
16384
innodb_print_all_deadlocks
OFF
innodb_purge_batch_size
300
innodb_purge_threads
1
innodb_random_read_ahead
OFF
innodb_read_ahead_threshold
56
innodb_read_io_threads
4
innodb_read_only
OFF
innodb_replication_delay
0
innodb_rollback_on_timeout
OFF
innodb_rollback_segments
128
innodb_sort_buffer_size
1048576
innodb_spin_wait_delay
6
innodb_stats_auto_recalc
ON
innodb_stats_method
nulls_equal
innodb_stats_on_metadata
OFF
innodb_stats_persistent
ON
innodb_stats_persistent_sample_pages
20
innodb_stats_sample_pages
8
innodb_stats_transient_sample_pages
8
innodb_status_output
OFF
innodb_status_output_locks
OFF
innodb_strict_mode
OFF
innodb_support_xa
ON
innodb_sync_array_size
1
innodb_sync_spin_loops
30
innodb_table_locks
ON
innodb_thread_concurrency
0
innodb_thread_sleep_delay
10000
innodb_undo_directory
.
innodb_undo_logs
128
innodb_undo_tablespaces
0
innodb_use_native_aio
ON
innodb_use_sys_malloc
ON
Variable_name
Value

innodb_version
5.6.16
innodb_write_io_threads
4
interactive_timeout
28800
join_buffer_size
131072
keep_files_on_create
OFF
key_buffer_size
536870912
key_cache_age_threshold
300
key_cache_block_size
1024
key_cache_division_limit
100
large_files_support
ON
large_page_size
0
large_pages
OFF
lc_messages
en_US
lc_messages_dir
/usr/share/mysql/
lc_time_names
en_US
license
GPL
local_infile
ON
lock_wait_timeout
31536000
locked_in_memory
OFF
log_bin
OFF
log_bin_basename
log_bin_index
log_bin_trust_function_creators
OFF
log_bin_use_v1_row_events
OFF
log_error
log_output
FILE
log_queries_not_using_indexes
OFF
log_slave_updates
OFF
log_slow_admin_statements
OFF
log_slow_slave_statements
OFF
log_throttle_queries_not_using_indexes
0
log_warnings
1
long_query_time
0.200000
low_priority_updates
OFF
lower_case_file_system
OFF
lower_case_table_names
0
master_info_repository
FILE
master_verify_checksum
OFF
max_allowed_packet
67108864
max_binlog_cache_size
18446744073709547520
max_binlog_size
1073741824
max_binlog_stmt_cache_size
18446744073709547520
max_connect_errors
100
max_connections
1024
max_delayed_threads
20
max_error_count
64
max_heap_table_size
536870912
max_insert_delayed_threads
20
max_join_size
18446744073709551615
max_length_for_sort_data
1024
max_prepared_stmt_count
16382
max_relay_log_size
0
max_seeks_for_key
18446744073709551615
max_sort_length
1024
max_sp_recursion_depth
0
max_tmp_tables
32
max_user_connections
128
max_write_lock_count
18446744073709551615
metadata_locks_cache_size
1024
metadata_locks_hash_instances
8
min_examined_row_limit
0
multi_range_count
256
myisam_data_pointer_size
6
myisam_max_sort_file_size
9223372036853727232
myisam_mmap_size
18446744073709551615
myisam_recover_options
OFF
myisam_repair_threads
1
myisam_sort_buffer_size
8388608
myisam_stats_method
nulls_unequal
myisam_use_mmap
OFF
net_buffer_length
16384
net_read_timeout
30
net_retry_count
10
net_write_timeout
60
new
OFF
old
OFF
old_alter_table
OFF
old_passwords
0
open_files_limit
25610
optimizer_prune_level
1
optimizer_search_depth
62
optimizer_switch
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on
optimizer_trace
enabled=off,one_line=off
optimizer_trace_features
greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
optimizer_trace_limit
1
optimizer_trace_max_mem_size
16384
optimizer_trace_offset
-1
performance_schema
ON
performance_schema_accounts_size
100
performance_schema_digests_size
10000
performance_schema_events_stages_history_long_size
10000
performance_schema_events_stages_history_size
10
performance_schema_events_statements_history_long_size
10000
performance_schema_events_statements_history_size
10
performance_schema_events_waits_history_long_size
10000
performance_schema_events_waits_history_size
10
performance_schema_hosts_size
100
performance_schema_max_cond_classes
80
performance_schema_max_cond_instances
8196
performance_schema_max_file_classes
50
Variable_name
Value

performance_schema_max_file_handles
32768
performance_schema_max_file_instances
39400
performance_schema_max_mutex_classes
200
performance_schema_max_mutex_instances
27144
performance_schema_max_rwlock_classes
40
performance_schema_max_rwlock_instances
14448
performance_schema_max_socket_classes
10
performance_schema_max_socket_instances
2068
performance_schema_max_stage_classes
150
performance_schema_max_statement_classes
168
performance_schema_max_table_handles
24576
performance_schema_max_table_instances
12500
performance_schema_max_thread_classes
50
performance_schema_max_thread_instances
2148
performance_schema_session_connect_attrs_size
512
performance_schema_setup_actors_size
100
performance_schema_setup_objects_size
100
performance_schema_users_size
100
pid_file
/var/lib/mysql/xxx.stratoserver.net.pid
plugin_dir
/usr/lib/mysql/plugin/
port
3306
preload_buffer_size
32768
profiling
OFF
profiling_history_size
15
protocol_version
10
query_alloc_block_size
8192
query_cache_limit
4194304
query_cache_min_res_unit
4096
query_cache_size
536870912
query_cache_type
OFF
query_cache_wlock_invalidate
OFF
query_prealloc_size
8192
range_alloc_block_size
4096
read_buffer_size
131072
read_only
OFF
read_rnd_buffer_size
262144
relay_log
relay_log_basename
relay_log_index
relay_log_info_file
relay-log.info
relay_log_info_repository
FILE
relay_log_purge
ON
relay_log_recovery
OFF
relay_log_space_limit
0
report_host
report_password
report_port
3306
report_user
rpl_stop_slave_timeout
31536000
secure_auth
ON
secure_file_priv
server_id
0
server_id_bits
32
server_uuid
f625f81f-3416-11e8-b131-02ab55a84b80
skip_external_locking
ON
skip_name_resolve
OFF
skip_networking
OFF
skip_show_database
OFF
slave_allow_batching
OFF
slave_checkpoint_group
512
slave_checkpoint_period
300
slave_compressed_protocol
OFF
slave_exec_mode
STRICT
slave_load_tmpdir
/tmp
slave_max_allowed_packet
1073741824
slave_net_timeout
3600
slave_parallel_workers
0
slave_pending_jobs_size_max
16777216
slave_rows_search_algorithms
TABLE_SCAN,INDEX_SCAN
slave_skip_errors
OFF
slave_sql_verify_checksum
ON
slave_transaction_retries
10
slave_type_conversions
slow_launch_time
2
slow_query_log
OFF
slow_query_log_file
/var/log/mysql/slow.log
socket
/var/run/mysqld/mysqld.sock
sort_buffer_size
2097152
sql_auto_is_null
OFF
sql_big_selects
ON
sql_buffer_result
OFF
sql_log_bin
ON
sql_log_off
OFF
sql_mode
NO_ENGINE_SUBSTITUTION
sql_notes
ON
sql_quote_show_create
ON
sql_safe_updates
OFF
sql_select_limit
18446744073709551615
sql_slave_skip_counter
0
sql_warnings
OFF
ssl_ca
ssl_capath
ssl_cert
ssl_cipher
ssl_crl
ssl_crlpath
ssl_key
storage_engine
InnoDB
stored_program_cache
256
sync_binlog
0
Variable_name
Value

sync_frm
ON
sync_master_info
10000
sync_relay_log
10000
sync_relay_log_info
10000
system_time_zone
CEST
table_definition_cache
2000
table_open_cache
12288
table_open_cache_instances
1
thread_cache_size
128
thread_concurrency
10
thread_handling
one-thread-per-connection
thread_stack
262144
time_format
%H:%i:%s
time_zone
SYSTEM
timed_mutexes
OFF
tmp_table_size
67108864
tmpdir
/tmp
transaction_alloc_block_size
8192
transaction_prealloc_size
4096
tx_isolation
REPEATABLE-READ
tx_read_only
OFF
unique_checks
ON
updatable_views_with_limit
YES
version
5.6.16-1~exp1
version_comment
(Ubuntu)
version_compile_machine
x86_64
version_compile_os
debian-linux-gnu
wait_timeout
28800

Output mysqltuner before any change:

 >>  MySQLTuner 1.7.12 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at 
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
[OK] Logged in using credentials from Debian maintenance account.
[OK] Currently running supported MySQL version 5.6.16-1~exp1
[OK] Operating on 64-bit architecture

-------- Log file Recommendations ------------------------------------------------------------------
[--] Log file: /var/lib/mysql/xxx.stratoserver.net.err(0B)
[!!] Log file /var/lib/mysql/xxx.stratoserver.net.err doesn't exist
[!!] Log file /var/lib/mysql/xxx.stratoserver.net.err isn't readable.

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 984.2M (Tables: 30)
[--] Data in InnoDB tables: 4.1G (Tables: 568)
[--] Data in MEMORY tables: 1.3M (Tables: 17)
[!!] Total fragmented tables: 1

-------- Analysis Performance Metrics --------------------------------------------------------------
[--] innodb_stats_on_metadata: OFF
[OK] No stat updates during querying INFORMATION_SCHEMA.

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

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

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 10d 23h 42m 17s (581M q [612.461 qps], 1M conn, TX: 889G, RX: 249G)
[--] Reads / Writes: 93% / 7%
[--] Binary logging is disabled
[--] Physical Memory     : 32.0G
[--] Max MySQL memory    : 22.6G
[--] Other process memory: 33.5M
[--] Total buffers: 19.1G global + 2.8M per thread (1024 max threads)
[--] P_S Max memory usage: 754M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 20.1G (62.80% of installed RAM)
[OK] Maximum possible memory usage: 22.6G (70.49% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (365K/581M)
[OK] Highest usage of available connections: 10% (108/1024)
[OK] Aborted connections: 0.02%  (338/1784303)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache may be disabled by default due to mutex contention.
[!!] Query cache efficiency: 0.0% (0 cached / 459M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 92M sorts)
[!!] Joins performed without indexes: 341633
[!!] Temporary tables created on disk: 47% (32M on disk / 69M total)
[OK] Thread cache hit rate: 99% (108 created / 1M connections)
[!!] Table cache hit rate: 2% (2K open / 98K opened)
[OK] Open file limit used: 0% (132/25K)
[OK] Table locks acquired immediately: 99% (1B immediate / 1B locks)

-------- Performance schema ------------------------------------------------------------------------
[--] Memory used by P_S: 754.8M
[--] Sys schema isn't installed.

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 52.9% (284M used / 536M cache)
[OK] Key buffer size / total MyISAM indexes: 512.0M/378.4M
[OK] Read Key buffer hit rate: 100.0% (484M cached / 171K reads)
[!!] Write Key buffer hit rate: 76.0% (42M cached / 32M writes)

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 16.0G/4.1G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (6.25 %): 512.0M * 2/16.0G should be equal 25%
[!!] InnoDB buffer pool instances: 8
[--] InnoDB Buffer Pool Chunk Size not used or defined in your version
[OK] InnoDB Read buffer efficiency: 100.00% (238662528177 hits/ 238662686106 total)
[!!] InnoDB Write Log efficiency: 88.34% (208711294 hits/ 236256359 total)
[OK] InnoDB log waits: 0.00% (0 waits / 27545065 writes)

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

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- XtraDB Metrics ----------------------------------------------------------------------------
[--] XtraDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] Binlog format: STATEMENT
[--] XA support enabled: ON
[--] Semi synchronous replication Master: Not Activated
[--] Semi synchronous replication Slave: Not Activated
[--] This is a standalone server

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
      OPTIMIZE TABLE `juwelierwebshop`.`cron_schedule`; -- can free 1064 MB
    Total freed space after theses OPTIMIZE TABLE : 1064 Mb
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: 
    This is MyISAM only table_cache scalability problem, InnoDB not affected.
    See more details here: 
    This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
    Beware that open_files_limit (25610) variable
    should be greater than table_open_cache (12288)
    Consider installing Sys schema from 
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: 
Variables to adjust:
    query_cache_size (=0)
    query_cache_type (=0)
    query_cache_limit (> 4M, or use smaller result sets)
    join_buffer_size (> 128.0K, or always use indexes with JOINs)
    tmp_table_size (> 64M)
    max_heap_table_size (> 512M)
    table_open_cache (> 12288)
    innodb_log_file_size should be (=2G) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
    innodb_buffer_pool_instances(=16)

- RickJames
- September 30, 2018 at 5:32 am
- 0 votes
0
Do not blindly raise values in hopes they help. The following are dangerously high and are likely to lead to swapping, which is much worse than having lower settings:
```
key_buffer              = 512M
thread_cache_size       = 512
join_buffer_size        = 128M
max_connections         = 1024
table_open_cache        = 12288
tmp_table_size          = 512M
max_heap_table_size     = 512M
query_cache_size        = 512M
innodb_log_buffer_size  = 2G
```
Consider moving the rest of your tables from MyISAM to InnoDB.

You seem to have a lot of poorly formulated queries and/or missing indexes. The Slowlog is configured, but turned off, so you cannot readily get further info. Turn it back on.

Are these indexed? Are they numbers or strings?
```
main_table.product_id
main_table.entity_id
catalog_category_entity.entity_id
sales_flat_quote_address.address_id
```
This is vicious to optimize; can it be rewritten some other way?..
```
    WHERE  (cat_index.visibility IN(3, 2, 4)
        OR  store_cat_index.visibility IN(3, 2, 4))
```
For example, is the “visibility” always the same in both those tables?

Can you somehow do the COUNT before JOINing? cf SELECT COUNT(DISTINCT e.entity_id) ...

Ouch! EAV made worse. customer_address_entity_varchar/_int/_text
```
    WHERE  (t_d.entity_type_id = 3)
      AND  (t_d.entity_id IN (260))
      AND  (t_d.attribute_id IN ('41'))
      AND  (t_d.store_id = 0)
```
begs for
```
`INDEX(entity_type_id, store_id,  -- first, in either order
       entity_id, attribute_id)
```
Fix those; run for a while; refetch VARIABLES and GLOBAL STATUS but unwrapped, then I’ll look at the rest of them.
Login or Signup to reply.

Please signup or login to give your own answer.

Click here to cancel reply.

Magento MySQL hanging

Answers

the following 4 are per CONNECTION values and you are OVER the TOP