List:General Discussion« Previous MessageNext Message »
From:<jabbott Date:November 4 2003 10:09pm
Subject:query time in ~3M row table
View as plain text  
I have a question about how long queries should be taking and if my server is too small
for what I want to be doing.  I have a table setup to record stats from an apache web
server.  I import the file currently once per month.  Here is my table:
mysql> describe hitStats;
+-------------+--------------+-------------------+------+-----+---------------------+----------------+
| Field       | Type         | Collation         | Null | Key | Default             |
Extra          |
+-------------+--------------+-------------------+------+-----+---------------------+----------------+
| hostIP      | varchar(24)  | latin1_swedish_ci |      |     |                     |     
          |
| apacheDate  | datetime     | latin1_swedish_ci |      | MUL | 0000-00-00 00:00:00 |     
          |
| status      | int(11)      | binary            |      |     | 0                   |     
          |
| bytes       | varchar(20)  | latin1_swedish_ci |      |     |                     |     
          |
| contentType | varchar(40)  | latin1_swedish_ci |      |     |                     |     
          |
| url         | varchar(255) | latin1_swedish_ci |      | MUL |                     |     
          |
| referer     | text         | latin1_swedish_ci |      |     |                     |     
          |
| agent       | text         | latin1_swedish_ci |      |     |                     |     
          |
| statID      | int(11)      | binary            |      | PRI | NULL                |
auto_increment |
+-------------+--------------+-------------------+------+-----+---------------------+----------------+

As for what it has in it:
mysql> select count(*) from hitStats;                                          
> +----------+
| count(*) |
+----------+
|  2749862 |
+----------+
1 row in set (0.00 sec)

So, about 3 million rows.

I have this running on a Sun Blade 150 workstation w/ Solaris 9.  I think it has 1G
memory.  MySql 4.1 is the only thing running on this server.  I have another server setup
just like this one only with mysql 3.x and the times were similar last month when I was
testing it.

I have read the http://www.mysql.com/doc/en/Server_parameters.html page and think that I
did what it told me to.  

When I try to do any sort of query the times are really long.  Such as:

mysql> select count(*) from hitstats where year(apacheDate) = 2003 and
month(apacheDate) = 9;                               +----------+
| count(*) |
+----------+
|   988759 |
+----------+
1 row in set (25.17 sec)

Running top shows:
last pid: 22934;  load averages:  0.42,  0.13,  0.08               
35 processes:  34 sleeping, 1 on cpu
CPU states: 12.4% idle, 87.6% user,  0.0% kernel,  0.0% iowait,  0.0% swap
Memory: 768M real, 405M free, 433M swap in use, 642M swap free

   PID USERNAME THR PRI NICE  SIZE   RES STATE    TIME    CPU COMMAND
 22921 mysql     13  59    0  414M  201M sleep    3:39 67.73% mysqld 

So I guess my question is, do I not have a fast enough server?  Is this database too much
for the machine I am running it on?  Or do I not have it optimized or is something else
going wrong?

--ja
-- 

Thread
query time in ~3M row tablejabbott4 Nov
  • Re: query time in ~3M row tableBrent Baisley4 Nov
    • Re: query time in ~3M row tablejabbott5 Nov
      • Re: query time in ~3M row tableMatt W5 Nov
        • Re: query time in ~3M row tableBrent Baisley5 Nov
          • Re: query time in ~3M row tableDan Nelson5 Nov
            • Re: query time in ~3M row tableBrent Baisley5 Nov
      • Re: query time in ~3M row tableBrent Baisley5 Nov
      • Re: query time in ~3M row tableMatt W5 Nov
  • Re: query time in ~3M row tableDan Nelson5 Nov