RE: Simultaneous SELECT and UPDATE on the same table
Hi there,
On Fri, 8 Mar 2002, Paul DuBois wrote:
> At 21:14 +0100 3/8/02, Roger Baklund wrote:
> >* Paul DuBois
> >> At 17:00 +0000 3/8/02, Corin Hartland-Swann wrote:
> >[...]
> >> >This works for a varying number of rows (usually 5,000 to 10,000) before
> >> >it silently exits the loop. I'm using MySQL 3.23.49a
> >[...]
> >>
> >> That's also your problem. With mysql_use_result, it's *required*
> >> that you completely finish the query before issuing another one.
> >>
> >> I suppose you could write the IDs to a file, then read them back in
> >> and use them to issue the UPDATE statements.
> >
> >Why not make two connections, SELECT from one and UPDATE through the other?
Roger/Paul - sorry - I was half asleep when I wrote the paraphrased code.
It should have featured _two_ database handles, as suggested:
--
$sth = $dbh1->prepare("SELECT id,num FROM table", { 'mysql_use_result' => 1 });
$sth->execute;
$sth->bind_columns(\$id, \$num);
while ($sth->fetch)
{
$new_num = $hash{$id};
$dbh2->do("UPDATE table SET num = $new_num WHERE id = $id")
if $num != $new_num;
}
$sth->finish;
--
> I thought of that, too, but I don't think it would work. The SELECT locks
> the table until it's done. That will lock out the UPDATE, which needs a
> WRITE lock no matter which connection it's done over.
>
> Of course, if anyone tries it and find that it works, I'd be interested
> to know that.
Well, as above, it works - but then it just stops after 5k-10k rows.
I've checked the database log and the UPDATE queries do NOT stall waiting
for the SELECT to complete - they appear in the log at a rate of about 300
per second. In checking the logs, I realised I should probably point out
that although 5k-10k rows are /scanned/, only 600 or so rows are
/updated/.
This is weird, and I really can't work out what is going on. If someone
conclusively tells me (Monty?) that things don't work this way then I'll
do it another way (as discussed above) but otherwise I'd like to know what
might be going wrong.
For the record, I am not using UPDATE LOW_PRIORITY - just a normal one.
Thanks again,
Corin
/+-\
| Corin Hartland-Swann |Tel: +44 (0) 20 7491 2000|
| Commerce Internet Ltd |Fax: +44 (0) 20 7491 2010|
| 22 Cavendish Buildings | Mobile: +44 (0) 79 5854 0027|
| Gilbert Street | |
| Mayfair|Web: http://www.commerce.uk.net/ |
| London W1K 5HJ | E-Mail: [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
Simultaneous SELECT and UPDATE on the same table
Hi there,
I have a medium-size table (3 million rows) with several pieces of data
including an ID and a number (called id and num below). I have a program
which builds a hash in perl from a data file of the 'new' numbers for each
id, only some of which have changed (5-10%).
To update the table I tried using the following code (paraphrased):
--
$sth = $dbh->prepare("SELECT id,num FROM table", { 'mysql_use_result' => 1 });
$sth->execute;
$sth->bind_columns(\$id, \$num);
while ($sth->fetch)
{
$new_num = $hash{$id};
$dbh->do("UPDATE table SET num = $new_num WHERE id = $id")
if $num != $new_num;
}
$sth->finish;
--
This works for a varying number of rows (usually 5,000 to 10,000) before
it silently exits the loop. I'm using MySQL 3.23.49a
I was wondering if there is a problem with doing this in MySQL. Because of
the size of the table and my script's already gargantuan memory footprint
(it tops out 600 MB by the time it gets to this stage) I need to avoid
storing the entire result in DBI, hence mysql_use_result.
As an alternative I could mark all the id's that have changed and then
perform the updates after I have closed the table, but I expected this way
of doing it to work fine. I have run it through numerous times, and it
always drops out of the loop at the same sort of interval (but often
differs from run to run).
Should this be working or should I find another way? If it is a bug then I
will of course try to produce more information, but I just want to check
I'm doing the right thing first.
Many Thanks,
Corin
/----+-\
| Corin Hartland-Swann |Tel: +44 (0) 20 7491 2000|
| Commerce Internet Ltd |Fax: +44 (0) 20 7491 2010|
| 22 Cavendish Buildings | Mobile: +44 (0) 79 5854 0027|
| Gilbert Street | |
| Mayfair|Web: http://www.commerce.uk.net/ |
| London W1K 5HJ | E-Mail: [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
ENUM NOT NULL without default value
Hi Monty,
I've been experimenting with ENUM NOT NULL columns without a default
value. The documentation (section 6.5.3 again) states:
"If no DEFAULT value is specified for a column, MySQL automatically
assigns one. If the column may take NULL as a value, the default value is
NULL. If the column is declared as NOT NULL, the default value depends on
the column type ... for ENUM, the default is the first enumeration value."
This is all fine (and seems like a reasonable assumption to make), but I
discovered that using ALTER TABLE on the column to DROP DEFAULT does
nothing - but that SET DEFAULT '' does remove it.
Would it be possible to make DROP DEFAULT set the default to '' on an
ENUM NOT NULL column?
Here's an example of what I'm talking about:
mysql> CREATE TABLE test_enum (e ENUM('a','b','c') NOT NULL);
mysql> DESCRIBE test_enum;
+---+---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+---+--+-+-+---+
| e | enum('a','b','c') | | | a | |
+---+---+--+-+-+---+
mysql> ALTER TABLE test_enum ALTER COLUMN e DROP DEFAULT;
mysql> DESCRIBE test_enum;
+---+---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+---+--+-+-+---+
| e | enum('a','b','c') | | | a | |
+---+---+--+-+-+---+
mysql> ALTER TABLE test_enum ALTER COLUMN e SET DEFAULT '';
mysql> DESCRIBE test_enum;
+---+---+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---+---+--+-+-+---+
| e | enum('a','b','c') | | | | |
+---+---+--+-+-+---+
Thanks,
Corin
/+-\
| Corin Hartland-Swann |Tel: +44 (0) 20 7491 2000|
| Commerce Internet Ltd |Fax: +44 (0) 20 7491 2010|
| 22 Cavendish Buildings | Mobile: +44 (0) 79 5854 0027|
| Gilbert Street | |
| Mayfair|Web: http://www.commerce.uk.net/ |
| London W1K 5HJ | E-Mail: [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: Optimising for 4096MB RAM
Hi, On Thu, 9 Aug 2001, Fournier Jocelyn [Presence-PC] wrote: > > set-variable = join_buffer=2048M > > set-variable = key_buffer=2048M > > set-variable = table_cache=1024 > > set-variable = record_buffer=2048M > > set-variable = sort_buffer=2048M > > set-variable = tmp_table_size=2048M > > Your record buffer and sort buffer setting seems to be too high :) > > Memory usage of MySQL is organised like this : > > All the thread share up to key_buffer (in your case 2048 Mo). > Each MySQL connections will also use up to (sort_buffer + record_buffer). > In your case, it means MySQL could eat up to (2048 Mo + 2048 Mo)*200 > (max_connections) = 819200 Mo :)) OK, I've re-read the manual section on that and it makes more sense now :) > I suggest you set key_buffer to 2048 Mo, and sort_buffer and record_buffer > to 5 Mo. I've been benchmarking my data using various settings. I'm using a table with 6.5 million rows which is packed and uses compressed keys. The data is 69M and the index is 123M. I was trying to identify the effect of the record_buffer, so I performed a sequential scan on it with: SELECT COUNT(*) FROM table WHERE char_field LIKE '%foo%' I found no difference in query time with a record_buffer of 4M, 2M, 1M, 512K, 16K and 1K (!) - any ideas on the cause of this? I have also set tmp_table_size to 1024M, which according to the manual should mean that temporary tables will be created in RAM unless they're going to be bigger than that. When I do the following query: SELECT char_field,COUNT(*) AS c FROM table GROUP BY char_field HAVING c > 3 ORDER BY char_field It creates a temporary table on disk even though it was only about 200MBs - huh? > The memory usage of MySQL thread will grow up with the time (each new key > scanned will stay in memory). Thanks for your help :) Corin /+-\ | Corin Hartland-Swann |Tel: +44 (0) 20 7491 2000| | Commerce Internet Ltd |Fax: +44 (0) 20 7491 2010| | 22 Cavendish Buildings | Mobile: +44 (0) 79 5854 0027| | Gilbert Street | | | Mayfair|Web: http://www.commerce.uk.net/ | | London W1K 5HJ | E-Mail: [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
Optimising for 4096MB RAM
Hi there, I am trying to optimise MySQL for use on a machine with: Linux 2.4.7-ac9 (only kernel to work with the disk controller) Dual Pentium III 1000 MHz 4096 MB memory RAID-1 Mirror over two 76GB 7200 RPM UDMA disks Reiserfs partition for MySQL As a reference point, I am running the benchmarking tests to measure the performance. I plan on measuring the performance with our particular queries after this. After reading the section of the manual dealing with performance tuning, I used the following in my.cnf: [mysqld] set-variable = join_buffer=2048M set-variable = key_buffer=2048M set-variable = table_cache=1024 set-variable = max_allowed_packet=16M set-variable = max_connections=200 set-variable = record_buffer=2048M set-variable = sort_buffer=2048M set-variable = tmp_table_size=2048M The problem is that these settings don't seem to have practically any effect on the amount of memory that MySQL uses when running the benchmarking suite - I expected it to chew up large amounts of memory with these settings. MySQL is currently only taking up 25MB of RAM - not the 512MB plus I was expecting. This machine is going to be a dedicated database server for a number of web servers - I expected MySQL to take up about half the memory, and disk caching the other half. I want it to run like a beast posessed :) Does anyone have any real world examples and/or suggestions for how to increase the performance? Any other variables I should be looking at? When the machine is operational, the biggest table I expect to be using has 55 million rows and takes up about 2G disk space (compressed, packed, everything). Please could you CC: any replies to me, since I am no longer on the list. Regards, Corin /+-\ | Corin Hartland-Swann |Tel: +44 (0) 20 7491 2000| | Commerce Internet Ltd |Fax: +44 (0) 20 7491 2010| | 22 Cavendish Buildings | Mobile: +44 (0) 79 5854 0027| | Gilbert Street | | | Mayfair|Web: http://www.commerce.uk.net/ | | London W1K 5HJ | E-Mail: [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
Effect of quoting numbers
Hi there, I am performing the following queries on a pretty large table (51 million rows, myisampacked, couple of gigs): SELECT * FROM foo WHERE char_field = '96' AND int_field = 1; SELECT * FROM foo WHERE char_field = 96 AND int_field = 1; Is it the expected behaviour for the second query to perform a numerical equivalence test on every row in the table in turn? It is returning rows with values like '096', '96foo' and the like. I had previously assumed that (when dealing with a CHAR field) you did not need to quote numbers. Please could you CC: any replies to me as I am no longer on the list. Many Thanks, Corin /+---------\ | Corin Hartland-Swann |Tel: +44 (0) 20 7491 2000| | Commerce Internet Ltd |Fax: +44 (0) 20 7491 2010| | 22 Cavendish Buildings | Mobile: +44 (0) 79 5854 0027| | Gilbert Street | | | Mayfair|Web: http://www.commerce.uk.net/ | | London W1K 5HJ | E-Mail: [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
