Home > Archive > MySQL ODBC Connector > April 2006 > stunningly slow query









You are viewing an archived Text-only version of the thread. To view this thread in it's original format and/or if you want to reply to this thread please [click here]

 

Author stunningly slow query
Chris Kantarjiev

2006-03-30, 8:26 pm

We're having some serious problems with concurrent queries.

This is a dual-processor amd64 machine with 16GB RAM, running NetBSD
and MySQL 4.0.25. key_buffer_size is 3GB.

When I have a long running query going, otherwise short queries take
a very very long time to execute. For example, I have

insert ignore into trimble.old_crumb select * from trimble.crumba_rolled

which is appending a lot of 'live' data to an 'archive' table.

Meanwhile, I'm trying to execute

INSERT IGNORE INTO link_area
(link_ID, dir_Travel, area_ID)
VALUES
(20202178, 'F', 21014195);

This latter query is taking between 6 and 45 *seconds* to run. Yow!

mysql> explain link_area;
+------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| link_ID | bigint(20) | | PRI | 0 | |
| dir_Travel | char(1) | | PRI | | |
| area_ID | int(11) | | MUL | 0 | |
+------------+------------+------+-----+---------+-------+

help?



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Mike Wexler

2006-03-30, 8:26 pm

Can you post the output of SHOW FULL PROCESSLIST during the time when
both sets of queries are running?
Also what storage engine are you using for your tables?


Chris Kantarjiev wrote:
> We're having some serious problems with concurrent queries.
>
> This is a dual-processor amd64 machine with 16GB RAM, running NetBSD
> and MySQL 4.0.25. key_buffer_size is 3GB.
>
> When I have a long running query going, otherwise short queries take
> a very very long time to execute. For example, I have
>
> insert ignore into trimble.old_crumb select * from trimble.crumba_rolled
>
> which is appending a lot of 'live' data to an 'archive' table.
>
> Meanwhile, I'm trying to execute
>
> INSERT IGNORE INTO link_area
> (link_ID, dir_Travel, area_ID)
> VALUES
> (20202178, 'F', 21014195);
>
> This latter query is taking between 6 and 45 *seconds* to run. Yow!
>
> mysql> explain link_area;
> +------------+------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +------------+------------+------+-----+---------+-------+
> | link_ID | bigint(20) | | PRI | 0 | |
> | dir_Travel | char(1) | | PRI | | |
> | area_ID | int(11) | | MUL | 0 | |
> +------------+------------+------+-----+---------+-------+
>
> help?
>
>
>
>



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Chris Kantarjiev

2006-03-30, 8:26 pm

>
> Can you post the output of SHOW FULL PROCESSLIST during the time when
> both sets of queries are running?


mysql> show full processlist;
+-----+------+--------------------------+-----------+---------+------+--------------+----------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+------+--------------------------+-----------+---------+------+--------------+----------------------------------------------------------------------------------------------------------------------+
| 201 | len | dick.landsonar.com:15405 | landsonar | Query | 4033 | Sending data | insert ignore into trimble.old_crumb select * from trimble.crumba_rolled |
| 209 | len | dick.landsonar.com:34684 | landsonar | Query | 2 | update | INSERT IGNORE INTO link_area
(link_ID, dir_Travel, area_ID)
VALUES
(20202282, 'T', 21014142) |
| 216 | len | localhost | NULL | Query | 0 | NULL | show full processlist |
+-----+------+--------------------------+-----------+---------+------+--------------+----------------------------------------------------------------------------------------------------------------------+

> Also what storage engine are you using for your tables?

MyISAM. We found InnoDb to be considerably slower for selects for our data,
though it's been a year since I did that experiment.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Mike Wexler

2006-03-30, 8:26 pm

-------------- 05020907030803030104
0709
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Chris Kantarjiev wrote:
That throws out my first theory about table locks.
What do vmstat and top say? Is it CPU bound? I/O bound?

