MySQL Forums
Forum List  »  InnoDB

Re: Help Optimizing Large Query Sort (Detailed)
Posted by: mysqluser 0013
Date: March 06, 2013 02:40PM

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!

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Help Optimizing Large Query Sort (Detailed)
1191
March 06, 2013 02:40PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.