southbeach030

I should add, that myphpadmin mentioned this about some joins.. and that I need indexes somewhere.. I just can’t figure out where (mysql was restarted just 4 hours ago):

Select_full_join 492
The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.

I’m just trying to find out which tables can be indexed, and if anyone can help determine the proper way to do it (i.e., which tables to index, and how)

The throttle plugin is ok, the bad thing about it however, is that when it’s using the coral cache site, users cannot comment..

I’d like to refresh this topic with three questions:

• Is there a simple tool that I can use to determine what SQL queries are taking up the most CPU %? MySQL database is taking 60-90% CPU resources at peak times. And by simple, I mean something a non-DBA would understand and can use with minimal effort. I have access to myphpadmin, and Putty. I’ve looked at some of the running processes and cannot seem to make sense of it.
• Can it tell me which tables, rows, etc are taking the longest?
• Once I know these things, how can I set up an index or indexes in MySQL phpmyadmin to at least TRY to reduce the query time and CPU load?

Thank you very much for your help.

Thanks omgitztrey.. However, let me explain..

The Query Cache LIMIT is 6MB..
The Query Cache SIZE is 32MB… I never use the whole cache.. for instance it’s now 17MB FREE. I reduced it from 128MB to 32MB…

How can I find out which plugins are using hogging OUTER joins instead of INNER or LEFT joins? And if so, how can I re-write the plugin to be more efficient and prevent exponential server performance degradations?

It seems the larger my site gets, the more and more slow it will become, because of the ill-written plugins…

Thanks!

FYI, this isn’t peak time anymore, but here’s a look at MySQL processes…

Do you see anything out of whack?

+—————————-+————+
| Variable_name | Value |
+—————————-+————+
| Aborted_clients | 70 |
| Aborted_connects | 6 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 248678028 |
| Bytes_sent | 2040588199 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 108826 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 623 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 6195 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 379 |
| Com_optimize | 134 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 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_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 377431 |
| Com_set_option | 91500 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 1 |
| Com_show_charsets | 30 |
| Com_show_collations | 30 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 30 |
| Com_show_errors | 0 |
| Com_show_fields | 14 |
| Com_show_grants | 25 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 14 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 2761 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 2625 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 108 |
| Com_show_variables | 82 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 51 |
| Com_unlock_tables | 379 |
| Com_update | 82739 |
| Com_update_multi | 2 |
| Connections | 108949 |
| Created_tmp_disk_tables | 28563 |
| Created_tmp_files | 14 |
| Created_tmp_tables | 42996 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 436 |
| Handler_discover | 0 |
| Handler_read_first | 168 |
| Handler_read_key | 860617083 |
| Handler_read_next | 1045433842 |
| Handler_read_prev | 98 |
| Handler_read_rnd | 5506672 |
| Handler_read_rnd_next | 1619630869 |
| Handler_rollback | 0 |
| Handler_update | 475236933 |
| Handler_write | 42462448 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 53866 |
| Key_blocks_used | 10477 |
| Key_read_requests | 2143416861 |
| Key_reads | 547649 |
| Key_write_requests | 40744743 |
| Key_writes | 7974 |
| Max_used_connections | 93 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 295 |
| Open_streams | 0 |
| Open_tables | 227 |
| Opened_tables | 2468 |
| Qcache_free_blocks | 999 |
| Qcache_free_memory | 16225912 |
| Qcache_hits | 1704671 |
| Qcache_inserts | 339029 |
| Qcache_lowmem_prunes | 53193 |
| Qcache_not_cached | 38340 |
| Qcache_queries_in_cache | 5017 |
| Qcache_total_blocks | 11319 |
| Questions | 2485169 |
| Rpl_status | NULL |
| Select_full_join | 3148 |
| Select_full_range_join | 0 |
| Select_range | 11354 |
| Select_range_check | 0 |
| Select_scan | 12771 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 7 |
| Slow_queries | 17593 |
| Sort_merge_passes | 7 |
| Sort_range | 109414 |
| Sort_rows | 5131944 |
| Sort_scan | 46120 |
| Table_locks_immediate | 536182 |
| Table_locks_waited | 3135 |
| Threads_cached | 81 |
| Threads_connected | 12 |
| Threads_created | 93 |
| Threads_running | 1 |
| Uptime | 42514 |
+—————————-+————+

Question.. almost the same issue as Original Poster…

What is the simplest and EASIEST way to find out WHICH plugin, or file is hogging the CPU %? I cannot do “process of elimination” as that is too time consuming.

Is there a way to find out? If so, can someone please simply detail what I need to do?

I have done everything possible too. Caching, opcode, varnish, ALL of it.

Site slower than molasses.

I actually solved the problem. It was two-fold.

1) I had to update the Category Visibility plugin to 1.0e
2) then I realized that the theme I spent 5 hours working on (porting the layout from the old one) was designed for WORDPRESS 2.1.X+ (I’m still on 2.0.X)

I should have realized that sooner.

Unrelated question: Does anyone know if someone is working on a similar plugin for Quoting Comments? The reason I haven’t upgraded to 2.2.X yet is because I rely on the Quoter plugin, and it’s not supported.

Oh, and the reason I mentioned 2.2 and better DB performance was due to the fact that HyperDB only works on 2.2 and higher.

sorry about that.. you can contact ( mrk030 -at- hotmail -dot- com ) to get in touch with me regarding any offers to help take my blog to 2.2

I was just afraid of spam… hope that helps.

I plan on giving that a test this weekend.

But I’d still like to go from 2.0.10 to 2.2 as my ultimate goal, as I’m looking for any DB performance increases as I can get.

— Anything I should be worried about going from 2.0.6 to 2.0.10 (especially with plugins, etc)?

Are there any performance improvements between version 2.0.6 and 2.0.10?

I couldn’t find “Function __ Destruct” in my wp-dp.php file.. I’m using WP 2.0.6… Is this named something else in my version?

What can I do similarly to force SQL to close connections in 2.0.6?

Thank you!

I’d like to further add that my host gave me this information.

Is this something I can do myself and investigate within wordpress? Are there any plugins that help close SQL connections anywhere?

I have seen a number of cases recently where the MySQL code was not properly closed which resulted in too many MySQL connections remaining open. You will want to check the code to ensure that not only does it close the MySQL connection, but that it also clears the RAM from the connection:

#connect to mysql
$link = mysql_connect(‘localhost’, ‘database_user’, ‘database_password’);

#run query
$sqlString = “(SELECT variable1, varable2, … and so on $result = mysql_query($sqlString);

#cleanup memory
mysql_free_result($result);

#close mysql connection
mysql_close($link);

FYI, I meant the post title to be “time capsule”. This is the plugin I’d like to use, and having difficulty with.

Thanks!

Agreed. Anyone have any idea to create these? It would save a lot of time for me as well.

How hard could it be? What file would I have to edit?