Thesql update query almost 5 minutes slower in 5.6 vs 5.5

EDIT 6:

I always thought an ALTER table recreated everything. When I add the optimizes shown in edit 5; it is always fast. (no more 4 minutes waits). The odd part is; if I am doing a lot with my system (VM open..etc) sometimes the update query is fast WITHOUT the optimize. It seems almost to me like it is doing something asynchronously and once in awhile the update is fast WITHOUT the optimize. It seems that it is always fast WITH the optimize statements.

I think this has something to do with it:
https://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

EDIT 5:

CREATE TABLE `phppos_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deleted` int(1) NOT NULL DEFAULT '0',
  `parent_id` int (11) NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  KEY `deleted` (`deleted`),
  KEY `parent_id_name_index`(`parent_id`,`name`),
  CONSTRAINT `phppos_categories_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `phppos_categories` (`id`),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO phppos_categories (name) 
SELECT DISTINCT category FROM phppos_items 
UNION 
SELECT DISTINCT category FROM phppos_item_kits;

ALTER TABLE phppos_items
ADD COLUMN category_id int(11) NULL AFTER `name`,
ADD CONSTRAINT `phppos_items_ibfk_3` FOREIGN KEY (`category_id`) REFERENCES `phppos_categories` (`id`);

ALTER TABLE phppos_item_kits
ADD COLUMN category_id int(11) NULL AFTER `name`,
ADD CONSTRAINT `phppos_item_kits_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `phppos_categories` (`id`);

-- Force re-index? (only really need items + item kits; but categories because why the hell not?)
optimize table phppos_items;
optimize table phppos_item_kits;
optimize table phppos_categories;

UPDATE phppos_items as i INNER JOIN phppos_categories as c ON c.name = i.category SET category_id = c.id;
UPDATE phppos_item_kits as i INNER JOIN phppos_categories as c ON c.name = i.category SET category_id = c.id; 

ALTER TABLE phppos_items
DROP COLUMN category;

ALTER TABLE phppos_item_kits
DROP COLUMN category;

EDIT 4:

Now this doesn't make much sense…but running the slow query by itself in phpmyadmin reduces time from 4 minutes + to 18 seconds. If I try to run all the commands at once it is super slow. Same result from CLI. (Faster if not on one connection);

https://www.dropbox.com/s/xf6zi01wspkrjeo/mysql_5.6_slow.mov?dl=0

EDIT 3:

mysql 5.6 my.cnf:

[mysqladmin]
user=root

[mysqld]
basedir=/Applications/phppos/mysql
datadir=/Applications/phppos/mysql/data
port=8889
socket=/Applications/phppos/mysql/tmp/mysql.sock
tmpdir=/Applications/phppos/mysql/tmp
max_allowed_packet=16M
bind-address=127.0.0.1

character-set-server=UTF8
collation-server=utf8_general_ci
[mysqld_safe]
mysqld=mysqld.bin

[client]
port=8889
socket=/Applications/phppos/mysql/tmp/mysql.sock

default-character-set=UTF8
[manager]
port=8889
socket=/Applications/phppos/mysql/tmp/mysql.sock
pid-file=/Applications/phppos/mysql/tmp/manager.pid
default-mysqld-path=/Applications/phppos/mysql/bin/mysqld.bin

mysql 5.5 my.cnf (FOUND)

# The following options will be passed to all MySQL clients
[client]
socket      = /Applications/MAMP/tmp/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
socket      = /Applications/MAMP/tmp/mysql/mysql.sock
key_buffer = 16M
max_allowed_packet = 16M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
sql_mode="STRICT_ALL_TABLES"

bind-address = 127.0.0.1


