List:General Discussion« Previous MessageNext Message »
From:Joey L Date:October 6 2011 1:40pm
Subject:Re: 4 minute slow on select count(*) from table - myisam type
View as plain text  
thanks for the response - but do not believe queries are the issue
because - Like I said - i have other websites doing the same exact
queries as I am doing on the site with the 9gig table.
-- my issue is optimizing mysql to handle lots of queries on a 9gig
db. --- i think that is the focus.
All other websites (10 websites) are being handled fine in terms of
performance - with same queries -- just that table is about 100meg.
I have run optimize on it and recover and prune,etc....no luck.
thanks
mjh

On Thu, Oct 6, 2011 at 9:15 AM, Johnny Withers <johnny@stripped> wrote:
> I think in order to solve your problem you will need to post the queries running
> against this table along with the explain output of each problem query. Optimizing server
> settings is a good start, however, individual query performance sounds like your problem
> now.
>
> Sent from my iPad
>
> On Oct 6, 2011, at 6:47 AM, Joey L <mjh2000@stripped> wrote:
>
>> Just as an fyi - I have other databases and their corresponding apache
>> websites on the same server - performing okay.
>> It seems that apache/mysql server is just having a hard time dealing
>> with the access to those pages that deal with the 9gig table on that
>> particular site.  -- Most of the access is done by webcrawlers to the
>> site - so there is a lot of activity occuring on the 9gig tables.
>>
>> thanks
>> mjh
>>
>> On Thu, Oct 6, 2011 at 6:13 AM, Joey L <mjh2000@stripped> wrote:
>>> guys - i am having such a hard time with this..it is killing me!!!
>>> Sorry - had to vent.
>>> my machine is running an tyan S2912G2NR  -- with 2 opterons and 12gig
>>> of memory. I have 2 software raided drives 1gig each.
>>> I run a couple of databases --- my largest table is about 9gig in
>>> size. --it is being accessed a lot.
>>> My my.cnf is as follows:
>>>
>>> #
>>> # The MySQL database server configuration file.
>>> #
>>> # You can copy this to one of:
>>> # - "/etc/mysql/my.cnf" to set global options,
>>> # - "~/.my.cnf" to set user-specific options.
>>> #
>>> # One can use all long options that the program supports.
>>> # Run program with --help to get a list of available options and with
>>> # --print-defaults to see which it would actually understand and use.
>>> #
>>> # For explanations see
>>> # http://dev.mysql.com/doc/mysql/en/server-system-variables.html
>>>
>>> # This will be passed to all mysql clients
>>> # It has been reported that passwords should be enclosed with ticks/quotes
>>> # escpecially if they contain "#" chars...
>>> # Remember to edit /etc/mysql/debian.cnf when changing the socket location.
>>> [client]
>>> port            = 3306
>>> socket          = /var/run/mysqld/mysqld.sock
>>>
>>> # Here is entries for some specific programs
>>> # The following values assume you have at least 32M ram
>>>
>>> # This was formally known as [safe_mysqld]. Both versions are currently
> parsed.
>>> [mysqld_safe]
>>> socket          = /var/run/mysqld/mysqld.sock
>>> nice            = 0
>>>
>>> [mysqld]
>>> #
>>> # * Basic Settings
>>> #
>>> user            = mysql
>>> pid-file        = /var/run/mysqld/mysqld.pid
>>> socket          = /var/run/mysqld/mysqld.sock
>>> port            = 3306
>>> basedir         = /usr
>>> datadir         = /var/lib/mysql
>>> tmpdir          = /tmp
>>> language        = /usr/share/mysql/english
>>> skip-external-locking
>>> #
>>> # Instead of skip-networking the default is now to listen only on
>>> # localhost which is more compatible and is not less secure.
>>> bind-address            = 127.0.0.1
>>> #
>>> # * Fine Tuning
>>> #
>>> key_buffer              = 2G
>>> key_buffer_size         = 2G
>>> max_allowed_packet      = 16M
>>> thread_stack            = 192K
>>> thread_cache_size       = 8
>>> join_buffer_size                =
> 128
>>> # This replaces the startup script and checks MyISAM tables if needed
>>> # the first time they are touched
>>> myisam-recover         = BACKUP
>>> max_connections        = 100
>>> table_cache            = 1024
>>> max_heap_table_size             = 32M
>>> tmp_table_size                
>  = 32M
>>> thread_concurrency     = 10
>>> #
>>> # * Query Cache Configuration
>>> #
>>> query_cache_limit       = 2M
>>> query_cache_size        = 16M
>>> #
>>> # * Logging and Replication
>>> #
>>> # Both location gets rotated by the cronjob.
>>> # Be aware that this log type is a performance killer.
>>> # As of 5.1 you can enable the log at runtime!
>>> general_log_file        = /var/log/mysql/mysql.log
>>> general_log             = 2
>>> #
>>> # Error logging goes to syslog due to
> /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
>>> #
>>> # Here you can see queries with especially long duration
>>> #log_slow_queries       = /var/log/mysql/mysql-slow.log
>>> #long_query_time = 2
>>> #log-queries-not-using-indexes
>>> #
>>> # The following can be used as easy to replay backup logs or for
> replication.
>>> # note: if you are setting up a replication slave, see README.Debian about
>>> #       other settings you may need to change.
>>> #server-id              = 1
>>> #log_bin                  
>      = /var/log/mysql/mysql-bin.log
>>> expire_logs_days        = 10
>>> max_binlog_size         = 100M
>>> #binlog_do_db           = include_database_name
>>> #binlog_ignore_db       = include_database_name
>>> #
>>> # * InnoDB
>>> #
>>> # InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
>>> # Read the manual for more InnoDB related options. There are many!
>>> #
>>> # * Security Features
>>> #
>>> # Read the manual, too, if you want chroot!
>>> # chroot = /var/lib/mysql/
>>> #
>>> # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
>>> #
>>> # ssl-ca=/etc/mysql/cacert.pem
>>> # ssl-cert=/etc/mysql/server-cert.pem
>>> # ssl-key=/etc/mysql/server-key.pem
>>>
>>>
>>>
>>> [mysqldump]
>>> quick
>>> quote-names
>>> max_allowed_packet      = 64M
>>>
>>> [mysql]
>>> #no-auto-rehash # faster start of mysql but no tab completition
>>>
>>> [isamchk]
>>> key_buffer              = 16M
>>>
>>> #
>>> # * IMPORTANT: Additional settings that can override those from this file!
>>> #   The files must end with '.cnf', otherwise they'll be ignored.
>>> #
>>> !includedir /etc/mysql/conf.d/
>>>
>>> any thoughts or help would be appricated.
>>> thanks
>>>
>>>
>>> On Mon, Oct 3, 2011 at 1:22 PM, Eric Bergen <eric.bergen@stripped>
> wrote:
>>>> Can you run show processlist in another connection while the select
>>>> count(*) query is running and say what the state column is?
>>>>
>>>> On Mon, Oct 3, 2011 at 7:00 AM, Joey L <mjh2000@stripped> wrote:
>>>>> this is not a real query on the site - it is just a way i am
> measuring
>>>>> performance on mysql - I do not know if it is such a great way to
> test.
>>>>> Looking for a better way to get a performance read on my site...do
> you have
>>>>> any ?? besides just viewing pages on it.
>>>>> thanks
>>>>> mjh
>>>>>
>>>>>
>>>>> On Mon, Oct 3, 2011 at 9:58 AM, Andrés Tello
> <mr.criptos@stripped> wrote:
>>>>>
>>>>>> have you tried
>>>>>>
>>>>>> select count(yourindex) instead of select count(*) ?
>>>>>>
>>>>>>
>>>>>> On Mon, Oct 3, 2011 at 7:53 AM, Joey L <mjh2000@stripped>
> wrote:
>>>>>>
>>>>>>> Thanks for the input -
>>>>>>> 1. I will wait 48 hours and see what happens.
>>>>>>> 2. can you tell me what are some performance tests I can do
> to help me
>>>>>>> better tune my server ?
>>>>>>> 3. I am concerned about this table : | w6h8a_sh404sef_urls
>>>>>>> |
>>>>>>> MyISAM |      10 | Dynamic    |
> 8908402 |            174 |  1551178184 |
>>>>>>>  281474976710655 |   2410850304 |    
>     0 |        8908777 | 2011-09-22
>>>>>>> 11:16:03 | 2011-10-02 21:17:20 | 2011-10-02 10:12:04 |
> utf8_general_ci   |
>>>>>>>  NULL |              
>  |                      
>             |
>>>>>>> what can I do to make it run faster - i did not write the
> code...but need
>>>>>>> to
>>>>>>> optimize server to handle this table when it gets larger.
>  It is used for
>>>>>>> url re-writes - so it has a lot of urls.
>>>>>>> thanks
>>>>>>> mjh
>>>>>>>
>>>>>>> On Mon, Oct 3, 2011 at 12:38 AM, Bruce Ferrell
> <bferrell@stripped
>>>>>>>> wrote:
>>>>>>>
>>>>>>>>
>>>>>>>> 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)
>>>>>>>>
>>>>>>>>
>>>>>>>> --
>>>>>>>> MySQL General Mailing List
>>>>>>>> For list archives: http://lists.mysql.com/mysql
>>>>>>>> To unsubscribe:  
>  http://lists.mysql.com/mysql?unsub=1
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Eric Bergen
>>>> eric.bergen@stripped
>>>> http://www.ebergen.net
>>>>
>>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>
>
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