We are running a portal site using mysql and I have been trying to sqeeze some more performance out of our 4-CPU Linix 2.4.20 intel box. Our content is all read-only and we use in-memory temp tables a lot in our queries. I was watching vmstat as I was issuing some queries and I noticed that almost every operation results in some amount of writes.
Swap space has been off. For example commands like thes all result in a small number of writes to disk. CREATE TEMPORARY TABLE TMP_TABLE_0 TYPE=HEAP; TRUNCATE TABLE TMP_TABLE_0; INSERT TMP_TABLE_0 SELECT DISTINCT pid, sid FROM SOME_TABLE; Here is the output of vmstat 1. procs memory swap io system cpu r b w swpd free buff cache si so bi bo in cs us sy id 0 0 0 0 153560 27668 635228 0 0 0 0 104 15 0 0 100 0 0 0 0 153560 27668 635228 0 0 0 0 106 13 0 0 100 0 0 0 0 153560 27668 635228 0 0 0 12 112 54 0 0 100 0 0 0 0 153560 27668 635228 0 0 0 0 104 11 0 0 100 0 0 0 0 153560 27668 635228 0 0 0 0 104 13 0 0 100 Note the 12 blocks written in the middle. Between 10-50 blocks get written for all of these operations on temp tables. I have a feeling this is OS related, but swap is off. I am quite sure that the writes are to disk. iostat shows the same problem and show the device being written to as /dev/sda - scsi. Any clues at to what might be happening? What is mysql or OS writing? In my opinion all of the queries above should be in-memory and result only in reads. Thanks, -Emile -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
