> -----Original Message----- > From: news [mailto:[EMAIL PROTECTED] On Behalf Of Jon Drukman > Sent: Thursday, May 13, 2004 3:21 PM > To: [EMAIL PROTECTED] > Subject: avoiding "Locked" threads > > I've got a very high traffic discussion forum database that is > constantly running into a problem with lots and lots of threads in the > "Locked" state. i was under the impression that MySQL could > update/insert and select from the same table at the same time, but it > doesn't seem to be the case. all tables are MyISAM. the machine is a > dual xeon 3.2G with 2G of RAM now but i am upgrading it to 4G.
NOTE: 4GB only helps because of system cache, mySQL in 32 bit cannot go beyond 2GB with out some experimental tweak that are separate from mySQL. the > database is on a 15K RPM SCSI RAID0+1. the cpu load and io load all > looks pretty good. here's my my.cnf: > > [mysqld] > set-variable = query_cache_size=384M > set-variable = key_buffer=384M > set-variable = max_allowed_packet=4M > set-variable = table_cache=64 > set-variable = sort_buffer=8M > set-variable = record_buffer=8M > set-variable = thread_cache=8 > set-variable = tmp_table_size=128M > set-variable = thread_concurrency=4 the above is for solaris only > set-variable = myisam_sort_buffer_size=128M > set-variable = max_connections=1800 > set-variable = max_connect_errors=100000 > set-variable = wait_timeout=120 > set-variable = max_binlog_size=500000000 > set-variable = long_query_time=5 > server-id = 1 > log-bin=/var/opt/mysql/db2-binlog > skip-innodb > log-error=/var/opt/mysql/db2-errlog This is on a separate drive? > > here's a sample of some of the locked threads at the moment: > > INSERT INTO MSGPOST VALUES (NULL, '931046', 'leon > is...<br/><b>(SPOILERS)</b><br/>-<br/>-<br/>-<br/> | > INSERT INTO MSGPOST VALUES (NULL, '943738', 'All I got is from the > British. It should be the same th | > INSERT INTO MSGPOST VALUES (NULL, '951538', '(i)Don\'t forgot that > Florina can BARELY hold a slim la | > INSERT INTO MSGPOST VALUES (NULL, '955788', 'it stands for cyberathletic > amateur league<br/>---<br/> | > INSERT INTO MSGPOST VALUES (NULL, '971210', 'I\'m getting a job at a > place that sells DnD books and | > > SELECT * FROM MSGPOST WHERE MsgPostId = 4466518 > > SELECT * FROM MSGPOST WHERE MsgPostId = 4466519 > > SELECT * FROM MSGPOST WHERE MsgPostId = 4466523 > > SELECT * FROM MSGPOST WHERE MsgPostId = 4466524 > > SELECT * FROM MSGPOST WHERE MsgPostId = 4466526 > > SELECT * FROM MSGPOST WHERE MsgPostId = 4466527 > > SELECT * FROM MSGPOST WHERE MsgPostId = 4466532 > > SELECT * FROM MSGPOST WHERE MsgPostId = 4466534 > > we've tried using MERGE tables on some of the real busy ones to break up > the updates but we're still seeing locks doing SELECT on the merged table. I assume that your msgPostId is a primary key? > > any ideas appreciated! Try setting low-priority-updates and delay-key-write=ALL Your running into a concurrency issue, the only other quick fix is to use innodb, but your blobs will kill you in disk space. > > -jsd- > > > -- > 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]
