-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]