What kind of queries are being run and what type of data is stored? There are a number of factors which causes MySQL to use on disk temporary tables instead of in memory tables. (If there a BLOB or TEXT columns in the table for example).
As a starting point you could (if you have the memory in your box) try increasing the values for tmp_table_size and max_heap_size, these control how large an in-memory temp table can grow before it is converted to an on disk temp table. Regards John =================== John Daisley MySQL 5.0 Certified Database Administrator (CMDBA) MySQL 5.0 Certified Developer Cognos BI Developer Telephone: +44(0)1283 537111 Mobile: +44(0)7812 451238 Email: john.dais...@butterflysystems.co.uk =================== Sent via HP IPAQ mobile device -----Original Message----- From: TianJing <jingtian.seu...@gmail.com> Sent: 15 December 2009 03:08 To: mysql@lists.mysql.com Subject: mysql server optimization Dear all, i am nowing having a problem with the mysql server optimization, i have 20 database on a server,each database is about 80Gb,the sql seems very slow,almost > 5s.and the server i/o is so high,when i check the processlist,the 'copying to tmp table' state takes a long time. i have already use index,but the sql use lots of 'and','or','order by', and for some reason i can not optimization the sql,i hope to do some optimization on mysql server to mitigate this phenomenon,could any one give me some suggestion? thanks. my server is linux,8CPU and 4G memery,the my.cnf is: [mysqld] port = 3306 skip-locking skip-name-resolve key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 64 sort_buffer_size = 512M net_buffer_length = 8K read_buffer_size = 512K read_rnd_buffer_size = 512M myisam_sort_buffer_size = 8M table_cache = 1024 log-bin=mysql-bin binlog_format=mixed -- Tianjing -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