Also you might want to do a "show status" before and after. See whether
the ratio of Key_reads/Key_read_requests or
Key_writes/Key_write_requests is high. This could be an indication that
your key_buffer_size is to small.
You might want to try putting the result of both "show status" calls
side by side to see if some unexpected resource is being used.
[color=darkred]
>
>
> mysql> show full processlist;
> +-----+------+--------------------------+-----------+---------+------+--------------+----------------------------------------------------------------------------------------------------------------------+
> | Id | User | Host | db | Command | Time | State | Info |
> +-----+------+--------------------------+-----------+---------+------+--------------+----------------------------------------------------------------------------------------------------------------------+
> | 201 | len | dick.landsonar.com:15405 | landsonar | Query | 4033 | Sending data | insert ignore into trimble.old_crumb select * from trimble.crumba_rolled |
> | 209 | len | dick.landsonar.com:34684 | landsonar | Query | 2 | update | INSERT IGNORE INTO link_area
> (link_ID, dir_Travel, area_ID)
> VALUES
> (20202282, 'T', 21014142) |
> | 216 | len | localhost | NULL | Query | 0 | NULL | show full processlist |
> +-----+------+--------------------------+-----------+---------+------+--------------+----------------------------------------------------------------------------------------------------------------------+
>
>
>
> MyISAM. We found InnoDb to be considerably slower for selects for our data,
> though it's been a year since I did that experiment.
>
>


-------------- 05020907030803030104
0709--
Chris Kantarjiev

2006-03-30, 8:26 pm

>
> That throws out my first theory about table locks.

That's what I thought, too.

> What do vmstat and top say? Is it CPU bound? I/O bound?

Certainly not CPU bound. Maybe I/O bound, not conclusive. My current
theory is that there is some thrashing on key buffer blocks.

>
> Also you might want to do a "show status" before and after.

Before and after the short query?

| Handler_commit | 0 |
| Handler_delete | 20075144 |
| Handler_read_first | 18 |
| Handler_read_key | 432092430 |
| Handler_read_next | 510230999 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 148798998 |
| Handler_read_rnd_nex
t | 1676270985 |
| Handler_rollback | 0 |
| Handler_update | 122968312 |
| Handler_write | 286299142 |
....
| Key_blocks_used | 2887383 |
| Key_read_requests | 2929797981 |
| Key_reads | 8856159 |
| Key_write_requests | 305442125 |
| Key_writes | 94187694 |
| Uptime | 268069 |

| Handler_commit | 0 |
| Handler_delete | 20075144 |
| Handler_read_first | 18 |
| Handler_read_key | 432092430 |
| Handler_read_next | 510230999 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 148798998 |
| Handler_read_rnd_nex
t | 1676280460 |
| Handler_rollback | 0 |
| Handler_update | 122968312 |
| Handler_write | 286308623 |
....
| Key_blocks_used | 2887383 |
| Key_read_requests | 2929979657 |
| Key_reads | 8859748 |
| Key_write_requests | 305476828 |
| Key_writes | 94187704 |
| Uptime | 268102 |

OK, so in 33 seconds, it did 181676 read requests and 3589 reads, and 34703
write requests and 10 actual writes. 108 reads/sec, less than 1 write/sec.

systat vmstat tells me that the overall data rate off the index disk
is relatively low, but the disk is pretty busy, so there's a lot of seeking.
The data disk is barely being touched.

> You might want to try putting the result of both "show status" calls
> side by side to see if some unexpected resource is being used.

Nothing jumps out at me. Handler_read_rnd_nex
t is increasing at 287/sec,
which isn't small...

The key_buffer is 3GB or 4GB on this system. I'm contemplating an upgrade
to 4.1 so I can have multiples.

Thanks for looking at this...


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Mike Wexler

2006-03-30, 8:26 pm

-------------- 00000908060805020908
0000
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

It doesn't really answer your question, but have you tried "INSERT
DELAYED" as a work around?

Also the "updated" status is strange, because that generally indicates
that its looking for the record to be updated, but since the record is
new, there is no record to be updated. Could it be checking for
duplicates? Not that it should be this slow, but you might try ALTER
TABLE xxx DISABLE KEYS and see how that effect performance. At least it
will tell you whether the problem is in updating the keys, or something
else.

