Hi there,
I'm having trouble configuring MySQL 3.23.36 running on a Sparc/SunOS 5.8 to
use the available 1 gig of memory and was wondering if anyone might be able
to help find a solution.
/usr/ucb/ps -aux gives the stats:
USER PID %CPU %MEM SZ RSS TT S START TIME COMMAND
root 2120 36.6 7.044881669800 ? O Sep 11 16:46
/u01/opt/MySQL/lib
when a single user is running a simple query like:
SELECT count(id) FROM ind WHERE cust=1 AND email<>'';
This query takes 8.29 seconds to run and there are less than 200,000 records
in the ind table. Different queries don't change the percentage of memory
being used, only the CPU usage seems to change.
I tried adding an index to the ind table for the cust and email fields, but
the query took even longer since about 175,000 records have nonempty email
fields.
mysqld is using the huge.cnf configuration file for 1G-2G systems (settings
from file are listed at end of this message)
I've also played around with tweaking command line settings via the
safe_mysqld commands suggested via
http://www.mysql.com/doc/S/e/Server_parameters.html such as
shell> safe_mysqld -O key_buffer=64M -O table_cache=256 \
-O sort_buffer=4M -O record_buffer=1M &
with little or no improvement.
Can someone explain why MySQL isn't using more memory or point me in the
right direction to improve MySQL's performance?
Many thanks in advance,
Joe Kaiping
==================
cnf file settings:
==================
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = key_buffer=384M
set-variable = max_allowed_packet=1M
set-variable = table_cache=512
set-variable = sort_buffer=2M
set-variable = record_buffer=2M
set-variable = thread_cache=8
set-variable = thread_concurrency=4 # Try number of CPU's*2
set-variable = myisam_sort_buffer_size=64M
log-bin
server-id = 1
[isamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[myisamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[mysqlhotcopy]
interactive-timeout
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
