Dear Gentlemen,
I have a performance problem with one of the tables of a given DB schema. The schema is not allowed to be modified (except adding index or changing db engines) because it is used by a third party SW which I cannot modify. For reading convenience I have attached a TXT file with the textbody below )
The table is named “result” and is 1.7GB in size.
The schema looks like this:
±------------------±-------------±-----±----±---------- --------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------------------±-------------±-----±----±---------- --------±---------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| create_time | int(11) | NO | | | |
| workunitid | int(11) | NO | MUL | | |
| server_state | int(11) | NO | MUL | | |
| outcome | int(11) | NO | | | |
| client_state | int(11) | NO | | | |
| hostid | int(11) | NO | MUL | | |
| userid | int(11) | NO | MUL | | |
| report_deadline | int(11) | NO | | | |
| sent_time | int(11) | NO | | | |
| received_time | int(11) | NO | MUL | | |
| name | varchar(254) | NO | UNI | | |
| cpu_time | double | NO | | | |
| xml_doc_in | blob | YES | | NULL | |
| xml_doc_out | blob | YES | | NULL | |
| stderr_out | blob | YES | | NULL | |
| batch | int(11) | NO | | | |
| file_delete_state | int(11) | NO | MUL | | |
| validate_state | int(11) | NO | | | |
| claimed_credit | double | NO | | | |
| granted_credit | double | NO | | | |
| opaque | double | NO | | | |
| random | int(11) | NO | | | |
| app_version_num | int(11) | NO | | | |
| appid | int(11) | NO | MUL | | |
| exit_status | int(11) | NO | MUL | | |
| teamid | int(11) | NO | | | |
| priority | int(11) | NO | | | |
| mod_time | timestamp | NO | | CURRENT_TIMESTAMP | |
±------------------±-------------±-----±----±---------- --------±---------------+
29 rows in set (0.07 sec)
mysql> show index from result;
±-------±-----------±------------------±-------------±- -----------------±----------±------------±---------±---- —±-----±-----------±--------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
±-------±-----------±------------------±-------------±- -----------------±----------±------------±---------±---- —±-----±-----------±--------+
| result | 0 | PRIMARY | 1 | id | A | 172984 | NULL | NULL | | BTREE | |
| result | 0 | name | 1 | name | A | 172984 | NULL | NULL | | BTREE | |
| result | 1 | res_wuid | 1 | workunitid | A | 172984 | NULL | NULL | | BTREE | |
| result | 1 | ind_res_st | 1 | server_state | A | 3 | NULL | NULL | | BTREE | |
| result | 1 | ind_res_st | 2 | priority | A | 15 | NULL | NULL | | BTREE | |
| result | 1 | res_app_state | 1 | appid | A | 1 | NULL | NULL | | BTREE | |
| result | 1 | res_app_state | 2 | server_state | A | 3 | NULL | NULL | | BTREE | |
| result | 1 | res_filedel | 1 | file_delete_state | A | 2 | NULL | NULL | | BTREE | |
| result | 1 | res_userid_id | 1 | userid | A | 1383 | NULL | NULL | | BTREE | |
| result | 1 | res_userid_id | 2 | id | A | 172984 | NULL | NULL | | BTREE | |
| result | 1 | res_userid_val | 1 | userid | A | 1383 | NULL | NULL | | BTREE | |
| result | 1 | res_userid_val | 2 | validate_state | A | 2084 | NULL | NULL | | BTREE | |
| result | 1 | res_hostid_id | 1 | hostid | A | 2507 | NULL | NULL | | BTREE | |
| result | 1 | res_hostid_id | 2 | id | A | 172984 | NULL | NULL | | BTREE | |
| result | 1 | res_wu_user | 1 | workunitid | A | 172984 | NULL | NULL | | BTREE | |
| result | 1 | res_wu_user | 2 | userid | A | 172984 | NULL | NULL | | BTREE | |
| result | 1 | idx_received_time | 1 | received_time | A | 10175 | NULL | NULL | | BTREE | |
| result | 1 | idx_exit_status | 1 | exit_status | A | 13 | NULL | NULL | | BTREE | |
±-------±-----------±------------------±-------------±- -----------------±----------±------------±---------±---- —±-----±-----------±--------+
18 rows in set (0.00 sec)
Some more statistics about the particular table:
mysql> SELECT COUNT() FROM result;
±---------+
| COUNT() |
±---------+
| 172984 |
±---------+
1 row in set (0.00 sec)
The following queries take quite long:
SELECT
received_time as raw_date,
DATE_FORMAT(FROM_UNIXTIME(received_time), ‘%d. %M %Y’) AS format_date,
count(case when server_state=5 and outcome=1 then 1 end) as wu_success,
sum(cpu_time/3600) as cpu_hours,
count(case when server_state=5 and outcome!=1 and outcome!=4 then 1 end) as wu_other
FROM
result
GROUP BY
format_date DESC
ORDER BY
raw_date DESC
±—±------------±-------±-----±--------------±-----± --------±-----±-------±--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±-------±-----±--------------±-----± --------±-----±-------±--------------------------------+
| 1 | SIMPLE | result | ALL | NULL | NULL | NULL | NULL | 172984 | Using temporary; Using filesort |
±—±------------±-------±-----±--------------±-----± --------±-----±-------±--------------------------------+
and the second query:
SELECT
count(case when server_state=4 then 1 end) as wu_progress,
count(case when server_state<4 then 1 end) as wu_unsent
FROM
result
WHERE
server_state<5 and received_time=0
±—±------------±-------±-----±----------------------- ------±------------------±--------±------±-------±----- -------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±-------±-----±----------------------- ------±------------------±--------±------±-------±----- -------+
| 1 | SIMPLE | result | ref | ind_res_st,idx_received_time | idx_received_time | 4 | const | 148234 | Using where |
±—±------------±-------±-----±----------------------- ------±------------------±--------±------±-------±----- -------+
They both take around 30seconds which is quite a lot for generating realtime statistics )
Any ideas how to tune them?
Mysql:
Server version: 5.0.30 Gentoo Linux mysql-5.0.30
using myisam tables (no innodb)
key_buffer_size = 240M
The box is a Pentium4/HT 2.4 GHZ (CPU is only at 6% when running the queries) with 512MB RAM.
TIA,
Thomas