Jay,
Here you go - at least according to mysqldump
CREATE TABLE `table1` (
`start_time` char(19) default NULL,
`start_time_epoch` int(10) default '0',
`call_duration` char(9) default NULL,
`call_source` char(15) default NULL,
`call_source_q931sig_port` int(5) default '0',
`call_dest` char(15) default NULL,
`undef1` char(1) default NULL,
`call_source_custid` char(20) default NULL,
`called_party_on_dest` char(32) default NULL,
`called_party_from_src` char(32) default NULL,
`call_type` char(2) default NULL,
`undef2` tinyint(1) default NULL,
`disconnect_error_type` char(1) default '',
`call_error_num` int(4) default '0',
`call_error` char(24) default NULL,
`undef3` char(1) default NULL,
`undef4` char(1) default NULL,
`ani` char(32) default NULL,
`undef5` char(1) default NULL,
`undef6` char(1) default NULL,
`undef7` char(1) default NULL,
`cdr_seq_no` int(9) NOT NULL default '0',
`undef8` char(1) default NULL,
`callid` char(50) NOT NULL default '',
`call_hold_time` char(9) default NULL,
`call_source_regid` char(20) default '',
`call_source_uport` int(1) default '0',
`call_dest_regid` char(20) default '',
`call_dest_uport` int(1) default '0',
`isdn_cause_code` int(3) default '0',
`called_party_after_src_calling_plan` char(32) default NULL,
`call_error_dest_num` int(4) default '0',
`call_error_dest` char(25) default NULL,
`call_error_event_str` char(20) default '',
`new_ani` char(32) default NULL,
`call_duration_seconds` int(5) default '0',
`incoming_leg_callid` char(1) default NULL,
`protocol` enum('sip','h323') default NULL,
`cdr_type` enum('start1','start2','end1','end2','hunt') default NULL,
`hunting_attempts` int(1) default '0',
`caller_trunk_group` int(3) default NULL,
`call_pdd` int(5) default '0',
`h323_dest_ras_error` int(2) default '0',
`h323_dest_h225_error` int(2) default '0',
`sip_dest_respcode` int(3) default '0',
`dest_trunk_group` char(1) default NULL,
`call_duration_fractional` decimal(8,3) default '0.000',
`timezone` char(3) default '',
`msw_name` char(10) default NULL,
`called_party_after_transit_route` char(1) default NULL,
`called_party_on_dest_num_type` int(1) default '0',
`called_party_from_src_num_type` int(1) default '0',
`call_source_realm_name` char(3) default NULL,
`call_dest_realm_name` char(3) default NULL,
`call_dest_crname` char(50) default NULL,
`call_dest_custid` char(20) default NULL,
`call_zone_data` char(20) default NULL,
`calling_party_on_dest_num_type` int(1) default '0',
`calling_party_from_src_num_type` int(1) default '0',
`original_isdn_cause_code` int(1) default '0',
PRIMARY KEY (`callid`,`cdr_seq_no`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=100000000;
>>>-----Original Message-----
>>>From: Jay Pipes [mailto:[EMAIL PROTECTED]
>>>Sent: Wednesday, August 30, 2006 3:06 PM
>>>To: George Law
>>>Cc: [email protected]
>>>Subject: RE: Degrading write performance using MySQL 5.0.24
>>>
>>>Hi!
>>>
>>>Could you please post a SHOW CREATE TABLE table1 \G
>>>
>>>thanks!
>>>
>>>On Wed, 2006-08-30 at 14:32 -0400, George Law wrote:
>>>> data is all alphanumeric - any char fields are all fixed
>>>lengths, no
>>>> varchars
>>>>
>>>>
>>>> Name: table1
>>>> Engine: MyISAM
>>>> Version: 10
>>>> Row_format: Fixed
>>>> Rows: 330344
>>>> Avg_row_length: 624
>>>> Data_length: 206134656
>>>> Max_data_length: 2680059592703
>>>> Index_length: 18638848
>>>> Data_free: 0
>>>> Auto_increment: NULL
>>>> Create_time: 2006-08-30 09:50:23
>>>> Update_time: 2006-08-30 14:17:17
>>>> Check_time: NULL
>>>> Collation: latin1_swedish_ci
>>>> Checksum: NULL
>>>> Create_options: max_rows=100000000
>>>> Comment:
>>>>
>>>>
>>>>
>>>+-------------------------------------+----------------------
>>>-----------
>>>> -------------+------+-----+---------+-------+
>>>> | Field | Type
>>>> | Null | Key | Default | Extra |
>>>>
>>>+-------------------------------------+----------------------
>>>-----------
>>>> -------------+------+-----+---------+-------+
>>>> | start_time | char(19)
>>>> | YES | | NULL | |
>>>> | start_time_epoch | int(10)
>>>> | YES | | 0 | |
>>>> | call_duration | char(9)
>>>> | YES | | NULL | |
>>>> | call_source | char(15)
>>>> | YES | | NULL | |
>>>> | call_source_q931sig_port | int(5)
>>>> | YES | | 0 | |
>>>> | call_dest | char(15)
>>>> | YES | | NULL | |
>>>> | undef1 | char(1)
>>>> | YES | | NULL | |
>>>> | call_source_custid | char(20)
>>>> | YES | | NULL | |
>>>> | called_party_on_dest | char(32)
>>>> | YES | | NULL | |
>>>> | called_party_from_src | char(32)
>>>> | YES | | NULL | |
>>>> | call_type | char(2)
>>>> | YES | | NULL | |
>>>> | undef2 | tinyint(1)
>>>> | YES | | NULL | |
>>>> | disconnect_error_type | char(1)
>>>> | YES | | | |
>>>> | call_error_num | int(4)
>>>> | YES | | 0 | |
>>>> | call_error | char(24)
>>>> | YES | | NULL | |
>>>> | undef3 | char(1)
>>>> | YES | | NULL | |
>>>> | undef4 | char(1)
>>>> | YES | | NULL | |
>>>> | ani | char(32)
>>>> | YES | | NULL | |
>>>> | undef5 | char(1)
>>>> | YES | | NULL | |
>>>> | undef6 | char(1)
>>>> | YES | | NULL | |
>>>> | undef7 | char(1)
>>>> | YES | | NULL | |
>>>> | cdr_seq_no | int(9)
>>>> | NO | PRI | 0 | |
>>>> | undef8 | char(1)
>>>> | YES | | NULL | |
>>>> | callid | char(50)
>>>> | NO | PRI | | |
>>>> | call_hold_time | char(9)
>>>> | YES | | NULL | |
>>>> | call_source_regid | char(20)
>>>> | YES | | | |
>>>> | call_source_uport | int(1)
>>>> | YES | | 0 | |
>>>> | call_dest_regid | char(20)
>>>> | YES | | | |
>>>> | call_dest_uport | int(1)
>>>> | YES | | 0 | |
>>>> | isdn_cause_code | int(3)
>>>> | YES | | 0 | |
>>>> | called_party_after_src_calling_plan | char(32)
>>>> | YES | | NULL | |
>>>> | call_error_dest_num | int(4)
>>>> | YES | | 0 | |
>>>> | call_error_dest | char(25)
>>>> | YES | | NULL | |
>>>> | call_error_event_str | char(20)
>>>> | YES | | | |
>>>> | new_ani | char(32)
>>>> | YES | | NULL | |
>>>> | call_duration_seconds | int(5)
>>>> | YES | | 0 | |
>>>> | incoming_leg_callid | char(1)
>>>> | YES | | NULL | |
>>>> | protocol | enum('sip','h323')
>>>> | YES | | NULL | |
>>>> | cdr_type |
>>>> enum('start1','start2','end1','end2','hunt') | YES |
>>>| NULL |
>>>> |
>>>> | hunting_attempts | int(1)
>>>> | YES | | 0 | |
>>>> | caller_trunk_group | int(3)
>>>> | YES | | NULL | |
>>>> | call_pdd | int(5)
>>>> | YES | | 0 | |
>>>> | h323_dest_ras_error | int(2)
>>>> | YES | | 0 | |
>>>> | h323_dest_h225_error | int(2)
>>>> | YES | | 0 | |
>>>> | sip_dest_respcode | int(3)
>>>> | YES | | 0 | |
>>>> | dest_trunk_group | char(1)
>>>> | YES | | NULL | |
>>>> | call_duration_fractional | decimal(8,3)
>>>> | YES | | 0.000 | |
>>>> | timezone | char(3)
>>>> | YES | | | |
>>>> | msw_name | char(10)
>>>> | YES | | NULL | |
>>>> | called_party_after_transit_route | char(1)
>>>> | YES | | NULL | |
>>>> | called_party_on_dest_num_type | int(1)
>>>> | YES | | 0 | |
>>>> | called_party_from_src_num_type | int(1)
>>>> | YES | | 0 | |
>>>> | call_source_realm_name | char(3)
>>>> | YES | | NULL | |
>>>> | call_dest_realm_name | char(3)
>>>> | YES | | NULL | |
>>>> | call_dest_crname | char(50)
>>>> | YES | | NULL | |
>>>> | call_dest_custid | char(20)
>>>> | YES | | NULL | |
>>>> | call_zone_data | char(20)
>>>> | YES | | NULL | |
>>>> | calling_party_on_dest_num_type | int(1)
>>>> | YES | | 0 | |
>>>> | calling_party_from_src_num_type | int(1)
>>>> | YES | | 0 | |
>>>> | original_isdn_cause_code | int(1)
>>>> | YES | | 0 | |
>>>>
>>>+-------------------------------------+----------------------
>>>-----------
>>>> -------------+------+-----+---------+-------+
>>>>
>>>>
>>>> >>>-----Original Message-----
>>>> >>>From: Jay Pipes [mailto:[EMAIL PROTECTED]
>>>> >>>Sent: Wednesday, August 30, 2006 1:44 PM
>>>> >>>To: George Law
>>>> >>>Cc: [email protected]
>>>> >>>Subject: RE: Degrading write performance using MySQL 5.0.24
>>>> >>>
>>>> >>>What type of data are you inserting? What storage
>>>engine are you
>>>> >>>inserting into? What is the average row size?
>>>> >>>
>>>> >>>On Wed, 2006-08-30 at 12:32 -0400, George Law wrote:
>>>> >>>> I see the same type of slow downs using 5.0.18
>>>> >>>>
>>>> >>>> I am using "load data in file" to load CSV files.
>>>> >>>>
>>>> >>>> with clean tables, I see fairly quick inserts (ie "instant")
>>>> >>>>
>>>> >>>> 2006-08-30 12:07:15 : begin import into table1
>>>> >>>> 2006-08-30 12:07:15: end import into table1 records (10962)
>>>> >>>>
>>>> >>>>
>>>> >>>> From earlier this morning, before I rotated my tables:
>>>> >>>> 2006-08-30 09:02:01 : begin import into table1
>>>> >>>> 2006-08-30 09:05:07: end import into table1 records (10082)
>>>> >>>>
>>>> >>>>
>>>> >>>> I've posted about this before - one person will say that
>>>> >>>its my indexes
>>>> >>>> getting rebuilt, others have said its disk io. I can never
>>>> >>>get a solid
>>>> >>>> answer.
>>>> >>>>
>>>> >>>> If I disable the keys, do the import, then re-enable the
>>>> >>>keys, it takes
>>>> >>>> just as long,
>>>> >>>> if not longer.
>>>> >>>>
>>>> >>>>
>>>> >>>> I have just about given up on finding a solution for
>>>this and just
>>>> >>>> rotate my tables out
>>>> >>>> regularly once the imports take over 5 minutes to process
>>>> >>>roughly 10,000
>>>> >>>> records
>>>> >>>>
>>>> >>>> --
>>>> >>>> George
>>>> >>>>
>>>> >>>>
>>>> >>>>
>>>> >>>>
>>>> >>>>
>>>> >>>> >>>-----Original Message-----
>>>> >>>> >>>From: Jay Pipes [mailto:[EMAIL PROTECTED]
>>>> >>>> >>>Sent: Wednesday, August 30, 2006 12:06 PM
>>>> >>>> >>>To: Phantom
>>>> >>>> >>>Cc: [email protected]
>>>> >>>> >>>Subject: Re: Degrading write performance using MySQL 5.0.24
>>>> >>>> >>>
>>>> >>>> >>>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]
>>>> >>>> >>>
>>>> >>>> >>>
>>>> >>>>
>>>> >>>
>>>> >>>
>>>>
>>>
>>>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
