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
--
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
