Well, clearly you've got a denormalized schema here, but I'll assume you
will be normalizing this after the import? I hope so. Having a PK of
CHAR(50) / INT will lead to poorer performance than a simply
incrementing integer key. Additionally, it seems all the fields are
NULLable, which wastes even more space. Plus, not quite sure what all
the undef1, undef2, undef3 ... etc fields are? Also, storing timestamps
in a CHAR(19) is another great way to kill performance.
As far as just simply *getting* this information into the database as
quickly as possible, you might try dumping it into a temporary table,
then normalizing it and INSERT ... SELECT * FROM temp_table to batch the
insert as one move into the main table...
Jay
On Wed, 2006-08-30 at 15:11 -0400, George Law wrote:
> 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: mysql@lists.mysql.com
> >>>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: 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]