Chris Kantarjiev wrote:
>
> That's what I thought, too.
>
>
>
> Certainly not CPU bound. Maybe I/O bound, not conclusive. My current
> theory is that there is some thrashing on key buffer blocks.
>
>
>
> Before and after the short query?
>
> | Handler_commit | 0 |
> | Handler_delete | 20075144 |
> | Handler_read_first | 18 |
> | Handler_read_key | 432092430 |
> | Handler_read_next | 510230999 |
> | Handler_read_prev | 0 |
> | Handler_read_rnd | 148798998 |
> | Handler_read_rnd_nex
t | 1676270985 |
> | Handler_rollback | 0 |
> | Handler_update | 122968312 |
> | Handler_write | 286299142 |
> ...
> | Key_blocks_used | 2887383 |
> | Key_read_requests | 2929797981 |
> | Key_reads | 8856159 |
> | Key_write_requests | 305442125 |
> | Key_writes | 94187694 |
> | Uptime | 268069 |
>
> | Handler_commit | 0 |
> | Handler_delete | 20075144 |
> | Handler_read_first | 18 |
> | Handler_read_key | 432092430 |
> | Handler_read_next | 510230999 |
> | Handler_read_prev | 0 |
> | Handler_read_rnd | 148798998 |
> | Handler_read_rnd_nex
t | 1676280460 |
> | Handler_rollback | 0 |
> | Handler_update | 122968312 |
> | Handler_write | 286308623 |
> ...
> | Key_blocks_used | 2887383 |
> | Key_read_requests | 2929979657 |
> | Key_reads | 8859748 |
> | Key_write_requests | 305476828 |
> | Key_writes | 94187704 |
> | Uptime | 268102 |
>
> OK, so in 33 seconds, it did 181676 read requests and 3589 reads, and 34703
> write requests and 10 actual writes. 108 reads/sec, less than 1 write/sec.
>
> systat vmstat tells me that the overall data rate off the index disk
> is relatively low, but the disk is pretty busy, so there's a lot of seeking.
> The data disk is barely being touched.
>
>
>
> Nothing jumps out at me. Handler_read_rnd_nex
t is increasing at 287/sec,
> which isn't small...
>
> The key_buffer is 3GB or 4GB on this system. I'm contemplating an upgrade
> to 4.1 so I can have multiples.
>
> Thanks for looking at this...
>
>
>


-------------- 00000908060805020908
0000--
Christopher A. Kantarjiev

2006-03-31, 3:29 am

Mike Wexler wrote:
> It doesn't really answer your question, but have you tried "INSERT
> DELAYED" as a work around?

We've not had a lot of luck with this in the past, but it's worth a try.

> Also the "updated" status is strange, because that generally indicates
> that its looking for the record to be updated, but since the record is
> new, there is no record to be updated. Could it be checking for
> duplicates? Not that it should be this slow, but you might try ALTER
> TABLE xxx DISABLE KEYS and see how that effect performance. At least it
> will tell you whether the problem is in updating the keys, or something
> else.

It's certainly checking for duplicates. There are 10034461 records in the
link_area table at the moment, and 514408715 in trimble.old_crumb.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

sheeri kritzer

2006-03-31, 9:30 am

Are your logs and data on the same partition? That's a bad idea for
recovering from a blown part of the disk, but we also saw that one of
our databases would crash when there were lots of
inserts/updates/replaces -- other databases, which had the same
version of MySQL and operating system, had the logs and data on a
separate partition, and they did not crash.

We posited that there was just too much disk seeking going on, things
would get slow, etc.

-Sheeri

On 3/30/06, Christopher A. Kantarjiev <cak@dimebank.com> wrote:
> Mike Wexler wrote:
>
> We've not had a lot of luck with this in the past, but it's worth a try.
>
>
> It's certainly checking for duplicates. There are 10034461 records in the
> link_area table at the moment, and 514408715 in trimble.old_crumb.
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql? unsub...mail
.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Chris Kantarjiev

2006-03-31, 8:25 pm

> Are your logs and data on the same partition? That's a bad idea for
> recovering from a blown part of the disk, but we also saw that one of
> our databases would crash when there were lots of
> inserts/updates/replaces -- other databases, which had the same
> version of MySQL and operating system, had the logs and data on a
> separate partition, and they did not crash.

It's a MyISAM table. Are there separate logs files? If so, where?
I thought it was just .frm, .MYI and .MYD. We have MYI and MYD on
separate drives.

We're investigating a possible MERGE organization. I'll report
back if we learn anything new.

Thanks,
chris

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

mos

2006-04-01, 3:24 am

At 04:14 PM 3/31/2006, Chris Kantarjiev wrote:
>
>It's a MyISAM table. Are there separate logs files? If so, where?
>I thought it was just .frm, .MYI and .MYD. We have MYI and MYD on
>separate drives.
>
>We're investigating a possible MERGE organization. I'll report
>back if we learn anything new.
>
>Thanks,
>chris

