I've optimized the main tables (the forums one and a few others) a few days ago.
About index cardinality, I don't know what to tell you. For a few tables it is high, like for the vbulletin postindex (higher than 11 000 000) but it's absolutely normal for such a forum. And about explain, we've got a few hundred tables so i can't tell you much :) Thanks for your help, Julien Lavigne du Cadet. ----- Original Message ----- From: "Victor Pendleton" <[EMAIL PROTECTED]> To: "'Julien Lavigne du Cadet '" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, July 26, 2004 3:06 PM Subject: RE: Slow server - any idea? > 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]
