Thanks for the improved query.The indexing didn't help much and still the
main problem is it locking all updates to the tables while it executes...
even if I am executing it on a copy of the tables in a different database
--
Dave
2008/11/27 Chandru <[EMAIL PROTECTED]>
> Hi David,
> please create
Hi David,
can you please let me know what is the select query and the update query
along with the explain plan of the same.
can you please let me know if you are using innodb storage engine?
Regards,
Chandru
www.mafiree.com
On Thu, Nov 27, 2008 at 4:45 PM, David Scott
<[EMAIL PROTECTED]>wrot
show full processlist
userX is the user the site is using to connect
databaseX is the database in question
1976156, 'userX', 'localhost', 'databaseX', 'Sleep', 13, '', ''
13508974, 'dave', 'IPX:29212', 'databaseX', 'Sleep', 0, '', ''
13759139, 'sen', '1 IPX:32775', '', 'Sleep', 160, '', ''
1377562
can u please do "show full processlist" when the update is happening, and
if its innodb
please do "SHOW INNODB STATUS", which will give complete activity on innodb
engine, including lock information.
Please show use the output of these.
regards
anandkl
On 11/27/08, David Scott <[EMAIL PROTEC
Hi list.We have 2 tables, both have a few inserts, many updates and the
occasional select.
When running a select joining the 2 tables (which can take upto 20 seconds
to complete, they are large tables) all updates are blocked and the
maxconnections is quickly reached.
We tried copying the data to
-
From: "Baron Schwartz" <[EMAIL PROTECTED]>
To: "Justin" <[EMAIL PROTECTED]>
Cc:
Sent: Monday, September 03, 2007 4:42 PM
Subject: Re: servers full potential / FT searches locking tables
Justin wrote:
lockup just happened again.. here's a innodb stat
Justin wrote:
lockup just happened again.. here's a innodb status.
InnoDB status will be basically useless, as full-text is only applicable
to MyISAM, and indeed your status output shows only one transaction is
running (the one running 'show innodb status') and InnoDB has done zero
work sinc
iday, August 31, 2007 4:28 PM
Subject: Re: servers full potential / FT searches locking tables
Alright.. I think I see what's is happening after this latest lockup..
here's what I think is happening..
When a replace into query locks a table for a few seconds there are a boot
load
- Original Message -
From: "Michael Dykman" <[EMAIL PROTECTED]>
To: "Justin" <[EMAIL PROTECTED]>
Cc:
Sent: Tuesday, August 28, 2007 1:31 PM
Subject: Re: servers full potential / FT searches locking tables
No, I'm afraid not. 32 bit architectures have
Sorry man, as everyone keeps saying, there is only 4 gig of ram in the
entire known 32 bit universe.. that includes space for
process-specific system buffers, file handles, internals... the
TOTAL amount of ram you can give to 32-bit MySQL in ANY combination is
around 3.5G (many will tell you, not
On Wed, 29 Aug 2007 18:02:31 -0400, "Michael Dykman" <[EMAIL PROTECTED]>
said:
> I mean that the theoretical limit of a 32-bit application is 4G... in
> practice, you won't quite get that (for a pile of practical reasons)..
> best to keep your configured memory requirements to around 3.5G or
> y
I mean that the theoretical limit of a 32-bit application is 4G... in
practice, you won't quite get that (for a pile of practical reasons)..
best to keep your configured memory requirements to around 3.5G or
you will run into weird errors.
- michael dykman
On 8/28/07, Ken Peng <[EMAIL PROTECTE
On Tue, 28 Aug 2007 13:31:43 -0400, "Michael Dykman" <[EMAIL PROTECTED]>
said:
> No, I'm afraid not. 32 bit architectures have a theoretical limit of
> 4G of memory space for the entire application: in actual practice, for
> a variety of reasons too complex to go into here (and are well
> documen
IL PROTECTED]>
Cc:
Sent: Tuesday, August 28, 2007 1:31 PM
Subject: Re: servers full potential / FT searches locking tables
No, I'm afraid not. 32 bit architectures have a theoretical limit of
4G of memory space for the entire application: in actual practice, for
a variety of reasons
t;
> To: "Justin" <[EMAIL PROTECTED]>
> Cc:
> Sent: Tuesday, August 28, 2007 12:51 AM
> Subject: Re: servers full potential / FT searches locking tables
>
>
> > Your settings doesn't seem optimized much.
> >
> > So here first question, do
eu Bruneau" <[EMAIL PROTECTED]>
To: "Justin" <[EMAIL PROTECTED]>
Cc:
Sent: Tuesday, August 28, 2007 12:51 AM
Subject: Re: servers full potential / FT searches locking tables
Your settings doesn't seem optimized much.
So here first question, do you use 32bits or 64 bi
Your settings doesn't seem optimized much.
So here first question, do you use 32bits or 64 bits platform? If you
have 64 bits platform with 64 bits mysql and os you can boost most the
settings to use almost the 8G of ram you have on the server. If you are
using 32bits you will have to do some
Justin wrote:
Sometimes I get about 300 connections to the server, all are selects
and all select and get the data returned but the connection doesn't go
away and the website doesn't load up.. usually if there is a lock, the
selects wait 2-3 secs and build up, but once unlocked the queries all
x27;s a LAMP backend..
- Original Message -
From: "Jay Pipes" <[EMAIL PROTECTED]>
To: "Rolando Edwards" <[EMAIL PROTECTED]>
Cc: ; "Justin" <[EMAIL PROTECTED]>
Sent: Monday, August 27, 2007 3:03 PM
Subject: Re: servers full potential / FT searches
ql.com
Sent: Monday, August 27, 2007 2:26:29 PM (GMT-0500) America/New_York
Subject: Re: servers full potential / FT searches locking tables
SELECTs don't lock the table. Are you having frequent UPDATEs while
selecting? That would be the reason for locks.
-jay
Justin wrote:
Ok.. Straight
ge -
From: "Jay Pipes" <[EMAIL PROTECTED]>
To: "Justin" <[EMAIL PROTECTED]>
Cc:
Sent: Monday, August 27, 2007 2:26 PM
Subject: Re: servers full potential / FT searches locking tables
SELECTs don't lock the table. Are you having frequent UPDATEs while
s
w_York
Subject: Re: servers full potential / FT searches locking tables
SELECTs don't lock the table. Are you having frequent UPDATEs while
selecting? That would be the reason for locks.
-jay
Justin wrote:
> Ok.. Straight to the point.. Here is what I currently have.
>
> MySQ
SELECTs don't lock the table. Are you having frequent UPDATEs while
selecting? That would be the reason for locks.
-jay
Justin wrote:
Ok.. Straight to the point.. Here is what I currently have.
MySQL Ver 14.12 Distrib 5.0.27
RHEL vs 5
584GB Raid 5 storage
8GB of RAM
and Dual 5130 processors
Ok.. Straight to the point.. Here is what I currently have.
MySQL Ver 14.12 Distrib 5.0.27
RHEL vs 5
584GB Raid 5 storage
8GB of RAM
and Dual 5130 processors (2.0GHz Intel Dual-Core Xeon)
what my question is.. is am I utilizing the servers potential with the
following as my settings. The serve
matt_lists wrote:
chris smith wrote:
On 8/23/06, matt_lists <[EMAIL PROTECTED]> wrote:
We did not see this on 5.0.19, with 5.0.24 our backup jobs lock the
tables for selects
the backup takes 3 hours, so the site is down the whole time
I'm using this backup line
mysqldump -d -f --quote-names
chris smith wrote:
On 8/23/06, matt_lists <[EMAIL PROTECTED]> wrote:
We did not see this on 5.0.19, with 5.0.24 our backup jobs lock the
tables for selects
the backup takes 3 hours, so the site is down the whole time
I'm using this backup line
mysqldump -d -f --quote-names --skip-add-locks da
On 8/23/06, matt_lists <[EMAIL PROTECTED]> wrote:
We did not see this on 5.0.19, with 5.0.24 our backup jobs lock the
tables for selects
the backup takes 3 hours, so the site is down the whole time
I'm using this backup line
mysqldump -d -f --quote-names --skip-add-locks database > outfile
mys
We did not see this on 5.0.19, with 5.0.24 our backup jobs lock the
tables for selects
the backup takes 3 hours, so the site is down the whole time
I'm using this backup line
mysqldump -d -f --quote-names --skip-add-locks database > outfile
mysqldump -t -f --skip-add-locks database > outfile
Hi there, this is a bit of black art, but I would like to setup locks on
inserting back queries and updates on MyIsam tables which need fulltext
search and other tables are Innodb. I am having issues with this query
LOCK TABLE complaint_threads as ct WRITE, complaint_info as ci WRITE;
For some wi
At 11:42 -0400 4/17/04, Mark Susol|Ultimate Creative Media wrote:
> The general answer to your question, if you're willing to cooperate
with the server, is to lock the tables from within the server so that
no other clients can modify them, and use FLUSH TABLES to flush any
changes to disk. Whi
> The general answer to your question, if you're willing to cooperate
> with the server, is to lock the tables from within the server so that
> no other clients can modify them, and use FLUSH TABLES to flush any
> changes to disk. While the lock remains in place, copy the table
> files. Then unlo
At 0:03 -0400 4/16/04, Mark Susol|Ultimate Creative Media wrote:
Did I really ask that tough a question? Anyone?
I'm not sure you're asking an answerable question. Consider this requirement
that you give below:
> So what do I need to do before running any backup scripts to ensure the
> tables wi
Did I really ask that tough a question? Anyone?
On 4/15/04 7:38 PM, "Mark Susol | Ultimate Creative Media"
<[EMAIL PROTECTED]> wrote:
> I've found a nice shell script to use to backup my server's MySQL databases.
> https://sourceforge.net/projects/automysqlbackup/
>
> However, when I tried this
I've found a nice shell script to use to backup my server's MySQL databases.
https://sourceforge.net/projects/automysqlbackup/
However, when I tried this earlier today it resulted in a corrupt table. Now
the table in question is one I've had issues with for other reasons, but it
has over 2 mil rec
;line too long".
Cheers
/rudy
-Original Message-
From: Phil Bitis [mailto:[EMAIL PROTECTED]
Sent: maandag 14 juli 2003 11:44
To: [EMAIL PROTECTED]
Subject: Re: Improving insertion performance by locking tables
Is there a limit to the number of records I can insert in a
multiple-value
insert?
RE: Improving insertion performance by locking tables
>From what I know is, that MySQL always locks the MyISAM table before you
insert, update or delete something from it. So the key here is not so
much if you should lock the table, but how you insert the data (single
inserts vs multi inse
ble you actually would loose time.
/rudy
-Original Message-
From: Phil Bitis [mailto:[EMAIL PROTECTED]
Sent: zaterdag 12 juli 2003 19:00
To: [EMAIL PROTECTED]
Subject: Improving insertion performance by locking tables
Hello,
We've got an application which does many multiple-value in
Hello,
We've got an application which does many multiple-value inserts to different
tables.
Is it worth locking a table before doing a multiple-value insert with say 50
records? If so, what is the number of records that makes it worthwhile?
If not, is it worth locking a table before doing 2 seper
hello.
You can read lock whole database's tables like this :
FLUSH TABLES WITH READ LOCK ;
and unlock with
UNLOCK TABLES ;
- Original Message -
From: "Franz, Fa. PostDirekt MA" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, February 05, 200
Dear Ladies and Gentlemen,
in order to backup databases or better the whole server file-based , I'd
like
to know , if there is a way to get a read-Lock for all tables in a database
or even better for all tables in all databases.
Something like:
LOCK TABLES .% READ;
which I tried cause I'm foool
Hi all,
We have just finished developing and application in C++ that uses a
mysql 3.23.52 database on Linux. It went into productions this morning and
now we are having problems with multiple select statements. When one user
runs a very learge select query, it locks the tables involved so no
.
- Original Message -
From: "Alexander Keremidarski" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.mysql
Sent: Thursday, May 23, 2002 1:54 AM
Subject: Re: Locking TABLES for myisamchk, please help!!
> Mark wrote:
> > Wednesday, May 22, 2002, 9:23:02 AM, louie w
t-in precaution. I can
> understand possible objections to myisasmchk globally locking tables out of
> the blue (it might disrupt what some clients are doing), but if myisamchk
> cannot run on a live table, it should not run on a live table. As simple as
> that.
Hi,
It is not that simple.
You can
sqld is running. That should be a built-in precaution. I can
understand possible objections to myisasmchk globally locking tables out of
the blue (it might disrupt what some clients are doing), but if myisamchk
cannot run on a live table, it should not run o
louie,
Wednesday, May 22, 2002, 9:23:02 AM, you wrote:
lm> Hi, i was thingking about locking the tables first so no connection
lm> could write so i can do myisamchk. Is this process safe?
lm> Procedures:
lm> 1. lockdb
lm> 2. run myisamchk -r or -o
lm> 3. unlock db
lm> Btw, mysqld is running. I
Hi,
I have a scenario..
I lock this mysql table and then a query/insert came in one of my script's.
the table is locked, what will happen to that query?
Will it retry again to insert on that table or no, it will not.
ty,
louie...
--
Hi, i was thingking about locking the tables first so no connection
could write so i can do myisamchk. Is this process safe?
Procedures:
1. lockdb
2. run myisamchk -r or -o
3. unlock db
Btw, mysqld is running. I want to off it but i can't its a production
server.
Hope anyone could give me more
> What error occurs if i try to access (read or write) a table that was
locked
> (LOCK TABLES mytable WRITE) by another thread?
My belief is that you do not get an error. The thread attempting to access
the
locked table is stalled until the lock is released. If the lock is never
released, you
Hi,
What error occurs if i try to access (read or write) a table that was locked
(LOCK TABLES mytable WRITE) by another thread?
Dirso.
PS: MYSQL
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual
Sorry about the mutli post of this, but I got some mail daemon errors, so
I'm sending again to make sure. The question is below.
Carl
-- Forwarded message --
Date: Fri, 12 Apr 2002 10:20:37 -0400
From: Carl Schmidt <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Subje
I understand that at some point, a sql table that has been locked by
someone
will automatically be unlocked, but I'm a little unclear as to the
circumstances. Say at the beginning of a function I open a connection,
lock a table, and then close the connection. Then, perhaps a few lines
down in th
"ERROR 1064: You have an error in your SQL
TC> syntax near '' at line 1" The syntax looks correct to me as per
TC> the online documentation. Am I doing something wrong or must a
TC> parameter be set to facilitate locking tables?
The syntax is incorrect. Look carefully
: <[EMAIL PROTECTED]>
Sent: Monday, February 25, 2002 3:41 AM
Subject: Error Locking Tables
> Hello,
>
> I am using MySQL 3.23.47 under Windows 98 (mysql-opt) and I am having a
strange problem. I issued the "LOCK TABLES ;" command at the
command prompt and I got the followin
syntax looks correct to me as per the online documentation. Am I doing something
wrong or must a parameter be set to facilitate locking tables?
Thanks in advance for your help,
Tom
On Mon, Sep 24, 2001 at 10:14:59AM +0200, Rafal Jank wrote:
> Hi,
>
> Why is mysql locking table during insert operastion? The version is
> 3.23.22, so it shouldn't behave like this...
Have you ever deleted records from the table (and not run an OPTIMIZE
TABLE afterward)? If there are "holes" i
Fred van Engen wrote:
>
> Rafal,
>
> On Mon, Sep 24, 2001 at 10:14:59AM +0200, Rafal Jank wrote:
> > Why is mysql locking table during insert operastion? The version is 3.23.22, so
> > it shouldn't behave like this...
> >
>
> If you mean it shouldn't behave like this because it does concurrent
Rafal,
On Mon, Sep 24, 2001 at 10:14:59AM +0200, Rafal Jank wrote:
> Why is mysql locking table during insert operastion? The version is 3.23.22, so
> it shouldn't behave like this...
>
If you mean it shouldn't behave like this because it does concurrent
inserts, then note that they work only o
Hi,
I have strange problem with mysql. When I run mysqladmin processlist i get
output (I cut off selects):
| 4045633 | konkursy | targi1.wp.pl | konkursy2 | Query |
14| Locked | insert into bb2_ludzie_tak values
('Marzena')
To avoid the "Fatal Embrace" where two users each lock a table, and
then attempt to read the table the other locked, you must lock
all table you intend to use for the duration of the lock.
The manual clearly states that a subsequent lock or unlock command
will first unlock all the tables you curre
Hello.
Im wondering whats up with lock tables in mysql.
i need to lock one table but also insert into another table that does not
have to be locked.
why does it need to lock both tables? this is pointless to me if one table
does not need
to be locked.
Also, from the mysql documentation :
If a t
60 matches
Mail list logo
