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;
You have a serious problem with your data types here... Using a VARBINARY(255), VARBINARY(255) as your primary key is not a good idea. What is the purpose of the sequence table? Why not simply use an auto_incrementing integer? Additionally, why are you using a BIGINT for an access time? Why not just use the (much smaller) TIMESTAMP datatype? > 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; Same comment from above applies here, but it's even worse here because *every one of the five secondary indexes* on the table will have a 512 byte primary key value appended to *each index record*, resulting in horrible performance problems. Again, what is the purpose of the sequence numbers as the primary key? > 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 <[EMAIL PROTECTED]> 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. > > [EMAIL PROTECTED] :: +1 614 406 1267 > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
