MySQL Forums
Forum List  »  Performance

Queries/sec = 0.02 !!
Posted by: Mike Muratet
Date: April 04, 2005 07:07AM

I have a fairly large query that has been running for days:

CREATE TABLE G_intraradices.nt_go_hits
SELECT d.accession as git, term.name, term.term_type, term.acc, term.is_obsolete, term.is_root, gene_product.symbol, gene_product.full_name,dbxref.xref_key, b.record_id, d.accession
FROM
dbxref INNER JOIN gene_product ON (dbxref.id = gene_product.dbxref_id)
INNER JOIN association ON (association.gene_product_id = gene_product.id)
INNER JOIN term ON (association.term_id = term.id)
INNER JOIN seqhound.dbxref a ON (a.record_id = dbxref.xref_key)
INNER JOIN seqhound.dbxref b ON (a.parent_id = b.id)
INNER JOIN G_intraradices.nref_hits_blast_target c ON (c.accession = b.record_id)
INNER JOIN G_intraradices.nref_hits_blast_query d ON (c.query_id=d.query_id)
;

The 'explain' output from this query looks OK according to the documentation (no red flags). I can email the output if need be.

This query has racked up over 5000 min of CPU time.

I checked the status of the server and it's only processing 0.02 querys per second!

show status reveals:

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 8 |
| Aborted_connects | 12 |
| Bytes_received | 130781206 |
| Bytes_sent | 39398444 |
| Com_admin_commands | 0 |
| Com_alter_table | 2 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 30 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 665 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 5 |
| Com_create_table | 1 |
| Com_delete | 0 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 2662 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 3 |
| Com_select | 6732 |
| Com_set_option | 2 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 36 |
| Com_show_fields | 370 |
| Com_show_grants | 0 |
| Com_show_keys | 10 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 5 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 39 |
| Com_show_variables | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 2 |
| Connections | 170 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 92 |
| Created_tmp_files | 4 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 665 |
| Handler_delete | 0 |
| Handler_read_first | 18 |
| Handler_read_key | 2767902599 |
| Handler_read_next | 2151970960 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 3081 |
| Handler_read_rnd_next | 51568336 |
| Handler_rollback | 103 |
| Handler_update | 15578 |
| Handler_write | 49104829 |
| Key_blocks_used | 7793 |
| Key_read_requests | 3020442243 |
| Key_reads | 676969 |
| Key_write_requests | 49929390 |
| Key_writes | 49929390 |
| Max_used_connections | 3 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 64 |
| Open_files | 59 |
| Open_streams | 0 |
| Opened_tables | 140 |
| Questions | 10717 |
| Qcache_queries_in_cache | 0 |
| Qcache_inserts | 0 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_free_memory | 0 |
| Qcache_free_blocks | 0 |
| Qcache_total_blocks | 0 |
| Rpl_status | NULL |
| Select_full_join | 33 |
| Select_full_range_join | 0 |
| Select_range | 6 |
| Select_range_check | 0 |
| Select_scan | 97 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 13 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 3081 |
| Sort_scan | 90 |
| Table_locks_immediate | 9500 |
| Table_locks_waited | 0 |
| Threads_cached | 0 |
| Threads_created | 169 |
| Threads_connected | 4 |
| Threads_running | 4 |
| Uptime | 528484 |

It's basically the way it comes out of the box.

Does anybody have any idea why the performance is so horrible?

thanks

Mike

Options: ReplyQuote


Subject
Views
Written By
Posted
Queries/sec = 0.02 !!
2448
April 04, 2005 07:07AM
1724
April 05, 2005 09:16PM


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.