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
>>>
>>>
>>
>
