Thank you very, very much for your reply to this. This database instance lives ona shared server so the most I can currently allocate is 16G to the buffer pool.
Here's the table information:
mysql> SHOW CREATE TABLE WF_JOBS \G;
*************************** 1. row ***************************
Table: WF_JOBS
Create Table: CREATE TABLE `WF_JOBS` (
`id` varchar(255) NOT NULL,
`app_name` varchar(255) default NULL,
`app_path` varchar(255) default NULL,
`conf` text,
`group_name` varchar(255) default NULL,
`run` int(11) default NULL,
`user_name` varchar(255) default NULL,
`bean_type` varchar(31) default NULL,
`auth_token` text,
`created_time` datetime default NULL,
`end_time` datetime default NULL,
`external_id` varchar(255) default NULL,
`last_modified_time` datetime default NULL,
`log_token` varchar(255) default NULL,
`proto_action_conf` text,
`sla_xml` text,
`start_time` datetime default NULL,
`status` varchar(255) default NULL,
`wf_instance` mediumblob,
PRIMARY KEY (`id`),
KEY `I_WF_JOBS_DTYPE` (`bean_type`),
KEY `I_WF_JOBS_END_TIME` (`end_time`),
KEY `I_WF_JOBS_EXTERNAL_ID` (`external_id`),
KEY `I_WF_JOBS_LAST_MODIFIED_TIME` (`last_modified_time`),
KEY `I_WF_JOBS_STATUS` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
mysql> show table status like 'WF_JOBS' \G;
*************************** 1. row ***************************
Name: WF_JOBS
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 838630
Avg_row_length: 66129
Data_length: 55458152448
Max_data_length: 0
Index_length: 429326336
Data_free: 0
Auto_increment: NULL
Create_time: 2013-03-02 03:19:35
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment: InnoDB free: 52224 kB
mysql> explain SELECT t0.id, t0.app_name, t0.status, t0.run, t0.user_name, t0.group_name, t0.created_time, t0.start_time, t0.last_modified_time, t0.end_time FROM WF_JOBS t0 WHERE t0.bean_type = 'WorkflowJobBean' ORDER BY t0.created_time DESC LIMIT 0, 50 \G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t0
type: ref
possible_keys: I_WF_JOBS_DTYPE
key: I_WF_JOBS_DTYPE
key_len: 34
ref: const
rows: 419265
Extra: Using where; Using filesort
mysql> show variables like '%buffer%';
+-------------------------------+-------------+
| Variable_name | Value |
+-------------------------------+-------------+
| bdb_log_buffer_size | 262144 |
| bulk_insert_buffer_size | 8388608 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 17179869184 |
| innodb_log_buffer_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 16777216 |
| myisam_sort_buffer_size | 8388608 |
| net_buffer_length | 16384 |
| preload_buffer_size | 32768 |
| read_buffer_size | 262144 |
| read_rnd_buffer_size | 524288 |
| sort_buffer_size | 131072 |
+-------------------------------+-------------+
The backups are being run using mysqldump on a nightly basis. I won't be able to upgrade DB versions for another 3-4 months so I am trying to make this work as best as possible.
Again, thank you for your reply!