[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

# DONT REMOVE: MAMP PRO my.cnf template compatibility version: 5

EDIT 2: Here is the explain of slow update query:

EXPLAIN UPDATE phppos_items as i INNER JOIN phppos_categories as c ON c.name = i.category SET category_id = c.id
--------------

+----+-------------+-------+-------+---------------+----------------------+---------+------+------+-----------------------------------------------------------------+
| id | select_type | table | type  | possible_keys | key                  | key_len | ref  | rows | Extra                                                           |
+----+-------------+-------+-------+---------------+----------------------+---------+------+------+-----------------------------------------------------------------+
|  1 | SIMPLE      | i     | ALL   | category      | NULL                 | NULL    | NULL |    1 | NULL                                                            |
|  1 | SIMPLE      | c     | index | NULL          | parent_id_name_index | 772     | NULL | 3591 | Using where; Using index; Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------+---------------+----------------------+---------+------+------+-----------------------------------------------------------------+
2 rows in set (0.00 sec)

--------------

EDIT:

The following are the only additions in 5.5 compared to mysql 5.6 my.cnf. I tried adding this to 5.6 but it wouldn't start after adding these.

key_buffer = 16M
max_allowed_packet = 16M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M

In my.cnf of 5.6 here there is not much:

[mysqladmin]
user=root

[mysqld]
basedir=/Applications/phppos/mysql
datadir=/Applications/phppos/mysql/data
port=8889
socket=/Applications/phppos/mysql/tmp/mysql.sock
tmpdir=/Applications/phppos/mysql/tmp
max_allowed_packet=16M
bind-address=127.0.0.1

character-set-server=UTF8
collation-server=utf8_general_ci
[mysqld_safe]
mysqld=mysqld.bin

[client]
port=8889
socket=/Applications/phppos/mysql/tmp/mysql.sock

default-character-set=UTF8
[manager]
port=8889
socket=/Applications/phppos/mysql/tmp/mysql.sock
pid-file=/Applications/phppos/mysql/tmp/manager.pid
default-mysqld-path=/Applications/phppos/mysql/bin/mysqld.bin

I have one query that takes almost 5 minutes in mysql 5.6 and takes 10 seconds in mysql 5.5. I have tried this on windows + mac. (This is the mac result). Is there a setting I am missing that is causing such a huge difference? Both queries run on the same exact data + schema.

mysql 5.5 info:

cmuench-air:~ cmuench$ mysql --version
mysql  Ver 14.14 Distrib 5.5.42, for osx10.6 (i386) using  EditLine wrapper

mysql 5.6 info:

cmuench-air:~ cmuench$ /Applications/phppos/mysql/bin/mysql --version
/Applications/phppos/mysql/bin/mysql.bin  Ver 14.14 Distrib 5.6.20, for osx10.6 (x86_64) using  EditLine wrapper

Tables involved:

mysql> show create table phppos_categories;
+-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table             | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
+-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_categories | CREATE TABLE `phppos_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deleted` int(1) NOT NULL DEFAULT '0',
  `parent_id` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `deleted` (`deleted`),
  KEY `parent_id_name_index` (`parent_id`,`name`),
  CONSTRAINT `phppos_categories_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `phppos_categories` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4096 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)


mysql> show create table phppos_items;
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_items | CREATE TABLE `phppos_items` (
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  `supplier_id` int(11) DEFAULT NULL,
  `item_number` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `product_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `size` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `tax_included` int(1) NOT NULL DEFAULT '0',
  `cost_price` decimal(23,10) NOT NULL,
  `unit_price` decimal(23,10) NOT NULL,
  `promo_price` decimal(23,10) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `reorder_level` decimal(23,10) DEFAULT NULL,
  `item_id` int(10) NOT NULL AUTO_INCREMENT,
  `allow_alt_description` tinyint(1) NOT NULL,
  `is_serialized` tinyint(1) NOT NULL,
  `image_id` int(10) DEFAULT NULL,
  `override_default_tax` int(1) NOT NULL DEFAULT '0',
  `is_service` int(1) NOT NULL DEFAULT '0',
  `commission_percent` decimal(23,10) DEFAULT '0.0000000000',
  `commission_fixed` decimal(23,10) DEFAULT '0.0000000000',
  `deleted` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`item_id`),
  UNIQUE KEY `item_number` (`item_number`),
  UNIQUE KEY `product_id` (`product_id`),
  KEY `phppos_items_ibfk_1` (`supplier_id`),
  KEY `name` (`name`),
  KEY `deleted` (`deleted`),
  KEY `phppos_items_ibfk_2` (`image_id`),
  KEY `phppos_items_ibfk_3` (`category_id`),
  CONSTRAINT `phppos_items_ibfk_1` FOREIGN KEY (`supplier_id`) REFERENCES `phppos_suppliers` (`person_id`),
  CONSTRAINT `phppos_items_ibfk_2` FOREIGN KEY (`image_id`) REFERENCES `phppos_app_files` (`file_id`),
  CONSTRAINT `phppos_items_ibfk_3` FOREIGN KEY (`category_id`) REFERENCES `phppos_categories` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=133095 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

