Hi
I have a long-running select that's basicly just summing a couple of columns
in a 1-2GB big table.
It lasts for 5-10 hours and I notice that the disc activity is *very* high:
vmstat 1 says:
5 0 0 30332 3068 5240 129260 0 0 1026 0 658 498 63 35 3
4 0 0 30332 3192 5244 129132 0 0 1024 0 617 441 54 44 2
6 0 0 30332 2140 5244 130156 0 0 1024 187 661 464 64 35 2
5 0 0 30332 2168 5240 130160 0 0 1152 0 787 595 65 35 0
6 0 0 30332 3292 5244 130160 0 0 1155 0 574 453 63 31 6
I have 256MB Ram and a Pentium II processor, which variables should I raise
to get this select faster? My current config and the select are attached below.
Thanks in advance for *any* help!
bye,
-christian-
EXPLAIN
SELECT
sum(
channel_traffic.in_bytes + channel_traffic.out_bytes
) as raw_traffic,
channel_traffic.in_bytes + channel_traffic.out_bytes - 1.2* (
channel_traffic.in_uc_packets + channel_traffic.out_uc_packets +
channel_traffic.in_mc_packets + channel_traffic.out_mc_packets
) as adj_traffic,
min(channel_traffic.timestamp) as start,
max(channel_traffic.timestamp) as end,
count(*)
FROM
channel_traffic
WHERE
channel_traffic.timestamp between "2001-02-01" and "2001-02-28 23:59:59"
GROUP BY
channel_traffic.hostname, channel_traffic.interfacename
+-----------------+------+---------------+------+---------+------+--------+-----------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
| |
+-----------------+------+---------------+------+---------+------+--------+-----------------------------+
| channel_traffic | ALL | NULL | NULL | NULL | NULL | 269474 | where
|used; Using temporary |
+-----------------+------+---------------+------+---------+------+--------+-----------------------------+
+----------------+------------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------------------+-------+
| timestamp | datetime | | PRI | 0000-00-00 00:00:00 | |
| hostname | varchar(15) | | PRI | | |
| interfacename | varchar(30) | | PRI | | |
| in_bytes | int(10) unsigned | | | 0 | |
| out_bytes | int(10) unsigned | | | 0 | |
| in_uc_packets | int(10) unsigned | | | 0 | |
| out_uc_packets | int(10) unsigned | | | 0 | |
| in_mc_packets | int(10) unsigned | | | 0 | |
| out_mc_packets | int(10) unsigned | | | 0 | |
+----------------+------------------+------+-----+---------------------+-------+
#log = /var/log/mysql.log
skip-locking
set-variable = key_buffer=64M
set-variable = max_allowed_packet=1M
set-variable = table_cache=128
set-variable = sort_buffer=2M
set-variable = record_buffer=2M
set-variable = thread_cache=2
set-variable = thread_concurrency=2 # Try number of CPU's*2
set-variable = myisam_sort_buffer_size=16M
--
Christian Hammers WESTEND GmbH - Aachen und Dueren Tel 0241/701333-0
[EMAIL PROTECTED] Internet & Security for Professionals Fax 0241/911879
WESTEND ist CISCO Systems Partner - Premium Certified
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
