List:Internals« Previous MessageNext Message »
From:Rick James Date:August 30 2006 10:13pm
Subject:RE: Degrading write performance using MySQL 5.0.24
View as plain text  
Your Indexes on ITEMS are HUGE!  You are probably pounding on the disk,
doing block splits, etc.

Note (in InnoDB):  the PK is included in all secondary keys.  That's 512
bytes extra for each secondary key.

Suggest you add a third table:
(
   ID INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
   USER_KEY VARBINARY(255) NOT NULL,
   SUBKEY VARBINARY(255) NOT NULL,
)

And then use ID instead of USER_KEY + SUBKEY as the PK for the other two.
This will change 512 to 4.  Much less stuff to shovel around, even though
you will need some JOINs.

And set appropriate referential integrety constraints.

> -----Original Message-----
> From: Phantom [mailto:ghostwhoowalks@stripped] 
> Sent: Wednesday, August 30, 2006 9:34 AM
> To: jay@stripped; internals@stripped
> Subject: Re: Degrading write performance using MySQL 5.0.24
> 
> Here is the schema for the table are generate Sequence 
> Numbers needed per
> item and the table that actually stores the data :
> 
> CREATE TABLE SEQUENCE_NUMBER
> (
>     USER_KEY VARBINARY(255) NOT NULL,
>     SUBKEY VARBINARY(255)NOT NULL,
>     SEQUENCE_NBR INT NOT NULL,
>     GLOBAL_GENERATION INT NOT NULL,
>     KEY_GENERATION INT NOT NULL,
>     LAST_ACCESS_TIME BIGINT NOT NULL,
>     PRIMARY KEY (USER_KEY, SUBKEY)
> )TYPE=InnoDB;
> 
> CREATE TABLE ITEMS
> (
>     USER_KEY VARBINARY(255) NOT NULL,
>     SUBKEY VARBINARY(255) NOT NULL,
>     VERSION_STAMP_HASH VARBINARY(255) NOT NULL,
>     VERSION_STAMP LONGBLOB NOT NULL,
>     USER_DATA LONGBLOB NOT NULL,
>     LAST_ACCESS_TIME BIGINT NOT NULL,
>     TYPE VARCHAR(255) NOT NULL,
>     DATA_HASH VARBINARY(255) NOT NULL,
>     HINTED_STORAGE_ID VARBINARY(255),
>     USER_KEY_HASH VARBINARY(255) NOT NULL,
>     ROWID BIGINT NOT NULL AUTO_INCREMENT,
>     PRIMARY KEY (USER_KEY, SUBKEY, VERSION_STAMP_HASH),
>     INDEX (ROWID),
>     INDEX (HINTED_STORAGE_ID),
>     INDEX (USER_KEY_HASH),
>     INDEX (LAST_ACCESS_TIME,TYPE),
>     INDEX (DATA_HASH)
> ) TYPE=InnoDB;
> 
> Sample queries are :
> 
> "SELECT GLOBAL_GENERATION, KEY_GENERATION, SEQUENCE_NBR, 
> LAST_ACCESS_TIME
> FROM SEQUENCE_NUMBER Where USER_KEY = ? AND SUBKEY = ?";
> 
> "UPDATE SEQUENCE_NUMBER SET KEY_GENERATION = ?, SEQUENCE_NBR = ?,
> GLOBAL_GENERATION = ?, LAST_ACCESS_TIME = ? WHERE USER_KEY = 
> ? AND SUBKEY =
> ? AND LAST_ACCESS_TIME = ?";
> 
> "INSERT INTO SEQUENCE_NUMBER VALUES (?, ?, ?, ?, ?, ?)";
> 
> "REPLACE INTO SEQUENCE_NUMBER VALUES (?, ?, ?, ?, ?, ?)";
> 
> "SELECT * FROM ITEMS Where USER_KEY = ? AND SUBKEY = ?";
> 
> "SELECT * FROM ITEMS WHERE USER_KEY = ? AND SUBKEY = ? AND
> VERSION_STAMP_HASH = ?";
> 
> "INSERT INTO ITEMS (USER_KEY, SUBKEY, VERSION_STAMP_HASH, 
> VERSION_STAMP,
> USER_DATA, LAST_ACCESS_TIME, TYPE, DATA_HASH, HINTED_STORAGE_ID,
> USER_KEY_HASH) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
> 
> "DELETE FROM ITEMS WHERE USER_KEY = ? AND SUBKEY = ? AND 
> VERSION_STAMP_HASH
> = ?";
> 
> These are the main queries that we execute.
> 
> Thanks
> A
> 
> 
> On 8/30/06, Jay Pipes <jay@stripped> wrote:
> >
> > On Wed, 2006-08-30 at 08:31 -0700, Phantom wrote:
> > > We have an application that stores versioned data in 
> MySQL. Everytime a
> > > piece of data is retrieved and written to, it is stored 
> in the database
> > with
> > > a new version and all old versions are subsequently 
> deleted. We have a
> > > request rate of 2 million reads per hour and 1.25 million 
> per hour. What
> > I
> > > am seeing is that as the DB grows the performance on the 
> writes degrades
> > > substantially. When I start with a fresh database writes 
> are at 70ms.
> > But
> > > once the database reaches around 10GB the writes are at 
> 200 ms. The DB
> > can
> > > grow upto 35GB. I have tried almost performance related 
> tuning described
> > in
> > > the MySQL documentation page.
> > >
> > > What do I need to look at to start addressing this 
> problem or this is
> > how
> > > the performance is going to be ?
> >
> > Before getting into server parameters, is it possible to 
> take a look at
> > your schema and a sample of your SQL queries from the 
> application?  That
> > would help immensely.  70ms for an UPDATE seems very 
> slow... and 200ms
> > is very slow.
> >
> > Cheers,
> > --
> > Jay Pipes
> > Community Relations Manager, North America, MySQL, Inc.
> > jay@stripped :: +1 614 406 1267
> >
> >
> 

Thread
Degrading write performance using MySQL 5.0.24Phantom30 Aug