List:General Discussion« Previous MessageNext Message »
From:Joey L Date:October 3 2011 2:02am
Subject:Re: 4 minute slow on select count(*) from table - myisam type
View as plain text  
Also i did run mysqlcheck and i did optimize and repair the database/table..
thanks

On Sun, Oct 2, 2011 at 9:19 PM, Joey L <mjh2000@stripped> wrote:

> The section called: Variables to adjust:  --when it says ">" -- does this
> mean I have to set it higher in my.cnf file ?? and if I have a "<" -- does
> this mean I have to set it lower ??
> thanks...here is the info below you both asked for :
>
>
> mysql> select count(*) from w6h8a_sh404sef_urls ;
>
> +----------+
> | count(*) |
> +----------+
> |  8908193 |
> +----------+
> 1 row in set (2 min 5.53 sec)
>
> | w6h8a_session                       | MyISAM |      10 | Dynamic    |
> 171 |           1576 |      531176 |  281474976710655 |        34816 |
>  261548 |           NULL | 2011-09-30 16:18:30 | 2011-10-02 21:17:19 |
> 2011-10-02 08:52:33 | utf8_general_ci   |     NULL |                |
>                             |
> | w6h8a_sh404sef_aliases              | MyISAM |      10 | Dynamic    |
>   0 |              0 |           0 |  281474976710655 |         4096 |
>   0 |              1 | 2011-09-22 11:16:03 | 2011-09-22 11:16:03 |
> 2011-09-23 00:00:58 | utf8_general_ci   |     NULL |                |
>                             |
> | w6h8a_sh404sef_metas                | MyISAM |      10 | Dynamic    |
>   0 |              0 |           0 |  281474976710655 |         4096 |
>   0 |              1 | 2011-09-22 11:16:03 | 2011-09-22 11:16:03 |
> 2011-09-23 00:00:58 | utf8_general_ci   |     NULL |                |
>                             |
> | w6h8a_sh404sef_pageids              | MyISAM |      10 | Dynamic    |
> 218 |             84 |       18484 |  281474976710655 |        35840 |
>   0 |            219 | 2011-09-22 11:16:03 | 2011-10-02 13:29:12 |
> 2011-10-02 08:52:33 | utf8_general_ci   |     NULL |                |
>                             |
> | 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 |                |
>                             |
> | w6h8a_states                        | MyISAM |      10 | Dynamic    |
>  51 |             22 |        1132 |  281474976710655 |         2048 |
>   0 |             57 | 2011-09-22 11:16:21 | 2011-09-22 11:16:21 |
> 2011-09-23 00:39:36 | utf8_general_ci   |     NULL |                |
>                             |
>
>
>
> -------- General Statistics
> --------------------------------------------------
> [--] Skipped version check for MySQLTuner script
> [OK] Currently running supported MySQL version 5.1.49-3-log
> [OK] Operating on 64-bit architecture
>
> -------- Storage Engine Statistics
> -------------------------------------------
> [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
>
>
> [--] Data in MyISAM tables: 5G (Tables: 2233)
> [--] Data in InnoDB tables: 1M (Tables: 36)
> [!!] Total fragmented tables: 46
>
> -------- Security Recommendations
>  -------------------------------------------
> [!!] User 'asterisk@%' has no password set.
>
> -------- Performance Metrics
> -------------------------------------------------
> [--] Up for: 9h 57m 33s (744K q [20.762 qps], 13K conn, TX: 1B, RX: 200M)
> [--] Reads / Writes: 87% / 13%
> [--] Total buffers: 794.0M global + 2.7M per thread (100 max threads)
> [OK] Maximum possible memory usage: 1.0G (26% of installed RAM)
> [OK] Slow queries: 0% (956/744K)
> [!!] Highest connection usage: 100%  (101/100)
> [!!] Key buffer size / total MyISAM indexes: 256.0M/7.8G
> [!!] Key buffer hit rate: 92.4% (4B cached / 372M reads)
> [OK] Query cache efficiency: 68.8% (450K cached / 655K selects)
> [OK] Query cache prunes per day: 0
> [OK] Sorts requiring temporary tables: 0% (1 temp sorts / 57K sorts)
> [!!] Joins performed without indexes: 23576
> [!!] Temporary tables created on disk: 32% (27K on disk / 85K total)
> [OK] Thread cache hit rate: 97% (329 created / 13K connections)
> [!!] Table cache hit rate: 7% (1K open / 14K opened)
> [OK] Open file limit used: 66% (1K/2K)
> [OK] Table locks acquired immediately: 98% (358K immediate / 362K locks)
> [!!] Connections aborted: 16%
> [OK] InnoDB data size / buffer pool: 1.1M/8.0M
>
> -------- Recommendations
> -----------------------------------------------------
> General recommendations:
>     Run OPTIMIZE TABLE to defragment tables for better performance
>     MySQL started within last 24 hours - recommendations may be inaccurate
>     Enable the slow query log to troubleshoot bad queries
>     Reduce or eliminate persistent connections to reduce connection usage
>     Adjust your join queries to always utilize indexes
>     When making adjustments, make tmp_table_size/max_heap_table_size equal
>     Reduce your SELECT DISTINCT queries without LIMIT clauses
>     Increase table_cache gradually to avoid file descriptor limits
>     Your applications are not closing MySQL connections properly
> 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)
>
>
>
>
>
> On Sun, Oct 2, 2011 at 12:56 PM, Andrew Moore <eroomydna@stripped> wrote:
>
>> Did you fix the issue?
>>
>>
>> On Sun, Oct 2, 2011 at 4:05 PM, Singer X.J. Wang
> <wang@stripped>wrote:
>>
>>> Are you sure? Do a show create table and send it to us please
>>>
>>>
>>>
>>>
>>> On Sun, Oct 2, 2011 at 10:02, Joey L <mjh2000@stripped> wrote:
>>>
>>>> thanks for the quick reply!
>>>> My table is MyISAM
>>>> further top says this:
>>>> top - 10:01:29 up  8:25,  4 users,  load average: 1.42, 1.85, 2.69
>>>> Tasks: 338 total,   1 running, 337 sleeping,   0 stopped,   0 zombie
>>>> Cpu(s): 10.3%us,  0.9%sy,  0.0%ni, 56.6%id, 32.0%wa,  0.0%hi,  0.2%si,
>>>>  0.0%st
>>>> Mem:   8198044k total,  8158784k used,    39260k free,   199852k buffers
>>>> Swap:  8210416k total,    44748k used,  8165668k free,  5457920k cached
>>>>
>>>>  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
>>>> 10682 mysql     20   0  958m 343m 6588 S   31  4.3  57:25.69
>>>> /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql
>>>> --pid-file=/va
>>>> 14627 www-data  20   0 50088  14m 4744 S    3  0.2   0:10.43
>>>> /usr/sbin/apache2 -k start
>>>> 14637 www-data  20   0 50088  14m 4744 S    3  0.2   0:07.66
>>>> /usr/sbin/apache2 -k start
>>>> 14737 www-data  20   0 50092  14m 4744 S    3  0.2   0:07.25
>>>> /usr/sbin/apache2 -k start
>>>> 14758 www-data  20   0 50092  14m 4748 S    3  0.2   0:07.36
>>>> /usr/sbin/apache2 -k start
>>>> 15145 root      20   0  2596 1328  896 R    1  0.0   0:00.55 top
>>>>  1895 bind      20   0 98452  24m 1980 S    1  0.3   0:31.34
>>>> /usr/sbin/named
>>>> -u bind
>>>>  401 root      20   0     0    0    0 D    0  0.0   0:42.63 [md0_raid1]
>>>>  1398 root      20   0     0    0    0 S    0  0.0   2:59.33 [flush-9:0]
>>>>  2428 asterisk -11   0 33500  15m 6660 S    0  0.2   0:19.39
>>>> /usr/sbin/asterisk -p -U asterisk
>>>>    1 root      20   0  2032  604  568 S    0  0.0   0:01.14 init [2]
>>>>    2 root      20   0     0    0    0 S    0  0.0   0:00.01 [kthreadd]
>>>>    3 root      RT   0     0    0    0 S    0  0.0   0:00.04
>>>> [migration/0]
>>>>    4 root      20   0     0    0    0 S    0  0.0   0:00.16
>>>> [ksoftirqd/0]
>>>>
>>>>
>>>> On Sun, Oct 2, 2011 at 9:55 AM, Andrew Moore <eroomydna@stripped>
>>>> wrote:
>>>>
>>>> > Is your table MyISAM or InnoDB?
>>>> >
>>>> > A
>>>> >
>>>> >
>>>> > On Sun, Oct 2, 2011 at 2:44 PM, Joey L <mjh2000@stripped>
> wrote:
>>>> >
>>>> >> I have having issues with mysql db - I am doing a "select
> count(*)
>>>> from
>>>> >> table" -- and it take 3 to 4 min.
>>>> >> My table has about 9,000,000 records in it.
>>>> >> I have noticed issues on my web pages so that is why i did this
> test.
>>>> >> I have about 4 gig of memory on the server.
>>>> >> Is there anything I can do to fix the issue ????
>>>> >> My my.cnf looks like this :
>>>> >> # * Fine Tuning
>>>> >> #
>>>> >> key_buffer              = 256M
>>>> >> max_allowed_packet      = 16M
>>>> >> thread_stack            = 192K
>>>> >> thread_cache_size       = 32
>>>> >> # 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
>>>> >> thread_concurrency     = 20
>>>> >> #
>>>> >> # * Query Cache Configuration
>>>> >> #
>>>> >> query_cache_limit       = 1M
>>>> >> query_cache_size        = 512M
>>>> >> #
>>>> >> # * 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             = 1
>>>> >> #
>>>> >> # 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
>>>> >>
>>>> >
>>>> >
>>>>
>>>
>>> --
>>> Pythian at Oracle OpenWorld: 8 sessions packed with hot tips, real-world
> experiences and valuable insight. bit.ly/pythianoow11
>>>
>>>
>>
>

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