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
On Mon, 3 Apr 2006 [EMAIL PROTECTED] wrote:
To: [EMAIL PROTECTED]
From: [EMAIL PROTECTED]
Subject: Re: stunningly slow query
[EMAIL PROTECTED] wrote on 04/02/2006 05:35:59 PM:
snip
Can you post your show create table tbl_name statement for
these tables that involve slow queries
In article [EMAIL PROTECTED],
[EMAIL PROTECTED] writes:
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
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.
It's a MyISAM table. Are there separate logs files?
On Sun, 2 Apr 2006, Chris Kantarjiev wrote:
To: mysql@lists.mysql.com
From: Chris Kantarjiev [EMAIL PROTECTED]
Subject: Re: stunningly slow query
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
[EMAIL PROTECTED] wrote on 04/02/2006 05:35:59 PM:
snip
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,
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
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,
At 04:14 PM 3/31/2006, Chris Kantarjiev wrote:
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
Kantarjiev [EMAIL PROTECTED]
Subject: Re: stunningly slow query
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
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
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
Can you post the output of SHOW FULL PROCESSLIST during the time when
both sets of queries are running?
mysql show full processlist;
Chris Kantarjiev wrote:
Can you post the output of SHOW FULL PROCESSLIST during the time when
both sets of queries are running?
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
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
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
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
17 matches
Mail list logo
