MySQL Forums
Forum List  »  InnoDB

INSERT/UPDATE within transactions (autocommit=0)
Posted by: Alexander Golubowitsch
Date: July 26, 2010 10:15AM

Hello community,

I hope someone will be able to help/advise.

We are running MySQL 5.0.60 on a Linux machine.
Programming language is PHP.
QPS is usually between 800 and 1.4k.
InnoDB transactions are set to REPEATABLE READ.

I have set-up an innotop instance but as yet been unable to track down the problem using it.

Situation:

We are using a DB object abstraction framework which normalizes tables completely.
Data for e.g. a class "product" are stored in an ID table "product", individual properties are stored in tables รก la "product_prop_name" and so on.
- Any prop-table's "object" column references the ID table's "id" column.
- ID tables have an ON UPDATE/DELETE CASCADE rule set, but that never fires in the current implmentation 'cause we never delete from there


The platform is under huge frontend load, where most of the QPS stem from.
The problems are encountered in backend functionality, where customer service manages invoices, order processing etc. - the load caused by SELECTS on individual tables is rather low there. Also, the structures used in backend workflow are hardly ever access from the frontend.

autocommit is per default usually on, but we turn it off explicitly in places to start a longer transaction. E.g.:

When we register an order by a new customer, a transaction wraps the entire process of storing the customer data to the database. Quite a lot of SELECTs take place inside that transaction as well.


PROBLEM:

On apparently random occasion INSERTs to the ID tables or INSERT/UPDATEs on the prop-tables take a very long time to complete, easily up to a minute. This even happens in really "small" transactions that hardly do anything more than a few SELECTs and then a single INSERT into the ID table.

What could be the reason?
Why would such an INSERT take that long while in parallel there's hardly any reads from the respective tables?
A couple of those INSERTs plus the final COMMIT that also may take a few dozen seconds and people are waiting for two minutes which is unbearable.

Thanks a lot for any help!


EXAMPLE CREATES:
-------------------------------------------------------

CREATE TABLE `product` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`created` int(10) unsigned NOT NULL default '0',
`deleted` int(10) unsigned default NULL,
PRIMARY KEY (`id`,`created`),
KEY `deleted` (`deleted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

CREATE TABLE `product_prop_name` (
`id` bigint(20) unsigned NOT NULL auto_increment,
`created` int(10) unsigned NOT NULL,
`deleted` int(10) unsigned default NULL,
`object` bigint(20) unsigned NOT NULL,
`value` varchar(255) collate utf8_bin default NULL,
PRIMARY KEY (`id`,`created`),
KEY `created` (`created`),
KEY `deleted` (`deleted`),
KEY `object` (`object`),
CONSTRAINT `product_prop_name_ibfk_1` FOREIGN KEY (`object`) REFERENCES `product` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin


INNODB
----------------

innodb additional mem pool size 25,165,824
innodb autoextend increment 8
innodb buffer pool awe mem mb 0
innodb buffer pool size 3,221,225,472
innodb checksums ON
innodb commit concurrency 0
innodb concurrency tickets 500
innodb data file path ibdata1:10M:autoextend
innodb data home dir
innodb adaptive hash index ON
innodb doublewrite ON
innodb fast shutdown 1
innodb file io threads 4
innodb file per table ON
innodb flush log at trx commit 2
innodb flush method
innodb force recovery 0
innodb lock wait timeout 50
innodb locks unsafe for binlog OFF
innodb log arch dir
innodb log archive OFF
innodb log buffer size 16,777,216
innodb log file size 268,435,456
innodb log files in group 2
innodb log group home dir ./
innodb max dirty pages pct 90
innodb max purge lag 0
innodb mirrored log groups 1
innodb open files 5,000
innodb rollback on timeout OFF
innodb support xa OFF
innodb sync spin loops 20
innodb table locks ON
innodb thread concurrency 0
innodb thread sleep delay 10,000

Options: ReplyQuote


Subject
Views
Written By
Posted
INSERT/UPDATE within transactions (autocommit=0)
6325
July 26, 2010 10:15AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.