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: mysql@lists.mysql.com
>>>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: mysql@lists.mysql.com
>>>> >>>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]
