Re: avoiding Locked threads
Dathan Vance Pattishall wrote: log-bin=/var/opt/mysql/db2-binlog skip-innodb log-error=/var/opt/mysql/db2-errlog This is on a separate drive? yes, the database is the only thing on the high speed RAID. everything else is on the other drive (also a RAID but only RAID0 with 2 drives). any ideas appreciated! Try setting low-priority-updates and delay-key-write=ALL i haven't tried this yet, but one of the other developers has objected that doing this will kill performance for people posting messages because their clients will hang waiting for the selects to finish. is this true? Your running into a concurrency issue, the only other quick fix is to use innodb, but your blobs will kill you in disk space. h we've got approx 60G free on the RAID so this may not be such a big problem. -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: avoiding Locked threads
Jon, an update statement is supposed to return the # of rows updated, so the client must wait for a response, which means the client will wait if you low_priority your updates. But if they are insert delayed then the client gets a return immediately. This behavior limits the usefullness of low priority with updates. I did ask on the list if there were any plans for a delayed update, but I was told there aren't. Trevor Jon Drukman wrote: Dathan Vance Pattishall wrote: log-bin=/var/opt/mysql/db2-binlog skip-innodb log-error=/var/opt/mysql/db2-errlog This is on a separate drive? yes, the database is the only thing on the high speed RAID. everything else is on the other drive (also a RAID but only RAID0 with 2 drives). any ideas appreciated! Try setting low-priority-updates and delay-key-write=ALL i haven't tried this yet, but one of the other developers has objected that doing this will kill performance for people posting messages because their clients will hang waiting for the selects to finish. is this true? Your running into a concurrency issue, the only other quick fix is to use innodb, but your blobs will kill you in disk space. h we've got approx 60G free on the RAID so this may not be such a big problem. -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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. 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 set-variable= myisam_sort_buffer_size=128M set-variable= max_connections=1800 set-variable= max_connect_errors=10 set-variable= wait_timeout=120 set-variable= max_binlog_size=5 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 here's a sample of some of the locked threads at the moment: INSERT INTO MSGPOST VALUES (NULL, '931046', 'leon is...br/b(SPOILERS)/bbr/-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 leaguebr/---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. any ideas appreciated! -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: avoiding Locked threads
-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=10 set-variable= wait_timeout=120 set-variable= max_binlog_size=5 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)/bbr/-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 leaguebr/---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]