mysql> show create table phppos_item_kits;
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table            | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| phppos_item_kits | CREATE TABLE `phppos_item_kits` (
  `item_kit_id` int(11) NOT NULL AUTO_INCREMENT,
  `item_kit_number` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `product_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `category_id` int(11) DEFAULT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `tax_included` int(1) NOT NULL DEFAULT '0',
  `unit_price` decimal(23,10) DEFAULT NULL,
  `cost_price` decimal(23,10) DEFAULT NULL,
  `override_default_tax` int(1) NOT NULL DEFAULT '0',
  `commission_percent` decimal(23,10) DEFAULT '0.0000000000',
  `commission_fixed` decimal(23,10) DEFAULT '0.0000000000',
  `deleted` int(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`item_kit_id`),
  UNIQUE KEY `item_kit_number` (`item_kit_number`),
  UNIQUE KEY `product_id` (`product_id`),
  KEY `name` (`name`),
  KEY `deleted` (`deleted`),
  KEY `phppos_item_kits_ibfk_1` (`category_id`),
  CONSTRAINT `phppos_item_kits_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `phppos_categories` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Queries (5th one VERY slow in mysql 5.6)

   CREATE TABLE `phppos_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `deleted` int(1) NOT NULL DEFAULT '0',
  `parent_id` int (11) NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  KEY `deleted` (`deleted`),
  KEY `parent_id_name_index`(`parent_id`,`name`),
  CONSTRAINT `phppos_categories_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `phppos_categories` (`id`),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO phppos_categories (name) 
SELECT DISTINCT category FROM phppos_items 
UNION 
SELECT DISTINCT category FROM phppos_item_kits;

ALTER TABLE phppos_items
    ADD COLUMN category_id int(11) NULL AFTER `name`,
    ADD CONSTRAINT `phppos_items_ibfk_3` FOREIGN KEY (`category_id`) REFERENCES `phppos_categories` (`id`);

ALTER TABLE phppos_item_kits
     ADD COLUMN category_id int(11) NULL AFTER `name`,
     ADD CONSTRAINT `phppos_item_kits_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `phppos_categories` (`id`);

UPDATE phppos_items as i INNER JOIN phppos_categories as c ON c.name = i.category SET category_id = c.id;
UPDATE phppos_item_kits as i INNER JOIN phppos_categories as c ON c.name = i.category SET category_id = c.id; 

ALTER TABLE phppos_items
    DROP COLUMN category;

ALTER TABLE phppos_item_kits
    DROP COLUMN category;

mysql 5.6 results:

Query OK, 0 rows affected (0.03 sec)

Query OK, 3591 rows affected (0.08 sec)
Records: 3591  Duplicates: 0  Warnings: 0

Query OK, 132924 rows affected (5.82 sec)
Records: 132924  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 132924 rows affected (4 min 32.90 sec)
Rows matched: 132924  Changed: 132924  Warnings: 0

Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Query OK, 0 rows affected (3.97 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.5 results:

Query OK, 0 rows affected (0.02 sec)

Query OK, 3591 rows affected (0.11 sec)
Records: 3591  Duplicates: 0  Warnings: 0

Query OK, 132924 rows affected (8.37 sec)
Records: 132924  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 132924 rows affected (10.22 sec)
Rows matched: 132924  Changed: 132924  Warnings: 0

Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Query OK, 132924 rows affected (8.14 sec)
Records: 132924  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

NOTE: BOTH run on the same exact schema + database data.

Is there a setting or configuration that would explain this almost 5 minute speed difference between mysql 5.5 and 5.6?

Best Answer

OPTIMIZE TABLE is essentially never needed for InnoDB.

New with 5.6: ALTER TABLE uses ALGORITHM=INPLACE for many actions (add/drop column/index, etc.). I would expect 5.6 to be faster.

What is that list of commands trying to do? Something to do with Normalization? Seems like it could be done in a couple of queries, without needing the extra table.

innodb_buffer_pool_size should be set to about 70% of available RAM. That will improve performance on any version.

Don't index flags (deleted), it will slow down INSERTs, without speeding up SELECTs.

Don't use 4-byte integers (INT) for flags; use the 1-byte TINYINT.

No currency in the world needs 10 decimal places: price decimal(23,10). And I doubt if room for 10 trillion 'dollars' is needed.

What's the value of autocommit during the UPDATEs?

Best Answer

Related Solutions

Mysql – Unexplained InnoDB timeouts

Mysql – Are two indexes needed

Related Question