Runaway mysqld
Hi! We are operating several MySQL servers and most are running just fine even under high load. However we have one site (High traffic PHP based site) where we currently is experiencing problems with some strange lock up that causes MySQL to spawn new threads and have a huge number of threads in "ready to run" mode. The load goes from below 1 (on a dual PIII machine, running Slackware 8 with kernel 2.4.18 and MySQL 3.23.51-max (binary from mysql.com). From what I have read, this is most likely due to an internal error that can't be delt with as a result of a specific query. Here's a snip from vmstat 5: 38 0 0 0 5472912 294552 0 0 051 365 625 96 4 0 56 0 0 0 8016912 294588 0 0 026 519 805 96 4 0 36 0 1 0 8904912 294624 0 0 0 0 466 884 95 5 0 63 0 1 0 8672912 294656 0 0 0 0 433 867 97 3 0 And a mysqladmin processlist shows a bunch of connections in state locked: ...snip... | 5362 | public | localhost | onside1 | Query | 3| Locked | select id from banners_new where (id=131 OR parent_id=131) AND pic!='' | | 5363 | public | localhost | onside1 | Query | 0| System lock | select id from banners_new where (id=131 OR parent_id=131) AND pic!='' | | 5364 | public | localhost | onside1 | Query | 0| Locked | select id from banners_new where (id=131 OR parent_id=131) AND pic!='' | | 5365 | public | localhost | onside1 | Query | 0| Locked | select id,pic from banners_new where (id=110 OR parent_id=110) AND pic!='' limit 0,1 | ...snip... The config (mostly a my-huge.cnf file): [mysqld] datadir = /data/db user= mysqld port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= max_connections=256 set-variable= key_buffer=256M set-variable= max_allowed_packet=1M set-variable= table_cache=256 set-variable= sort_buffer=1M set-variable= record_buffer=1M set-variable= myisam_sort_buffer_size=64M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 #log-bin server-id = 1 Any ideas on how to debug this? Logging all queries is almost not an option since the traffic (when the error occurs) is several hundred queries / second (according to mysqladmin status). Thanks in advance! Brgds /Stefan -- DISCUSSION, n. A method of confirming others in their errors. - 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: MAX_KEYS
Sinisa Milivojevic wrote: > > Stefan Olsson writes: > > Hi! > > > > I'm trying to recompile the server with support for more than 16 keys. > > This is what i have changed: > > sql/unireg.h:#define MAX_KEY 32 /* Max used keys > > */ > > > > But when i try to insert data into a table with more than 16 defined > > keys i get: > > ERROR 1062: Duplicate entry '1' for key 1 > > > > Note! The table is empty and does not accept any values, changing the > > value of the unique column only generates the same error but with > > diffrent entries/key numbers, example: > > ERROR 1062: Duplicate entry '0' for key 3 > > > > Thanks in advance! > > > > /Stefan > > > > Hi! > > You should create your tables with a new mysql. By new mysql you mean which version ? In this new version, is it the same variable to change? /Stefan > > Regards, > > Sinisa > > __ _ _ ___ == MySQL AB > /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic > /*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED] >/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaka, Cyprus > /*/ /*/ /*/\*\_/*/ \*\_/*/ |*| > /*/^^^\*\^^^ > /*/ \*\Developers Team -- I have yet to see any problem, however complicated, which, when looked at in the right way, did not become still more complicated. - 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
MAX_KEYS
Hi! I'm trying to recompile the server with support for more than 16 keys. This is what i have changed: sql/unireg.h:#define MAX_KEY 32 /* Max used keys */ But when i try to insert data into a table with more than 16 defined keys i get: ERROR 1062: Duplicate entry '1' for key 1 Note! The table is empty and does not accept any values, changing the value of the unique column only generates the same error but with diffrent entries/key numbers, example: ERROR 1062: Duplicate entry '0' for key 3 Thanks in advance! /Stefan -- I have yet to see any problem, however complicated, which, when looked at in the right way, did not become still more complicated. - 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
