List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:September 12 2001 4:16pm
Subject:Re: Can't get MySQL to use available memory (performance very slow)
View as plain text  
In the last episode (Sep 12), Joe Kaiping said:
> 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.

Mysql does not cache table data for the MyISAM type, so you won't see
any memory change there.  What does an EXPLAIN SELECT.. print for the
above query?
 
> 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.

A single 2-column index, or two separate indexes?  Mysql can only use
one index per query, so a compound index would help the most here.

If your problem truly is disk I/O, you can try using the InnoDB table
type, which caches both index and table data in memory.

-- 
	Dan Nelson
	dnelson@stripped
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