Due to the nature of myisam tables, when you are doing a query then the table will be locked for writes. Reads will still be permitted until another write request is made, at which time all further reads and writes will be blocked until the query completes.
This, however, is already happening even without "-q" and adding the "-q" will likely significantly shorten the time to execute, depending on exactly how large this table is. myisam is a very limiting table type as soon as you want to do anything more than read from or write to a single row at a time using indexed lookups. innodb tables do not have this problem, although they have limitations of their own. On Thu, 2 Sep 2004 14:30:24 -0400, Sun, Jennifer <[EMAIL PROTECTED]> wrote: > The command is issued from mysql command line. Is there any parameters or options I > can use without locking the table? > > > > > -----Original Message----- > From: Marc Slemko [mailto:[EMAIL PROTECTED] > Sent: Thursday, September 02, 2004 2:24 PM > To: Sun, Jennifer > Cc: [EMAIL PROTECTED] > Subject: Re: tuning suggestion for large query > > On Wed, 1 Sep 2004 11:40:34 -0400, Sun, Jennifer > <[EMAIL PROTECTED]> wrote: > > Hi, > > > > We have a job that do 'select * from big-table' on a staging mysql database, then > > dump to data warehouse, it is scheduled to run once a day, but may be run > > manually. Also we have several other small OLTP database on the same server. > > When the big job run, it would use all the physical mem and swap, all other > > process slow down because of this. > > > > I would like to limit the resource usage for each mysql client so that they can > > use only certain max amount of RAM, and don't select everything into memory before > > display it to users. However, I couldn't find any parameter would let me implement > > it. > > Anybody ever encounter the same issue before? Please share your experience. > > How exactly are you running this "select * from big-table"? From the > "mysql" command line client? Is that what is using memory? It isn't > clear from your post if it is the server or something else using > memory. > > If it is the mysql command line client that is the issue, try adding a > "-q" parameter. If you are using myisam tables, however, keep in mind > that table will be effectively locked for the whole duration... but if > it is the mysql command line client (which defaults to buffering > everything in memory), it may be faster to use -q anyway. > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
