List:General Discussion« Previous MessageNext Message »
From:Joe Kaiping Date:September 12 2001 4:28pm
Subject:RE: Can't get MySQL to use available memory (performance very slow)
View as plain text  
Thanks for the quick reply, Dan.

The EXPLAIN SELECT for that SELECT outputs:

mysql> EXPLAIN SELECT count(id) FROM ind WHERE cust=1 AND email<>'';
+-------+------+---------------+------+---------+------+--------+-----------
-+
| table | type | possible_keys | key  | key_len | ref  | rows   | Extra
|
+-------+------+---------------+------+---------+------+--------+-----------
-+
| ind   | ALL  | NULL          | NULL |    NULL | NULL | 181831 | where used
|
+-------+------+---------------+------+---------+------+--------+-----------
-+

I first tried using a 2 field index with slower results and then tried using
separate single field indexes, but that was also slower than not using any
index.

The InnoDB table type sounds promising, so I'll start looking into that.

If anyone else has other suggestions, they really are most appreciated!!

Thanks again,
Joe

> -----Original Message-----
> From: Dan Nelson [mailto:dnelson@stripped]
> Sent: Wednesday, September 12, 2001 9:17 AM
> To: Joe Kaiping
> Cc: mysql@stripped
> Subject: Re: Can't get MySQL to use available memory (performance very
> slow)
>
>
> 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