Chris,
The problem with Load Data is the larger the table, the slower it
gets because it has to keep updating the index during the loading process.
If you use Load Data on an empty table is will be considerably faster
because the indexes are built after all the data has been loaded. You could
try removing the unique/primary index from the table you are using Load
Data on just as a test to see if it speeds up. Also you can break the Load
Data up into smaller number of rows, say files of 1000 rows each and try that.

Mike


>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql? unsub...tmail
.fm


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

mysql@karsites.net

2006-04-01, 3:24 am

<Quote>
If you use ALTER TABLE on a MyISAM table, all non-unique
indexes are created in a separate batch (as for REPAIR
TABLE). This should make ALTER TABLE much faster when you
have many indexes.

This feature can be activated explicitly. ALTER TABLE ...
DISABLE KEYS tells MySQL to stop updating non-unique indexes
for a MyISAM table. ALTER TABLE ... ENABLE KEYS then should
be used to re-create missing indexes. MySQL does this with a
special algorithm that is much faster than inserting keys
one by one, so disabling keys before performing bulk insert
operations should give a considerable speedup. Using ALTER
TABLE ... DISABLE KEYS requires the INDEX privilege in
addition to the privileges mentioned earlier.
</Quote>

Can you post your show create table tbl_name statement for
these tables that involve slow queries?

Do you have alot of indexes on these slow queries?

If so, would using the above help? (may have been mentioned already)

ALTER TABLE tbl_name DISABLE KEYS;

your slow insert or update query here;

ALTER TABLE tbl_name ENABLE KEYS;

HTH

Keith

more ->

On Fri, 31 Mar 2006, Chris Kantarjiev wrote:

> To: awfief@gmail.com, mysql@lists.mysql.com
> From: Chris Kantarjiev <cak@dimebank.com>
> Subject: Re: stunningly slow query
>
>
> It's a MyISAM table. Are there separate logs files? If so, where?
> I thought it was just .frm, .MYI and .MYD. We have MYI and MYD on
> separate drives.

Log files usually default to the mysql data directory, eg.
/var/lib/mysql/

Putting the database files on seperate drives may slow
things down alot too - unless others know better.

..frm is the database definition file. .MYI is the index
file, and .MYD is the data file. There is one each of these
files for each myisam table in the database.

I may be wrong, but I would have thought it better if these
are all together on the same disk and partition for each
table in the database?

> We're investigating a possible MERGE organization. I'll report
> back if we learn anything new.
>
> Thanks,
> chris

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Chris Kantarjiev

2006-04-02, 8:26 pm

> The problem with Load Data is the larger the table, the slower it
> gets because it has to keep updating the index during the loading process.

Um, thanks. I'm not sure how Load Data got involved here, because
that's not what's going on.

>
>
> Log files usually default to the mysql data directory, eg.
> /var/lib/mysql/

As I said, I don't think there are any log files for a MyISAM table.
InnoDB has separate logs.

>
> Putting the database files on seperate drives may slow
> things down alot too - unless others know better.
>
> .frm is the database definition file. .MYI is the index
> file, and .MYD is the data file. There is one each of these
> files for each myisam table in the database.
>
> I may be wrong, but I would have thought it better if these
> are all together on the same disk and partition for each
> table in the database?

This is counter-intuitive. Separating .MYI and .MYD means that
I can overlap the i/o. This is a standard strategy for other
databases (Oracle, in particular). I would be really surprised
if this was causing my problem.

> This feature can be activated explicitly. ALTER TABLE ...
> DISABLE KEYS tells MySQL to stop updating non-unique indexes
> for a MyISAM table. ALTER TABLE ... ENABLE KEYS then should
> be used to re-create missing indexes.

> Can you post your show create table tbl_name statement for
> these tables that involve slow queries?

