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 <eroomy...@gmail.com> wrote:
> Did you fix the issue?
>
>
> On Sun, Oct 2, 2011 at 4:05 PM, Singer X.J. Wang <w...@singerwang.com>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 <mjh2...@gmail.com> 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 <eroomy...@gmail.com>
>>> wrote:
>>>
>>> > Is your table MyISAM or InnoDB?
>>> >
>>> > A
>>> >
>>> >
>>> > On Sun, Oct 2, 2011 at 2:44 PM, Joey L <mjh2...@gmail.com> 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
>>
>>
>
