GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

mysql> SHOW STATUS; Optimization of my.cnf

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • mysql> SHOW STATUS; Optimization of my.cnf

    mysql> SHOW STATUS;+----------------------------+------------+| Variable_name | Value |+----------------------------+------------+| Aborted_clients | 8010 || Aborted_connects | 2 || Binlog_cache_disk_use | 0 || Binlog_cache_use | 0 || Bytes_received | 3657146200 || Bytes_sent | 224393733 || Com_admin_commands | 383491 || Com_alter_db | 0 || Com_alter_table | 0 || Com_analyze | 0 || Com_backup_table | 0 || Com_begin | 0 || Com_change_db | 383522 || 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 | 261857 || 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 | 78712 || Com_insert_select | 3713 || Com_kill | 0 || Com_load | 0 || Com_load_master_data | 0 || Com_load_master_table | 0 || Com_lock_tables | 0 || Com_optimize | 0 || 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 | 2134999 || Com_set_option | 0 || Com_show_binlog_events | 0 || Com_show_binlogs | 0 || Com_show_charsets | 0 || Com_show_collations | 0 || Com_show_column_types | 0 || Com_show_create_db | 0 || Com_show_create_table | 0 || Com_show_databases | 4 || Com_show_errors | 0 || Com_show_fields | 0 || Com_show_grants | 0 || Com_show_innodb_status | 0 || Com_show_keys | 0 || 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 | 0 || Com_show_slave_hosts | 0 || Com_show_slave_status | 0 || Com_show_status | 32 || Com_show_storage_engines | 0 || Com_show_tables | 13 || Com_show_variables | 30 || 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 | 0 || Com_unlock_tables | 0 || Com_update | 555381 || Com_update_multi | 0 || Connections | 5208 || Created_tmp_disk_tables | 93842 || Created_tmp_files | 0 || Created_tmp_tables | 373179 || Delayed_errors | 0 || Delayed_insert_threads | 0 || Delayed_writes | 0 || Flush_commands | 2 || Handler_commit | 0 || Handler_delete | 87953 || Handler_discover | 0 || Handler_read_first | 39857 || Handler_read_key | 927004079 || Handler_read_next | 701287506 || Handler_read_prev | 0 || Handler_read_rnd | 54818936 || Handler_read_rnd_next | 3501770244 || Handler_rollback | 0 || Handler_update | 270485714 || Handler_write | 59013466 || Key_blocks_not_flushed | 0 || Key_blocks_unused | 377684 || Key_blocks_used | 144707 || Key_read_requests | 2502344390 || Key_reads | 238504 || Key_write_requests | 2206636 || Key_writes | 524553 || Max_used_connections | 17 || Not_flushed_delayed_rows | 0 || Open_files | 170 || Open_streams | 0 || Open_tables | 146 || Opened_tables | 146 || Qcache_free_blocks | 4496 || Qcache_free_memory | 93720960 || Qcache_hits | 3359978 || Qcache_inserts | 2132197 || Qcache_lowmem_prunes | 0 || Qcache_not_cached | 2802 || Qcache_queries_in_cache | 10143 || Qcache_total_blocks | 24808 || Questions | 9585208 || Rpl_status | NULL || Select_full_join | 0 || Select_full_range_join | 0 || Select_range | 224361 || Select_range_check | 0 || Select_scan | 591332 || Slave_open_temp_tables | 0 || Slave_retried_transactions | 0 || Slave_running | OFF || Slow_launch_threads | 0 || Slow_queries | 653 || Sort_merge_passes | 0 || Sort_range | 342065 || Sort_rows | 1262701352 || Sort_scan | 395665 || Table_locks_immediate | 5921110 || Table_locks_waited | 7383 || Threads_cached | 2 || Threads_connected | 15 || Threads_created | 21 || Threads_running | 1 || Uptime | 145450 |+----------------------------+------------+163 rows in set (0.44 sec)



    this is what SHOW STATUS; commands give me can anyone provide me any helpful tips on what I can adjust in my.cnf to lower my server load which is above 10 during peak hours and mysql is the only thing eating this serve up I have lots og 3gb ram and a 64bit AMD 2800 server

    Heres what my.cnf looks like

    [mysqld]safe-show-database#old_passwordsback_log = 100skip-innodbmax_connections = 600key_buffer = 768Mmyisam_sort_buffer_size = 64Mjoin_buffer_size = 1Mread_buffer_size = 1Msort_buffer_size = 3Mtable_cache = 93072thread_cache_size = 320wait_timeout = 30connect_timeout = 10tmp_table_size = 128Mmax_heap_table_size = 64Mmax_allowed_packet = 64Mmax_connect_errors = 10read_rnd_buffer_size = 5Mbulk_insert_buffer_size = 16Mquery_cache_limit = 5Mquery_cache_size = 100Mquery_cache_type = 1query_prealloc_size = 163840query_alloc_block_size = 32768default-storage-engine = MyISAMlow_priority_updates=1


    Heres a screenshot of TOP COmmand
    http://img300.imageshack.us/img300/3771/untitledaf0.gif


    If you need anything else tell me the command and I will run it and give screenshot please help me

  • #2
    Adjusting my.cnf won't help much. There are however some things that can be changed:
    1) decrease size of key buffer since you're not using most of it
    2) unset low_priority_updates unless you really did it knowingly and it helped
    3) unset query_prealloc_size and query_alloc_block_size unless your values really work better than default ones
    4) decrease table_cache - you're using 146, there's no need to set it at 93k (although, like with key_buffer, having larger value doesn't hurt)
    5) set both tmp_table_size and max_heap_table_size to the same value (having them different is weird).
    6) decrease sort_buffer_size as MySQL has a bug with it

    Remember, all this stuff won't help much with performance. You'll have to analyze slow queries and optimize indexes and/or queries.

    Comment


    • #3
      well do but can you tell me the exact size to lower down to?

      what else can I do to make Mysql use more ram and increase performances

      Comment


      • #4
        a good place to start is to turn on the slow queries log


        # log slow queries
        log-slow-queries=/var/log/mysql/slow-queries.log

        # defines a slow query as any query >= 1 second
        long_query_time=1

        then run those queries with EXPLAIN to find out if they are using indexes etc and how you might improve them.

        if you are using 5.0 you can also log-queries-not-using-indexes

        after you do that, then start tweaking your other settings for memory etc.

        Comment


        • #5
          I added that in my.cnf and restarted mysql but nothing was logged its a empty blank file??

          Comment


          • #6
            # log slow queries
            log-slow-queries=/www/mysql-slow.log

            # defines a slow query as any query >= 1 second
            long_query_time=1

            thats what I placed in my.cnf

            Comment


            • #7
              does the mysql user have permissions to write to /www/mysql-slow.sql ?

              probably not, and you probably do not want mysql to have write permission to /www


              # make a log directory for mysql under /var/log

              mkdir -p /var/log/mysql

              # chown it to the mysql user
              chown mysql.mysql /var/log/mysql


              then change your log-slow-queries line to this...

              log-slow-queries=/var/log/mysql/mysql-slow.log

              Comment


              • #8
                I followed your instruction I will check back in 3 hours for any logs and will post them

                Comment


                • #9
                  if you have the proper permissions you should get a little message at the beginning of your slow log like this...

                  /usr/sbin/mysqld, Version: 4.1.11-standard-log. started with:
                  Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock

                  Comment


                  • #10
                    its still blank

                    Comment


                    • #11
                      Time Id Command Argument# Time: 070219 19:17:04# User@Host: admin_db[admin_db] @ localhost []# Query_time: 11 Lock_time: 0 Rows_sent: 0 Rows_examined: 13273805use admin_db17;SELECT word_id FROM phpbb_search_wordmatch GROUP BY word_id HAVING COUNT(word_id) > 259734;


                      This is one of the logged queries, what can I do to optimze this query?

                      Comment


                      • #12
                        Time Id Command Argument# Time: 070219 19:17:04# User@Host: admin_kbdb[admin_kbdb] @ localhost []# Query_time: 11 Lock_time: 0 Rows_sent: 0 Rows_examined: 13273805use admin_admin;SELECT word_id FROM phpbb_search_wordmatch GROUP BY word_id HAVING COUNT(word_id) > 259734;

                        Thats one of the Slow queries that was logged it seems its this table giving all bad slow queries phpbb_search_wordmatch.

                        Comment


                        • #13
                          Sorry, I have been away for a few days..

                          please post the results of these two queries....


                          SHOW INDEX FROM word_id;



                          EXPLAIN SELECT word_id FROM phpbb_search_wordmatch GROUP BY word_id HAVING COUNT(word_id) > 259734;

                          Comment


                          • #14
                            red_wolf wrote on Tue, 20 February 2007 06:56


                            Time Id Command Argument# Time: 070219 19:17:04# User@Host: admin_db[admin_db] @ localhost []# Query_time: 11 Lock_time: 0 Rows_sent: 0 Rows_examined: 13273805use admin_db17;SELECT word_id FROM phpbb_search_wordmatch GROUP BY word_id HAVING COUNT(word_id) > 259734;


                            This is one of the logged queries, what can I do to optimze this query?


                            There's nothing you can do about it. It's a very bad application design.
                            Consider disabling phpBB search, or finding some advanced phpBB fulltext search extension.

                            For a big forum I'd switch to one of the proprietary scripts like VB or IPB.

                            Comment

                            Working...
                            X