Phantom, please use the main mysql mailing list, not the internals
mailing list, as this is not an internals question... please remove
internals@ from your cc. I will respond to your question on the regular
mailing list (mysql@stripped)
On Wed, 2006-08-30 at 09:34 -0700, Phantom wrote:
> 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
> >
> >
