List:General Discussion« Previous MessageNext Message »
From:Joe Kaiping Date:September 12 2001 4:11pm
Subject:Can't get MySQL to use available memory (performance very slow)
View as plain text  
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


Thread
Can't get MySQL to use available memory (performance very slow)Joe Kaiping12 Sep
  • Re: Can't get MySQL to use available memory (performance very slow)Dan Nelson12 Sep
    • RE: Can't get MySQL to use available memory (performance very slow)Joe Kaiping12 Sep
      • Undefined symbol "strtoll"Amber@aspd.net12 Sep
RE: Can't get MySQL to use available memory (performance very slow)Kent Hoover12 Sep