| old_crumb |CREATE TABLE `old_crumb` (
`link_ID` bigint(20) default NULL,
`dir_Travel` char(1) default NULL,
`customer_ID` int(11) NOT NULL default '0',
`source_ID` int(11) NOT NULL default '0',
`vehicle_ID` int(11) NOT NULL default '0',
`actual_Time` datetime NOT NULL default '0000-00-00 00:00:00',
`actual_TZ` varchar(30) default NULL,
`reported_Time` datetime default NULL,
`reported_TZ` varchar(30) default NULL,
`speed_Format` int(11) default NULL,
`speed` float default NULL,
`direction` char(2) default NULL,
`compass` int(11) default NULL,
`speed_NS` float default NULL,
`speed_EW` float default NULL,
`distance` decimal(10,0) default NULL,
`duration` decimal(10,0) default NULL,
`latitude` decimal(10,5) default NULL,
`longitude` decimal(10,5) default NULL,
`report_Landmark` varchar(255) default NULL,
`report_Address` varchar(255) default NULL,
`report_Cross` varchar(255) default NULL,
`report_City` varchar(255) default NULL,
`report_State` char(2) default NULL,
`report_Zip` varchar(10) default NULL,
`report_County` varchar(255) default NULL,
`category` int(11) default NULL,
`speed_Limit` int(11) default NULL,
`street` varchar(255) default NULL,
`city` varchar(255) default NULL,
`state` char(2) default NULL,
`zip` varchar(10) default NULL,
`county` varchar(255) default NULL,
`match_Name` tinyint(1) default NULL,
`name_Matched` tinyint(1) default NULL,
`last_Modified` datetime default NULL,
PRIMARY KEY (`customer_ID`,`sour
ce_ID`,`vehicle_ID`,
`actual_Time`),
KEY `old_crumb_ix_report
ed_Time` (`reported_Time`),
KEY `old_crumb_ix_link_I
D` (`link_ID`,`dir_Trav
el`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=1000000000 COMMENT='List of breadcrumbs already rolled up.' INDEX DIRECTORY='/var/mysql_idx/trimble/' |

This is the other

link_area | CREATE TABLE `link_area` (
`link_ID` bigint(20) NOT NULL default '0',
`dir_Travel` char(1) NOT NULL default '',
`area_ID` int(11) NOT NULL default '0',
PRIMARY KEY (`link_ID`,`dir_Trav
el`),
KEY `link_area_ix_area_I
D` (`area_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Link area table' INDEX DIRECTORY='/var/mysql_idx/landsonar/' |

Inserts into the link_area were going very very slowly while data
was being moved into old_crumb. old_crumb is large - my suspicion
at this point is that the process of looking for key conflicts was
slowing things down and starving other query traffic.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

mysql@karsites.net

2006-04-02, 8:26 pm

On Sun, 2 Apr 2006, Chris Kantarjiev wrote:

> To: mysql@lists.mysql.com
> From: Chris Kantarjiev <cak@dimebank.com>
> Subject: Re: stunningly slow query
>
>
> Um, thanks. I'm not sure how Load Data got involved here, because
> that's not what's going on.
>
>
> As I said, I don't think there are any log files for a MyISAM table.
> InnoDB has separate logs.
>
>
> This is counter-intuitive. Separating .MYI and .MYD means that
> I can overlap the i/o. This is a standard strategy for other
> databases (Oracle, in particular). I would be really surprised
> if this was causing my problem.

OK - something new I've just learnt Chris.

>
>
> | old_crumb |CREATE TABLE `old_crumb` (
> `link_ID` bigint(20) default NULL,
> `dir_Travel` char(1) default NULL,
> `customer_ID` int(11) NOT NULL default '0',
> `source_ID` int(11) NOT NULL default '0',
> `vehicle_ID` int(11) NOT NULL default '0',
> `actual_Time` datetime NOT NULL default '0000-00-00 00:00:00',
> `actual_TZ` varchar(30) default NULL,
> `reported_Time` datetime default NULL,
> `reported_TZ` varchar(30) default NULL,
> `speed_Format` int(11) default NULL,
> `speed` float default NULL,
> `direction` char(2) default NULL,
> `compass` int(11) default NULL,
> `speed_NS` float default NULL,
> `speed_EW` float default NULL,
> `distance` decimal(10,0) default NULL,
> `duration` decimal(10,0) default NULL,
> `latitude` decimal(10,5) default NULL,
> `longitude` decimal(10,5) default NULL,
> `report_Landmark` varchar(255) default NULL,
> `report_Address` varchar(255) default NULL,
> `report_Cross` varchar(255) default NULL,
> `report_City` varchar(255) default NULL,
> `report_State` char(2) default NULL,
> `report_Zip` varchar(10) default NULL,
> `report_County` varchar(255) default NULL,
> `category` int(11) default NULL,
> `speed_Limit` int(11) default NULL,
> `street` varchar(255) default NULL,
> `city` varchar(255) default NULL,
> `state` char(2) default NULL,
> `zip` varchar(10) default NULL,
> `county` varchar(255) default NULL,
> `match_Name` tinyint(1) default NULL,
> `name_Matched` tinyint(1) default NULL,
> `last_Modified` datetime default NULL,
> PRIMARY KEY (`customer_ID`,`sour
ce_ID`,`vehicle_ID`,
`actual_Time`),
> KEY `old_crumb_ix_report
ed_Time` (`reported_Time`),
> KEY `old_crumb_ix_link_I
D` (`link_ID`,`dir_Trav
el`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=1000000000 COMMENT='List of breadcrumbs already rolled up.' INDEX DIRECTORY='/var/mysql_idx/trimble/' |

I'm no DB expert Chris but do you really need to create a
primary key index over 4 columns?

What about something simple and possibly faster like adding
a seperate ID primary key column to the table like:

| old_crumb |CREATE TABLE `old_crumb` (
`ID` int unsigned not null auto_increment
`link_ID` bigint(20) default NULL,
`dir_Travel` char(1) default NULL,
`customer_ID` int(11) NOT NULL default '0',
`source_ID` int(11) NOT NULL default '0',
`vehicle_ID` int(11) NOT NULL default '0',
snip
PRIMARY KEY (`ID`),
snip

An unsigned int will take an extra 4 bytes of storage space
per row, and will give you an index range of 0 - 4294967295.

If that is not enough range, an unsigned bigint will take an
extra 8 bytes of storage space, and will give you an index
range of 0 - 18446744073709551615
.

Although this will increase the amount of storage space
required in the .MYD file, it may also decrease the amount
of space required in the .MYI index file, as you would not
be needing to store multi-column indexes.

> This is the other
>
> link_area | CREATE TABLE `link_area` (
> `link_ID` bigint(20) NOT NULL default '0',
> `dir_Travel` char(1) NOT NULL default '',
> `area_ID` int(11) NOT NULL default '0',
> PRIMARY KEY (`link_ID`,`dir_Trav
el`),
> KEY `link_area_ix_area_I
D` (`area_ID`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Link area table' INDEX DIRECTORY='/var/mysql_idx/landsonar/' |
>
> Inserts into the link_area were going very very slowly while data
> was being moved into old_crumb. old_crumb is large - my suspicion
> at this point is that the process of looking for key conflicts was
> slowing things down and starving other query traffic.

The same could be applied to the link_area table:
Do you need dir_travel as part of the primary key?

> link_area | CREATE TABLE `link_area` (
> `link_ID` bigint(20) NOT NULL default '0',
> `dir_Travel` char(1) NOT NULL default '',
> `area_ID` int(11) NOT NULL default '0',
> PRIMARY KEY (`link_ID`),
> KEY `link_area_ix_area_I
D` (`area_ID`)
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Link area
table' INDEX DI$

Regards

Keith

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

SGreen@unimin.com

2006-04-03, 3:27 am

--=_alternative 00193D5785257145_=
Content-Type: text/plain; charset="US-ASCII"

mysql@karsites.net wrote on 04/02/2006 05:35:59 PM:

<snip>
<snip>[color=darkred]
> COMMENT='List of breadcrumbs already rolled up.' INDEX
> DIRECTORY='/var/mysql_idx/trimble/' |
>
> I'm no DB expert Chris but do you really need to create a
> primary key index over 4 columns?
>
> What about something simple and possibly faster like adding
> a seperate ID primary key column to the table like:
>
> | old_crumb |CREATE TABLE `old_crumb` (
> `ID` int unsigned not null auto_increment
> `link_ID` bigint(20) default NULL,
> `dir_Travel` char(1) default NULL,
> `customer_ID` int(11) NOT NULL default '0',
> `source_ID` int(11) NOT NULL default '0',
> `vehicle_ID` int(11) NOT NULL default '0',
> snip
> PRIMARY KEY (`ID`),
> snip
>
> An unsigned int will take an extra 4 bytes of storage space
> per row, and will give you an index range of 0 - 4294967295.
>
> If that is not enough range, an unsigned bigint will take an
> extra 8 bytes of storage space, and will give you an index
> range of 0 - 18446744073709551615
.
>
> Although this will increase the amount of storage space
> required in the .MYD file, it may also decrease the amount
> of space required in the .MYI index file, as you would not
> be needing to store multi-column indexes.
>

Keith,
Your method won't guarantee that there are no rows where the combination
of the values in those four columns fails to repeat in any other row. To
do that would require an EXTRA four-column unique index of type UNIQUE.
Your proposal would actually make the situation worse as now there would
be two indexes to maintain to achieve the same effect as the previous
single PK.

> INDEX DIRECTORY='/var/mysql_idx/landsonar/' |
>
> The same could be applied to the link_area table:
> Do you need dir_travel as part of the primary key?

If dir_travel is part of what makes each row different than every other
row, then YES he needs that column as part of his primary key.

>
> table' INDEX DI$
>
> Regards
>
> Keith
>

Your suggestions were well intended. However, this seems to me that his
key caches are just not large enough to keep the whole key in memory. It
may be possible for him to maintain a smaller "current" or "daily" table
that is then batch merged into the larger "historic" copy of his old-crum
table. I believe he is correct in guessing that his insert traffic to
old_crum is interfering with the inserts into link_area and that the most
likely cause is the need to both validate the new rows against the PK and
add the new rows into the PK.

In the spirit of Keith's suggestion, is there any reason why you cannot
make a hash or lookup table of all of your
(`customer_ID`,`sour
ce_ID`,`vehicle_ID`)
triplets and replace those
columns in old_crum (and it's PK) with the single value? That way you
don't lose your row uniqueness but gain space in your PK. You could also
reduce your actual_time column to an integer value (instead of a date
value) so that you are comparing against a numeric value when you compare
against the PK?

When you are dealing with 10s of millions of rows like you are, these
little changes can make some big differences.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 00193D5785257145_=--
mysql@karsites.net

2006-04-03, 3:27 am


On Mon, 3 Apr 2006 SGreen@unimin.com wrote:

> To: mysql@karsites.net
> From: SGreen@unimin.com
> Subject: Re: stunningly slow query
>
> mysql@karsites.net wrote on 04/02/2006 05:35:59 PM:
>
> <snip>
> <snip>
>
> Keith,
> Your method won't guarantee that there are no rows where the combination
> of the values in those four columns fails to repeat in any other row. To
> do that would require an EXTRA four-column unique index of type UNIQUE.
> Your proposal would actually make the situation worse as now there would
> be two indexes to maintain to achieve the same effect as the previous
> single PK.

Thankyou for your expert reply Shawn.

Is it not possible to mark each of those those column values
as UNIQUE without them becoming a part of the index as
well? Or is this a contradiction in terms?

Regards

Keith


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Harald Fuchs

2006-04-03, 3:27 am

In article <Pine.LNX.4.61.0604030856040.19075@karsites.net>,
mysql@karsites.net writes:

[color=darkred]
> Thankyou for your expert reply Shawn.

> Is it not possible to mark each of those those column values
> as UNIQUE without them becoming a part of the index as
> well? Or is this a contradiction in terms?

In theory it would be possible to add a uniqueness constraint to a
column without an index, but this would cause a full table scan on
every INSERT. AFAIK therefore MySQL automatically puts an index on
this column.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

mysql@karsites.net

2006-04-04, 1:28 pm


I have just noticed this from section 13.4.5. LOCK TABLES
and UNLOCK TABLES Syntax of the 5.0.18 ref manual, and
wondered if it will help improve the speed of your query:

<snip>
Normally, you do not need to lock tables, because all single
UPDATE statements are atomic; no other thread can interfere
with any other currently executing SQL statement. However,
there are a few cases when locking tables may provide an
advantage:

If you are going to run many operations on a set of MyISAM
tables, it is much faster to lock the tables you are going
to use. Locking MyISAM tables speeds up inserting, updating,
or deleting on them. The downside is that no thread can
update a READ-locked table (including the one holding the
lock) and no thread can access a WRITE-locked table other
than the one holding the lock.

The reason some MyISAM operations are faster under LOCK
TABLES is that MySQL does not flush the key cache for the
locked tables until UNLOCK TABLES is called. Normally, the
key cache is flushed after each SQL statement.
</snip>

Regards

Keith Roberts

In theory, theory and practice are the same;
in practice they are not.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links





Also available: Server administration forum archive | Web Design forum archive | Software forum archive | Hardware reviews archive | Programming forum archive

Copyright 2009 droptable.com