i have been running 3.22.25 on solaris 7 for quite a while. i ran into a
problem with it a few days ago where it basically took forever to delete
rows (these are huge tables - several million rows, with lots of indexes,
and i needed to delete some millions of rows... i started it, and let it
run for over 25 hours before giving up and pulling the plug on it.)
anyway i decided to upgrade to the latest & greatest to see if it could
handle the load better. i installed the binary distribution of 3.23.42 for
solaris 2.7 (sparc).
i am running into a problem now where the database server locks up
totally. it is repeatable.
the sequence of events looks like this:
start mysqld with the following arguments:
/space/mysql/bin/mysqld --datadir=/space/mysql/data --user=mysql -l
a sequence of queries resembling this runs:
010914 15:51:06 3 Connect [EMAIL PROTECTED] on page2
3 Query SELECT url1,url2,url3,id FROM url
WHERE site='vgs'
3 Query INSERT INTO dates (date) VALUES
('2001-8-30')
3 Query INSERT INTO dates (date) VALUES
('2001-8-31')
010914 15:51:07 3 Query INSERT INTO url (site,url1,url2,url3)
VALUES ('vgs','features','universal','tenspot_multiplayerr
eader')
3 Query INSERT INTO url (site,url1,url2,url3)
VALUES ('vgs','features','universal','tenspot_soundtracksr
eader')
3 Query INSERT INTO url (site,url1,url2,url3)
VALUES ('vgs','guides','tpm_gg','35.html')
3 Query INSERT INTO url (site,url1,url2,url3)
VALUES ('vgs','features','index_strategyguides.html','')
010914 15:51:08 3 Query LOCK TABLES pv WRITE
3 Query LOAD DATA LOCAL INFILE 'pageview.data'
INTO TABLE pv (id,count,date)
010914 15:51:10 3 Query UNLOCK TABLES
the application processes web site logs and stores them for report
generation. the SELECT is used to find out what urls are recorded for a
given site. the INSERTs create new URL records for those which aren't
already known. then i LOCK TABLES, LOAD DATA, and UNLOCK TABLES. after
several successful iterations, somehow the UNLOCK TABLES never finishes,
and this prevents all further queries on any table, although i don't
understand why.
example:
# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14 to server version: 3.23.42-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> connect page2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
[at this point, the command line mysql is completely hung.]
here's what mysqladmin processlist shows:
| 11 | jsd | loathing.gamespot.com | page2 | Query | 548 | closing
tables | UNLOCK TABLES |
| 12 | jsd | loathing.gamespot.com | page2 | Query | 535 |
statistics | SELECT url1,url2,url3,id FROM url WHERE site='gs' |
| 15 | root | localhost | page2 | Field List | 54 | Opening
table | |
| 19 | root | localhost | | Query |
0 | | show processlist |
mysqladmin shutdown does not work. it reports the following:
010914 16:05:41 /space/mysql/bin/mysqld: Normal shutdown
010914 16:05:42 /space/mysql/bin/mysqld: Forcing close of thread 15 user:
'root'
010914 16:05:42 /space/mysql/bin/mysqld: Forcing close of thread 12 user:
'jsd'
010914 16:05:42 /space/mysql/bin/mysqld: Forcing close of thread 11 user:
'jsd'
and returns to the shell - however, the mysqld is still running. i have to
kill -9 the mysqld, restart, and repair the tables. mysqlcheck -A reports
mysql.columns_priv OK
mysql.db OK
mysql.func OK
mysql.host OK
mysql.tables_priv OK
mysql.user OK
page2.dates
warning : 1 clients is using or hasn't closed the table properly
status : OK
page2.pv
warning : 1 clients is using or hasn't closed the table properly
warning : Size of indexfile is: 25808896 Should be: 25645056
warning : Size of datafile is: 9243510 Should be: 9073770
error : Key in wrong position at page 2381824
error : Corrupt
page2.url
warning : 1 clients is using or hasn't closed the table properly
status : OK
i'm going to have to switch back to the 3.22 version of mysql for now. any
advice greatly appreciated.
-jsd-
---------------------------------------------------------------------
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
