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]
