List:General Discussion« Previous MessageNext Message »
From:Bruce Ferrell Date:October 3 2011 4:38am
Subject:Re: 4 minute slow on select count(*) from table - myisam type
View as plain text  
The meaning is:

increase max_connections
reduce wait_timeout
-- 28800 is wait 8 hours before closing out dead connections
same for interactive_timeout


increase key_buffer_size (> 7.8G) increase join_buffer_size
-- This keeps mysql from having to run to disk constantly for keys
-- Key buffer size / total MyISAM indexes: 256.0M/7.8G
-- You have a key buffer of 256M and 7.8G of keys

join_buffer_size (> 128.0K, or always use indexes with joins)
Joins performed without indexes: 23576 of 744k queries.
-- You probably want to look at the slow query log.  Generalize the queries and the do an
explain on the query.  I have seen instances where a query I thought was using an index
wasn't and I had to re-write... with help from this list :-)  Thanks gang!


increase tmp_table_size (> 16M)
increase max_heap_table_size (> 16M)
-- When making adjustments, make tmp_table_size/max_heap_table_size equal

increase table_cache ( > 1k )
-- Table cache hit rate: 7% (1K open / 14K opened)
-- Increase table_cache gradually to avoid file descriptor limits

All of the aside, you need to let this run for at least 24 hours. I
prefer 48 hours.  The first line says mysql has only been running 9
hours.   You can reset the timeouts interactivly by entering at the
mysql prompt:

set global wait_timeout=<some value>

You can do the same for the interactive_timeout.

Setting these values too low will cause long running queries to abort


On 10/02/2011 07:02 PM, Joey L wrote:
> Variables to adjust:
> >     max_connections (> 100)
> >     wait_timeout (< 28800)
> >     interactive_timeout (< 28800)
> >     key_buffer_size (> 7.8G)
> >     join_buffer_size (> 128.0K, or always use indexes with joins)
> >     tmp_table_size (> 16M)
> >     max_heap_table_size (> 16M)
> >     table_cache (> 1024)

Thread
4 minute slow on select count(*) from table - myisam typeJoey L2 Oct
  • Re: 4 minute slow on select count(*) from table - myisam typeAndrew Moore2 Oct
    • Re: 4 minute slow on select count(*) from table - myisam typeJoey L2 Oct
  • Re: 4 minute slow on select count(*) from table - myisam typeBruce Ferrell2 Oct
Re: 4 minute slow on select count(*) from table - myisam typeAndrew Moore2 Oct
  • Re: 4 minute slow on select count(*) from table - myisam typeJoey L3 Oct
    • Re: 4 minute slow on select count(*) from table - myisam typeJoey L3 Oct
      • Re: 4 minute slow on select count(*) from table - myisam typeBruce Ferrell3 Oct
        • Re: 4 minute slow on select count(*) from table - myisam typeJoey L3 Oct
          • Re: 4 minute slow on select count(*) from table - myisam typeAndrĂ©s Tello3 Oct
            • Re: 4 minute slow on select count(*) from table - myisam typeJoey L3 Oct
              • Re: 4 minute slow on select count(*) from table - myisam typeEric Bergen3 Oct
                • Re: 4 minute slow on select count(*) from table - myisam typeJoey L6 Oct
                  • Re: 4 minute slow on select count(*) from table - myisam typeJoey L6 Oct
                    • Re: 4 minute slow on select count(*) from table - myisam typeJohnny Withers6 Oct
                      • Re: 4 minute slow on select count(*) from table - myisam typeJoey L6 Oct
                        • Re: 4 minute slow on select count(*) from table - myisam typeJohnny Withers6 Oct
                        • Re: 4 minute slow on select count(*) from table - myisam typeJohan De Meersman6 Oct
                        • Re: 4 minute slow on select count(*) from table - myisam typeRik Wasmus6 Oct
                          • Re: 4 minute slow on select count(*) from table - myisam typeJoey L6 Oct
                            • Re: 4 minute slow on select count(*) from table - myisam typeAndrew Moore6 Oct
                              • Re: 4 minute slow on select count(*) from table - myisam typeAndrew Moore6 Oct
                                • Re: 4 minute slow on select count(*) from table - myisam typeJoey L6 Oct
                                  • Re: 4 minute slow on select count(*) from table - myisam typeMichael Dykman6 Oct
Re: 4 minute slow on select count(*) from table - myisam typeAndrew Moore6 Oct
  • Re: 4 minute slow on select count(*) from table - myisam typeJoey L6 Oct
Re: 4 minute slow on select count(*) from table - myisam typeJohnny Withers6 Oct
  • Re: 4 minute slow on select count(*) from table - myisam typeJohan De Meersman7 Oct
Re: 4 minute slow on select count(*) from table - myisam typeJan Steinman7 Oct
  • Re: 4 minute slow on select count(*) from table - myisam typeJoey L7 Oct
    • Re: 4 minute slow on select count(*) from table - myisam typeAndrew Moore7 Oct