How often do you optimize/analyze your tables? Have you checked the index cardinality? What does an explain plan show?
-----Original Message----- From: Julien Lavigne du Cadet To: [EMAIL PROTECTED] Sent: 7/25/04 4:26 PM Subject: Slow server - any idea? Hi eveybody, I've got problems since a few weeks with my mysql server. There are a lot of slow queries (about 1200 in less than 48 hours), even some that should absolutely not be slow like this one which is performing on a HEAP table : SELECT * FROM vb3_session WHERE sessionhash = '31d429cc3820a8bb141733de2cd306ba' AND lastactivity > 1090778091 AND host = '65.50.5.140' AND idhash = '385f8c8da967afdd86399fbcccc72d05'; I'm running a p4 2,4. 1Go RAM, DD IDE 80Go under FreeBSD and I've got the 4.0.20 version installed (anyway I tried to downgrade to 4.0.18 and it didn't changed anything). There are about 20 sites and a vb3 forum with 200 to 300 visitors at once. The server doesn't seem to consume much cpu as shown : 42992 mysql 2 0 226M 66256K poll 87:38 4.83% 4.83% mysqld Here is my config file : [mysqld] datadir=/var/db/mysql socket=/tmp/mysql.sock skip-locking skip-innodb query_cache_limit=1M query_cache_size=32M query_cache_type=1 max_connections=500 interactive_timeout=100 wait_timeout=100 connect_timeout=10 thread_cache_size=64 key_buffer=150M join_buffer=1M max_allowed_packet=2M table_cache=768 record_buffer=1M sort_buffer_size=1M read_buffer_size=1M #read_rnd_buffer_size=768K max_connect_errors=10 # Try number of CPU's*2 for thread_concurrency thread_concurrency=2 myisam_sort_buffer_size=64M #log-bin server-id=1 log_slow_queries=/var/log/slow-queries.log long_query_time=1 [mysql.server] user=mysql basedir=/usr/local [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/db/mysql/srv1.pid open_files_limit=8192 [mysqldump] quick max_allowed_packet=16M [mysql] no-auto-rehash #safe-updates [isamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M [myisamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M [mysqlhotcopy] interactive-timeout Here is the status : Created tmp disk tables 706 Created tmp tables 162301 Created tmp files 138 Delayed insert threads 0 Delayed writes 0 Delayed errors 0 Flush commands 1 Handler commit 0 Handler delete 62700 Handler read first 10465 Handler read key 53413365 Handler read next 20806399 Handler read prev 8431183 Handler read rnd 12619723 Handler read rnd next 670650172 Handler rollback 0 Handler update 2921336 Handler write 23073711 Key blocks used 108984 Key read requests 135302387 Key reads 107438 Key write requests 214624 Key writes 184195 Max used connections 41 Not flushed key blocks 0 Not flushed delayed rows 0 Open tables 768 Open files 1321 Open streams 0 Opened tables 9238 Qcache queries in cache 4900 Qcache inserts 954259 Qcache hits 1556783 Qcache lowmem prunes 143367 Qcache not cached 120513 Qcache free memory 7149624 Qcache free blocks 2438 Qcache total blocks 14367 Rpl status NULL Select full join 739 Select full range join 63 Select range 135410 Select range check 0 Select scan 415678 Slave open temp tables 0 Slave running OFF Slow launch threads 0 Slow queries 1280 Sort merge passes 69 Sort range 128597 Sort rows 13431446 Sort scan 200597 Table locks immediate 2514328 Table locks waited 7966 Threads cached 39 Threads created 42 Threads connected 3 Threads running 1 I also have got this kind of messages in mysqld.log : 040725 12:56:47 Aborted connection 250044 to db: 'mondespe_lineage2' user: 'root' host: `localhost' (Got timeout reading communication packets) 040725 12:58:40 Aborted connection 250285 to db: 'animelan' user: 'animelan' host: `localhost' (Got timeout reading communication packets) 040725 13:09:59 Aborted connection 251722 to db: 'mondespe_forums' user: 'mondespe' host: `localhost' (Got timeout reading communication packets) 040725 13:10:59 Aborted connection 251896 to db: 'unconnected' user: 'root' host: `localhost' (Got timeout reading communication packets) 040725 13:10:59 Aborted connection 251891 to db: 'vb3_fansite' user: 'root' host: `localhost' (Got timeout reading communication packets) 040725 13:11:06 Aborted connection 251914 to db: 'mysql' user: 'root' host: `localhost' (Got timeout reading communication packets) 040725 13:17:37 Aborted connection 252812 to db: 'mondespe_forums' user: 'mondespe' host: `localhost' (Got timeout reading communication packets) 040725 13:30:18 Aborted connection 254752 to db: 'mmoblogs' user: 'root' host: `localhost' (Got timeout reading communication packets) 040725 13:30:21 Aborted connection 254750 to db: 'mysql' user: 'root' host: `localhost' (Got timeout reading communication packets) 040725 13:32:37 Aborted connection 255067 to db: 'mysql' user: 'root' host: `localhost' (Got timeout reading communication packets) Any idea to solve the problem is welcome, Thanks to all, Julien. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
