Re: SELECT locking tables.... in other databases
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 PROTECTED] wrote: 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 a 2nd database (in the same MySQL install) to run the select on that but for some reason that still prevents the updates on the original database, we watch the connections and we see them build up, when the select finishes they quickly clear. My question is how can we prevent this backing up of updates when running a select and why would doing a select on one database cause connections on another to back up? Thanks -- David
Re: SELECT locking tables.... in other databases
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, '', '' 13775621, 'sen', 'IPX:43603', 'databaseX', 'Sleep', 0, '', '' 13821806, 'dave', 'IPX:55885', '', 'Query', 0, '', 'show full processlist' 13821878, 'userX', 'localhost', 'databaseX', 'Sleep', 0, '', '' Show innodb status: ' = 081127 11:12:38 INNODB MONITOR OUTPUT = Per second averages calculated from the last 1 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 164975, signal count 155476 Mutex spin waits 0, rounds 7441650, OS waits 120688 RW-shared spins 37873, OS waits 17328; RW-excl spins 24776, OS waits 4966 TRANSACTIONS Trx id counter 0 25118320 Purge done for trx's n:o 0 25118124 undo n:o 0 0 History list length 89 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 19166, OS thread id 1096026448 MySQL thread id 13833472, query id 57762790 IPX dave SHOW INNODB STATUS ---TRANSACTION 0 25063765, not started, process no 19166, OS thread id 1182529872 MySQL thread id 13508974, query id 57762327 IPX dave ---TRANSACTION 0 0, not started, process no 19166, OS thread id 1176140112 MySQL thread id 13775621, query id 57762659 IPX sen ---TRANSACTION 0 0, not started, process no 19166, OS thread id 1181997392 MySQL thread id 13759139, query id 57665031 IPX sen ---TRANSACTION 0 25117242, not started, process no 19166, OS thread id 1188387152 MySQL thread id 1976156, query id 57760643 localhost beta_cc FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 28 OS file reads, 12527564 OS file writes, 12361532 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 79.92 writes/s, 79.92 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 2310107, used cells 513846, node heap has 859 buffer(s) 34.97 hash searches/s, 99.90 non-hash searches/s --- LOG --- Log sequence number 1 501773721 Log flushed up to 1 501773721 Last checkpoint at 1 500074343 0 pending log writes, 0 pending chkp writes 12329746 log i/o's done, 79.92 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 1201497898; in additional pool allocated 558592 Buffer pool size 64000 Free buffers 3328 Database pages 59813 Modified db pages 253 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 65, created 59748, written 573841 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 19166, id 1171347792, state: sleeping Number of rows inserted 17853779, updated 10095603, deleted 18, read 948444635 13.99 inserts/s, 65.93 updates/s, 0.00 deletes/s, 98.90 reads/s END OF INNODB MONITOR OUTPUT ' I hope that helps-- Dave 2008/11/27 Ananda Kumar [EMAIL PROTECTED] 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 PROTECTED] wrote: 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 a 2nd database (in the same MySQL install) to run the select on that but for some reason that still prevents the updates on the original database, we watch the connections and we see them build up, when the select finishes they quickly clear. My question is how can we prevent this backing up of updates when running a select and why would doing a select on one database cause connections on another to back up? Thanks -- David
Re: SELECT locking tables.... in other databases
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]wrote: 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, '', '' 13775621, 'sen', 'IPX:43603', 'databaseX', 'Sleep', 0, '', '' 13821806, 'dave', 'IPX:55885', '', 'Query', 0, '', 'show full processlist' 13821878, 'userX', 'localhost', 'databaseX', 'Sleep', 0, '', '' Show innodb status: ' = 081127 11:12:38 INNODB MONITOR OUTPUT = Per second averages calculated from the last 1 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 164975, signal count 155476 Mutex spin waits 0, rounds 7441650, OS waits 120688 RW-shared spins 37873, OS waits 17328; RW-excl spins 24776, OS waits 4966 TRANSACTIONS Trx id counter 0 25118320 Purge done for trx's n:o 0 25118124 undo n:o 0 0 History list length 89 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 19166, OS thread id 1096026448 MySQL thread id 13833472, query id 57762790 IPX dave SHOW INNODB STATUS ---TRANSACTION 0 25063765, not started, process no 19166, OS thread id 1182529872 MySQL thread id 13508974, query id 57762327 IPX dave ---TRANSACTION 0 0, not started, process no 19166, OS thread id 1176140112 MySQL thread id 13775621, query id 57762659 IPX sen ---TRANSACTION 0 0, not started, process no 19166, OS thread id 1181997392 MySQL thread id 13759139, query id 57665031 IPX sen ---TRANSACTION 0 25117242, not started, process no 19166, OS thread id 1188387152 MySQL thread id 1976156, query id 57760643 localhost beta_cc FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 28 OS file reads, 12527564 OS file writes, 12361532 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 79.92 writes/s, 79.92 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 2310107, used cells 513846, node heap has 859 buffer(s) 34.97 hash searches/s, 99.90 non-hash searches/s --- LOG --- Log sequence number 1 501773721 Log flushed up to 1 501773721 Last checkpoint at 1 500074343 0 pending log writes, 0 pending chkp writes 12329746 log i/o's done, 79.92 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 1201497898; in additional pool allocated 558592 Buffer pool size 64000 Free buffers 3328 Database pages 59813 Modified db pages 253 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 65, created 59748, written 573841 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 19166, id 1171347792, state: sleeping Number of rows inserted 17853779, updated 10095603, deleted 18, read 948444635 13.99 inserts/s, 65.93 updates/s, 0.00 deletes/s, 98.90 reads/s END OF INNODB MONITOR OUTPUT ' I hope that helps-- Dave 2008/11/27 Ananda Kumar [EMAIL PROTECTED] 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 PROTECTED] wrote: 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 a 2nd database (in the same MySQL install) to run the select on that but for some reason that still prevents the updates on the original database, we watch the
SELECT locking tables.... in other databases
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 a 2nd database (in the same MySQL install) to run the select on that but for some reason that still prevents the updates on the original database, we watch the connections and we see them build up, when the select finishes they quickly clear. My question is how can we prevent this backing up of updates when running a select and why would doing a select on one database cause connections on another to back up? Thanks -- David
Re: SELECT locking tables.... in other databases
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 index on games_sessions_levels table on the column startTime
and the query can be rewritten as
gSL.starttime between unix_timestamp('2008-11-26') and
unix_timestamp('2008-11-26') and gSL.endTime 0
gSL.startTime 0 is invalid since already the value that you verify is
between unix_timestamp('2008-11-26') and unix_timestamp('2008-11-26')
gSL.starttime unix_timestamp('2008-11-26')
AND gSL.startTime 0 AND gSL.endTime 0
AND gSL.starttime unix_timestamp('2008-11-26')
Regards,
Chandru
www.mafiree.com
On Thu, Nov 27, 2008 at 5:50 PM, David Scott
[EMAIL PROTECTED] wrote:
Ah yes, sorry, here it is:
SELECT gS.gameid, g.gamename, COUNT(DISTINCT(gS.sessionid)) 'sessions',
SUM(gSL.endTime - gSL.startTime)/COUNT(DISTINCT(gS.sessionid))/60 'average
SESSION time',
SUM(gSL.totalTime)/COUNT(DISTINCT(gS.sessionid))/60 'average PLAY time',
SUM(gSL.totalTime)/60
FROM databaseX.games_sessions_levels gSL
JOIN databaseX.games_sessions gS ON gS.sessionid = gSL.sessionid
JOIN databaseX.games g ON g.id = gS.gameid
WHERE gSL.starttime unix_timestamp('2008-11-26')
AND gSL.startTime 0 AND gSL.endTime 0
AND gSL.starttime unix_timestamp('2008-11-27')
GROUP BY gS.gameid
ORDER BY SUM(gSL.totalTime) DESC
CREATE TABLE `databaseX `.`Xgames_sessions` (
`sessionid` int(99) NOT NULL auto_increment,
`playerid` varchar(32) NOT NULL,
`gameid` int(99) NOT NULL,
`starttime` int(20) NOT NULL,
`zone` varchar(255) NOT NULL,
`host` varchar(255) NOT NULL,
`loadref` varchar(50) NOT NULL,
PRIMARY KEY (`sessionid`)
) ENGINE=InnoDB AUTO_INCREMENT=604907 DEFAULT CHARSET=latin1
ROW_FORMAT=DYNAMIC;
CREATE TABLE `databaseX`.`Xgames_sessions_levels` (
`id` int(99) NOT NULL auto_increment,
`sessionid` int(99) NOT NULL,
`levelnumber` int(99) NOT NULL,
`levelname` varchar(50) default NULL,
`starttime` int(20) NOT NULL,
`endtime` int(20) NOT NULL,
`totaltime` int(20) NOT NULL default '0',
`info` int(11) NOT NULL,
`score` int(99) NOT NULL,
`done` tinyint(1) NOT NULL default '0',
`zone` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4213995 DEFAULT CHARSET=latin1
ROW_FORMAT=DYNAMIC;
--
Dave
2008/11/27 Chandru [EMAIL PROTECTED]
Hai david,
without seeing the query i cant comment if the index that is created
is efficient or not. Please send the query if you need more help.
Regards,
Chandru
www.mafiree.com
On Thu, Nov 27, 2008 at 5:36 PM, David Scott
[EMAIL PROTECTED] wrote:
We only indexed the id on the table as it has many many updates and very
rarely do we select, we assumed this would be more efficient? --
Dave
2008/11/27 Chandru [EMAIL PROTECTED]
Hi David,
I find that the query is going for a full table scan. i think u need
to optimize the query. Can you please send the original query and also the
output of show index from tablename;
Regards,
Chandru.
www.mafiree.com
On Thu, Nov 27, 2008 at 5:18 PM, David Scott
[EMAIL PROTECTED] wrote:
We are using innodb.
EXPLAIN on the select:
1, 'SIMPLE', 'gSL', 'ALL', '', '', '', '', 4210688, 'Using where;
Using temporary; Using filesort'
1, 'SIMPLE', 'gS', 'eq_ref', 'PRIMARY', 'PRIMARY', '4',
'databaseX.gSL.sessionid', 1, ''
1, 'SIMPLE', 'g', 'eq_ref', 'PRIMARY', 'PRIMARY', '4',
'databaseX.gS.gameid', 1, ''
We are baffled by how doing it on one DB can lock updates in another
--
Dave
2008/11/27 Pradeep Chandru [EMAIL PROTECTED]
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] wrote:
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, '', ''
13775621, 'sen', 'IPX:43603', 'databaseX', 'Sleep', 0, '', ''
13821806, 'dave', 'IPX:55885', '', 'Query', 0, '', 'show full
processlist'
13821878, 'userX', 'localhost', 'databaseX', 'Sleep', 0, '', ''
Show innodb status:
'
=
081127 11:12:38 INNODB MONITOR OUTPUT
=
Per second averages calculated from the last 1 seconds
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 164975, signal count 155476
Mutex spin waits 0, rounds 7441650, OS waits 120688
RW-shared spins 37873, OS waits 17328; RW-excl spins
Re: servers full potential / FT searches locking tables
Figure it out at 3am this morning while I was sleeping I remembered there was an rsync on the databases every 10 mins to replicate the files across the network. I killed the rsync.. and all has been flawless! =) gotta love the dream world. answers are always there! - Original Message - From: Baron Schwartz [EMAIL PROTECTED] To: Justin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com 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 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 since the server was started (look in the ROW OPERATIONS section). So your server isn't using InnoDB for any tables (at least none you've queried since starting the server). But you're still allocating some memory and other resources to it. If I were you I'd put the following in your /etc/my.cnf just to ensure InnoDB isn't interfering or complicating anything: skip_innodb Cheers Baron -- 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]
Re: servers full potential / FT searches locking tables
| ++ 1 row in set, 1 warning (0.06 sec) - Original Message - From: Justin [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, 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 of connections to the db, and then when the table is unlocked the connections start to filter through and usually they all finish and de-queue nicely but this last time it seemed there were 400-500 constant connections never actually going away.. the query it's self finished. but there was one right behind it to take it's place.. Almost like it's giving it's self a dos.. Is there any settings I can adjust on the server to help with this? or would it be more on the code side. As always when I restart the instance of mysql all goes back smoothly so it makes me wonder if it's something in the mysql config that is lagging for some reason. thanks. - Original Message - From: Michael Dykman [EMAIL PROTECTED] To: Justin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com 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 too complex to go into here (and are well documented elsewhere) your key buffer should be limited to around 2.5G max, and this is assuming a pure MyISAM implementation. There simply is no way a 32 bit build can make use of all that RAM, regardless of OS. - michael dykman On 8/28/07, Justin [EMAIL PROTECTED] wrote: 32bit, but I have all available memory.. MemTotal: 8179612 kB MemFree: 43684 kB on the box. I think the 4gb is only windows. All my tables are in myisam so if I was to set key_buffer_size=5500M That'd be acceptable? - Original Message - From: Mathieu Bruneau [EMAIL PROTECTED] To: Justin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com 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 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 calculation so you don't go over ~2.6G (why not 4Gb?, go read on that on the net) So the 2 most importants settings are: key_buffer_size (mainly myisam table) and/or innodb_buffer_pool_size (innodb table) Depending if you're using more innodb or myisam (or a mix) you'll tweak those pamareters differently, it's usually however not recommended to go over 4Gb for the key_buffer_size. MyIsam only stores the key into that buffer, so you don't have much index, not worth taking it too big for no reason. Innodb however can cache data as well, and will benefit from the biggest value possible. The server generate statistic that you can look to know the effect of that. If you are using phpmyadmin in the variables and status part you can see the index usage to guide you. You can have a look at the different my.cnf that comes with mysql distribution they put comment in there with interesting value for thumbs rule. Here the except for key_buffer_size and innodb_buffer_pool_size: # Size of the Key Buffer, used to cache index blocks for MyISAM tables. # Do not set it larger than 30% of your available memory, as some memory # is also required by the OS to cache rows. Even if you're not using # MyISAM tables, you should still set it to 8-64M as it will also be # used for internal temporary disk tables. key_buffer_size=2G # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and # row data. The bigger you set this the less disk I/O is needed to # access data in tables. On a dedicated database server you may set this # parameter up to 80% of the machine physical memory size. Do not set it # too large, though, because competition of the physical memory may # cause paging in the operating system. Note that on 32bit systems you # might be limited to 2-3.5G of user level memory per process, so do not # set it too high. innodb_buffer_pool_size=2G Regards, -- Mathieu Bruneau aka ROunofF === GPG keys available @ http://rounoff.darktech.org Justin a écrit : 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
Re: servers full potential / FT searches locking tables
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 since the server was started (look in the ROW OPERATIONS section). So your server isn't using InnoDB for any tables (at least none you've queried since starting the server). But you're still allocating some memory and other resources to it. If I were you I'd put the following in your /etc/my.cnf just to ensure InnoDB isn't interfering or complicating anything: skip_innodb Cheers Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 of connections to the db, and then when the table is unlocked the connections start to filter through and usually they all finish and de-queue nicely but this last time it seemed there were 400-500 constant connections never actually going away.. the query it's self finished. but there was one right behind it to take it's place.. Almost like it's giving it's self a dos.. Is there any settings I can adjust on the server to help with this? or would it be more on the code side. As always when I restart the instance of mysql all goes back smoothly so it makes me wonder if it's something in the mysql config that is lagging for some reason. thanks. - Original Message - From: Michael Dykman [EMAIL PROTECTED] To: Justin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com 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 too complex to go into here (and are well documented elsewhere) your key buffer should be limited to around 2.5G max, and this is assuming a pure MyISAM implementation. There simply is no way a 32 bit build can make use of all that RAM, regardless of OS. - michael dykman On 8/28/07, Justin [EMAIL PROTECTED] wrote: 32bit, but I have all available memory.. MemTotal: 8179612 kB MemFree: 43684 kB on the box. I think the 4gb is only windows. All my tables are in myisam so if I was to set key_buffer_size=5500M That'd be acceptable? - Original Message - From: Mathieu Bruneau [EMAIL PROTECTED] To: Justin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com 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 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 calculation so you don't go over ~2.6G (why not 4Gb?, go read on that on the net) So the 2 most importants settings are: key_buffer_size (mainly myisam table) and/or innodb_buffer_pool_size (innodb table) Depending if you're using more innodb or myisam (or a mix) you'll tweak those pamareters differently, it's usually however not recommended to go over 4Gb for the key_buffer_size. MyIsam only stores the key into that buffer, so you don't have much index, not worth taking it too big for no reason. Innodb however can cache data as well, and will benefit from the biggest value possible. The server generate statistic that you can look to know the effect of that. If you are using phpmyadmin in the variables and status part you can see the index usage to guide you. You can have a look at the different my.cnf that comes with mysql distribution they put comment in there with interesting value for thumbs rule. Here the except for key_buffer_size and innodb_buffer_pool_size: # Size of the Key Buffer, used to cache index blocks for MyISAM tables. # Do not set it larger than 30% of your available memory, as some memory # is also required by the OS to cache rows. Even if you're not using # MyISAM tables, you should still set it to 8-64M as it will also be # used for internal temporary disk tables. key_buffer_size=2G # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and # row data. The bigger you set this the less disk I/O is needed to # access data in tables. On a dedicated database server you may set this # parameter up to 80% of the machine physical memory size. Do not set it # too large, though, because competition of the physical memory may # cause paging in the operating system. Note that on 32bit systems you # might be limited to 2-3.5G of user level memory per process, so do not # set it too high. innodb_buffer_pool_size=2G Regards, -- Mathieu Bruneau aka ROunofF === GPG keys available @ http://rounoff.darktech.org Justin a écrit : 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 server is a dedicated MySQL server so I want all power to go to the server. It just seems to be laggy at times. And I want to be sure I've optimized to the fullest potential My biggest issue is with FT searches. Tables get locked during larger queries and I can't select anything when that happens. Is there any way
Re: servers full potential / FT searches locking tables
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 PROTECTED] wrote: 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 documented elsewhere) your key buffer should be limited to around 2.5G max, and this is assuming a pure MyISAM implementation. There simply is no way a 32 bit build can make use of all that RAM, regardless of OS. Hello Michael, Do you mean the entire mysqld server including its child,modules should only use 4G memory? If so,the config below for 32 bit OS is may wrong,is it?Thanks. key_buffer_size=2G innodb_buffer_pool_size=2G -- Ken Peng [EMAIL PROTECTED] -- http://www.fastmail.fm - The way an email service should be -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: servers full potential / FT searches locking tables
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 you will run into weird errors. Sorry,I mean is the entire mysql application limited to 4G? Or single configuration argument is limited to 4G? for example, config_directive_a 2G config_directive_b 2G config_directive_c 2G Though here I configured 6G totally,but each directive is 2G. Is this right for a 32bit system?Thanks. -- Ken Peng [EMAIL PROTECTED] -- http://www.fastmail.fm - I mean, what is it about a decent email service? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: servers full potential / FT searches locking tables
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 without reason the it's closer to 3.2 or 3.4.. situations vary) - md On 8/29/07, Ken Peng [EMAIL PROTECTED] wrote: 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 you will run into weird errors. Sorry,I mean is the entire mysql application limited to 4G? Or single configuration argument is limited to 4G? for example, config_directive_a 2G config_directive_b 2G config_directive_c 2G Though here I configured 6G totally,but each directive is 2G. Is this right for a 32bit system?Thanks. -- Ken Peng [EMAIL PROTECTED] -- http://www.fastmail.fm - I mean, what is it about a decent email service? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: servers full potential / FT searches locking tables
32bit, but I have all available memory.. MemTotal: 8179612 kB MemFree: 43684 kB on the box. I think the 4gb is only windows. All my tables are in myisam so if I was to set key_buffer_size=5500M That'd be acceptable? - Original Message - From: Mathieu Bruneau [EMAIL PROTECTED] To: Justin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com 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 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 calculation so you don't go over ~2.6G (why not 4Gb?, go read on that on the net) So the 2 most importants settings are: key_buffer_size (mainly myisam table) and/or innodb_buffer_pool_size (innodb table) Depending if you're using more innodb or myisam (or a mix) you'll tweak those pamareters differently, it's usually however not recommended to go over 4Gb for the key_buffer_size. MyIsam only stores the key into that buffer, so you don't have much index, not worth taking it too big for no reason. Innodb however can cache data as well, and will benefit from the biggest value possible. The server generate statistic that you can look to know the effect of that. If you are using phpmyadmin in the variables and status part you can see the index usage to guide you. You can have a look at the different my.cnf that comes with mysql distribution they put comment in there with interesting value for thumbs rule. Here the except for key_buffer_size and innodb_buffer_pool_size: # Size of the Key Buffer, used to cache index blocks for MyISAM tables. # Do not set it larger than 30% of your available memory, as some memory # is also required by the OS to cache rows. Even if you're not using # MyISAM tables, you should still set it to 8-64M as it will also be # used for internal temporary disk tables. key_buffer_size=2G # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and # row data. The bigger you set this the less disk I/O is needed to # access data in tables. On a dedicated database server you may set this # parameter up to 80% of the machine physical memory size. Do not set it # too large, though, because competition of the physical memory may # cause paging in the operating system. Note that on 32bit systems you # might be limited to 2-3.5G of user level memory per process, so do not # set it too high. innodb_buffer_pool_size=2G Regards, -- Mathieu Bruneau aka ROunofF === GPG keys available @ http://rounoff.darktech.org Justin a écrit : 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 server is a dedicated MySQL server so I want all power to go to the server. It just seems to be laggy at times. And I want to be sure I've optimized to the fullest potential My biggest issue is with FT searches. Tables get locked during larger queries and I can't select anything when that happens. Is there any way not to lock the tables on a Full Text search? (does that make sense?) thanks again for any insight Justin. Here's a dump of the my.cnf and the phpmyadmin dump of vars. /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock wait_timeout=60 default-character-set=utf8 max_allowed_packet = 3000M max_connections = 5000 ft_min_word_len=3 server-id=1 log-error = /var/log/mysql/error.log expire_logs_days = 3 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=0 [mysql.server] user=mysql [mysqld_safe] err-log=/var/log/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid auto increment increment 1 auto increment offset 1 automatic sp privileges ON back log 50 basedir / binlog cache size 32,768 bulk insert buffer size 8,388,608 character set client utf8 character set connection utf8 character set database utf8 character set filesystem binary character set results utf8 character set server utf8 character set system utf8 character sets dir /usr/share/mysql/charsets/ collation connection utf8_general_ci collation database utf8_general_ci collation server utf8_general_ci completion type 0 concurrent insert 1 connect timeout 5 datadir /var/lib/mysql/ date format %Y-%m-%d datetime format %Y-%m-%d %H:%i:%s default week format 0 delay key write ON delayed insert limit 100 delayed insert timeout 300 delayed queue size 1,000 div precision increment 4 engine condition pushdown OFF expire logs days 3 flush OFF flush
Re: servers full potential / FT searches locking tables
heh.. ok I'll throw that at my system tech. and see what it's going to take to get a 64bit version of an OS. for now I'll limit to 2.5 thanks michael. - Original Message - From: Michael Dykman [EMAIL PROTECTED] To: Justin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com 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 too complex to go into here (and are well documented elsewhere) your key buffer should be limited to around 2.5G max, and this is assuming a pure MyISAM implementation. There simply is no way a 32 bit build can make use of all that RAM, regardless of OS. - michael dykman On 8/28/07, Justin [EMAIL PROTECTED] wrote: 32bit, but I have all available memory.. MemTotal: 8179612 kB MemFree: 43684 kB on the box. I think the 4gb is only windows. All my tables are in myisam so if I was to set key_buffer_size=5500M That'd be acceptable? - Original Message - From: Mathieu Bruneau [EMAIL PROTECTED] To: Justin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com 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 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 calculation so you don't go over ~2.6G (why not 4Gb?, go read on that on the net) So the 2 most importants settings are: key_buffer_size (mainly myisam table) and/or innodb_buffer_pool_size (innodb table) Depending if you're using more innodb or myisam (or a mix) you'll tweak those pamareters differently, it's usually however not recommended to go over 4Gb for the key_buffer_size. MyIsam only stores the key into that buffer, so you don't have much index, not worth taking it too big for no reason. Innodb however can cache data as well, and will benefit from the biggest value possible. The server generate statistic that you can look to know the effect of that. If you are using phpmyadmin in the variables and status part you can see the index usage to guide you. You can have a look at the different my.cnf that comes with mysql distribution they put comment in there with interesting value for thumbs rule. Here the except for key_buffer_size and innodb_buffer_pool_size: # Size of the Key Buffer, used to cache index blocks for MyISAM tables. # Do not set it larger than 30% of your available memory, as some memory # is also required by the OS to cache rows. Even if you're not using # MyISAM tables, you should still set it to 8-64M as it will also be # used for internal temporary disk tables. key_buffer_size=2G # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and # row data. The bigger you set this the less disk I/O is needed to # access data in tables. On a dedicated database server you may set this # parameter up to 80% of the machine physical memory size. Do not set it # too large, though, because competition of the physical memory may # cause paging in the operating system. Note that on 32bit systems you # might be limited to 2-3.5G of user level memory per process, so do not # set it too high. innodb_buffer_pool_size=2G Regards, -- Mathieu Bruneau aka ROunofF === GPG keys available @ http://rounoff.darktech.org Justin a écrit : 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 server is a dedicated MySQL server so I want all power to go to the server. It just seems to be laggy at times. And I want to be sure I've optimized to the fullest potential My biggest issue is with FT searches. Tables get locked during larger queries and I can't select anything when that happens. Is there any way not to lock the tables on a Full Text search? (does that make sense?) thanks again for any insight Justin. Here's a dump of the my.cnf and the phpmyadmin dump of vars. /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock wait_timeout=60 default-character-set=utf8 max_allowed_packet = 3000M max_connections = 5000 ft_min_word_len=3 server-id=1 log-error = /var/log/mysql/error.log expire_logs_days = 3 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=0 [mysql.server] user=mysql [mysqld_safe] err-log=/var/log/mysql/mysqld.log pid
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 too complex to go into here (and are well documented elsewhere) your key buffer should be limited to around 2.5G max, and this is assuming a pure MyISAM implementation. There simply is no way a 32 bit build can make use of all that RAM, regardless of OS. - michael dykman On 8/28/07, Justin [EMAIL PROTECTED] wrote: 32bit, but I have all available memory.. MemTotal: 8179612 kB MemFree: 43684 kB on the box. I think the 4gb is only windows. All my tables are in myisam so if I was to set key_buffer_size=5500M That'd be acceptable? - Original Message - From: Mathieu Bruneau [EMAIL PROTECTED] To: Justin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com 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 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 calculation so you don't go over ~2.6G (why not 4Gb?, go read on that on the net) So the 2 most importants settings are: key_buffer_size (mainly myisam table) and/or innodb_buffer_pool_size (innodb table) Depending if you're using more innodb or myisam (or a mix) you'll tweak those pamareters differently, it's usually however not recommended to go over 4Gb for the key_buffer_size. MyIsam only stores the key into that buffer, so you don't have much index, not worth taking it too big for no reason. Innodb however can cache data as well, and will benefit from the biggest value possible. The server generate statistic that you can look to know the effect of that. If you are using phpmyadmin in the variables and status part you can see the index usage to guide you. You can have a look at the different my.cnf that comes with mysql distribution they put comment in there with interesting value for thumbs rule. Here the except for key_buffer_size and innodb_buffer_pool_size: # Size of the Key Buffer, used to cache index blocks for MyISAM tables. # Do not set it larger than 30% of your available memory, as some memory # is also required by the OS to cache rows. Even if you're not using # MyISAM tables, you should still set it to 8-64M as it will also be # used for internal temporary disk tables. key_buffer_size=2G # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and # row data. The bigger you set this the less disk I/O is needed to # access data in tables. On a dedicated database server you may set this # parameter up to 80% of the machine physical memory size. Do not set it # too large, though, because competition of the physical memory may # cause paging in the operating system. Note that on 32bit systems you # might be limited to 2-3.5G of user level memory per process, so do not # set it too high. innodb_buffer_pool_size=2G Regards, -- Mathieu Bruneau aka ROunofF === GPG keys available @ http://rounoff.darktech.org Justin a écrit : 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 server is a dedicated MySQL server so I want all power to go to the server. It just seems to be laggy at times. And I want to be sure I've optimized to the fullest potential My biggest issue is with FT searches. Tables get locked during larger queries and I can't select anything when that happens. Is there any way not to lock the tables on a Full Text search? (does that make sense?) thanks again for any insight Justin. Here's a dump of the my.cnf and the phpmyadmin dump of vars. /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock wait_timeout=60 default-character-set=utf8 max_allowed_packet = 3000M max_connections = 5000 ft_min_word_len=3 server-id=1 log-error = /var/log/mysql/error.log expire_logs_days = 3 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=0 [mysql.server] user=mysql [mysqld_safe] err-log=/var/log/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid auto increment increment 1 auto increment offset 1 automatic sp privileges ON back log 50 basedir / binlog cache size 32,768 bulk insert buffer size 8,388,608 character set client utf8 character set
Re: servers full potential / FT searches locking tables
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 documented elsewhere) your key buffer should be limited to around 2.5G max, and this is assuming a pure MyISAM implementation. There simply is no way a 32 bit build can make use of all that RAM, regardless of OS. Hello Michael, Do you mean the entire mysqld server including its child,modules should only use 4G memory? If so,the config below for 32 bit OS is may wrong,is it?Thanks. key_buffer_size=2G innodb_buffer_pool_size=2G -- Ken Peng [EMAIL PROTECTED] -- http://www.fastmail.fm - The way an email service should be -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
servers full potential / FT searches locking tables
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 server is a dedicated MySQL server so I want all power to go to the server. It just seems to be laggy at times. And I want to be sure I've optimized to the fullest potential My biggest issue is with FT searches. Tables get locked during larger queries and I can't select anything when that happens. Is there any way not to lock the tables on a Full Text search? (does that make sense?) thanks again for any insight Justin. Here's a dump of the my.cnf and the phpmyadmin dump of vars. /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock wait_timeout=60 default-character-set=utf8 max_allowed_packet = 3000M max_connections = 5000 ft_min_word_len=3 server-id=1 log-error = /var/log/mysql/error.log expire_logs_days = 3 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=0 [mysql.server] user=mysql [mysqld_safe] err-log=/var/log/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid auto increment increment 1 auto increment offset 1 automatic sp privileges ON back log 50 basedir / binlog cache size 32,768 bulk insert buffer size 8,388,608 character set client utf8 character set connection utf8 character set database utf8 character set filesystem binary character set results utf8 character set server utf8 character set system utf8 character sets dir /usr/share/mysql/charsets/ collation connection utf8_general_ci collation database utf8_general_ci collation server utf8_general_ci completion type 0 concurrent insert 1 connect timeout 5 datadir /var/lib/mysql/ date format %Y-%m-%d datetime format %Y-%m-%d %H:%i:%s default week format 0 delay key write ON delayed insert limit 100 delayed insert timeout 300 delayed queue size 1,000 div precision increment 4 engine condition pushdown OFF expire logs days 3 flush OFF flush time 0 ft boolean syntax + -()~*:| ft max word len 84 ft min word len 3 ft query expansion limit 20 ft stopword file (built-in) group concat max len 1,024 have archive YES have bdb NO have blackhole engine NO have compress YES have crypt YES have csv NO have dynamic loading YES have example engine NO have federated engine NO have geometry YES have innodb YES have isam NO have merge engine YES have ndbcluster NO have openssl DISABLED have query cache YES have raid NO have rtree keys YES have symlink YES init connect init file init slave innodb additional mem pool size 1,048,576 innodb autoextend increment 8 innodb buffer pool awe mem mb 0 innodb buffer pool size 8,388,608 innodb checksums ON innodb commit concurrency 0 innodb concurrency tickets 500 innodb data file path ibdata1:10M:autoextend innodb data home dir innodb doublewrite ON innodb fast shutdown 1 innodb file io threads 4 innodb file per table OFF innodb flush log at trx commit 1 innodb flush method innodb force recovery 0 innodb lock wait timeout 50 innodb locks unsafe for binlog OFF innodb log arch dir innodb log archive OFF innodb log buffer size 1,048,576 innodb log file size 5,242,880 innodb log files in group 2 innodb log group home dir ./ innodb max dirty pages pct 90 innodb max purge lag 0 innodb mirrored log groups 1 innodb open files 300 innodb support xa ON innodb sync spin loops 20 innodb table locks ON innodb thread concurrency 8 innodb thread sleep delay 10,000 interactive timeout 28,800 join buffer size 131,072 key buffer size 8,388,600 key cache age threshold 300 key cache block size 1,024 key cache division limit 100 language /usr/share/mysql/english/ large files support ON large page size 0 large pages OFF lc time names en_US license GPL local infile ON locked in memory OFF log OFF log bin OFF log bin trust function creators OFF log error /var/log/mysql/error.log log queries not using indexes OFF log slave updates OFF log slow queries OFF log warnings 1 long query time 10 low priority updates OFF lower case file system OFF lower case table names 0 max allowed packet 1,073,740,800 max binlog cache size 4,294,967,295 max binlog size 1,073,741,824 max connect errors 10 max connections 5,000 max delayed threads 20 max error count 64 max heap table size 16,777,216 max insert delayed threads 20 max join size 18446744073709551615 max length for sort data 1,024 max prepared stmt count 16,382 max relay log size 0 max seeks for key 4,294,967,295 max sort length 1,024 max sp recursion depth 0 max tmp tables 32 max user connections 0 max write lock count 4,294,967,295 multi range count 256 myisam data pointer size 6 myisam max sort file size 2,147,483,647
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. 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 server is a dedicated MySQL server so I want all power to go to the server. It just seems to be laggy at times. And I want to be sure I've optimized to the fullest potential My biggest issue is with FT searches. Tables get locked during larger queries and I can't select anything when that happens. Is there any way not to lock the tables on a Full Text search? (does that make sense?) thanks again for any insight Justin. Here's a dump of the my.cnf and the phpmyadmin dump of vars. /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock wait_timeout=60 default-character-set=utf8 max_allowed_packet = 3000M max_connections = 5000 ft_min_word_len=3 server-id=1 log-error = /var/log/mysql/error.log expire_logs_days = 3 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=0 [mysql.server] user=mysql [mysqld_safe] err-log=/var/log/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid auto increment increment 1 auto increment offset 1 automatic sp privileges ON back log 50 basedir / binlog cache size 32,768 bulk insert buffer size 8,388,608 character set client utf8 character set connection utf8 character set database utf8 character set filesystem binary character set results utf8 character set server utf8 character set system utf8 character sets dir /usr/share/mysql/charsets/ collation connection utf8_general_ci collation database utf8_general_ci collation server utf8_general_ci completion type 0 concurrent insert 1 connect timeout 5 datadir /var/lib/mysql/ date format %Y-%m-%d datetime format %Y-%m-%d %H:%i:%s default week format 0 delay key write ON delayed insert limit 100 delayed insert timeout 300 delayed queue size 1,000 div precision increment 4 engine condition pushdown OFF expire logs days 3 flush OFF flush time 0 ft boolean syntax + -()~*:| ft max word len 84 ft min word len 3 ft query expansion limit 20 ft stopword file (built-in) group concat max len 1,024 have archive YES have bdb NO have blackhole engine NO have compress YES have crypt YES have csv NO have dynamic loading YES have example engine NO have federated engine NO have geometry YES have innodb YES have isam NO have merge engine YES have ndbcluster NO have openssl DISABLED have query cache YES have raid NO have rtree keys YES have symlink YES init connect init file init slave innodb additional mem pool size 1,048,576 innodb autoextend increment 8 innodb buffer pool awe mem mb 0 innodb buffer pool size 8,388,608 innodb checksums ON innodb commit concurrency 0 innodb concurrency tickets 500 innodb data file path ibdata1:10M:autoextend innodb data home dir innodb doublewrite ON innodb fast shutdown 1 innodb file io threads 4 innodb file per table OFF innodb flush log at trx commit 1 innodb flush method innodb force recovery 0 innodb lock wait timeout 50 innodb locks unsafe for binlog OFF innodb log arch dir innodb log archive OFF innodb log buffer size 1,048,576 innodb log file size 5,242,880 innodb log files in group 2 innodb log group home dir ./ innodb max dirty pages pct 90 innodb max purge lag 0 innodb mirrored log groups 1 innodb open files 300 innodb support xa ON innodb sync spin loops 20 innodb table locks ON innodb thread concurrency 8 innodb thread sleep delay 10,000 interactive timeout 28,800 join buffer size 131,072 key buffer size 8,388,600 key cache age threshold 300 key cache block size 1,024 key cache division limit 100 language /usr/share/mysql/english/ large files support ON large page size 0 large pages OFF lc time names en_US license GPL local infile ON locked in memory OFF log OFF log bin OFF log bin trust function creators OFF log error /var/log/mysql/error.log log queries not using indexes OFF log slave updates OFF log slow queries OFF log warnings 1 long query time 10 low priority updates OFF lower case file system OFF lower case table names 0 max allowed packet 1,073,740,800 max binlog cache size 4,294,967,295 max binlog size 1,073,741,824 max connect errors 10 max connections 5,000 max delayed threads 20 max error count 64 max heap table size 16,777,216 max insert delayed threads 20 max join size 18446744073709551615 max length for sort data 1,024 max prepared stmt count 16,382 max relay log size 0 max seeks for key 4,294,967,295 max sort length 1,024 max sp recursion depth 0 max tmp tables 32 max user
Re: servers full potential / FT searches locking tables
SELECTs do lock the tables implicitly. According to Page 400 (Section 28.1 : Locking Concepts) of MySQL 5.0 Certification Study Guide (ISBN 0-672-32812-7), here is what the first bulletpoint says under the heading A lock on data can be acquired implicitly or explicitly: For a client that does nothing special to acquires locks, the MySQL server implicitly acquires locks as necessary to process the client's statments sdafely. For example, the server acquires a read lock when the client issues a SELECT statement and a write lock when the client issues an INSERT statement. Implicit locks are acquired only for the duration of a single statement. - Original Message - From: Jay Pipes [EMAIL PROTECTED] To: Justin [EMAIL PROTECTED] Cc: mysql@lists.mysql.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 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 server is a dedicated MySQL server so I want all power to go to the server. It just seems to be laggy at times. And I want to be sure I've optimized to the fullest potential My biggest issue is with FT searches. Tables get locked during larger queries and I can't select anything when that happens. Is there any way not to lock the tables on a Full Text search? (does that make sense?) thanks again for any insight Justin. Here's a dump of the my.cnf and the phpmyadmin dump of vars. /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock wait_timeout=60 default-character-set=utf8 max_allowed_packet = 3000M max_connections = 5000 ft_min_word_len=3 server-id=1 log-error = /var/log/mysql/error.log expire_logs_days = 3 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=0 [mysql.server] user=mysql [mysqld_safe] err-log=/var/log/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid auto increment increment 1 auto increment offset 1 automatic sp privileges ON back log 50 basedir / binlog cache size 32,768 bulk insert buffer size 8,388,608 character set client utf8 character set connection utf8 character set database utf8 character set filesystem binary character set results utf8 character set server utf8 character set system utf8 character sets dir /usr/share/mysql/charsets/ collation connection utf8_general_ci collation database utf8_general_ci collation server utf8_general_ci completion type 0 concurrent insert 1 connect timeout 5 datadir /var/lib/mysql/ date format %Y-%m-%d datetime format %Y-%m-%d %H:%i:%s default week format 0 delay key write ON delayed insert limit 100 delayed insert timeout 300 delayed queue size 1,000 div precision increment 4 engine condition pushdown OFF expire logs days 3 flush OFF flush time 0 ft boolean syntax + -()~*:| ft max word len 84 ft min word len 3 ft query expansion limit 20 ft stopword file (built-in) group concat max len 1,024 have archive YES have bdb NO have blackhole engine NO have compress YES have crypt YES have csv NO have dynamic loading YES have example engine NO have federated engine NO have geometry YES have innodb YES have isam NO have merge engine YES have ndbcluster NO have openssl DISABLED have query cache YES have raid NO have rtree keys YES have symlink YES init connect init file init slave innodb additional mem pool size 1,048,576 innodb autoextend increment 8 innodb buffer pool awe mem mb 0 innodb buffer pool size 8,388,608 innodb checksums ON innodb commit concurrency 0 innodb concurrency tickets 500 innodb data file path ibdata1:10M:autoextend innodb data home dir innodb doublewrite ON innodb fast shutdown 1 innodb file io threads 4 innodb file per table OFF innodb flush log at trx commit 1 innodb flush method innodb force recovery 0 innodb lock wait timeout 50 innodb locks unsafe for binlog OFF innodb log arch dir innodb log archive OFF innodb log buffer size 1,048,576 innodb log file size 5,242,880 innodb log files in group 2 innodb log group home dir ./ innodb max dirty pages pct 90 innodb max purge lag 0 innodb mirrored log groups 1 innodb open files 300 innodb support xa ON innodb sync spin loops 20 innodb table locks ON innodb thread concurrency 8 innodb thread sleep delay 10,000 interactive timeout 28,800 join buffer size 131,072 key
Re: servers full potential / FT searches locking tables
sorry.. you're right.. that came out wrong.. actually shouldn't of even of put that in there like that.. The locking is some other issue that I can't for the life of me remember what the query was.. I know it wasn't an Update but it was copying to a tmp table then sorting the result which was locking the table from the process I was looking at. I'll keep an eye on it to see if I Can replicate it again.. I just want to make sure I've got the server config'd right to utilize everything I have. thanks for the response - Original Message - From: Jay Pipes [EMAIL PROTECTED] To: Justin [EMAIL PROTECTED] Cc: mysql@lists.mysql.com 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 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 (2.0GHz Intel Dual-Core Xeon) what my question is.. is am I utilizing the servers potential with the following as my settings. The server is a dedicated MySQL server so I want all power to go to the server. It just seems to be laggy at times. And I want to be sure I've optimized to the fullest potential My biggest issue is with FT searches. Tables get locked during larger queries and I can't select anything when that happens. Is there any way not to lock the tables on a Full Text search? (does that make sense?) thanks again for any insight Justin. Here's a dump of the my.cnf and the phpmyadmin dump of vars. /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock wait_timeout=60 default-character-set=utf8 max_allowed_packet = 3000M max_connections = 5000 ft_min_word_len=3 server-id=1 log-error = /var/log/mysql/error.log expire_logs_days = 3 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=0 [mysql.server] user=mysql [mysqld_safe] err-log=/var/log/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid auto increment increment 1 auto increment offset 1 automatic sp privileges ON back log 50 basedir / binlog cache size 32,768 bulk insert buffer size 8,388,608 character set client utf8 character set connection utf8 character set database utf8 character set filesystem binary character set results utf8 character set server utf8 character set system utf8 character sets dir /usr/share/mysql/charsets/ collation connection utf8_general_ci collation database utf8_general_ci collation server utf8_general_ci completion type 0 concurrent insert 1 connect timeout 5 datadir /var/lib/mysql/ date format %Y-%m-%d datetime format %Y-%m-%d %H:%i:%s default week format 0 delay key write ON delayed insert limit 100 delayed insert timeout 300 delayed queue size 1,000 div precision increment 4 engine condition pushdown OFF expire logs days 3 flush OFF flush time 0 ft boolean syntax + -()~*:| ft max word len 84 ft min word len 3 ft query expansion limit 20 ft stopword file (built-in) group concat max len 1,024 have archive YES have bdb NO have blackhole engine NO have compress YES have crypt YES have csv NO have dynamic loading YES have example engine NO have federated engine NO have geometry YES have innodb YES have isam NO have merge engine YES have ndbcluster NO have openssl DISABLED have query cache YES have raid NO have rtree keys YES have symlink YES init connect init file init slave innodb additional mem pool size 1,048,576 innodb autoextend increment 8 innodb buffer pool awe mem mb 0 innodb buffer pool size 8,388,608 innodb checksums ON innodb commit concurrency 0 innodb concurrency tickets 500 innodb data file path ibdata1:10M:autoextend innodb data home dir innodb doublewrite ON innodb fast shutdown 1 innodb file io threads 4 innodb file per table OFF innodb flush log at trx commit 1 innodb flush method innodb force recovery 0 innodb lock wait timeout 50 innodb locks unsafe for binlog OFF innodb log arch dir innodb log archive OFF innodb log buffer size 1,048,576 innodb log file size 5,242,880 innodb log files in group 2 innodb log group home dir ./ innodb max dirty pages pct 90 innodb max purge lag 0 innodb mirrored log groups 1 innodb open files 300 innodb support xa ON innodb sync spin loops 20 innodb table locks ON innodb thread concurrency 8 innodb thread sleep delay 10,000 interactive timeout 28,800 join buffer size 131,072 key buffer size 8,388,600 key cache age threshold 300 key cache block size 1,024 key cache division limit 100 language /usr/share/mysql/english/ large files support ON large page size 0 large pages OFF lc time names en_US license GPL local infile ON locked in memory OFF log OFF log bin
Re: servers full potential / FT searches locking tables
A read lock does not prevent other reads. Rolando Edwards wrote: SELECTs do lock the tables implicitly. According to Page 400 (Section 28.1 : Locking Concepts) of MySQL 5.0 Certification Study Guide (ISBN 0-672-32812-7), here is what the first bulletpoint says under the heading A lock on data can be acquired implicitly or explicitly: For a client that does nothing special to acquires locks, the MySQL server implicitly acquires locks as necessary to process the client's statments sdafely. For example, the server acquires a read lock when the client issues a SELECT statement and a write lock when the client issues an INSERT statement. Implicit locks are acquired only for the duration of a single statement. - Original Message - From: Jay Pipes [EMAIL PROTECTED] To: Justin [EMAIL PROTECTED] Cc: mysql@lists.mysql.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 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 server is a dedicated MySQL server so I want all power to go to the server. It just seems to be laggy at times. And I want to be sure I've optimized to the fullest potential My biggest issue is with FT searches. Tables get locked during larger queries and I can't select anything when that happens. Is there any way not to lock the tables on a Full Text search? (does that make sense?) thanks again for any insight Justin. Here's a dump of the my.cnf and the phpmyadmin dump of vars. /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock wait_timeout=60 default-character-set=utf8 max_allowed_packet = 3000M max_connections = 5000 ft_min_word_len=3 server-id=1 log-error = /var/log/mysql/error.log expire_logs_days = 3 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=0 [mysql.server] user=mysql [mysqld_safe] err-log=/var/log/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid auto increment increment 1 auto increment offset 1 automatic sp privileges ON back log 50 basedir / binlog cache size 32,768 bulk insert buffer size 8,388,608 character set client utf8 character set connection utf8 character set database utf8 character set filesystem binary character set results utf8 character set server utf8 character set system utf8 character sets dir /usr/share/mysql/charsets/ collation connection utf8_general_ci collation database utf8_general_ci collation server utf8_general_ci completion type 0 concurrent insert 1 connect timeout 5 datadir /var/lib/mysql/ date format %Y-%m-%d datetime format %Y-%m-%d %H:%i:%s default week format 0 delay key write ON delayed insert limit 100 delayed insert timeout 300 delayed queue size 1,000 div precision increment 4 engine condition pushdown OFF expire logs days 3 flush OFF flush time 0 ft boolean syntax + -()~*:| ft max word len 84 ft min word len 3 ft query expansion limit 20 ft stopword file (built-in) group concat max len 1,024 have archive YES have bdb NO have blackhole engine NO have compress YES have crypt YES have csv NO have dynamic loading YES have example engine NO have federated engine NO have geometry YES have innodb YES have isam NO have merge engine YES have ndbcluster NO have openssl DISABLED have query cache YES have raid NO have rtree keys YES have symlink YES init connect init file init slave innodb additional mem pool size 1,048,576 innodb autoextend increment 8 innodb buffer pool awe mem mb 0 innodb buffer pool size 8,388,608 innodb checksums ON innodb commit concurrency 0 innodb concurrency tickets 500 innodb data file path ibdata1:10M:autoextend innodb data home dir innodb doublewrite ON innodb fast shutdown 1 innodb file io threads 4 innodb file per table OFF innodb flush log at trx commit 1 innodb flush method innodb force recovery 0 innodb lock wait timeout 50 innodb locks unsafe for binlog OFF innodb log arch dir innodb log archive OFF innodb log buffer size 1,048,576 innodb log file size 5,242,880 innodb log files in group 2 innodb log group home dir ./ innodb max dirty pages pct 90 innodb max purge lag 0 innodb mirrored log groups 1 innodb open files 300 innodb support xa ON innodb sync spin loops 20 innodb table locks ON innodb thread concurrency 8 innodb thread sleep delay 10,000 interactive timeout 28,800 join buffer size 131,072 key buffer size 8,388,600 key cache age threshold 300 key cache block size 1,024
Re: servers full potential / FT searches locking tables
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 are performed and then go away.. and the sites load fine.. 3 times today this has happened and I'm trying to figure out what could be the cause of it. if I restart MySQL everything is good. Anyone have any ideas or any ideas on how I can trace where the culprit would be.. it's a LAMP backend.. - Original Message - From: Jay Pipes [EMAIL PROTECTED] To: Rolando Edwards [EMAIL PROTECTED] Cc: mysql@lists.mysql.com; Justin [EMAIL PROTECTED] Sent: Monday, August 27, 2007 3:03 PM Subject: Re: servers full potential / FT searches locking tables A read lock does not prevent other reads. Rolando Edwards wrote: SELECTs do lock the tables implicitly. According to Page 400 (Section 28.1 : Locking Concepts) of MySQL 5.0 Certification Study Guide (ISBN 0-672-32812-7), here is what the first bulletpoint says under the heading A lock on data can be acquired implicitly or explicitly: For a client that does nothing special to acquires locks, the MySQL server implicitly acquires locks as necessary to process the client's statments sdafely. For example, the server acquires a read lock when the client issues a SELECT statement and a write lock when the client issues an INSERT statement. Implicit locks are acquired only for the duration of a single statement. - Original Message - From: Jay Pipes [EMAIL PROTECTED] To: Justin [EMAIL PROTECTED] Cc: mysql@lists.mysql.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 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 server is a dedicated MySQL server so I want all power to go to the server. It just seems to be laggy at times. And I want to be sure I've optimized to the fullest potential My biggest issue is with FT searches. Tables get locked during larger queries and I can't select anything when that happens. Is there any way not to lock the tables on a Full Text search? (does that make sense?) thanks again for any insight Justin. Here's a dump of the my.cnf and the phpmyadmin dump of vars. /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock wait_timeout=60 default-character-set=utf8 max_allowed_packet = 3000M max_connections = 5000 ft_min_word_len=3 server-id=1 log-error = /var/log/mysql/error.log expire_logs_days = 3 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=0 [mysql.server] user=mysql [mysqld_safe] err-log=/var/log/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid auto increment increment 1 auto increment offset 1 automatic sp privileges ON back log 50 basedir / binlog cache size 32,768 bulk insert buffer size 8,388,608 character set client utf8 character set connection utf8 character set database utf8 character set filesystem binary character set results utf8 character set server utf8 character set system utf8 character sets dir /usr/share/mysql/charsets/ collation connection utf8_general_ci collation database utf8_general_ci collation server utf8_general_ci completion type 0 concurrent insert 1 connect timeout 5 datadir /var/lib/mysql/ date format %Y-%m-%d datetime format %Y-%m-%d %H:%i:%s default week format 0 delay key write ON delayed insert limit 100 delayed insert timeout 300 delayed queue size 1,000 div precision increment 4 engine condition pushdown OFF expire logs days 3 flush OFF flush time 0 ft boolean syntax + -()~*:| ft max word len 84 ft min word len 3 ft query expansion limit 20 ft stopword file (built-in) group concat max len 1,024 have archive YES have bdb NO have blackhole engine NO have compress YES have crypt YES have csv NO have dynamic loading YES have example engine NO have federated engine NO have geometry YES have innodb YES have isam NO have merge engine YES have ndbcluster NO have openssl DISABLED have query cache YES have raid NO have rtree keys YES have symlink YES init connect init file init slave innodb additional mem pool size 1,048,576 innodb autoextend increment 8 innodb buffer pool awe mem mb 0 innodb buffer pool size 8,388,608 innodb checksums ON innodb commit concurrency 0 innodb concurrency tickets 500 innodb
Re: servers full potential / FT searches locking tables
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 are performed and then go away.. and the sites load fine.. 3 times today this has happened and I'm trying to figure out what could be the cause of it. if I restart MySQL everything is good. Anyone have any ideas or any ideas on how I can trace where the culprit would be.. it's a LAMP backend.. Are you classing insert...select or create tableselect as selects? They have very different locking effects. Is the database in replication? if so is it a master or slave? Do you have NFS mounts anywhere on the database server? Are you using exclusively innodb tables? if not the source is probably table contention. Get a couple of 'show full processlist', 'show innodb status' query outputs during the lockups and run vmstat 1 -S M in another terminal. With the outputs from both you've something to work with. Nigel Wood -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 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 calculation so you don't go over ~2.6G (why not 4Gb?, go read on that on the net) So the 2 most importants settings are: key_buffer_size (mainly myisam table) and/or innodb_buffer_pool_size (innodb table) Depending if you're using more innodb or myisam (or a mix) you'll tweak those pamareters differently, it's usually however not recommended to go over 4Gb for the key_buffer_size. MyIsam only stores the key into that buffer, so you don't have much index, not worth taking it too big for no reason. Innodb however can cache data as well, and will benefit from the biggest value possible. The server generate statistic that you can look to know the effect of that. If you are using phpmyadmin in the variables and status part you can see the index usage to guide you. You can have a look at the different my.cnf that comes with mysql distribution they put comment in there with interesting value for thumbs rule. Here the except for key_buffer_size and innodb_buffer_pool_size: # Size of the Key Buffer, used to cache index blocks for MyISAM tables. # Do not set it larger than 30% of your available memory, as some memory # is also required by the OS to cache rows. Even if you're not using # MyISAM tables, you should still set it to 8-64M as it will also be # used for internal temporary disk tables. key_buffer_size=2G # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and # row data. The bigger you set this the less disk I/O is needed to # access data in tables. On a dedicated database server you may set this # parameter up to 80% of the machine physical memory size. Do not set it # too large, though, because competition of the physical memory may # cause paging in the operating system. Note that on 32bit systems you # might be limited to 2-3.5G of user level memory per process, so do not # set it too high. innodb_buffer_pool_size=2G Regards, -- Mathieu Bruneau aka ROunofF === GPG keys available @ http://rounoff.darktech.org Justin a écrit : 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 server is a dedicated MySQL server so I want all power to go to the server. It just seems to be laggy at times. And I want to be sure I've optimized to the fullest potential My biggest issue is with FT searches. Tables get locked during larger queries and I can't select anything when that happens. Is there any way not to lock the tables on a Full Text search? (does that make sense?) thanks again for any insight Justin. Here's a dump of the my.cnf and the phpmyadmin dump of vars. /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock wait_timeout=60 default-character-set=utf8 max_allowed_packet = 3000M max_connections = 5000 ft_min_word_len=3 server-id=1 log-error = /var/log/mysql/error.log expire_logs_days = 3 # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=0 [mysql.server] user=mysql [mysqld_safe] err-log=/var/log/mysql/mysqld.log pid-file=/var/run/mysqld/mysqld.pid auto increment increment 1 auto increment offset 1 automatic sp privileges ON back log 50 basedir / binlog cache size 32,768 bulk insert buffer size 8,388,608 character set client utf8 character set connection utf8 character set database utf8 character set filesystem binary character set results utf8 character set server utf8 character set system utf8 character sets dir /usr/share/mysql/charsets/ collation connection utf8_general_ci collation database utf8_general_ci collation server utf8_general_ci completion type 0 concurrent insert 1 connect timeout 5 datadir /var/lib/mysql/ date format %Y-%m-%d datetime format %Y-%m-%d %H:%i:%s default week format 0 delay key write ON delayed insert limit 100 delayed insert timeout 300 delayed queue size 1,000 div precision increment 4 engine condition pushdown OFF expire logs days 3 flush OFF flush time 0 ft boolean syntax + -()~*:| ft max word len 84 ft min word len 3 ft query expansion limit 20 ft stopword file (built-in) group concat max len 1,024 have archive YES have bdb NO have blackhole engine NO have compress YES have crypt YES have csv NO have dynamic loading YES have example engine NO have federated engine NO have geometry YES have innodb YES have isam NO have merge engine YES have ndbcluster NO have openssl DISABLED have query
backup locking tables on 5.0.24
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 when I kill the dump, I see it trying to issue an unlock tables sql and erroring due to the connection being killed is this not the proper way to keep it from locking any table? All we do is selects, there are no inserts/replaces/updates going on, during a backup -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup locking tables on 5.0.24
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 mysqldump -t -f --skip-add-locks database outfile when I kill the dump, I see it trying to issue an unlock tables sql and erroring due to the connection being killed is this not the proper way to keep it from locking any table? All we do is selects, there are no inserts/replaces/updates going on, during a backup The whole table has to be locked so you get a consistent state for the table. Not sure why you're only seeing this after your upgrade though. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup locking tables on 5.0.24
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 database outfile mysqldump -t -f --skip-add-locks database outfile when I kill the dump, I see it trying to issue an unlock tables sql and erroring due to the connection being killed is this not the proper way to keep it from locking any table? All we do is selects, there are no inserts/replaces/updates going on, during a backup The whole table has to be locked so you get a consistent state for the table. Not sure why you're only seeing this after your upgrade though. My understanding was it was a read lock, so concurrent selects would still work -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup locking tables on 5.0.24
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 --skip-add-locks database outfile mysqldump -t -f --skip-add-locks database outfile when I kill the dump, I see it trying to issue an unlock tables sql and erroring due to the connection being killed is this not the proper way to keep it from locking any table? All we do is selects, there are no inserts/replaces/updates going on, during a backup The whole table has to be locked so you get a consistent state for the table. Not sure why you're only seeing this after your upgrade though. My understanding was it was a read lock, so concurrent selects would still work Do you have any stored procedures? I wonder if any bugs were fixed regarding those. I could select my_procedure(); and it does an update of a table's view count or something (think forum post views type scenario). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Locking tables in MyIsam
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 wierd reason it affects all tables in all databases ! I am using Mysql 4.0.12, Let me know thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Locking tables
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 unlock the tables. Remember that the client that locks the table *must remain connected* while you copy the table files, because any locks are released automatically when the client connection ends. Have a look at the mysqlhotcopy source for any idea of how it uses this approach. Essentially, what it does is open a connection to the server, tells the server to lock the tables, and then while the tables are locked, goes behind the server's back to directly copy table files. (This is why mysqlhotcopy must be run on the server host. It's also (I believe) why it doesn't work on WIndows: Windows file locking semantics do not allow you to copy a file while the server has it locked.) By the way, it's difficult to see how automysqlbackup could corrupt any tables. A quick look through it seems to indicate that it only uses mysqldump to perform backups. Yes the script is using mysqldump with --opt , so these seems like what to expect for mysqlhotcopy. When you read the MYSQL docs, mysqlhotcopy apears to be the tool for choice for live servers. Maybe I'm not interpeting the documentation correctly, maybe it refers to having to be on the SAME server to run. I want the automysqlbackup script to run from cron at 4am..one site in question is very busy still during that time. What does the user experience who may in the middle of a write action to the database as the server starts the mysqldump --opt process? How can I do myisamchk with tables locked to make sure the tables are not corrupt before I try this shell script again? I want to make sure I start clean before I try it so that if it repeats the problem I can relate the problem to the action just performed. Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Locking tables
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. While the lock remains in place, copy the table files. Then unlock the tables. Remember that the client that locks the table *must remain connected* while you copy the table files, because any locks are released automatically when the client connection ends. Have a look at the mysqlhotcopy source for any idea of how it uses this approach. Essentially, what it does is open a connection to the server, tells the server to lock the tables, and then while the tables are locked, goes behind the server's back to directly copy table files. (This is why mysqlhotcopy must be run on the server host. It's also (I believe) why it doesn't work on WIndows: Windows file locking semantics do not allow you to copy a file while the server has it locked.) By the way, it's difficult to see how automysqlbackup could corrupt any tables. A quick look through it seems to indicate that it only uses mysqldump to perform backups. Yes the script is using mysqldump with --opt , so these seems like what to expect for mysqlhotcopy. ? It's not necessary that one program must operate the same way as another. When you read the MYSQL docs, mysqlhotcopy apears to be the tool for choice for live servers. Maybe I'm not interpeting the documentation correctly, maybe it refers to having to be on the SAME server to run. http://dev.mysql.com/doc/mysql/en/mysqlhotcopy.html indicates that you must run mysqlhotcopy on the same machine where the database directories are located. A server only manages databases on the same machine where it runs so that means the server must also be run on that same machine. I want the automysqlbackup script to run from cron at 4am..one site in question is very busy still during that time. What does the user experience who may in the middle of a write action to the database as the server starts the mysqldump --opt process? A user who is in the *middle* of a write action will see no effect. Any backup program attempting to acquire a lock will not succeed in getting the lock until the write action has completed. A user who attempts to write *after* the backup program has acquired a lock will be blocked until the backup has finished. And that is what you want. (If you're backing up a table by copying its files directly, you do *not* want people messing with that table.) How can I do myisamchk with tables locked to make sure the tables are not corrupt before I try this shell script again? I want to make sure I start clean before I try it so that if it repeats the problem I can relate the problem to the action just performed. I don't understand this question. I thought you wanted to make a backup. If you want to check your tables, I suggest using CHECK TABLES, and if you want to repair them, I suggest REPAIR TABLES. These operate by having the server perform the check or repair operation, so it takes care of locking the tables so that other clients cannot change them during the operation. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Locking tables
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 will not be corrupted during any kind of shell operation? Do I have I'm not sure I know of any precaution that can prevent you from corrupting your tables during *any kind of shell operation*. It's usually possible to cause a violation of table integrity from the shell if you're determined to do so. (I know that may not be quite the question you intended to ask, but it's what you actually *did* ask.) 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 unlock the tables. Remember that the client that locks the table *must remain connected* while you copy the table files, because any locks are released automatically when the client connection ends. Have a look at the mysqlhotcopy source for any idea of how it uses this approach. Essentially, what it does is open a connection to the server, tells the server to lock the tables, and then while the tables are locked, goes behind the server's back to directly copy table files. (This is why mysqlhotcopy must be run on the server host. It's also (I believe) why it doesn't work on WIndows: Windows file locking semantics do not allow you to copy a file while the server has it locked.) By the way, it's difficult to see how automysqlbackup could corrupt any tables. A quick look through it seems to indicate that it only uses mysqldump to perform backups. 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 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 records in it when only partially built. What I need to do is put a lock on the database/tables before the script is run. The script allows for a pre post shell script to run. So what do I need to do before running any backup scripts to ensure the tables will not be corrupted during any kind of shell operation? Do I have to stop services like mysqld or httpd before hand? Does anyone use MySQL 4.0.18 and the hot copy instead? I'm using 4.0.17-max -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Locking tables
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 records in it when only partially built. What I need to do is put a lock on the database/tables before the script is run. The script allows for a pre post shell script to run. So what do I need to do before running any backup scripts to ensure the tables will not be corrupted during any kind of shell operation? Do I have to stop services like mysqld or httpd before hand? Does anyone use MySQL 4.0.18 and the hot copy instead? I'm using 4.0.17-max Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Locking tables
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 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 records in it when only partially built. What I need to do is put a lock on the database/tables before the script is run. The script allows for a pre post shell script to run. So what do I need to do before running any backup scripts to ensure the tables will not be corrupted during any kind of shell operation? Do I have to stop services like mysqld or httpd before hand? Does anyone use MySQL 4.0.18 and the hot copy instead? I'm using 4.0.17-max Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 Mark Súsol --- u l t i m a t e CreativeMedia Web | Print | CD Media | eCommerce www.ultimatecreativemedia.com Ph: 301-668-0588 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 inserts). Multi inserts are the way to go. By locking the table 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 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 seperate such inserts? How do these locks affect select statements involving the locked tables? Thanks in advance, -Phil -- 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]
Re: Improving insertion performance by locking tables
Is there a limit to the number of records I can insert in a multiple-value insert? - Original Message - From: Rudy Metzger [EMAIL PROTECTED] To: Phil Bitis [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, July 14, 2003 10:18 AM Subject: 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 inserts). Multi inserts are the way to go. By locking the table 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 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 seperate such inserts? How do these locks affect select statements involving the locked tables? Thanks in advance, -Phil -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Improving insertion performance by locking tables
Not by MySQL (or it is that high it doesn't really matter). Your OS could put a limit on it with max file size. Just do yourself a favor and apply \n after every row so that you can open it with an editor. Otherwise a lot of editors will complain of 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? - Original Message - From: Rudy Metzger [EMAIL PROTECTED] To: Phil Bitis [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Monday, July 14, 2003 10:18 AM Subject: 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 inserts). Multi inserts are the way to go. By locking the table 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 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 seperate such inserts? How do these locks affect select statements involving the locked tables? Thanks in advance, -Phil -- 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] -- 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]
Improving insertion performance by locking tables
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 seperate such inserts? How do these locks affect select statements involving the locked tables? Thanks in advance, -Phil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: locking tables , mysql 3.23.41
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, 2003 10:35 PM Subject: locking tables , mysql 3.23.41 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 db_name.% READ; which I tried cause I'm fooolish but it didn't work :o) If not , is there a nice tool (best would be command-line) to do a full online-backup of all data on a server. Linux 2.4.10 , mysql 3.23.41 If I missed anything in the docs , just tell me where , please. Klaus Topaktuelle Consumer-Adressen anmieten www.consumeradressen.de Diese Mail ist von: Deutsche Post Direkt GmbH Beleglese Center Mannheim Klaus Franz Manager Abgleichsysteme Willy-Brandt-Platz 13 Tel. 06 21.129 56 436 68161 Mannheim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
locking tables , mysql 3.23.41
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 db_name.% READ; which I tried cause I'm fooolish but it didn't work :o) If not , is there a nice tool (best would be command-line) to do a full online-backup of all data on a server. Linux 2.4.10 , mysql 3.23.41 If I missed anything in the docs , just tell me where , please. Klaus Topaktuelle Consumer-Adressen anmieten www.consumeradressen.de Diese Mail ist von: Deutsche Post Direkt GmbH Beleglese Center Mannheim Klaus Franz Manager Abgleichsysteme Willy-Brandt-Platz 13 Tel. 06 21.129 56 436 68161 Mannheim - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Select statement locking tables in the from clause....
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 other user can select, insert, or update them. Here is our my.cnf file for this system. Thanks for any help. [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= max_connections=1000 set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=1024 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 set-variable= myisam_sort_buffer_size=64M log-bin server-id = 1 [mysqldump] quick set-variable= max_allowed_packet=16M [isamchk] set-variable= key_buffer=384M set-variable= sort_buffer=384M set-variable= read_buffer=128M set-variable= write_buffer=128M [myisamchk] set-variable= key_buffer=384M set-variable= sort_buffer=384M set-variable= read_buffer=128M set-variable= write_buffer=128M [mysqlhotcopy] interactive-timeout James Crippen Sr. LAN Engineer Elite International ph 281-775-2237 email [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Locking TABLES for myisamchk, please help!!
I understand all of the reasons given by Alexander. Yet I think that in 99% of cases, it would be best for myisamchk to do as Mark suggest, and not run when a MySQL server is active. Safety would be the default. There could be a special option (--no-safety) for myisamchk to override that check. - 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 wrote: cut Personally, I find the behavior of myisasmchk to be a bit beta when it comes live tables. It would be real easy for myisasmchk to refuse to run when mysqld 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 on a live table. As simple as that. Hi, It is not that simple. You can run several mysqld simultaneously. So checking if mysqld is running is not enough. Or you might wish to use myisamchk knowing in advance that mysqld will not write to table. Typical case is to check 'offline' database or table. Backup copy for example. Another case: You want to check table which is read-only for all mysql users and for some reason (different priority, buffers used etc.) you don't want to do it with REPAIR TABLE. myisamchk uses tables as files so it is admin responcibility to avoid problems. It is dangerous to use it but it is possible. You can compare it to filesystem checks. It is admin to decide if he can handle it with live server or not. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Locking TABLES for myisamchk, please help!!
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 info. ty, louie... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Locking TABLES, help.
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... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Locking TABLES for myisamchk, please help!!
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 want to off it but i can't its a production lm server. lm Hope anyone could give me more info. Don't run myisamchk when MySQL is running! Use REPAIR TABLE instead of myisamchk: http://www.mysql.com/doc/R/E/REPAIR_TABLE.html lm ty, lm louie... -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Locking TABLES for myisamchk, please help!!
Wednesday, May 22, 2002, 9:23:02 AM, louie 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 want to off it but i can't its a production lm server. lm Hope anyone could give me more info. Personally, I find the behavior of myisasmchk to be a bit beta when it comes live tables. It would be real easy for myisasmchk to refuse to run when mysqld 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 on a live table. As simple as that. - Mark - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Locking TABLES for myisamchk, please help!!
Mark wrote: Wednesday, May 22, 2002, 9:23:02 AM, louie wrote: cut Personally, I find the behavior of myisasmchk to be a bit beta when it comes live tables. It would be real easy for myisasmchk to refuse to run when mysqld 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 on a live table. As simple as that. Hi, It is not that simple. You can run several mysqld simultaneously. So checking if mysqld is running is not enough. Or you might wish to use myisamchk knowing in advance that mysqld will not write to table. Typical case is to check 'offline' database or table. Backup copy for example. Another case: You want to check table which is read-only for all mysql users and for some reason (different priority, buffers used etc.) you don't want to do it with REPAIR TABLE. myisamchk uses tables as files so it is admin responcibility to avoid problems. It is dangerous to use it but it is possible. You can compare it to filesystem checks. It is admin to decide if he can handle it with live server or not. -- Best regards -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Alexander Keremidarski [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria ___/ www.mysql.com M: +359 88 231668 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Locking Tables
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) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Locking Tables
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 may eventually get a timeout. It would not be a good thing if one database user could create unexpected errors for another by use of locking. Alec Cawley Mysql query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Locking tables
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 the code I open a connection again. Will that table that I locked in the first connection still be locked? Carl - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Locking tables
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] Subject: Locking tables 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 the code I open a connection again. Will that table that I locked in the first connection still be locked? Carl - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Error Locking Tables
Hi Tom, ... you forgot to put the locking purpose: write or read: Ex: lock mytables write; Gelu G.NET SOFTWARE COMPANY - Original Message - From: Tom Caruso [EMAIL PROTECTED] To: [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 table_name; command at the command prompt and I got the following error: ERROR 1064: You have an error in your SQL syntax near '' at line 1 The 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Error Locking Tables
Tom, Monday, February 25, 2002, 3:41:27 AM, you wrote: TC Hello, TC I am using MySQL 3.23.47 under Windows 98 (mysql-opt) and I am TC having a strange problem. I issued the LOCK TABLES TC table_name; command at the command prompt and I got the TC following error: 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 in the documantation: http://www.mysql.com/doc/L/O/LOCK_TABLES.html You should set a type of locking (READ, WRITE etc.) TC Thanks in advance for your help, TC Tom -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
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 table_name; command at the command prompt and I got the following error: ERROR 1064: You have an error in your SQL syntax near '' at line 1 The 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
Re: Why is mysql locking tables on insert
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 in your table, then MySQL will not allow concurrent inserts (yet?). There is some talk about possibly allowing dirty reads on MyISAM tables. Basically, it means that MyISAM tables will respect the READ UNCOMMITTED isolation level. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 21 days, processed 395,245,024 queries (217/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Why is mysql locking tables on insert
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')|
| 4046092 | konkursy | targi1.wp.pl | konkursy2 | Query |
97| update | insert into bb2_ludzie_tak values
('David') |
| 4046334 | konkursy | targi1.wp.pl | konkursy2 | Query |
87| Locked | insert into bb2_ludzie_tak values
('Basia') |
| 4046623 | konkursy | targi2.wp.pl | konkursy2 | Query |
24| Locked | insert into bb2_ludzie_tak values
('Wojciech') | 4045633 | konkursy | targi1.wp.pl |
konkursy2 | Query | 14| Locked | insert into
bb2_ludzie_tak values
('Marzena')|
| 4046092 | konkursy | targi1.wp.pl | konkursy2 | Query |
97| update | insert into bb2_ludzie_tak values
('David') |
| 4046334 | konkursy | targi1.wp.pl | konkursy2 | Query |
87| Locked | insert into bb2_ludzie_tak values
('Basia') |
| 4046623 | konkursy | targi2.wp.pl | konkursy2 | Query |
24| Locked | insert into bb2_ludzie_tak values
('Wojciech')
Why is mysql locking table during insert operastion? The version is 3.23.22, so
it shouldn't behave like this...
--
_/_/ _/_/_/ - Rafa Jank [EMAIL PROTECTED] -
_/ _/ _/ _/ _/ Wirtualna Polska SA http://www.wp.pl
_/_/_/_/ _/_/_/ul. Uphagena 2, 80-237 Gdansk, tel/fax. (58) 5215625
_/ _/ _/ ==* http://szukaj.wp.pl *==--
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Why is mysql locking tables on insert
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 on MyISAM tables and only if you have never deleted any rows from them. Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Why is mysql locking tables on insert
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 inserts, then note that they work only on MyISAM tables and only if you have never deleted any rows from them. This is my case... And I did optimize table just to be sure... -- _/_/ _/_/_/ - Rafa Jank [EMAIL PROTECTED] - _/ _/ _/ _/ _/ Wirtualna Polska SA http://www.wp.pl _/_/_/_/ _/_/_/ul. Uphagena 2, 80-237 Gdansk, tel/fax. (58) 5215625 _/ _/ _/ ==* http://szukaj.wp.pl *==-- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: locking tables.
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 currently have locked. lazybrain wrote: 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 thread obtains a WRITE lock on a table, then only the thread holding the lock can READ from or WRITE to the table. Other threads are blocked this is not true. if you have a write lock, any other connections can read from that table just not write to it. The documentation does not mention much about unlock tables either if you do unlock table beer_table does it unlock all over tables that are lock or just that one?? can someone give me some insight as to how this really works please. is there any plans to make it so you can lock one table and do whatever you want to tables that are not locked without locking them? it makes sense to me. Also in the docs its says not to use lock tables with insert delayed. but i found somewhere else on mysql.com/docs that it says you can. that is a big conflicting. thanks to anyone how sheds light on this. peace - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Gerald L. Clark [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
locking tables.
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 thread obtains a WRITE lock on a table, then only the thread holding the lock can READ from or WRITE to the table. Other threads are blocked this is not true. if you have a write lock, any other connections can read from that table just not write to it. The documentation does not mention much about unlock tables either if you do unlock table beer_table does it unlock all over tables that are lock or just that one?? can someone give me some insight as to how this really works please. is there any plans to make it so you can lock one table and do whatever you want to tables that are not locked without locking them? it makes sense to me. Also in the docs its says not to use lock tables with insert delayed. but i found somewhere else on mysql.com/docs that it says you can. that is a big conflicting. thanks to anyone how sheds light on this. peace - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
