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 <mjh2...@gmail.com> 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 <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 >>> >>> >> >
