Claus and Andrew,
Claus Guttesen wrote:
Isn't sort_mem quite high? Remember that sort_mem size is allocated
for each sort, not for each connection. Mine is 4096 (4 MB). My
effective_cache_size is set to 27462.
I tested sort mem from 4096 to 32768 (4096, 8192, 16384, 32768) this
afternoon and 32768 is definitely the best value for us. We still have
free memory using it, we don't have any swap and queries are generally
faster (I log all queries taking more than 500ms and the log is growing
far faster with lower values of sort_mem).
What OS are you running?
# cat /etc/redhat-release
CentOS release 3.6 (Final)
so it's a RHEL 3 upd 6.
# uname -a
Linux our.host 2.4.21-37.ELsmp #1 SMP Wed Sep 28 14:05:46 EDT 2005 i686
i686 i386 GNU/Linux
# cat /proc/cpuinfo
4x
processor : 3
vendor_id : GenuineIntel
cpu family : 15
model : 2
model name : Intel(R) Xeon(TM) MP CPU 2.20GHz
stepping : 6
cpu MHz : 2192.976
cache size : 512 KB
# cat /proc/meminfo
total: used: free: shared: buffers: cached:
Mem: 3949797376 3478376448 471420928 0 83410944 2679156736
Swap: 4384022528 9797632 4374224896
MemTotal: 3857224 kB
MemFree: 460372 kB
MemShared: 0 kB
Buffers: 81456 kB
Cached: 2610764 kB
HTH
Regards,
--
Guillaume
