List:General Discussion« Previous MessageNext Message »
From:Joey L Date:October 3 2011 1:19am
Subject:Re: 4 minute slow on select count(*) from table - myisam type
View as plain text  
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