4 minute slow on select count(*) from table - myisam type

--00504502ca28f09ac004ae510e5a
Content-Type: text/plain; charset=ISO-8859-1

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

--00504502ca28f09ac004ae510e5a--

Re: 4 minute slow on select count(*) from table - myisam type

--000e0cd71996b4e13204ae513675
Content-Type: text/plain; charset=ISO-8859-1

Is your table MyISAM or InnoDB?

A

On Sun, Oct 2, 2011 at 2:44 PM, Joey L 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
>

--000e0cd71996b4e13204ae513675--

Re: 4 minute slow on select count(*) from table - myisam type

--0016e6de03ce41ff9104ae5150ae
Content-Type: text/plain; charset=ISO-8859-1

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

> Is your table MyISAM or InnoDB?
>
> A
>
>
> On Sun, Oct 2, 2011 at 2:44 PM, Joey L 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
>>
>
>

--0016e6de03ce41ff9104ae5150ae--

Re: 4 minute slow on select count(*) from table - myisam type

I'd suggest mysqltuner. You can get it by using:

wget http://mysqltuner.pl

See what suggestions that makes




On 10/02/2011 06:44 AM, Joey L 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
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: 4 minute slow on select count(*) from table - myisam type

--000e0cd719964da83a04ae53bdd5
Content-Type: text/plain; charset=ISO-8859-1

Did you fix the issue?

On Sun, Oct 2, 2011 at 4:05 PM, Singer X.J. Wang 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 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 wrote:
>>
>> > Is your table MyISAM or InnoDB?
>> >
>> > A
>> >
>> >
>> > On Sun, Oct 2, 2011 at 2:44 PM, Joey L 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
>
>

--000e0cd719964da83a04ae53bdd5--

Re: 4 minute slow on select count(*) from table - myisam type

--0016e6d564c63fb3e804ae5ac4ff
Content-Type: text/plain; charset=ISO-8859-1

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

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

--0016e6d564c63fb3e804ae5ac4ff--

Re: 4 minute slow on select count(*) from table - myisam type

--0016e6d648430b5e2404ae5b5e29
Content-Type: text/plain; charset=ISO-8859-1

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

--0016e6d648430b5e2404ae5b5e29--

Re: 4 minute slow on select count(*) from table - myisam type

The meaning is:

increase max_connections
reduce wait_timeout
-- 28800 is wait 8 hours before closing out dead connections
same for interactive_timeout


increase key_buffer_size (> 7.8G) increase join_buffer_size
-- This keeps mysql from having to run to disk constantly for keys
-- Key buffer size / total MyISAM indexes: 256.0M/7.8G
-- You have a key buffer of 256M and 7.8G of keys

join_buffer_size (> 128.0K, or always use indexes with joins)
Joins performed without indexes: 23576 of 744k queries.
-- You probably want to look at the slow query log. Generalize the queries and the do an explain on the query. I have seen instances where a query I thought was using an index wasn't and I had to re-write... with help from this list :-) Thanks gang!


increase tmp_table_size (> 16M)
increase max_heap_table_size (> 16M)
-- When making adjustments, make tmp_table_size/max_heap_table_size equal

increase table_cache ( > 1k )
-- Table cache hit rate: 7% (1K open / 14K opened)
-- Increase table_cache gradually to avoid file descriptor limits

All of the aside, you need to let this run for at least 24 hours. I
prefer 48 hours. The first line says mysql has only been running 9
hours. You can reset the timeouts interactivly by entering at the
mysql prompt:

set global wait_timeout=

You can do the same for the interactive_timeout.

Setting these values too low will cause long running queries to abort


On 10/02/2011 07:02 PM, Joey L wrote:
> 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)


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: 4 minute slow on select count(*) from table - myisam type

--0016e6d64843b768b204ae64766c
Content-Type: text/plain; charset=ISO-8859-1

Thanks for the input -
1. I will wait 48 hours and see what happens.
2. can you tell me what are some performance tests I can do to help me
better tune my server ?
3. I am concerned about this table : | 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 | | |
what can I do to make it run faster - i did not write the code...but need to
optimize server to handle this table when it gets larger. It is used for
url re-writes - so it has a lot of urls.
thanks
mjh

On Mon, Oct 3, 2011 at 12:38 AM, Bruce Ferrell wrote:

>
> The meaning is:
>
> increase max_connections
> reduce wait_timeout
> -- 28800 is wait 8 hours before closing out dead connections
> same for interactive_timeout
>
>
> increase key_buffer_size (> 7.8G) increase join_buffer_size
> -- This keeps mysql from having to run to disk constantly for keys
> -- Key buffer size / total MyISAM indexes: 256.0M/7.8G
> -- You have a key buffer of 256M and 7.8G of keys
>
> join_buffer_size (> 128.0K, or always use indexes with joins)
> Joins performed without indexes: 23576 of 744k queries.
> -- You probably want to look at the slow query log. Generalize the queries
> and the do an explain on the query. I have seen instances where a query I
> thought was using an index wasn't and I had to re-write... with help from
> this list :-) Thanks gang!
>
>
> increase tmp_table_size (> 16M)
> increase max_heap_table_size (> 16M)
> -- When making adjustments, make tmp_table_size/max_heap_table_size equal
>
> increase table_cache ( > 1k )
> -- Table cache hit rate: 7% (1K open / 14K opened)
> -- Increase table_cache gradually to avoid file descriptor limits
>
> All of the aside, you need to let this run for at least 24 hours. I
> prefer 48 hours. The first line says mysql has only been running 9
> hours. You can reset the timeouts interactivly by entering at the
> mysql prompt:
>
> set global wait_timeout=
>
> You can do the same for the interactive_timeout.
>
> Setting these values too low will cause long running queries to abort
>
>
> On 10/02/2011 07:02 PM, Joey L wrote:
> > 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)
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mjh2000@gmail.com
>
>

--0016e6d64843b768b204ae64766c--

Re: 4 minute slow on select count(*) from table - myisam type

--bcaec51d2eb41bfae704ae655e07
Content-Type: text/plain; charset=ISO-8859-1

have you tried

select count(yourindex) instead of select count(*) ?


On Mon, Oct 3, 2011 at 7:53 AM, Joey L wrote:

> Thanks for the input -
> 1. I will wait 48 hours and see what happens.
> 2. can you tell me what are some performance tests I can do to help me
> better tune my server ?
> 3. I am concerned about this table : | 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 | | |
> what can I do to make it run faster - i did not write the code...but need
> to
> optimize server to handle this table when it gets larger. It is used for
> url re-writes - so it has a lot of urls.
> thanks
> mjh
>
> On Mon, Oct 3, 2011 at 12:38 AM, Bruce Ferrell > >wrote:
>
> >
> > The meaning is:
> >
> > increase max_connections
> > reduce wait_timeout
> > -- 28800 is wait 8 hours before closing out dead connections
> > same for interactive_timeout
> >
> >
> > increase key_buffer_size (> 7.8G) increase join_buffer_size
> > -- This keeps mysql from having to run to disk constantly for keys
> > -- Key buffer size / total MyISAM indexes: 256.0M/7.8G
> > -- You have a key buffer of 256M and 7.8G of keys
> >
> > join_buffer_size (> 128.0K, or always use indexes with joins)
> > Joins performed without indexes: 23576 of 744k queries.
> > -- You probably want to look at the slow query log. Generalize the
> queries
> > and the do an explain on the query. I have seen instances where a query
> I
> > thought was using an index wasn't and I had to re-write... with help from
> > this list :-) Thanks gang!
> >
> >
> > increase tmp_table_size (> 16M)
> > increase max_heap_table_size (> 16M)
> > -- When making adjustments, make tmp_table_size/max_heap_table_size equal
> >
> > increase table_cache ( > 1k )
> > -- Table cache hit rate: 7% (1K open / 14K opened)
> > -- Increase table_cache gradually to avoid file descriptor limits
> >
> > All of the aside, you need to let this run for at least 24 hours. I
> > prefer 48 hours. The first line says mysql has only been running 9
> > hours. You can reset the timeouts interactivly by entering at the
> > mysql prompt:
> >
> > set global wait_timeout=
> >
> > You can do the same for the interactive_timeout.
> >
> > Setting these values too low will cause long running queries to abort
> >
> >
> > On 10/02/2011 07:02 PM, Joey L wrote:
> > > 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)
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=mjh2000@gmail.com
> >
> >
>

--bcaec51d2eb41bfae704ae655e07--

Re: 4 minute slow on select count(*) from table - myisam type

--00504502ca2847621004ae656640
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

this is not a real query on the site - it is just a way i am measuring
performance on mysql - I do not know if it is such a great way to test.
Looking for a better way to get a performance read on my site...do you have
any ?? besides just viewing pages on it.
thanks
mjh


On Mon, Oct 3, 2011 at 9:58 AM, Andr=E9s Tello wrote=
:

> have you tried
>
> select count(yourindex) instead of select count(*) ?
>
>
> On Mon, Oct 3, 2011 at 7:53 AM, Joey L wrote:
>
>> Thanks for the input -
>> 1. I will wait 48 hours and see what happens.
>> 2. can you tell me what are some performance tests I can do to help me
>> better tune my server ?
>> 3. I am concerned about this table : | w6h8a_sh404sef_urls
>> |
>> MyISAM | 10 | Dynamic | 8908402 | 174 | 1551178184 |
>> 281474976710655 | 2410850304 | 0 | 8908777 | 2011-09-2=
2
>> 11:16:03 | 2011-10-02 21:17:20 | 2011-10-02 10:12:04 | utf8_general_ci =
|
>> NULL | | |
>> what can I do to make it run faster - i did not write the code...but nee=
d
>> to
>> optimize server to handle this table when it gets larger. It is used fo=
r
>> url re-writes - so it has a lot of urls.
>> thanks
>> mjh
>>
>> On Mon, Oct 3, 2011 at 12:38 AM, Bruce Ferrell >> >wrote:
>>
>> >
>> > The meaning is:
>> >
>> > increase max_connections
>> > reduce wait_timeout
>> > -- 28800 is wait 8 hours before closing out dead connections
>> > same for interactive_timeout
>> >
>> >
>> > increase key_buffer_size (> 7.8G) increase join_buffer_size
>> > -- This keeps mysql from having to run to disk constantly for keys
>> > -- Key buffer size / total MyISAM indexes: 256.0M/7.8G
>> > -- You have a key buffer of 256M and 7.8G of keys
>> >
>> > join_buffer_size (> 128.0K, or always use indexes with joins)
>> > Joins performed without indexes: 23576 of 744k queries.
>> > -- You probably want to look at the slow query log. Generalize the
>> queries
>> > and the do an explain on the query. I have seen instances where a que=
ry
>> I
>> > thought was using an index wasn't and I had to re-write... with help
>> from
>> > this list :-) Thanks gang!
>> >
>> >
>> > increase tmp_table_size (> 16M)
>> > increase max_heap_table_size (> 16M)
>> > -- When making adjustments, make tmp_table_size/max_heap_table_size
>> equal
>> >
>> > increase table_cache ( > 1k )
>> > -- Table cache hit rate: 7% (1K open / 14K opened)
>> > -- Increase table_cache gradually to avoid file descriptor limits
>> >
>> > All of the aside, you need to let this run for at least 24 hours. I
>> > prefer 48 hours. The first line says mysql has only been running 9
>> > hours. You can reset the timeouts interactivly by entering at the
>> > mysql prompt:
>> >
>> > set global wait_timeout=3D
>> >
>> > You can do the same for the interactive_timeout.
>> >
>> > Setting these values too low will cause long running queries to abort
>> >
>> >
>> > On 10/02/2011 07:02 PM, Joey L wrote:
>> > > 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)
>> >
>> >
>> > --
>> > MySQL General Mailing List
>> > For list archives: http://lists.mysql.com/mysql
>> > To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmjh2000@gmail.=
com
>> >
>> >
>>
>
>

--00504502ca2847621004ae656640--

Re: 4 minute slow on select count(*) from table - myisam type

Can you run show processlist in another connection while the select
count(*) query is running and say what the state column is?

On Mon, Oct 3, 2011 at 7:00 AM, Joey L wrote:
> this is not a real query on the site - it is just a way i am measuring
> performance on mysql - I do not know if it is such a great way to test.
> Looking for a better way to get a performance read on my site...do you ha=
ve
> any ?? besides just viewing pages on it.
> thanks
> mjh
>
>
> On Mon, Oct 3, 2011 at 9:58 AM, Andr=E9s Tello wro=
te:
>
>> have you tried
>>
>> select count(yourindex) instead of select count(*) ?
>>
>>
>> On Mon, Oct 3, 2011 at 7:53 AM, Joey L wrote:
>>
>>> Thanks for the input -
>>> 1. I will wait 48 hours and see what happens.
>>> 2. can you tell me what are some performance tests I can do to help me
>>> better tune my server ?
>>> 3. I am concerned about this table : | w6h8a_sh404sef_urls
>>> |
>>> MyISAM | =A0 =A0 =A010 | Dynamic =A0 =A0| 8908402 | =A0 =A0 =A0 =A0 =A0=
=A0174 | =A01551178184 |
>>> =A0281474976710655 | =A0 2410850304 | =A0 =A0 =A0 =A0 0 | =A0 =A0 =A0 =
=A08908777 | 2011-09-22
>>> 11:16:03 | 2011-10-02 21:17:20 | 2011-10-02 10:12:04 | utf8_general_ci =
=A0 |
>>> =A0NULL | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 |
>>> what can I do to make it run faster - i did not write the code...but ne=
ed
>>> to
>>> optimize server to handle this table when it gets larger. =A0It is used=
for
>>> url re-writes - so it has a lot of urls.
>>> thanks
>>> mjh
>>>
>>> On Mon, Oct 3, 2011 at 12:38 AM, Bruce Ferrell >>> >wrote:
>>>
>>> >
>>> > The meaning is:
>>> >
>>> > increase max_connections
>>> > reduce wait_timeout
>>> > -- 28800 is wait 8 hours before closing out dead connections
>>> > same for interactive_timeout
>>> >
>>> >
>>> > increase key_buffer_size (> 7.8G) increase join_buffer_size
>>> > -- This keeps mysql from having to run to disk constantly for keys
>>> > -- Key buffer size / total MyISAM indexes: 256.0M/7.8G
>>> > -- You have a key buffer of 256M and 7.8G of keys
>>> >
>>> > join_buffer_size (> 128.0K, or always use indexes with joins)
>>> > Joins performed without indexes: 23576 of 744k queries.
>>> > -- You probably want to look at the slow query log. =A0Generalize the
>>> queries
>>> > and the do an explain on the query. =A0I have seen instances where a =
query
>>> I
>>> > thought was using an index wasn't and I had to re-write... with help
>>> from
>>> > this list :-) =A0Thanks gang!
>>> >
>>> >
>>> > increase tmp_table_size (> 16M)
>>> > increase max_heap_table_size (> 16M)
>>> > -- When making adjustments, make tmp_table_size/max_heap_table_size
>>> equal
>>> >
>>> > increase table_cache ( > 1k )
>>> > -- Table cache hit rate: 7% (1K open / 14K opened)
>>> > -- Increase table_cache gradually to avoid file descriptor limits
>>> >
>>> > All of the aside, you need to let this run for at least 24 hours. I
>>> > prefer 48 hours. =A0The first line says mysql has only been running 9
>>> > hours. =A0 You can reset the timeouts interactivly by entering at the
>>> > mysql prompt:
>>> >
>>> > set global wait_timeout=3D
>>> >
>>> > You can do the same for the interactive_timeout.
>>> >
>>> > Setting these values too low will cause long running queries to abort
>>> >
>>> >
>>> > On 10/02/2011 07:02 PM, Joey L wrote:
>>> > > Variables to adjust:
>>> > > > =A0 =A0 max_connections (> 100)
>>> > > > =A0 =A0 wait_timeout (< 28800)
>>> > > > =A0 =A0 interactive_timeout (< 28800)
>>> > > > =A0 =A0 key_buffer_size (> 7.8G)
>>> > > > =A0 =A0 join_buffer_size (> 128.0K, or always use indexes with jo=
ins)
>>> > > > =A0 =A0 tmp_table_size (> 16M)
>>> > > > =A0 =A0 max_heap_table_size (> 16M)
>>> > > > =A0 =A0 table_cache (> 1024)
>>> >
>>> >
>>> > --
>>> > MySQL General Mailing List
>>> > For list archives: http://lists.mysql.com/mysql
>>> > To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmjh2000@g=
mail.com
>>> >
>>> >
>>>
>>
>>
>



--=20
Eric Bergen
eric.bergen@gmail.com
http://www.ebergen.net

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: 4 minute slow on select count(*) from table - myisam type

guys - i am having such a hard time with this..it is killing me!!!
Sorry - had to vent.
my machine is running an tyan S2912G2NR -- with 2 opterons and 12gig
of memory. I have 2 software raided drives 1gig each.
I run a couple of databases --- my largest table is about 9gig in
size. --it is being accessed a lot.
My my.cnf is as follows:

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.ht ml

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port =3D 3306
socket =3D /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently par=
sed.
[mysqld_safe]
socket =3D /var/run/mysqld/mysqld.sock
nice =3D 0

[mysqld]
#
# * Basic Settings
#
user =3D mysql
pid-file =3D /var/run/mysqld/mysqld.pid
socket =3D /var/run/mysqld/mysqld.sock
port =3D 3306
basedir =3D /usr
datadir =3D /var/lib/mysql
tmpdir =3D /tmp
language =3D /usr/share/mysql/english
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address =3D 127.0.0.1
#
# * Fine Tuning
#
key_buffer =3D 2G
key_buffer_size =3D 2G
max_allowed_packet =3D 16M
thread_stack =3D 192K
thread_cache_size =3D 8
join_buffer_size =3D 128
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover =3D BACKUP
max_connections =3D 100
table_cache =3D 1024
max_heap_table_size =3D 32M
tmp_table_size =3D 32M
thread_concurrency =3D 10
#
# * Query Cache Configuration
#
query_cache_limit =3D 2M
query_cache_size =3D 16M
#
# * 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 =3D /var/log/mysql/mysql.log
general_log =3D 2
#
# 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 =3D /var/log/mysql/mysql-slow.log
#long_query_time =3D 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replicatio=
n.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id =3D 1
#log_bin =3D /var/log/mysql/mysql-bin.log
expire_logs_days =3D 10
max_binlog_size =3D 100M
#binlog_do_db =3D include_database_name
#binlog_ignore_db =3D 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 =3D /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=3D/etc/mysql/cacert.pem
# ssl-cert=3D/etc/mysql/server-cert.pem
# ssl-key=3D/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet =3D 64M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer =3D 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

any thoughts or help would be appricated.
thanks


On Mon, Oct 3, 2011 at 1:22 PM, Eric Bergen wrote:
> Can you run show processlist in another connection while the select
> count(*) query is running and say what the state column is?
>
> On Mon, Oct 3, 2011 at 7:00 AM, Joey L wrote:
>> this is not a real query on the site - it is just a way i am measuring
>> performance on mysql - I do not know if it is such a great way to test.
>> Looking for a better way to get a performance read on my site...do you h=
ave
>> any ?? besides just viewing pages on it.
>> thanks
>> mjh
>>
>>
>> On Mon, Oct 3, 2011 at 9:58 AM, Andr=E9s Tello wr=
ote:
>>
>>> have you tried
>>>
>>> select count(yourindex) instead of select count(*) ?
>>>
>>>
>>> On Mon, Oct 3, 2011 at 7:53 AM, Joey L wrote:
>>>
>>>> Thanks for the input -
>>>> 1. I will wait 48 hours and see what happens.
>>>> 2. can you tell me what are some performance tests I can do to help me
>>>> better tune my server ?
>>>> 3. I am concerned about this table : | w6h8a_sh404sef_urls
>>>> |
>>>> MyISAM | =A0 =A0 =A010 | Dynamic =A0 =A0| 8908402 | =A0 =A0 =A0 =A0 =
=A0 =A0174 | =A01551178184 |
>>>> =A0281474976710655 | =A0 2410850304 | =A0 =A0 =A0 =A0 0 | =A0 =A0 =A0 =
=A08908777 | 2011-09-22
>>>> 11:16:03 | 2011-10-02 21:17:20 | 2011-10-02 10:12:04 | utf8_general_ci=
=A0 |
>>>> =A0NULL | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0 =A0=
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 |
>>>> what can I do to make it run faster - i did not write the code...but n=
eed
>>>> to
>>>> optimize server to handle this table when it gets larger. =A0It is use=
d for
>>>> url re-writes - so it has a lot of urls.
>>>> thanks
>>>> mjh
>>>>
>>>> On Mon, Oct 3, 2011 at 12:38 AM, Bruce Ferrell >>>> >wrote:
>>>>
>>>> >
>>>> > The meaning is:
>>>> >
>>>> > increase max_connections
>>>> > reduce wait_timeout
>>>> > -- 28800 is wait 8 hours before closing out dead connections
>>>> > same for interactive_timeout
>>>> >
>>>> >
>>>> > increase key_buffer_size (> 7.8G) increase join_buffer_size
>>>> > -- This keeps mysql from having to run to disk constantly for keys
>>>> > -- Key buffer size / total MyISAM indexes: 256.0M/7.8G
>>>> > -- You have a key buffer of 256M and 7.8G of keys
>>>> >
>>>> > join_buffer_size (> 128.0K, or always use indexes with joins)
>>>> > Joins performed without indexes: 23576 of 744k queries.
>>>> > -- You probably want to look at the slow query log. =A0Generalize th=
e
>>>> queries
>>>> > and the do an explain on the query. =A0I have seen instances where a=
query
>>>> I
>>>> > thought was using an index wasn't and I had to re-write... with help
>>>> from
>>>> > this list :-) =A0Thanks gang!
>>>> >
>>>> >
>>>> > increase tmp_table_size (> 16M)
>>>> > increase max_heap_table_size (> 16M)
>>>> > -- When making adjustments, make tmp_table_size/max_heap_table_size
>>>> equal
>>>> >
>>>> > increase table_cache ( > 1k )
>>>> > -- Table cache hit rate: 7% (1K open / 14K opened)
>>>> > -- Increase table_cache gradually to avoid file descriptor limits
>>>> >
>>>> > All of the aside, you need to let this run for at least 24 hours. I
>>>> > prefer 48 hours. =A0The first line says mysql has only been running =
9
>>>> > hours. =A0 You can reset the timeouts interactivly by entering at th=
e
>>>> > mysql prompt:
>>>> >
>>>> > set global wait_timeout=3D
>>>> >
>>>> > You can do the same for the interactive_timeout.
>>>> >
>>>> > Setting these values too low will cause long running queries to abor=
t
>>>> >
>>>> >
>>>> > On 10/02/2011 07:02 PM, Joey L wrote:
>>>> > > Variables to adjust:
>>>> > > > =A0 =A0 max_connections (> 100)
>>>> > > > =A0 =A0 wait_timeout (< 28800)
>>>> > > > =A0 =A0 interactive_timeout (< 28800)
>>>> > > > =A0 =A0 key_buffer_size (> 7.8G)
>>>> > > > =A0 =A0 join_buffer_size (> 128.0K, or always use indexes with j=
oins)
>>>> > > > =A0 =A0 tmp_table_size (> 16M)
>>>> > > > =A0 =A0 max_heap_table_size (> 16M)
>>>> > > > =A0 =A0 table_cache (> 1024)
>>>> >
>>>> >
>>>> > --
>>>> > MySQL General Mailing List
>>>> > For list archives: http://lists.mysql.com/mysql
>>>> > To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmjh2000@=
gmail.com
>>>> >
>>>> >
>>>>
>>>
>>>
>>
>
>
>
> --
> Eric Bergen
> eric.bergen@gmail.com
> http://www.ebergen.net
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: 4 minute slow on select count(*) from table - myisam type

Just as an fyi - I have other databases and their corresponding apache
websites on the same server - performing okay.
It seems that apache/mysql server is just having a hard time dealing
with the access to those pages that deal with the 9gig table on that
particular site. -- Most of the access is done by webcrawlers to the
site - so there is a lot of activity occuring on the 9gig tables.

thanks
mjh

On Thu, Oct 6, 2011 at 6:13 AM, Joey L wrote:
> guys - i am having such a hard time with this..it is killing me!!!
> Sorry - had to vent.
> my machine is running an tyan S2912G2NR =A0-- with 2 opterons and 12gig
> of memory. I have 2 software raided drives 1gig each.
> I run a couple of databases --- my largest table is about 9gig in
> size. --it is being accessed a lot.
> My my.cnf is as follows:
>
> #
> # The MySQL database server configuration file.
> #
> # You can copy this to one of:
> # - "/etc/mysql/my.cnf" to set global options,
> # - "~/.my.cnf" to set user-specific options.
> #
> # One can use all long options that the program supports.
> # Run program with --help to get a list of available options and with
> # --print-defaults to see which it would actually understand and use.
> #
> # For explanations see
> # http://dev.mysql.com/doc/mysql/en/server-system-variables.ht ml
>
> # This will be passed to all mysql clients
> # It has been reported that passwords should be enclosed with ticks/quote=
s
> # escpecially if they contain "#" chars...
> # Remember to edit /etc/mysql/debian.cnf when changing the socket locatio=
n.
> [client]
> port =A0 =A0 =A0 =A0 =A0  = 3306
> socket =A0 =A0 =A0 =A0  = /var/run/mysqld/mysqld.sock
>
> # Here is entries for some specific programs
> # The following values assume you have at least 32M ram
>
> # This was formally known as [safe_mysqld]. Both versions are currently p=
arsed.
> [mysqld_safe]
> socket =A0 =A0 =A0 =A0  = /var/run/mysqld/mysqld.sock
> nice =A0 =A0 =A0 =A0 =A0  = 0
>
> [mysqld]
> #
> # * Basic Settings
> #
> user =A0 =A0 =A0 =A0 =A0  = mysql
> pid-file =A0 =A0 =A0  = /var/run/mysqld/mysqld.pid
> socket =A0 =A0 =A0 =A0  = /var/run/mysqld/mysqld.sock
> port =A0 =A0 =A0 =A0 =A0  = 3306
> basedir =A0 =A0 =A0 =A0 =3D /usr
> datadir =A0 =A0 =A0 =A0 =3D /var/lib/mysql
> tmpdir =A0 =A0 =A0 =A0  = /tmp
> language =A0 =A0 =A0  = /usr/share/mysql/english
> skip-external-locking
> #
> # Instead of skip-networking the default is now to listen only on
> # localhost which is more compatible and is not less secure.
> bind-address =A0 =A0 =A0 =A0 =A0  = 127.0.0.1
> #
> # * Fine Tuning
> #
> key_buffer =A0 =A0 =A0 =A0 =A0 =A0  = 2G
> key_buffer_size =A0 =A0 =A0 =A0 =3D 2G
> max_allowed_packet =A0 =A0  = 16M
> thread_stack =A0 =A0 =A0 =A0 =A0  = 192K
> thread_cache_size =A0 =A0 =A0 =3D 8
> join_buffer_size =A0 =A0 =A0 =A0 =A0 =A0 =A0  = 128
> # This replaces the startup script and checks MyISAM tables if needed
> # the first time they are touched
> myisam-recover =A0 =A0 =A0 =A0 =3D BACKUP
> max_connections =A0 =A0 =A0  = 100
> table_cache =A0 =A0 =A0 =A0 =A0  = 1024
> max_heap_table_size =A0 =A0 =A0 =A0 =A0 =A0 =3D 32M
> tmp_table_size =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0  = 32M
> thread_concurrency =A0 =A0 =3D 10
> #
> # * Query Cache Configuration
> #
> query_cache_limit =A0 =A0 =A0 =3D 2M
> query_cache_size =A0 =A0 =A0  = 16M
> #
> # * 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 =A0 =A0 =A0  = /var/log/mysql/mysql.log
> general_log =A0 =A0 =A0 =A0 =A0 =A0 =3D 2
> #
> # Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslo=
g.cnf.
> #
> # Here you can see queries with especially long duration
> #log_slow_queries =A0 =A0 =A0 =3D /var/log/mysql/mysql-slow.log
> #long_query_time =3D 2
> #log-queries-not-using-indexes
> #
> # The following can be used as easy to replay backup logs or for replicat=
ion.
> # note: if you are setting up a replication slave, see README.Debian abou=
t
> # =A0 =A0 =A0 other settings you may need to change.
> #server-id =A0 =A0 =A0 =A0 =A0 =A0  = 1
> #log_bin =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0  = /var/log/mysq=
l/mysql-bin.log
> expire_logs_days =A0 =A0 =A0  = 10
> max_binlog_size =A0 =A0 =A0 =A0 =3D 100M
> #binlog_do_db =A0 =A0 =A0 =A0 =A0 =3D include_database_name
> #binlog_ignore_db =A0 =A0 =A0 =3D 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 =3D /var/lib/mysql/
> #
> # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
> #
> # ssl-ca=3D/etc/mysql/cacert.pem
> # ssl-cert=3D/etc/mysql/server-cert.pem
> # ssl-key=3D/etc/mysql/server-key.pem
>
>
>
> [mysqldump]
> quick
> quote-names
> max_allowed_packet =A0 =A0  = 64M
>
> [mysql]
> #no-auto-rehash # faster start of mysql but no tab completition
>
> [isamchk]
> key_buffer =A0 =A0 =A0 =A0 =A0 =A0  = 16M
>
> #
> # * IMPORTANT: Additional settings that can override those from this file=
!
> # =A0 The files must end with '.cnf', otherwise they'll be ignored.
> #
> !includedir /etc/mysql/conf.d/
>
> any thoughts or help would be appricated.
> thanks
>
>
> On Mon, Oct 3, 2011 at 1:22 PM, Eric Bergen wrote=
:
>> Can you run show processlist in another connection while the select
>> count(*) query is running and say what the state column is?
>>
>> On Mon, Oct 3, 2011 at 7:00 AM, Joey L wrote:
>>> this is not a real query on the site - it is just a way i am measuring
>>> performance on mysql - I do not know if it is such a great way to test.
>>> Looking for a better way to get a performance read on my site...do you =
have
>>> any ?? besides just viewing pages on it.
>>> thanks
>>> mjh
>>>
>>>
>>> On Mon, Oct 3, 2011 at 9:58 AM, Andr=E9s Tello w=
rote:
>>>
>>>> have you tried
>>>>
>>>> select count(yourindex) instead of select count(*) ?
>>>>
>>>>
>>>> On Mon, Oct 3, 2011 at 7:53 AM, Joey L wrote:
>>>>
>>>>> Thanks for the input -
>>>>> 1. I will wait 48 hours and see what happens.
>>>>> 2. can you tell me what are some performance tests I can do to help m=
e
>>>>> better tune my server ?
>>>>> 3. I am concerned about this table : | w6h8a_sh404sef_urls
>>>>> |
>>>>> MyISAM | =A0 =A0 =A010 | Dynamic =A0 =A0| 8908402 | =A0 =A0 =A0 =A0 =
=A0 =A0174 | =A01551178184 |
>>>>> =A0281474976710655 | =A0 2410850304 | =A0 =A0 =A0 =A0 0 | =A0 =A0 =A0=
=A08908777 | 2011-09-22
>>>>> 11:16:03 | 2011-10-02 21:17:20 | 2011-10-02 10:12:04 | utf8_general_c=
i =A0 |
>>>>> =A0NULL | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 |
>>>>> what can I do to make it run faster - i did not write the code...but =
need
>>>>> to
>>>>> optimize server to handle this table when it gets larger. =A0It is us=
ed for
>>>>> url re-writes - so it has a lot of urls.
>>>>> thanks
>>>>> mjh
>>>>>
>>>>> On Mon, Oct 3, 2011 at 12:38 AM, Bruce Ferrell >>>>> >wrote:
>>>>>
>>>>> >
>>>>> > The meaning is:
>>>>> >
>>>>> > increase max_connections
>>>>> > reduce wait_timeout
>>>>> > -- 28800 is wait 8 hours before closing out dead connections
>>>>> > same for interactive_timeout
>>>>> >
>>>>> >
>>>>> > increase key_buffer_size (> 7.8G) increase join_buffer_size
>>>>> > -- This keeps mysql from having to run to disk constantly for keys
>>>>> > -- Key buffer size / total MyISAM indexes: 256.0M/7.8G
>>>>> > -- You have a key buffer of 256M and 7.8G of keys
>>>>> >
>>>>> > join_buffer_size (> 128.0K, or always use indexes with joins)
>>>>> > Joins performed without indexes: 23576 of 744k queries.
>>>>> > -- You probably want to look at the slow query log. =A0Generalize t=
he
>>>>> queries
>>>>> > and the do an explain on the query. =A0I have seen instances where =
a query
>>>>> I
>>>>> > thought was using an index wasn't and I had to re-write... with hel=
p
>>>>> from
>>>>> > this list :-) =A0Thanks gang!
>>>>> >
>>>>> >
>>>>> > increase tmp_table_size (> 16M)
>>>>> > increase max_heap_table_size (> 16M)
>>>>> > -- When making adjustments, make tmp_table_size/max_heap_table_size
>>>>> equal
>>>>> >
>>>>> > increase table_cache ( > 1k )
>>>>> > -- Table cache hit rate: 7% (1K open / 14K opened)
>>>>> > -- Increase table_cache gradually to avoid file descriptor limits
>>>>> >
>>>>> > All of the aside, you need to let this run for at least 24 hours. I
>>>>> > prefer 48 hours. =A0The first line says mysql has only been running=
9
>>>>> > hours. =A0 You can reset the timeouts interactivly by entering at t=
he
>>>>> > mysql prompt:
>>>>> >
>>>>> > set global wait_timeout=3D
>>>>> >
>>>>> > You can do the same for the interactive_timeout.
>>>>> >
>>>>> > Setting these values too low will cause long running queries to abo=
rt
>>>>> >
>>>>> >
>>>>> > On 10/02/2011 07:02 PM, Joey L wrote:
>>>>> > > Variables to adjust:
>>>>> > > > =A0 =A0 max_connections (> 100)
>>>>> > > > =A0 =A0 wait_timeout (< 28800)
>>>>> > > > =A0 =A0 interactive_timeout (< 28800)
>>>>> > > > =A0 =A0 key_buffer_size (> 7.8G)
>>>>> > > > =A0 =A0 join_buffer_size (> 128.0K, or always use indexes with =
joins)
>>>>> > > > =A0 =A0 tmp_table_size (> 16M)
>>>>> > > > =A0 =A0 max_heap_table_size (> 16M)
>>>>> > > > =A0 =A0 table_cache (> 1024)
>>>>> >
>>>>> >
>>>>> > --
>>>>> > MySQL General Mailing List
>>>>> > For list archives: http://lists.mysql.com/mysql
>>>>> > To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmjh2000=
@gmail.com
>>>>> >
>>>>> >
>>>>>
>>>>
>>>>
>>>
>>
>>
>>
>> --
>> Eric Bergen
>> eric.bergen@gmail.com
>> http://www.ebergen.net
>>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: 4 minute slow on select count(*) from table - myisam type

I think in order to solve your problem you will need to post the queries run=
ning against this table along with the explain output of each problem query.=
Optimizing server settings is a good start, however, individual query perfo=
rmance sounds like your problem now.=20

Sent from my iPad

On Oct 6, 2011, at 6:47 AM, Joey L wrote:

> Just as an fyi - I have other databases and their corresponding apache
> websites on the same server - performing okay.
> It seems that apache/mysql server is just having a hard time dealing
> with the access to those pages that deal with the 9gig table on that
> particular site. -- Most of the access is done by webcrawlers to the
> site - so there is a lot of activity occuring on the 9gig tables.
>=20
> thanks
> mjh
>=20
> On Thu, Oct 6, 2011 at 6:13 AM, Joey L wrote:
>> guys - i am having such a hard time with this..it is killing me!!!
>> Sorry - had to vent.
>> my machine is running an tyan S2912G2NR -- with 2 opterons and 12gig
>> of memory. I have 2 software raided drives 1gig each.
>> I run a couple of databases --- my largest table is about 9gig in
>> size. --it is being accessed a lot.
>> My my.cnf is as follows:
>>=20
>> #
>> # The MySQL database server configuration file.
>> #
>> # You can copy this to one of:
>> # - "/etc/mysql/my.cnf" to set global options,
>> # - "~/.my.cnf" to set user-specific options.
>> #
>> # One can use all long options that the program supports.
>> # Run program with --help to get a list of available options and with
>> # --print-defaults to see which it would actually understand and use.
>> #
>> # For explanations see
>> # http://dev.mysql.com/doc/mysql/en/server-system-variables.ht ml
>>=20
>> # This will be passed to all mysql clients
>> # It has been reported that passwords should be enclosed with ticks/quote=
s
>> # escpecially if they contain "#" chars...
>> # Remember to edit /etc/mysql/debian.cnf when changing the socket locatio=
n.
>> [client]
>> port =3D 3306
>> socket =3D /var/run/mysqld/mysqld.sock
>>=20
>> # Here is entries for some specific programs
>> # The following values assume you have at least 32M ram
>>=20
>> # This was formally known as [safe_mysqld]. Both versions are currently p=
arsed.
>> [mysqld_safe]
>> socket =3D /var/run/mysqld/mysqld.sock
>> nice =3D 0
>>=20
>> [mysqld]
>> #
>> # * Basic Settings
>> #
>> user =3D mysql
>> pid-file =3D /var/run/mysqld/mysqld.pid
>> socket =3D /var/run/mysqld/mysqld.sock
>> port =3D 3306
>> basedir =3D /usr
>> datadir =3D /var/lib/mysql
>> tmpdir =3D /tmp
>> language =3D /usr/share/mysql/english
>> skip-external-locking
>> #
>> # Instead of skip-networking the default is now to listen only on
>> # localhost which is more compatible and is not less secure.
>> bind-address =3D 127.0.0.1
>> #
>> # * Fine Tuning
>> #
>> key_buffer =3D 2G
>> key_buffer_size =3D 2G
>> max_allowed_packet =3D 16M
>> thread_stack =3D 192K
>> thread_cache_size =3D 8
>> join_buffer_size =3D 128
>> # This replaces the startup script and checks MyISAM tables if needed
>> # the first time they are touched
>> myisam-recover =3D BACKUP
>> max_connections =3D 100
>> table_cache =3D 1024
>> max_heap_table_size =3D 32M
>> tmp_table_size =3D 32M
>> thread_concurrency =3D 10
>> #
>> # * Query Cache Configuration
>> #
>> query_cache_limit =3D 2M
>> query_cache_size =3D 16M
>> #
>> # * 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 =3D /var/log/mysql/mysql.log
>> general_log =3D 2
>> #
>> # Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslo=
g.cnf.
>> #
>> # Here you can see queries with especially long duration
>> #log_slow_queries =3D /var/log/mysql/mysql-slow.log
>> #long_query_time =3D 2
>> #log-queries-not-using-indexes
>> #
>> # The following can be used as easy to replay backup logs or for replicat=
ion.
>> # note: if you are setting up a replication slave, see README.Debian abou=
t
>> # other settings you may need to change.
>> #server-id =3D 1
>> #log_bin =3D /var/log/mysql/mysql-bin.log
>> expire_logs_days =3D 10
>> max_binlog_size =3D 100M
>> #binlog_do_db =3D include_database_name
>> #binlog_ignore_db =3D 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 =3D /var/lib/mysql/
>> #
>> # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
>> #
>> # ssl-ca=3D/etc/mysql/cacert.pem
>> # ssl-cert=3D/etc/mysql/server-cert.pem
>> # ssl-key=3D/etc/mysql/server-key.pem
>>=20
>>=20
>>=20
>> [mysqldump]
>> quick
>> quote-names
>> max_allowed_packet =3D 64M
>>=20
>> [mysql]
>> #no-auto-rehash # faster start of mysql but no tab completition
>>=20
>> [isamchk]
>> key_buffer =3D 16M
>>=20
>> #
>> # * IMPORTANT: Additional settings that can override those from this file=
!
>> # The files must end with '.cnf', otherwise they'll be ignored.
>> #
>> !includedir /etc/mysql/conf.d/
>>=20
>> any thoughts or help would be appricated.
>> thanks
>>=20
>>=20
>> On Mon, Oct 3, 2011 at 1:22 PM, Eric Bergen wrote=
:
>>> Can you run show processlist in another connection while the select
>>> count(*) query is running and say what the state column is?
>>>=20
>>> On Mon, Oct 3, 2011 at 7:00 AM, Joey L wrote:
>>>> this is not a real query on the site - it is just a way i am measuring
>>>> performance on mysql - I do not know if it is such a great way to test.=

>>>> Looking for a better way to get a performance read on my site...do you h=
ave
>>>> any ?? besides just viewing pages on it.
>>>> thanks
>>>> mjh
>>>>=20
>>>>=20
>>>> On Mon, Oct 3, 2011 at 9:58 AM, Andrés Tello > wrote:
>>>>=20
>>>>> have you tried
>>>>>=20
>>>>> select count(yourindex) instead of select count(*) ?
>>>>>=20
>>>>>=20
>>>>> On Mon, Oct 3, 2011 at 7:53 AM, Joey L wrote:
>>>>>=20
>>>>>> Thanks for the input -
>>>>>> 1. I will wait 48 hours and see what happens.
>>>>>> 2. can you tell me what are some performance tests I can do to help m=
e
>>>>>> better tune my server ?
>>>>>> 3. I am concerned about this table : | w6h8a_sh404sef_urls
>>>>>> |
>>>>>> MyISAM | 10 | Dynamic | 8908402 | 174 | 155117818=
4 |
>>>>>> 281474976710655 | 2410850304 | 0 | 8908777 | 2011-0=
9-22
>>>>>> 11:16:03 | 2011-10-02 21:17:20 | 2011-10-02 10:12:04 | utf8_general_c=
i |
>>>>>> NULL | | |
>>>>>> what can I do to make it run faster - i did not write the code...but n=
eed
>>>>>> to
>>>>>> optimize server to handle this table when it gets larger. It is used=
for
>>>>>> url re-writes - so it has a lot of urls.
>>>>>> thanks
>>>>>> mjh
>>>>>>=20
>>>>>> On Mon, Oct 3, 2011 at 12:38 AM, Bruce Ferrell
>>>>>>> wrote:
>>>>>>=20
>>>>>>>=20
>>>>>>> The meaning is:
>>>>>>>=20
>>>>>>> increase max_connections
>>>>>>> reduce wait_timeout
>>>>>>> -- 28800 is wait 8 hours before closing out dead connections
>>>>>>> same for interactive_timeout
>>>>>>>=20
>>>>>>>=20
>>>>>>> increase key_buffer_size (> 7.8G) increase join_buffer_size
>>>>>>> -- This keeps mysql from having to run to disk constantly for keys
>>>>>>> -- Key buffer size / total MyISAM indexes: 256.0M/7.8G
>>>>>>> -- You have a key buffer of 256M and 7.8G of keys
>>>>>>>=20
>>>>>>> join_buffer_size (> 128.0K, or always use indexes with joins)
>>>>>>> Joins performed without indexes: 23576 of 744k queries.
>>>>>>> -- You probably want to look at the slow query log. Generalize the
>>>>>> queries
>>>>>>> and the do an explain on the query. I have seen instances where a q=
uery
>>>>>> I
>>>>>>> thought was using an index wasn't and I had to re-write... with help=

>>>>>> from
>>>>>>> this list :-) Thanks gang!
>>>>>>>=20
>>>>>>>=20
>>>>>>> increase tmp_table_size (> 16M)
>>>>>>> increase max_heap_table_size (> 16M)
>>>>>>> -- When making adjustments, make tmp_table_size/max_heap_table_size
>>>>>> equal
>>>>>>>=20
>>>>>>> increase table_cache ( > 1k )
>>>>>>> -- Table cache hit rate: 7% (1K open / 14K opened)
>>>>>>> -- Increase table_cache gradually to avoid file descriptor limits
>>>>>>>=20
>>>>>>> All of the aside, you need to let this run for at least 24 hours. I
>>>>>>> prefer 48 hours. The first line says mysql has only been running 9
>>>>>>> hours. You can reset the timeouts interactivly by entering at the
>>>>>>> mysql prompt:
>>>>>>>=20
>>>>>>> set global wait_timeout=3D
>>>>>>>=20
>>>>>>> You can do the same for the interactive_timeout.
>>>>>>>=20
>>>>>>> Setting these values too low will cause long running queries to abor=
t
>>>>>>>=20
>>>>>>>=20
>>>>>>> On 10/02/2011 07:02 PM, Joey L wrote:
>>>>>>>> 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)
>>>>>>>=20
>>>>>>>=20
>>>>>>> --
>>>>>>> MySQL General Mailing List
>>>>>>> For list archives: http://lists.mysql.com/mysql
>>>>>>> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmjh2000@gmai=
l.com
>>>>>>>=20
>>>>>>>=20
>>>>>>=20
>>>>>=20
>>>>>=20
>>>>=20
>>>=20
>>>=20
>>>=20
>>> --
>>> Eric Bergen
>>> eric.bergen@gmail.com
>>> http://www.ebergen.net
>>>=20
>>=20
>=20
> --=20
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Djohnny@pixelated.n=
et
>=20

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: 4 minute slow on select count(*) from table - myisam type

thanks for the response - but do not believe queries are the issue
because - Like I said - i have other websites doing the same exact
queries as I am doing on the site with the 9gig table.
-- my issue is optimizing mysql to handle lots of queries on a 9gig
db. --- i think that is the focus.
All other websites (10 websites) are being handled fine in terms of
performance - with same queries -- just that table is about 100meg.
I have run optimize on it and recover and prune,etc....no luck.
thanks
mjh

On Thu, Oct 6, 2011 at 9:15 AM, Johnny Withers wrote=
:
> I think in order to solve your problem you will need to post the queries =
running against this table along with the explain output of each problem qu=
ery. Optimizing server settings is a good start, however, individual query =
performance sounds like your problem now.
>
> Sent from my iPad
>
> On Oct 6, 2011, at 6:47 AM, Joey L wrote:
>
>> Just as an fyi - I have other databases and their corresponding apache
>> websites on the same server - performing okay.
>> It seems that apache/mysql server is just having a hard time dealing
>> with the access to those pages that deal with the 9gig table on that
>> particular site. =A0-- Most of the access is done by webcrawlers to the
>> site - so there is a lot of activity occuring on the 9gig tables.
>>
>> thanks
>> mjh
>>
>> On Thu, Oct 6, 2011 at 6:13 AM, Joey L wrote:
>>> guys - i am having such a hard time with this..it is killing me!!!
>>> Sorry - had to vent.
>>> my machine is running an tyan S2912G2NR =A0-- with 2 opterons and 12gig
>>> of memory. I have 2 software raided drives 1gig each.
>>> I run a couple of databases --- my largest table is about 9gig in
>>> size. --it is being accessed a lot.
>>> My my.cnf is as follows:
>>>
>>> #
>>> # The MySQL database server configuration file.
>>> #
>>> # You can copy this to one of:
>>> # - "/etc/mysql/my.cnf" to set global options,
>>> # - "~/.my.cnf" to set user-specific options.
>>> #
>>> # One can use all long options that the program supports.
>>> # Run program with --help to get a list of available options and with
>>> # --print-defaults to see which it would actually understand and use.
>>> #
>>> # For explanations see
>>> # http://dev.mysql.com/doc/mysql/en/server-system-variables.ht ml
>>>
>>> # This will be passed to all mysql clients
>>> # It has been reported that passwords should be enclosed with ticks/quo=
tes
>>> # escpecially if they contain "#" chars...
>>> # Remember to edit /etc/mysql/debian.cnf when changing the socket locat=
ion.
>>> [client]
>>> port =A0 =A0 =A0 =A0 =A0  = 3306
>>> socket =A0 =A0 =A0 =A0  = /var/run/mysqld/mysqld.sock
>>>
>>> # Here is entries for some specific programs
>>> # The following values assume you have at least 32M ram
>>>
>>> # This was formally known as [safe_mysqld]. Both versions are currently=
parsed.
>>> [mysqld_safe]
>>> socket =A0 =A0 =A0 =A0  = /var/run/mysqld/mysqld.sock
>>> nice =A0 =A0 =A0 =A0 =A0  = 0
>>>
>>> [mysqld]
>>> #
>>> # * Basic Settings
>>> #
>>> user =A0 =A0 =A0 =A0 =A0  = mysql
>>> pid-file =A0 =A0 =A0  = /var/run/mysqld/mysqld.pid
>>> socket =A0 =A0 =A0 =A0  = /var/run/mysqld/mysqld.sock
>>> port =A0 =A0 =A0 =A0 =A0  = 3306
>>> basedir =A0 =A0 =A0 =A0 =3D /usr
>>> datadir =A0 =A0 =A0 =A0 =3D /var/lib/mysql
>>> tmpdir =A0 =A0 =A0 =A0  = /tmp
>>> language =A0 =A0 =A0  = /usr/share/mysql/english
>>> skip-external-locking
>>> #
>>> # Instead of skip-networking the default is now to listen only on
>>> # localhost which is more compatible and is not less secure.
>>> bind-address =A0 =A0 =A0 =A0 =A0  = 127.0.0.1
>>> #
>>> # * Fine Tuning
>>> #
>>> key_buffer =A0 =A0 =A0 =A0 =A0 =A0  = 2G
>>> key_buffer_size =A0 =A0 =A0 =A0 =3D 2G
>>> max_allowed_packet =A0 =A0  = 16M
>>> thread_stack =A0 =A0 =A0 =A0 =A0  = 192K
>>> thread_cache_size =A0 =A0 =A0 =3D 8
>>> join_buffer_size =A0 =A0 =A0 =A0 =A0 =A0 =A0  = 128
>>> # This replaces the startup script and checks MyISAM tables if needed
>>> # the first time they are touched
>>> myisam-recover =A0 =A0 =A0 =A0 =3D BACKUP
>>> max_connections =A0 =A0 =A0  = 100
>>> table_cache =A0 =A0 =A0 =A0 =A0  = 1024
>>> max_heap_table_size =A0 =A0 =A0 =A0 =A0 =A0 =3D 32M
>>> tmp_table_size =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0  = 32M
>>> thread_concurrency =A0 =A0 =3D 10
>>> #
>>> # * Query Cache Configuration
>>> #
>>> query_cache_limit =A0 =A0 =A0 =3D 2M
>>> query_cache_size =A0 =A0 =A0  = 16M
>>> #
>>> # * 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 =A0 =A0 =A0  = /var/log/mysql/mysql.log
>>> general_log =A0 =A0 =A0 =A0 =A0 =A0 =3D 2
>>> #
>>> # Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_sys=
log.cnf.
>>> #
>>> # Here you can see queries with especially long duration
>>> #log_slow_queries =A0 =A0 =A0 =3D /var/log/mysql/mysql-slow.log
>>> #long_query_time =3D 2
>>> #log-queries-not-using-indexes
>>> #
>>> # The following can be used as easy to replay backup logs or for replic=
ation.
>>> # note: if you are setting up a replication slave, see README.Debian ab=
out
>>> # =A0 =A0 =A0 other settings you may need to change.
>>> #server-id =A0 =A0 =A0 =A0 =A0 =A0  = 1
>>> #log_bin =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0  = /var/log/my=
sql/mysql-bin.log
>>> expire_logs_days =A0 =A0 =A0  = 10
>>> max_binlog_size =A0 =A0 =A0 =A0 =3D 100M
>>> #binlog_do_db =A0 =A0 =A0 =A0 =A0 =3D include_database_name
>>> #binlog_ignore_db =A0 =A0 =A0 =3D 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 =3D /var/lib/mysql/
>>> #
>>> # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
>>> #
>>> # ssl-ca=3D/etc/mysql/cacert.pem
>>> # ssl-cert=3D/etc/mysql/server-cert.pem
>>> # ssl-key=3D/etc/mysql/server-key.pem
>>>
>>>
>>>
>>> [mysqldump]
>>> quick
>>> quote-names
>>> max_allowed_packet =A0 =A0  = 64M
>>>
>>> [mysql]
>>> #no-auto-rehash # faster start of mysql but no tab completition
>>>
>>> [isamchk]
>>> key_buffer =A0 =A0 =A0 =A0 =A0 =A0  = 16M
>>>
>>> #
>>> # * IMPORTANT: Additional settings that can override those from this fi=
le!
>>> # =A0 The files must end with '.cnf', otherwise they'll be ignored.
>>> #
>>> !includedir /etc/mysql/conf.d/
>>>
>>> any thoughts or help would be appricated.
>>> thanks
>>>
>>>
>>> On Mon, Oct 3, 2011 at 1:22 PM, Eric Bergen wro=
te:
>>>> Can you run show processlist in another connection while the select
>>>> count(*) query is running and say what the state column is?
>>>>
>>>> On Mon, Oct 3, 2011 at 7:00 AM, Joey L wrote:
>>>>> this is not a real query on the site - it is just a way i am measurin=
g
>>>>> performance on mysql - I do not know if it is such a great way to tes=
t.
>>>>> Looking for a better way to get a performance read on my site...do yo=
u have
>>>>> any ?? besides just viewing pages on it.
>>>>> thanks
>>>>> mjh
>>>>>
>>>>>
>>>>> On Mon, Oct 3, 2011 at 9:58 AM, Andr=E9s Tello =
wrote:
>>>>>
>>>>>> have you tried
>>>>>>
>>>>>> select count(yourindex) instead of select count(*) ?
>>>>>>
>>>>>>
>>>>>> On Mon, Oct 3, 2011 at 7:53 AM, Joey L wrote:
>>>>>>
>>>>>>> Thanks for the input -
>>>>>>> 1. I will wait 48 hours and see what happens.
>>>>>>> 2. can you tell me what are some performance tests I can do to help=
me
>>>>>>> better tune my server ?
>>>>>>> 3. I am concerned about this table : | w6h8a_sh404sef_urls
>>>>>>> |
>>>>>>> MyISAM | =A0 =A0 =A010 | Dynamic =A0 =A0| 8908402 | =A0 =A0 =A0 =A0=
=A0 =A0174 | =A01551178184 |
>>>>>>> =A0281474976710655 | =A0 2410850304 | =A0 =A0 =A0 =A0 0 | =A0 =A0 =
=A0 =A08908777 | 2011-09-22
>>>>>>> 11:16:03 | 2011-10-02 21:17:20 | 2011-10-02 10:12:04 | utf8_general=
_ci =A0 |
>>>>>>> =A0NULL | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 |
>>>>>>> what can I do to make it run faster - i did not write the code...bu=
t need
>>>>>>> to
>>>>>>> optimize server to handle this table when it gets larger. =A0It is =
used for
>>>>>>> url re-writes - so it has a lot of urls.
>>>>>>> thanks
>>>>>>> mjh
>>>>>>>
>>>>>>> On Mon, Oct 3, 2011 at 12:38 AM, Bruce Ferrell rg
>>>>>>>> wrote:
>>>>>>>
>>>>>>>>
>>>>>>>> The meaning is:
>>>>>>>>
>>>>>>>> increase max_connections
>>>>>>>> reduce wait_timeout
>>>>>>>> -- 28800 is wait 8 hours before closing out dead connections
>>>>>>>> same for interactive_timeout
>>>>>>>>
>>>>>>>>
>>>>>>>> increase key_buffer_size (> 7.8G) increase join_buffer_size
>>>>>>>> -- This keeps mysql from having to run to disk constantly for keys
>>>>>>>> -- Key buffer size / total MyISAM indexes: 256.0M/7.8G
>>>>>>>> -- You have a key buffer of 256M and 7.8G of keys
>>>>>>>>
>>>>>>>> join_buffer_size (> 128.0K, or always use indexes with joins)
>>>>>>>> Joins performed without indexes: 23576 of 744k queries.
>>>>>>>> -- You probably want to look at the slow query log. =A0Generalize =
the
>>>>>>> queries
>>>>>>>> and the do an explain on the query. =A0I have seen instances where=
a query
>>>>>>> I
>>>>>>>> thought was using an index wasn't and I had to re-write... with he=
lp
>>>>>>> from
>>>>>>>> this list :-) =A0Thanks gang!
>>>>>>>>
>>>>>>>>
>>>>>>>> increase tmp_table_size (> 16M)
>>>>>>>> increase max_heap_table_size (> 16M)
>>>>>>>> -- When making adjustments, make tmp_table_size/max_heap_table_siz=
e
>>>>>>> equal
>>>>>>>>
>>>>>>>> increase table_cache ( > 1k )
>>>>>>>> -- Table cache hit rate: 7% (1K open / 14K opened)
>>>>>>>> -- Increase table_cache gradually to avoid file descriptor limits
>>>>>>>>
>>>>>>>> All of the aside, you need to let this run for at least 24 hours. =
I
>>>>>>>> prefer 48 hours. =A0The first line says mysql has only been runnin=
g 9
>>>>>>>> hours. =A0 You can reset the timeouts interactivly by entering at =
the
>>>>>>>> mysql prompt:
>>>>>>>>
>>>>>>>> set global wait_timeout=3D
>>>>>>>>
>>>>>>>> You can do the same for the interactive_timeout.
>>>>>>>>
>>>>>>>> Setting these values too low will cause long running queries to ab=
ort
>>>>>>>>
>>>>>>>>
>>>>>>>> On 10/02/2011 07:02 PM, Joey L wrote:
>>>>>>>>> Variables to adjust:
>>>>>>>>>> =A0 =A0 max_connections (> 100)
>>>>>>>>>> =A0 =A0 wait_timeout (< 28800)
>>>>>>>>>> =A0 =A0 interactive_timeout (< 28800)
>>>>>>>>>> =A0 =A0 key_buffer_size (> 7.8G)
>>>>>>>>>> =A0 =A0 join_buffer_size (> 128.0K, or always use indexes with j=
oins)
>>>>>>>>>> =A0 =A0 tmp_table_size (> 16M)
>>>>>>>>>> =A0 =A0 max_heap_table_size (> 16M)
>>>>>>>>>> =A0 =A0 table_cache (> 1024)
>>>>>>>>
>>>>>>>>
>>>>>>>> --
>>>>>>>> MySQL General Mailing List
>>>>>>>> For list archives: http://lists.mysql.com/mysql
>>>>>>>> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmjh200=
0@gmail.com
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Eric Bergen
>>>> eric.bergen@gmail.com
>>>> http://www.ebergen.net
>>>>
>>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Djohnny@pixel=
ated.net
>>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: 4 minute slow on select count(*) from table - myisam type

Doing the same query on a table that fits into memory is a completely differ=
ent thing than doing the query on a table where half the needed data resides=
on disk. Maybe your queries are not using an index? On a table with a few 1=
00-thousand records this is probably a non issue for the server, when the ta=
ble has millions, well then it could be a problem. Maybe your indexes for th=
is table don't fit into memory? Who knows? That is why you need to look at t=
he problem queries.=20

I can tweak server settings all day long, but if my query is doing a join on=
a table with 40 million records with no index, it'll never work.=20

Sent from my iPad

On Oct 6, 2011, at 8:40 AM, Joey L wrote:

> thanks for the response - but do not believe queries are the issue
> because - Like I said - i have other websites doing the same exact
> queries as I am doing on the site with the 9gig table.
> -- my issue is optimizing mysql to handle lots of queries on a 9gig
> db. --- i think that is the focus.
> All other websites (10 websites) are being handled fine in terms of
> performance - with same queries -- just that table is about 100meg.
> I have run optimize on it and recover and prune,etc....no luck.
> thanks
> mjh
>=20
> On Thu, Oct 6, 2011 at 9:15 AM, Johnny Withers wrot=
e:
>> I think in order to solve your problem you will need to post the queries r=
unning against this table along with the explain output of each problem quer=
y. Optimizing server settings is a good start, however, individual query per=
formance sounds like your problem now.
>>=20
>> Sent from my iPad
>>=20
>> On Oct 6, 2011, at 6:47 AM, Joey L wrote:
>>=20
>>> Just as an fyi - I have other databases and their corresponding apache
>>> websites on the same server - performing okay.
>>> It seems that apache/mysql server is just having a hard time dealing
>>> with the access to those pages that deal with the 9gig table on that
>>> particular site. -- Most of the access is done by webcrawlers to the
>>> site - so there is a lot of activity occuring on the 9gig tables.
>>>=20
>>> thanks
>>> mjh
>>>=20
>>> On Thu, Oct 6, 2011 at 6:13 AM, Joey L wrote:
>>>> guys - i am having such a hard time with this..it is killing me!!!
>>>> Sorry - had to vent.
>>>> my machine is running an tyan S2912G2NR -- with 2 opterons and 12gig
>>>> of memory. I have 2 software raided drives 1gig each.
>>>> I run a couple of databases --- my largest table is about 9gig in
>>>> size. --it is being accessed a lot.
>>>> My my.cnf is as follows:
>>>>=20
>>>> #
>>>> # The MySQL database server configuration file.
>>>> #
>>>> # You can copy this to one of:
>>>> # - "/etc/mysql/my.cnf" to set global options,
>>>> # - "~/.my.cnf" to set user-specific options.
>>>> #
>>>> # One can use all long options that the program supports.
>>>> # Run program with --help to get a list of available options and with
>>>> # --print-defaults to see which it would actually understand and use.
>>>> #
>>>> # For explanations see
>>>> # http://dev.mysql.com/doc/mysql/en/server-system-variables.ht ml
>>>>=20
>>>> # This will be passed to all mysql clients
>>>> # It has been reported that passwords should be enclosed with ticks/quo=
tes
>>>> # escpecially if they contain "#" chars...
>>>> # Remember to edit /etc/mysql/debian.cnf when changing the socket locat=
ion.
>>>> [client]
>>>> port =3D 3306
>>>> socket =3D /var/run/mysqld/mysqld.sock
>>>>=20
>>>> # Here is entries for some specific programs
>>>> # The following values assume you have at least 32M ram
>>>>=20
>>>> # This was formally known as [safe_mysqld]. Both versions are currently=
parsed.
>>>> [mysqld_safe]
>>>> socket =3D /var/run/mysqld/mysqld.sock
>>>> nice =3D 0
>>>>=20
>>>> [mysqld]
>>>> #
>>>> # * Basic Settings
>>>> #
>>>> user =3D mysql
>>>> pid-file =3D /var/run/mysqld/mysqld.pid
>>>> socket =3D /var/run/mysqld/mysqld.sock
>>>> port =3D 3306
>>>> basedir =3D /usr
>>>> datadir =3D /var/lib/mysql
>>>> tmpdir =3D /tmp
>>>> language =3D /usr/share/mysql/english
>>>> skip-external-locking
>>>> #
>>>> # Instead of skip-networking the default is now to listen only on
>>>> # localhost which is more compatible and is not less secure.
>>>> bind-address =3D 127.0.0.1
>>>> #
>>>> # * Fine Tuning
>>>> #
>>>> key_buffer =3D 2G
>>>> key_buffer_size =3D 2G
>>>> max_allowed_packet =3D 16M
>>>> thread_stack =3D 192K
>>>> thread_cache_size =3D 8
>>>> join_buffer_size =3D 128
>>>> # This replaces the startup script and checks MyISAM tables if needed
>>>> # the first time they are touched
>>>> myisam-recover =3D BACKUP
>>>> max_connections =3D 100
>>>> table_cache =3D 1024
>>>> max_heap_table_size =3D 32M
>>>> tmp_table_size =3D 32M
>>>> thread_concurrency =3D 10
>>>> #
>>>> # * Query Cache Configuration
>>>> #
>>>> query_cache_limit =3D 2M
>>>> query_cache_size =3D 16M
>>>> #
>>>> # * 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 =3D /var/log/mysql/mysql.log
>>>> general_log =3D 2
>>>> #
>>>> # Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_sys=
log.cnf.
>>>> #
>>>> # Here you can see queries with especially long duration
>>>> #log_slow_queries =3D /var/log/mysql/mysql-slow.log
>>>> #long_query_time =3D 2
>>>> #log-queries-not-using-indexes
>>>> #
>>>> # The following can be used as easy to replay backup logs or for replic=
ation.
>>>> # note: if you are setting up a replication slave, see README.Debian ab=
out
>>>> # other settings you may need to change.
>>>> #server-id =3D 1
>>>> #log_bin =3D /var/log/mysql/mysql-bin.log
>>>> expire_logs_days =3D 10
>>>> max_binlog_size =3D 100M
>>>> #binlog_do_db =3D include_database_name
>>>> #binlog_ignore_db =3D 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 =3D /var/lib/mysql/
>>>> #
>>>> # For generating SSL certificates I recommend the OpenSSL GUI "tinyca".=

>>>> #
>>>> # ssl-ca=3D/etc/mysql/cacert.pem
>>>> # ssl-cert=3D/etc/mysql/server-cert.pem
>>>> # ssl-key=3D/etc/mysql/server-key.pem
>>>>=20
>>>>=20
>>>>=20
>>>> [mysqldump]
>>>> quick
>>>> quote-names
>>>> max_allowed_packet =3D 64M
>>>>=20
>>>> [mysql]
>>>> #no-auto-rehash # faster start of mysql but no tab completition
>>>>=20
>>>> [isamchk]
>>>> key_buffer =3D 16M
>>>>=20
>>>> #
>>>> # * IMPORTANT: Additional settings that can override those from this fi=
le!
>>>> # The files must end with '.cnf', otherwise they'll be ignored.
>>>> #
>>>> !includedir /etc/mysql/conf.d/
>>>>=20
>>>> any thoughts or help would be appricated.
>>>> thanks
>>>>=20
>>>>=20
>>>> On Mon, Oct 3, 2011 at 1:22 PM, Eric Bergen wro=
te:
>>>>> Can you run show processlist in another connection while the select
>>>>> count(*) query is running and say what the state column is?
>>>>>=20
>>>>> On Mon, Oct 3, 2011 at 7:00 AM, Joey L wrote:
>>>>>> this is not a real query on the site - it is just a way i am measurin=
g
>>>>>> performance on mysql - I do not know if it is such a great way to tes=
t.
>>>>>> Looking for a better way to get a performance read on my site...do yo=
u have
>>>>>> any ?? besides just viewing pages on it.
>>>>>> thanks
>>>>>> mjh
>>>>>>=20
>>>>>>=20
>>>>>> On Mon, Oct 3, 2011 at 9:58 AM, Andrés Tello om> wrote:
>>>>>>=20
>>>>>>> have you tried
>>>>>>>=20
>>>>>>> select count(yourindex) instead of select count(*) ?
>>>>>>>=20
>>>>>>>=20
>>>>>>> On Mon, Oct 3, 2011 at 7:53 AM, Joey L wrote:
>>>>>>>=20
>>>>>>>> Thanks for the input -
>>>>>>>> 1. I will wait 48 hours and see what happens.
>>>>>>>> 2. can you tell me what are some performance tests I can do to help=
me
>>>>>>>> better tune my server ?
>>>>>>>> 3. I am concerned about this table : | w6h8a_sh404sef_urls
>>>>>>>> |
>>>>>>>> MyISAM | 10 | Dynamic | 8908402 | 174 | 1551178=
184 |
>>>>>>>> 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 | | |
>>>>>>>> what can I do to make it run faster - i did not write the code...bu=
t need
>>>>>>>> to
>>>>>>>> optimize server to handle this table when it gets larger. It is us=
ed for
>>>>>>>> url re-writes - so it has a lot of urls.
>>>>>>>> thanks
>>>>>>>> mjh
>>>>>>>>=20
>>>>>>>> On Mon, Oct 3, 2011 at 12:38 AM, Bruce Ferrell rg
>>>>>>>>> wrote:
>>>>>>>>=20
>>>>>>>>>=20
>>>>>>>>> The meaning is:
>>>>>>>>>=20
>>>>>>>>> increase max_connections
>>>>>>>>> reduce wait_timeout
>>>>>>>>> -- 28800 is wait 8 hours before closing out dead connections
>>>>>>>>> same for interactive_timeout
>>>>>>>>>=20
>>>>>>>>>=20
>>>>>>>>> increase key_buffer_size (> 7.8G) increase join_buffer_size
>>>>>>>>> -- This keeps mysql from having to run to disk constantly for keys=

>>>>>>>>> -- Key buffer size / total MyISAM indexes: 256.0M/7.8G
>>>>>>>>> -- You have a key buffer of 256M and 7.8G of keys
>>>>>>>>>=20
>>>>>>>>> join_buffer_size (> 128.0K, or always use indexes with joins)
>>>>>>>>> Joins performed without indexes: 23576 of 744k queries.
>>>>>>>>> -- You probably want to look at the slow query log. Generalize th=
e
>>>>>>>> queries
>>>>>>>>> and the do an explain on the query. I have seen instances where a=
query
>>>>>>>> I
>>>>>>>>> thought was using an index wasn't and I had to re-write... with he=
lp
>>>>>>>> from
>>>>>>>>> this list :-) Thanks gang!
>>>>>>>>>=20
>>>>>>>>>=20
>>>>>>>>> increase tmp_table_size (> 16M)
>>>>>>>>> increase max_heap_table_size (> 16M)
>>>>>>>>> -- When making adjustments, make tmp_table_size/max_heap_table_siz=
e
>>>>>>>> equal
>>>>>>>>>=20
>>>>>>>>> increase table_cache ( > 1k )
>>>>>>>>> -- Table cache hit rate: 7% (1K open / 14K opened)
>>>>>>>>> -- Increase table_cache gradually to avoid file descriptor limits
>>>>>>>>>=20
>>>>>>>>> All of the aside, you need to let this run for at least 24 hours. I=

>>>>>>>>> prefer 48 hours. The first line says mysql has only been running 9=

>>>>>>>>> hours. You can reset the timeouts interactivly by entering at th=
e
>>>>>>>>> mysql prompt:
>>>>>>>>>=20
>>>>>>>>> set global wait_timeout=3D
>>>>>>>>>=20
>>>>>>>>> You can do the same for the interactive_timeout.
>>>>>>>>>=20
>>>>>>>>> Setting these values too low will cause long running queries to ab=
ort
>>>>>>>>>=20
>>>>>>>>>=20
>>>>>>>>> On 10/02/2011 07:02 PM, Joey L wrote:
>>>>>>>>>> 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)
>>>>>>>>>=20
>>>>>>>>>=20
>>>>>>>>> --
>>>>>>>>> MySQL General Mailing List
>>>>>>>>> For list archives: http://lists.mysql.com/mysql
>>>>>>>>> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dmjh2000@gm=
ail.com
>>>>>>>>>=20
>>>>>>>>>=20
>>>>>>>>=20
>>>>>>>=20
>>>>>>>=20
>>>>>>=20
>>>>>=20
>>>>>=20
>>>>>=20
>>>>> --
>>>>> Eric Bergen
>>>>> eric.bergen@gmail.com
>>>>> http://www.ebergen.net
>>>>>=20
>>>>=20
>>>=20
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Djohnny@pixelated=
..net
>>>=20
>>=20

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: 4 minute slow on select count(*) from table - myisam type

I keep finding it extremely peculiar that a count(*) on a MyISAM table would take that long. InnoDB needs to effectively *count* the records, but MyISAM keeps accurate statistics and can just read it from the metadata.

This suggests to me that not all your metadata (ie., table descriptors et al) can be kept in memory. That's just a hunch, though, and I haven't actively followed this thread.

Another possibility I see is that the table is kept locked by long-running transactions (or by a shitload of activity on it) - I /think/ that also prevents access to the metadata. Does "show open tables" show something?

As a longer shot (almost over the horizon, really) could something be stopping mysqld from accessing the table's datafiles or slowing that access down considerably?


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: 4 minute slow on select count(*) from table - myisam type

> thanks for the response - but do not believe queries are the issue
> because - Like I said - i have other websites doing the same exact
> queries as I am doing on the site with the 9gig table.

Contrary to popular believe, size DOES matter... And having a table large
enough so it doesn't fit in memory could require another approach entirely for
query optimization.

Another good start would be to examine the output of mysqlreport, it will tel
you a lot.
--
Rik Wasmus

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: 4 minute slow on select count(*) from table - myisam type

here is mysqlreport ---
------------------------------------------------
root@rider:~/tmp# ./mysqlreport --user root --password barakobomb
Use of uninitialized value $is in multiplication (*) at ./mysqlreport line =
829.
Use of uninitialized value in formline at ./mysqlreport line 1227.
MySQL 5.1.49-3-log uptime 0 0:25:5 Thu Oct 6 10:20:49 2011

__ Key ____________________________________________________________ _____
Buffer used 727.43M of 2.00G %Used: 35.52
Current 963.24M %Usage: 47.03
Write hit 29.41%
Read hit 99.79%

__ Questions ___________________________________________________________
Total 50.20k 33.4/s
QC Hits 32.56k 21.6/s %Total: 64.87
DMS 12.28k 8.2/s 24.46
Com_ 3.21k 2.1/s 6.39
COM_QUIT 2.89k 1.9/s 5.76
-Unknown 745 0.5/s 1.48
Slow 10 s 68 0.0/s 0.14 %DMS: 0.55 Log: OFF
DMS 12.28k 8.2/s 24.46
SELECT 11.09k 7.4/s 22.10 90.36
UPDATE 539 0.4/s 1.07 4.39
INSERT 384 0.3/s 0.77 3.13
DELETE 260 0.2/s 0.52 2.12
REPLACE 0 0/s 0.00 0.00
Com_ 3.21k 2.1/s 6.39
set_option 1.10k 0.7/s 2.20
show_fields 1.03k 0.7/s 2.05
admin_comma 707 0.5/s 1.41

__ SELECT and Sort _____________________________________________________
Scan 1.65k 1.1/s %SELECT: 14.87
Range 493 0.3/s 4.44
Full join 310 0.2/s 2.79
Range check 339 0.2/s 3.06
Full rng join 0 0/s 0.00
Sort scan 887 0.6/s
Sort range 628 0.4/s
Sort mrg pass 0 0/s

__ Query Cache _________________________________________________________
Memory usage 5.96M of 16.00M %Used: 37.25
Block Fragmnt 5.17%
Hits 32.56k 21.6/s
Inserts 5.66k 3.8/s
Insrt:Prune 5.66k:1 3.8/s
Hit:Insert 5.76:1

__ Table Locks _________________________________________________________
Waited 513 0.3/s %Total: 3.62
Immediate 13.65k 9.1/s

__ Tables ____________________________________________________________ __
Open 1024 of 1024 %Cache: 100.00
Opened 14.96k 9.9/s

__ Connections _________________________________________________________
Max used 70 of 100 %Max: 70.00
Total 2.89k 1.9/s

__ Created Temp ________________________________________________________
Disk table 1.34k 0.9/s
Table 2.35k 1.6/s Size: 32.0M
File 5 0.0/s

__ Threads ____________________________________________________________ _
Running 32 of 37
Cached 0 of 8 %Hit: 93.26
Created 195 0.1/s
Slow 0 0/s

__ Aborted ____________________________________________________________ _
Clients 0 0/s
Connects 2 0.0/s

__ Bytes ____________________________________________________________ ___
Sent 100.33M 66.7k/s
Received 12.48M 8.3k/s

__ InnoDB Buffer Pool __________________________________________________
Usage 1.67M of 8.00M %Used: 20.90
Read hit 99.70%
Pages
Free 405 %Total: 79.10
Data 107 20.90 %Drty: 0.00
Misc 0 0.00
Latched 0.00
Reads 26.18k 17.4/s
From file 78 0.1/s 0.30
Ahead Rnd 2 0.0/s
Ahead Sql 1 0.0/s
Writes 3 0.0/s
Flushes 3 0.0/s
Wait Free 0 0/s

__ InnoDB Lock _________________________________________________________
Waits 0 0/s
Current 0
Time acquiring
Total 0 ms
Average 0 ms
Max 0 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 96 0.1/s
Writes 12 0.0/s
fsync 11 0.0/s
Pending
Reads 0
Writes 0
fsync 0

Pages
Created 0 0/s
Read 107 0.1/s
Written 3 0.0/s

Rows
Deleted 0 0/s
Inserted 0 0/s
Read 20.98k 13.9/s
Updated 0 0/s
root@rider:~/tmp#

and the mysqltuner.pl report :
------------------------------------------------------------ -

root@rider:~/tmp# perl mysqltuner.pl

>> MySQLTuner 1.2.0 - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

-------- 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: 7G (Tables: 2408)
[--] Data in InnoDB tables: 1M (Tables: 37)
[!!] Total fragmented tables: 49

-------- Security Recommendations ----------------------------------------=
---
[OK] All database users have passwords assigned

-------- Performance Metrics ----------------------------------------------=
---
[--] Up for: 26m 26s (54K q [34.593 qps], 2K conn, TX: 110M, RX: 13M)
[--] Reads / Writes: 90% / 10%
[--] Total buffers: 2.1G global + 2.6M per thread (100 max threads)
[OK] Maximum possible memory usage: 2.3G (19% of installed RAM)
[OK] Slow queries: 0% (69/54K)
[OK] Highest usage of available connections: 70% (70/100)
[OK] Key buffer size / total MyISAM indexes: 2.0G/9.2G
[OK] Key buffer hit rate: 99.8% (363M cached / 745K reads)
[OK] Query cache efficiency: 76.1% (36K cached / 47K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
[!!] Joins performed without indexes: 689
[!!] Temporary tables created on disk: 36% (1K on disk / 4K total)
[OK] Thread cache hit rate: 93% (198 created / 2K connections)
[!!] Table cache hit rate: 6% (1K open / 14K opened)
[!!] Open file limit used: 89% (1K/2K)
[OK] Table locks acquired immediately: 96% (14K immediate / 14K locks)
[OK] InnoDB data size / buffer pool: 1.2M/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
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
Variables to adjust:
join_buffer_size (> 8.0K, or always use indexes with joins)
tmp_table_size (> 32M)
max_heap_table_size (> 32M)
table_cache (> 1024)
open_files_limit (> 2158)

root@rider:~/tmp#



On Thu, Oct 6, 2011 at 10:09 AM, Rik Wasmus wrote:
>> thanks for the response - but do not believe queries are the issue
>> because - Like I said - i have other websites doing the same exact
>> queries as I am doing on the site with the 9gig table.
>
> Contrary to popular believe, size DOES matter... And having a table large
> enough so it doesn't fit in memory could require another approach entirel=
y for
> query optimization.
>
> Another good start would be to examine the output of mysqlreport, it will=
tel
> you a lot.
> --
> Rik Wasmus
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmjh2000@gmail=
..com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: 4 minute slow on select count(*) from table - myisam type

--000e0cd51dc438928e04aea37d3d
Content-Type: text/plain; charset=ISO-8859-1

Joey, does your 'large' table get

On Thu, Oct 6, 2011 at 3:22 PM, Joey L wrote:

> here is mysqlreport ---
> ------------------------------------------------
> root@rider:~/tmp# ./mysqlreport --user root --password barakobomb
> Use of uninitialized value $is in multiplication (*) at ./mysqlreport line
> 829.
> Use of uninitialized value in formline at ./mysqlreport line 1227.
> MySQL 5.1.49-3-log uptime 0 0:25:5 Thu Oct 6 10:20:49 2011
>
> __ Key ____________________________________________________________ _____
> Buffer used 727.43M of 2.00G %Used: 35.52
> Current 963.24M %Usage: 47.03
> Write hit 29.41%
> Read hit 99.79%
>
> __ Questions ___________________________________________________________
> Total 50.20k 33.4/s
> QC Hits 32.56k 21.6/s %Total: 64.87
> DMS 12.28k 8.2/s 24.46
> Com_ 3.21k 2.1/s 6.39
> COM_QUIT 2.89k 1.9/s 5.76
> -Unknown 745 0.5/s 1.48
> Slow 10 s 68 0.0/s 0.14 %DMS: 0.55 Log: OFF
> DMS 12.28k 8.2/s 24.46
> SELECT 11.09k 7.4/s 22.10 90.36
> UPDATE 539 0.4/s 1.07 4.39
> INSERT 384 0.3/s 0.77 3.13
> DELETE 260 0.2/s 0.52 2.12
> REPLACE 0 0/s 0.00 0.00
> Com_ 3.21k 2.1/s 6.39
> set_option 1.10k 0.7/s 2.20
> show_fields 1.03k 0.7/s 2.05
> admin_comma 707 0.5/s 1.41
>
> __ SELECT and Sort _____________________________________________________
> Scan 1.65k 1.1/s %SELECT: 14.87
> Range 493 0.3/s 4.44
> Full join 310 0.2/s 2.79
> Range check 339 0.2/s 3.06
> Full rng join 0 0/s 0.00
> Sort scan 887 0.6/s
> Sort range 628 0.4/s
> Sort mrg pass 0 0/s
>
> __ Query Cache _________________________________________________________
> Memory usage 5.96M of 16.00M %Used: 37.25
> Block Fragmnt 5.17%
> Hits 32.56k 21.6/s
> Inserts 5.66k 3.8/s
> Insrt:Prune 5.66k:1 3.8/s
> Hit:Insert 5.76:1
>
> __ Table Locks _________________________________________________________
> Waited 513 0.3/s %Total: 3.62
> Immediate 13.65k 9.1/s
>
> __ Tables ____________________________________________________________ __
> Open 1024 of 1024 %Cache: 100.00
> Opened 14.96k 9.9/s
>
> __ Connections _________________________________________________________
> Max used 70 of 100 %Max: 70.00
> Total 2.89k 1.9/s
>
> __ Created Temp ________________________________________________________
> Disk table 1.34k 0.9/s
> Table 2.35k 1.6/s Size: 32.0M
> File 5 0.0/s
>
> __ Threads ____________________________________________________________ _
> Running 32 of 37
> Cached 0 of 8 %Hit: 93.26
> Created 195 0.1/s
> Slow 0 0/s
>
> __ Aborted ____________________________________________________________ _
> Clients 0 0/s
> Connects 2 0.0/s
>
> __ Bytes ____________________________________________________________ ___
> Sent 100.33M 66.7k/s
> Received 12.48M 8.3k/s
>
> __ InnoDB Buffer Pool __________________________________________________
> Usage 1.67M of 8.00M %Used: 20.90
> Read hit 99.70%
> Pages
> Free 405 %Total: 79.10
> Data 107 20.90 %Drty: 0.00
> Misc 0 0.00
> Latched 0.00
> Reads 26.18k 17.4/s
> From file 78 0.1/s 0.30
> Ahead Rnd 2 0.0/s
> Ahead Sql 1 0.0/s
> Writes 3 0.0/s
> Flushes 3 0.0/s
> Wait Free 0 0/s
>
> __ InnoDB Lock _________________________________________________________
> Waits 0 0/s
> Current 0
> Time acquiring
> Total 0 ms
> Average 0 ms
> Max 0 ms
>
> __ InnoDB Data, Pages, Rows ____________________________________________
> Data
> Reads 96 0.1/s
> Writes 12 0.0/s
> fsync 11 0.0/s
> Pending
> Reads 0
> Writes 0
> fsync 0
>
> Pages
> Created 0 0/s
> Read 107 0.1/s
> Written 3 0.0/s
>
> Rows
> Deleted 0 0/s
> Inserted 0 0/s
> Read 20.98k 13.9/s
> Updated 0 0/s
> root@rider:~/tmp#
>
> and the mysqltuner.pl report :
> ------------------------------------------------------------ -
>
> root@rider:~/tmp# perl mysqltuner.pl
>
> >> MySQLTuner 1.2.0 - Major Hayden
> >> Bug reports, feature requests, and downloads at
> http://mysqltuner.com/
> >> Run with '--help' for additional options and output filtering
> Please enter your MySQL administrative login: root
> Please enter your MySQL administrative password:
>
> -------- 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: 7G (Tables: 2408)
> [--] Data in InnoDB tables: 1M (Tables: 37)
> [!!] Total fragmented tables: 49
>
> -------- Security Recommendations
> -------------------------------------------
> [OK] All database users have passwords assigned
>
> -------- Performance Metrics
> -------------------------------------------------
> [--] Up for: 26m 26s (54K q [34.593 qps], 2K conn, TX: 110M, RX: 13M)
> [--] Reads / Writes: 90% / 10%
> [--] Total buffers: 2.1G global + 2.6M per thread (100 max threads)
> [OK] Maximum possible memory usage: 2.3G (19% of installed RAM)
> [OK] Slow queries: 0% (69/54K)
> [OK] Highest usage of available connections: 70% (70/100)
> [OK] Key buffer size / total MyISAM indexes: 2.0G/9.2G
> [OK] Key buffer hit rate: 99.8% (363M cached / 745K reads)
> [OK] Query cache efficiency: 76.1% (36K cached / 47K selects)
> [OK] Query cache prunes per day: 0
> [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
> [!!] Joins performed without indexes: 689
> [!!] Temporary tables created on disk: 36% (1K on disk / 4K total)
> [OK] Thread cache hit rate: 93% (198 created / 2K connections)
> [!!] Table cache hit rate: 6% (1K open / 14K opened)
> [!!] Open file limit used: 89% (1K/2K)
> [OK] Table locks acquired immediately: 96% (14K immediate / 14K locks)
> [OK] InnoDB data size / buffer pool: 1.2M/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
> 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
> Variables to adjust:
> join_buffer_size (> 8.0K, or always use indexes with joins)
> tmp_table_size (> 32M)
> max_heap_table_size (> 32M)
> table_cache (> 1024)
> open_files_limit (> 2158)
>
> root@rider:~/tmp#
>
>
>
> On Thu, Oct 6, 2011 at 10:09 AM, Rik Wasmus wrote:
> >> thanks for the response - but do not believe queries are the issue
> >> because - Like I said - i have other websites doing the same exact
> >> queries as I am doing on the site with the 9gig table.
> >
> > Contrary to popular believe, size DOES matter... And having a table large
> > enough so it doesn't fit in memory could require another approach
> entirely for
> > query optimization.
> >
> > Another good start would be to examine the output of mysqlreport, it will
> tel
> > you a lot.
> > --
> > Rik Wasmus
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=mjh2000@gmail.com
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=eroomydna@gmail.com
>
>

--000e0cd51dc438928e04aea37d3d--

Re: 4 minute slow on select count(*) from table - myisam type

--000e0cd719966947e704aea3a55e
Content-Type: text/plain; charset=ISO-8859-1

Sorry, hit send by accident there! *face palm*

Just had a quick scan of the report. You've got 2 1GB disks in software raid
- RAID1 or RAID5? I can also see you're creating a lot of temporary files on
disk. I think in your previous email that your biggest table's index(s) were
larger then the keybuffer size. I would suspect that you're disk bound with
limited IO performance through 2 disks and effectively 1 if in a mirrored
configuration. The stats show that you're configured for MyISAM and that
you're tables are taking reads and writes (read heavy though), MyISAM
doesn't like high concurrency mixed workloads such as yours, it will cause
locking and maybe thats why your count has such a delay. Such activity may
be better suited to InnoDB engine (you must configure and tune for this, not
JUST change the engine).

HTH

Andy



On Thu, Oct 6, 2011 at 5:05 PM, Andrew Moore wrote:

> Joey, does your 'large' table get
>
>
> On Thu, Oct 6, 2011 at 3:22 PM, Joey L wrote:
>
>> here is mysqlreport ---
>> ------------------------------------------------
>> root@rider:~/tmp# ./mysqlreport --user root --password barakobomb
>> Use of uninitialized value $is in multiplication (*) at ./mysqlreport line
>> 829.
>> Use of uninitialized value in formline at ./mysqlreport line 1227.
>> MySQL 5.1.49-3-log uptime 0 0:25:5 Thu Oct 6 10:20:49 2011
>>
>> __ Key ____________________________________________________________ _____
>> Buffer used 727.43M of 2.00G %Used: 35.52
>> Current 963.24M %Usage: 47.03
>> Write hit 29.41%
>> Read hit 99.79%
>>
>> __ Questions ___________________________________________________________
>> Total 50.20k 33.4/s
>> QC Hits 32.56k 21.6/s %Total: 64.87
>> DMS 12.28k 8.2/s 24.46
>> Com_ 3.21k 2.1/s 6.39
>> COM_QUIT 2.89k 1.9/s 5.76
>> -Unknown 745 0.5/s 1.48
>> Slow 10 s 68 0.0/s 0.14 %DMS: 0.55 Log: OFF
>> DMS 12.28k 8.2/s 24.46
>> SELECT 11.09k 7.4/s 22.10 90.36
>> UPDATE 539 0.4/s 1.07 4.39
>> INSERT 384 0.3/s 0.77 3.13
>> DELETE 260 0.2/s 0.52 2.12
>> REPLACE 0 0/s 0.00 0.00
>> Com_ 3.21k 2.1/s 6.39
>> set_option 1.10k 0.7/s 2.20
>> show_fields 1.03k 0.7/s 2.05
>> admin_comma 707 0.5/s 1.41
>>
>> __ SELECT and Sort _____________________________________________________
>> Scan 1.65k 1.1/s %SELECT: 14.87
>> Range 493 0.3/s 4.44
>> Full join 310 0.2/s 2.79
>> Range check 339 0.2/s 3.06
>> Full rng join 0 0/s 0.00
>> Sort scan 887 0.6/s
>> Sort range 628 0.4/s
>> Sort mrg pass 0 0/s
>>
>> __ Query Cache _________________________________________________________
>> Memory usage 5.96M of 16.00M %Used: 37.25
>> Block Fragmnt 5.17%
>> Hits 32.56k 21.6/s
>> Inserts 5.66k 3.8/s
>> Insrt:Prune 5.66k:1 3.8/s
>> Hit:Insert 5.76:1
>>
>> __ Table Locks _________________________________________________________
>> Waited 513 0.3/s %Total: 3.62
>> Immediate 13.65k 9.1/s
>>
>> __ Tables ____________________________________________________________ __
>> Open 1024 of 1024 %Cache: 100.00
>> Opened 14.96k 9.9/s
>>
>> __ Connections _________________________________________________________
>> Max used 70 of 100 %Max: 70.00
>> Total 2.89k 1.9/s
>>
>> __ Created Temp ________________________________________________________
>> Disk table 1.34k 0.9/s
>> Table 2.35k 1.6/s Size: 32.0M
>> File 5 0.0/s
>>
>> __ Threads ____________________________________________________________ _
>> Running 32 of 37
>> Cached 0 of 8 %Hit: 93.26
>> Created 195 0.1/s
>> Slow 0 0/s
>>
>> __ Aborted ____________________________________________________________ _
>> Clients 0 0/s
>> Connects 2 0.0/s
>>
>> __ Bytes ____________________________________________________________ ___
>> Sent 100.33M 66.7k/s
>> Received 12.48M 8.3k/s
>>
>> __ InnoDB Buffer Pool __________________________________________________
>> Usage 1.67M of 8.00M %Used: 20.90
>> Read hit 99.70%
>> Pages
>> Free 405 %Total: 79.10
>> Data 107 20.90 %Drty: 0.00
>> Misc 0 0.00
>> Latched 0.00
>> Reads 26.18k 17.4/s
>> From file 78 0.1/s 0.30
>> Ahead Rnd 2 0.0/s
>> Ahead Sql 1 0.0/s
>> Writes 3 0.0/s
>> Flushes 3 0.0/s
>> Wait Free 0 0/s
>>
>> __ InnoDB Lock _________________________________________________________
>> Waits 0 0/s
>> Current 0
>> Time acquiring
>> Total 0 ms
>> Average 0 ms
>> Max 0 ms
>>
>> __ InnoDB Data, Pages, Rows ____________________________________________
>> Data
>> Reads 96 0.1/s
>> Writes 12 0.0/s
>> fsync 11 0.0/s
>> Pending
>> Reads 0
>> Writes 0
>> fsync 0
>>
>> Pages
>> Created 0 0/s
>> Read 107 0.1/s
>> Written 3 0.0/s
>>
>> Rows
>> Deleted 0 0/s
>> Inserted 0 0/s
>> Read 20.98k 13.9/s
>> Updated 0 0/s
>> root@rider:~/tmp#
>>
>> and the mysqltuner.pl report :
>> ------------------------------------------------------------ -
>>
>> root@rider:~/tmp# perl mysqltuner.pl
>>
>> >> MySQLTuner 1.2.0 - Major Hayden
>> >> Bug reports, feature requests, and downloads at
>> http://mysqltuner.com/
>> >> Run with '--help' for additional options and output filtering
>> Please enter your MySQL administrative login: root
>> Please enter your MySQL administrative password:
>>
>> -------- 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: 7G (Tables: 2408)
>> [--] Data in InnoDB tables: 1M (Tables: 37)
>> [!!] Total fragmented tables: 49
>>
>> -------- Security Recommendations
>> -------------------------------------------
>> [OK] All database users have passwords assigned
>>
>> -------- Performance Metrics
>> -------------------------------------------------
>> [--] Up for: 26m 26s (54K q [34.593 qps], 2K conn, TX: 110M, RX: 13M)
>> [--] Reads / Writes: 90% / 10%
>> [--] Total buffers: 2.1G global + 2.6M per thread (100 max threads)
>> [OK] Maximum possible memory usage: 2.3G (19% of installed RAM)
>> [OK] Slow queries: 0% (69/54K)
>> [OK] Highest usage of available connections: 70% (70/100)
>> [OK] Key buffer size / total MyISAM indexes: 2.0G/9.2G
>> [OK] Key buffer hit rate: 99.8% (363M cached / 745K reads)
>> [OK] Query cache efficiency: 76.1% (36K cached / 47K selects)
>> [OK] Query cache prunes per day: 0
>> [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
>> [!!] Joins performed without indexes: 689
>> [!!] Temporary tables created on disk: 36% (1K on disk / 4K total)
>> [OK] Thread cache hit rate: 93% (198 created / 2K connections)
>> [!!] Table cache hit rate: 6% (1K open / 14K opened)
>> [!!] Open file limit used: 89% (1K/2K)
>> [OK] Table locks acquired immediately: 96% (14K immediate / 14K locks)
>> [OK] InnoDB data size / buffer pool: 1.2M/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
>> 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
>> Variables to adjust:
>> join_buffer_size (> 8.0K, or always use indexes with joins)
>> tmp_table_size (> 32M)
>> max_heap_table_size (> 32M)
>> table_cache (> 1024)
>> open_files_limit (> 2158)
>>
>> root@rider:~/tmp#
>>
>>
>>
>> On Thu, Oct 6, 2011 at 10:09 AM, Rik Wasmus wrote:
>> >> thanks for the response - but do not believe queries are the issue
>> >> because - Like I said - i have other websites doing the same exact
>> >> queries as I am doing on the site with the 9gig table.
>> >
>> > Contrary to popular believe, size DOES matter... And having a table
>> large
>> > enough so it doesn't fit in memory could require another approach
>> entirely for
>> > query optimization.
>> >
>> > Another good start would be to examine the output of mysqlreport, it
>> will tel
>> > you a lot.
>> > --
>> > Rik Wasmus
>> >
>> > --
>> > MySQL General Mailing List
>> > For list archives: http://lists.mysql.com/mysql
>> > To unsubscribe: http://lists.mysql.com/mysql?unsub=mjh2000@gmail.com
>> >
>> >
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=eroomydna@gmail.com
>>
>>
>

--000e0cd719966947e704aea3a55e--

Re: 4 minute slow on select count(*) from table - myisam type

i did google search - myisam is faster...i am not really doing any
transaction stuff.
thanks

On Thu, Oct 6, 2011 at 12:16 PM, Andrew Moore wrote:
> Sorry, hit send by accident there! *face palm*
> Just had a quick scan of the report. You've got 2 1GB disks in software r=
aid
> - RAID1 or RAID5? I can also see you're creating a lot of temporary files=
on
> disk. I think in your previous email that your biggest table's index(s) w=
ere
> larger then the keybuffer size. I would suspect that you're disk bound wi=
th
> limited IO performance through 2 disks and effectively 1 if in a mirrored
> configuration. The stats show that you're configured for MyISAM and that
> you're tables are taking reads and writes (read heavy though), MyISAM
> doesn't like high concurrency mixed workloads such as yours, it will caus=
e
> locking and maybe thats why your count has such a delay. Such activity ma=
y
> be better suited to InnoDB engine (you must configure and tune for this, =
not
> JUST change the engine).
> HTH
> Andy
>
>
> On Thu, Oct 6, 2011 at 5:05 PM, Andrew Moore wrote:
>>
>> Joey, does your 'large' table get
>>
>> On Thu, Oct 6, 2011 at 3:22 PM, Joey L wrote:
>>>
>>> here is mysqlreport ---
>>> ------------------------------------------------
>>> root@rider:~/tmp# ./mysqlreport --user root --password barakobomb
>>> Use of uninitialized value $is in multiplication (*) at ./mysqlreport
>>> line 829.
>>> Use of uninitialized value in formline at ./mysqlreport line 1227.
>>> MySQL 5.1.49-3-log =A0 =A0 =A0 uptime 0 0:25:5 =A0 =A0 =A0 =A0Thu Oct =
=A06 10:20:49 2011
>>>
>>> __ Key ____________________________________________________________ ____=
_
>>> Buffer used =A0 727.43M of =A0 2.00G =A0%Used: =A035.52
>>> =A0Current =A0 =A0 963.24M =A0 =A0 =A0 =A0 =A0 =A0%Usage: =A047.03
>>> Write hit =A0 =A0 =A029.41%
>>> Read hit =A0 =A0 =A0 99.79%
>>>
>>> __ Questions __________________________________________________________=
_
>>> Total =A0 =A0 =A0 =A0 =A050.20k =A0 =A033.4/s
>>> =A0QC Hits =A0 =A0 =A032.56k =A0 =A021.6/s =A0%Total: =A064.87
>>> =A0DMS =A0 =A0 =A0 =A0 =A012.28k =A0 =A0 8.2/s =A0 =A0 =A0 =A0 =A0 24.4=
6
>>> =A0Com_ =A0 =A0 =A0 =A0 =A03.21k =A0 =A0 2.1/s =A0 =A0 =A0 =A0 =A0 =A06=
..39
>>> =A0COM_QUIT =A0 =A0 =A02.89k =A0 =A0 1.9/s =A0 =A0 =A0 =A0 =A0 =A05.76
>>> =A0-Unknown =A0 =A0 =A0 =A0745 =A0 =A0 0.5/s =A0 =A0 =A0 =A0 =A0 =A01.4=
8
>>> Slow 10 s =A0 =A0 =A0 =A0 =A068 =A0 =A0 0.0/s =A0 =A0 =A0 =A0 =A0 =A00.=
14 =A0%DMS: =A0 0.55 =A0Log: OFF
>>> DMS =A0 =A0 =A0 =A0 =A0 =A012.28k =A0 =A0 8.2/s =A0 =A0 =A0 =A0 =A0 24.=
46
>>> =A0SELECT =A0 =A0 =A0 11.09k =A0 =A0 7.4/s =A0 =A0 =A0 =A0 =A0 22.10 =
=A0 =A0 =A0 =A0 90.36
>>> =A0UPDATE =A0 =A0 =A0 =A0 =A0539 =A0 =A0 0.4/s =A0 =A0 =A0 =A0 =A0 =A01=
..07 =A0 =A0 =A0 =A0 =A04.39
>>> =A0INSERT =A0 =A0 =A0 =A0 =A0384 =A0 =A0 0.3/s =A0 =A0 =A0 =A0 =A0 =A00=
..77 =A0 =A0 =A0 =A0 =A03.13
>>> =A0DELETE =A0 =A0 =A0 =A0 =A0260 =A0 =A0 0.2/s =A0 =A0 =A0 =A0 =A0 =A00=
..52 =A0 =A0 =A0 =A0 =A02.12
>>> =A0REPLACE =A0 =A0 =A0 =A0 =A0 0 =A0 =A0 =A0 0/s =A0 =A0 =A0 =A0 =A0 =
=A00.00 =A0 =A0 =A0 =A0 =A00.00
>>> Com_ =A0 =A0 =A0 =A0 =A0 =A03.21k =A0 =A0 2.1/s =A0 =A0 =A0 =A0 =A0 =A0=
6.39
>>> =A0set_option =A0 =A01.10k =A0 =A0 0.7/s =A0 =A0 =A0 =A0 =A0 =A02.20
>>> =A0show_fields =A0 1.03k =A0 =A0 0.7/s =A0 =A0 =A0 =A0 =A0 =A02.05
>>> =A0admin_comma =A0 =A0 707 =A0 =A0 0.5/s =A0 =A0 =A0 =A0 =A0 =A01.41
>>>
>>> __ SELECT and Sort ____________________________________________________=
_
>>> Scan =A0 =A0 =A0 =A0 =A0 =A01.65k =A0 =A0 1.1/s %SELECT: =A014.87
>>> Range =A0 =A0 =A0 =A0 =A0 =A0 493 =A0 =A0 0.3/s =A0 =A0 =A0 =A0 =A0 =A0=
4.44
>>> Full join =A0 =A0 =A0 =A0 310 =A0 =A0 0.2/s =A0 =A0 =A0 =A0 =A0 =A02.79
>>> Range check =A0 =A0 =A0 339 =A0 =A0 0.2/s =A0 =A0 =A0 =A0 =A0 =A03.06
>>> Full rng join =A0 =A0 =A0 0 =A0 =A0 =A0 0/s =A0 =A0 =A0 =A0 =A0 =A00.00
>>> Sort scan =A0 =A0 =A0 =A0 887 =A0 =A0 0.6/s
>>> Sort range =A0 =A0 =A0 =A0628 =A0 =A0 0.4/s
>>> Sort mrg pass =A0 =A0 =A0 0 =A0 =A0 =A0 0/s
>>>
>>> __ Query Cache ________________________________________________________=
_
>>> Memory usage =A0 =A05.96M of =A016.00M =A0%Used: =A037.25
>>> Block Fragmnt =A0 5.17%
>>> Hits =A0 =A0 =A0 =A0 =A0 32.56k =A0 =A021.6/s
>>> Inserts =A0 =A0 =A0 =A0 5.66k =A0 =A0 3.8/s
>>> Insrt:Prune =A0 5.66k:1 =A0 =A0 3.8/s
>>> Hit:Insert =A0 =A0 5.76:1
>>>
>>> __ Table Locks ________________________________________________________=
_
>>> Waited =A0 =A0 =A0 =A0 =A0 =A0513 =A0 =A0 0.3/s =A0%Total: =A0 3.62
>>> Immediate =A0 =A0 =A013.65k =A0 =A0 9.1/s
>>>
>>> __ Tables ____________________________________________________________ _=
_
>>> Open =A0 =A0 =A0 =A0 =A0 =A0 1024 of 1024 =A0 =A0%Cache: 100.00
>>> Opened =A0 =A0 =A0 =A0 14.96k =A0 =A0 9.9/s
>>>
>>> __ Connections ________________________________________________________=
_
>>> Max used =A0 =A0 =A0 =A0 =A0 70 of =A0100 =A0 =A0 =A0%Max: =A070.00
>>> Total =A0 =A0 =A0 =A0 =A0 2.89k =A0 =A0 1.9/s
>>>
>>> __ Created Temp _______________________________________________________=
_
>>> Disk table =A0 =A0 =A01.34k =A0 =A0 0.9/s
>>> Table =A0 =A0 =A0 =A0 =A0 2.35k =A0 =A0 1.6/s =A0 =A0Size: =A032.0M
>>> File =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A05 =A0 =A0 0.0/s
>>>
>>> __ Threads ____________________________________________________________ =
_
>>> Running =A0 =A0 =A0 =A0 =A0 =A032 of =A0 37
>>> Cached =A0 =A0 =A0 =A0 =A0 =A0 =A00 of =A0 =A08 =A0 =A0 =A0%Hit: =A093.=
26
>>> Created =A0 =A0 =A0 =A0 =A0 195 =A0 =A0 0.1/s
>>> Slow =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A00 =A0 =A0 =A0 0/s
>>>
>>> __ Aborted ____________________________________________________________ =
_
>>> Clients =A0 =A0 =A0 =A0 =A0 =A0 0 =A0 =A0 =A0 0/s
>>> Connects =A0 =A0 =A0 =A0 =A0 =A02 =A0 =A0 0.0/s
>>>
>>> __ Bytes ____________________________________________________________ __=
_
>>> Sent =A0 =A0 =A0 =A0 =A0100.33M =A0 66.7k/s
>>> Received =A0 =A0 =A0 12.48M =A0 =A08.3k/s
>>>
>>> __ InnoDB Buffer Pool _________________________________________________=
_
>>> Usage =A0 =A0 =A0 =A0 =A0 1.67M of =A0 8.00M =A0%Used: =A020.90
>>> Read hit =A0 =A0 =A0 99.70%
>>> Pages
>>> =A0Free =A0 =A0 =A0 =A0 =A0 =A0405 =A0 =A0 =A0 =A0 =A0 =A0%Total: =A079=
..10
>>> =A0Data =A0 =A0 =A0 =A0 =A0 =A0107 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 20.90 %Drty: =A0 0.00
>>> =A0Misc =A0 =A0 =A0 =A0 =A0 =A0 =A00 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A00.00
>>> =A0Latched =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A00.00
>>> Reads =A0 =A0 =A0 =A0 =A026.18k =A0 =A017.4/s
>>> =A0From file =A0 =A0 =A0 =A078 =A0 =A0 0.1/s =A0 =A0 =A0 =A0 =A0 =A00.3=
0
>>> =A0Ahead Rnd =A0 =A0 =A0 =A0 2 =A0 =A0 0.0/s
>>> =A0Ahead Sql =A0 =A0 =A0 =A0 1 =A0 =A0 0.0/s
>>> Writes =A0 =A0 =A0 =A0 =A0 =A0 =A03 =A0 =A0 0.0/s
>>> Flushes =A0 =A0 =A0 =A0 =A0 =A0 3 =A0 =A0 0.0/s
>>> Wait Free =A0 =A0 =A0 =A0 =A0 0 =A0 =A0 =A0 0/s
>>>
>>> __ InnoDB Lock ________________________________________________________=
_
>>> Waits =A0 =A0 =A0 =A0 =A0 =A0 =A0 0 =A0 =A0 =A0 0/s
>>> Current =A0 =A0 =A0 =A0 =A0 =A0 0
>>> Time acquiring
>>> =A0Total =A0 =A0 =A0 =A0 =A0 =A0 0 ms
>>> =A0Average =A0 =A0 =A0 =A0 =A0 0 ms
>>> =A0Max =A0 =A0 =A0 =A0 =A0 =A0 =A0 0 ms
>>>
>>> __ InnoDB Data, Pages, Rows ___________________________________________=
_
>>> Data
>>> =A0Reads =A0 =A0 =A0 =A0 =A0 =A096 =A0 =A0 0.1/s
>>> =A0Writes =A0 =A0 =A0 =A0 =A0 12 =A0 =A0 0.0/s
>>> =A0fsync =A0 =A0 =A0 =A0 =A0 =A011 =A0 =A0 0.0/s
>>> =A0Pending
>>> =A0 =A0Reads =A0 =A0 =A0 =A0 =A0 0
>>> =A0 =A0Writes =A0 =A0 =A0 =A0 =A00
>>> =A0 =A0fsync =A0 =A0 =A0 =A0 =A0 0
>>>
>>> Pages
>>> =A0Created =A0 =A0 =A0 =A0 =A0 0 =A0 =A0 =A0 0/s
>>> =A0Read =A0 =A0 =A0 =A0 =A0 =A0107 =A0 =A0 0.1/s
>>> =A0Written =A0 =A0 =A0 =A0 =A0 3 =A0 =A0 0.0/s
>>>
>>> Rows
>>> =A0Deleted =A0 =A0 =A0 =A0 =A0 0 =A0 =A0 =A0 0/s
>>> =A0Inserted =A0 =A0 =A0 =A0 =A00 =A0 =A0 =A0 0/s
>>> =A0Read =A0 =A0 =A0 =A0 20.98k =A0 =A013.9/s
>>> =A0Updated =A0 =A0 =A0 =A0 =A0 0 =A0 =A0 =A0 0/s
>>> root@rider:~/tmp#
>>>
>>> and the mysqltuner.pl report :
>>> ------------------------------------------------------------ -
>>>
>>> root@rider:~/tmp# perl mysqltuner.pl
>>>
>>> =A0>> =A0MySQLTuner 1.2.0 - Major Hayden
>>> =A0>> =A0Bug reports, feature requests, and downloads at
>>> http://mysqltuner.com/
>>> =A0>> =A0Run with '--help' for additional options and output filtering
>>> Please enter your MySQL administrative login: root
>>> Please enter your MySQL administrative password:
>>>
>>> -------- 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: 7G (Tables: 2408)
>>> [--] Data in InnoDB tables: 1M (Tables: 37)
>>> [!!] Total fragmented tables: 49
>>>
>>> -------- Security Recommendations
>>> =A0-------------------------------------------
>>> [OK] All database users have passwords assigned
>>>
>>> -------- Performance Metrics
>>> -------------------------------------------------
>>> [--] Up for: 26m 26s (54K q [34.593 qps], 2K conn, TX: 110M, RX: 13M)
>>> [--] Reads / Writes: 90% / 10%
>>> [--] Total buffers: 2.1G global + 2.6M per thread (100 max threads)
>>> [OK] Maximum possible memory usage: 2.3G (19% of installed RAM)
>>> [OK] Slow queries: 0% (69/54K)
>>> [OK] Highest usage of available connections: 70% (70/100)
>>> [OK] Key buffer size / total MyISAM indexes: 2.0G/9.2G
>>> [OK] Key buffer hit rate: 99.8% (363M cached / 745K reads)
>>> [OK] Query cache efficiency: 76.1% (36K cached / 47K selects)
>>> [OK] Query cache prunes per day: 0
>>> [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
>>> [!!] Joins performed without indexes: 689
>>> [!!] Temporary tables created on disk: 36% (1K on disk / 4K total)
>>> [OK] Thread cache hit rate: 93% (198 created / 2K connections)
>>> [!!] Table cache hit rate: 6% (1K open / 14K opened)
>>> [!!] Open file limit used: 89% (1K/2K)
>>> [OK] Table locks acquired immediately: 96% (14K immediate / 14K locks)
>>> [OK] InnoDB data size / buffer pool: 1.2M/8.0M
>>>
>>> -------- Recommendations
>>> -----------------------------------------------------
>>> General recommendations:
>>> =A0 =A0Run OPTIMIZE TABLE to defragment tables for better performance
>>> =A0 =A0MySQL started within last 24 hours - recommendations may be inac=
curate
>>> =A0 =A0Enable the slow query log to troubleshoot bad queries
>>> =A0 =A0Adjust your join queries to always utilize indexes
>>> =A0 =A0When making adjustments, make tmp_table_size/max_heap_table_size=
equal
>>> =A0 =A0Reduce your SELECT DISTINCT queries without LIMIT clauses
>>> =A0 =A0Increase table_cache gradually to avoid file descriptor limits
>>> Variables to adjust:
>>> =A0 =A0join_buffer_size (> 8.0K, or always use indexes with joins)
>>> =A0 =A0tmp_table_size (> 32M)
>>> =A0 =A0max_heap_table_size (> 32M)
>>> =A0 =A0table_cache (> 1024)
>>> =A0 =A0open_files_limit (> 2158)
>>>
>>> root@rider:~/tmp#
>>>
>>>
>>>
>>> On Thu, Oct 6, 2011 at 10:09 AM, Rik Wasmus wrote:
>>> >> thanks for the response - but do not believe queries are the issue
>>> >> because - Like I said - i have other websites doing the same exact
>>> >> queries as I am doing on the site with the 9gig table.
>>> >
>>> > Contrary to popular believe, size DOES matter... And having a table
>>> > large
>>> > enough so it doesn't fit in memory could require another approach
>>> > entirely for
>>> > query optimization.
>>> >
>>> > Another good start would be to examine the output of mysqlreport, it
>>> > will tel
>>> > you a lot.
>>> > --
>>> > Rik Wasmus
>>> >
>>> > --
>>> > MySQL General Mailing List
>>> > For list archives: http://lists.mysql.com/mysql
>>> > To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmjh2000@g=
mail.com
>>> >
>>> >
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Deroomydna@g=
mail.com
>>>
>>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: 4 minute slow on select count(*) from table - myisam type

--0015175dd79c4c7fba04aea4430b
Content-Type: text/plain; charset=ISO-8859-1

I am curious.. Are you the only client on this database or or there other
connections doing work in the background? A busy insert/update heavy
application could cause these effects.

- michael dykman

On Thu, Oct 6, 2011 at 12:35 PM, Joey L wrote:

> i did google search - myisam is faster...i am not really doing any
> transaction stuff.
> thanks
>
> On Thu, Oct 6, 2011 at 12:16 PM, Andrew Moore wrote:
> > Sorry, hit send by accident there! *face palm*
> > Just had a quick scan of the report. You've got 2 1GB disks in software
> raid
> > - RAID1 or RAID5? I can also see you're creating a lot of temporary files
> on
> > disk. I think in your previous email that your biggest table's index(s)
> were
> > larger then the keybuffer size. I would suspect that you're disk bound
> with
> > limited IO performance through 2 disks and effectively 1 if in a mirrored
> > configuration. The stats show that you're configured for MyISAM and that
> > you're tables are taking reads and writes (read heavy though), MyISAM
> > doesn't like high concurrency mixed workloads such as yours, it will
> cause
> > locking and maybe thats why your count has such a delay. Such activity
> may
> > be better suited to InnoDB engine (you must configure and tune for this,
> not
> > JUST change the engine).
> > HTH
> > Andy
> >
> >
> > On Thu, Oct 6, 2011 at 5:05 PM, Andrew Moore
> wrote:
> >>
> >> Joey, does your 'large' table get
> >>
> >> On Thu, Oct 6, 2011 at 3:22 PM, Joey L wrote:
> >>>
> >>> here is mysqlreport ---
> >>> ------------------------------------------------
> >>> root@rider:~/tmp# ./mysqlreport --user root --password barakobomb
> >>> Use of uninitialized value $is in multiplication (*) at ./mysqlreport
> >>> line 829.
> >>> Use of uninitialized value in formline at ./mysqlreport line 1227.
> >>> MySQL 5.1.49-3-log uptime 0 0:25:5 Thu Oct 6 10:20:49
> 2011
> >>>
> >>> __ Key
> ____________________________________________________________ _____
> >>> Buffer used 727.43M of 2.00G %Used: 35.52
> >>> Current 963.24M %Usage: 47.03
> >>> Write hit 29.41%
> >>> Read hit 99.79%
> >>>
> >>> __ Questions
> ___________________________________________________________
> >>> Total 50.20k 33.4/s
> >>> QC Hits 32.56k 21.6/s %Total: 64.87
> >>> DMS 12.28k 8.2/s 24.46
> >>> Com_ 3.21k 2.1/s 6.39
> >>> COM_QUIT 2.89k 1.9/s 5.76
> >>> -Unknown 745 0.5/s 1.48
> >>> Slow 10 s 68 0.0/s 0.14 %DMS: 0.55 Log: OFF
> >>> DMS 12.28k 8.2/s 24.46
> >>> SELECT 11.09k 7.4/s 22.10 90.36
> >>> UPDATE 539 0.4/s 1.07 4.39
> >>> INSERT 384 0.3/s 0.77 3.13
> >>> DELETE 260 0.2/s 0.52 2.12
> >>> REPLACE 0 0/s 0.00 0.00
> >>> Com_ 3.21k 2.1/s 6.39
> >>> set_option 1.10k 0.7/s 2.20
> >>> show_fields 1.03k 0.7/s 2.05
> >>> admin_comma 707 0.5/s 1.41
> >>>
> >>> __ SELECT and Sort
> _____________________________________________________
> >>> Scan 1.65k 1.1/s %SELECT: 14.87
> >>> Range 493 0.3/s 4.44
> >>> Full join 310 0.2/s 2.79
> >>> Range check 339 0.2/s 3.06
> >>> Full rng join 0 0/s 0.00
> >>> Sort scan 887 0.6/s
> >>> Sort range 628 0.4/s
> >>> Sort mrg pass 0 0/s
> >>>
> >>> __ Query Cache
> _________________________________________________________
> >>> Memory usage 5.96M of 16.00M %Used: 37.25
> >>> Block Fragmnt 5.17%
> >>> Hits 32.56k 21.6/s
> >>> Inserts 5.66k 3.8/s
> >>> Insrt:Prune 5.66k:1 3.8/s
> >>> Hit:Insert 5.76:1
> >>>
> >>> __ Table Locks
> _________________________________________________________
> >>> Waited 513 0.3/s %Total: 3.62
> >>> Immediate 13.65k 9.1/s
> >>>
> >>> __ Tables
> ____________________________________________________________ __
> >>> Open 1024 of 1024 %Cache: 100.00
> >>> Opened 14.96k 9.9/s
> >>>
> >>> __ Connections
> _________________________________________________________
> >>> Max used 70 of 100 %Max: 70.00
> >>> Total 2.89k 1.9/s
> >>>
> >>> __ Created Temp
> ________________________________________________________
> >>> Disk table 1.34k 0.9/s
> >>> Table 2.35k 1.6/s Size: 32.0M
> >>> File 5 0.0/s
> >>>
> >>> __ Threads
> ____________________________________________________________ _
> >>> Running 32 of 37
> >>> Cached 0 of 8 %Hit: 93.26
> >>> Created 195 0.1/s
> >>> Slow 0 0/s
> >>>
> >>> __ Aborted
> ____________________________________________________________ _
> >>> Clients 0 0/s
> >>> Connects 2 0.0/s
> >>>
> >>> __ Bytes
> ____________________________________________________________ ___
> >>> Sent 100.33M 66.7k/s
> >>> Received 12.48M 8.3k/s
> >>>
> >>> __ InnoDB Buffer Pool
> __________________________________________________
> >>> Usage 1.67M of 8.00M %Used: 20.90
> >>> Read hit 99.70%
> >>> Pages
> >>> Free 405 %Total: 79.10
> >>> Data 107 20.90 %Drty: 0.00
> >>> Misc 0 0.00
> >>> Latched 0.00
> >>> Reads 26.18k 17.4/s
> >>> From file 78 0.1/s 0.30
> >>> Ahead Rnd 2 0.0/s
> >>> Ahead Sql 1 0.0/s
> >>> Writes 3 0.0/s
> >>> Flushes 3 0.0/s
> >>> Wait Free 0 0/s
> >>>
> >>> __ InnoDB Lock
> _________________________________________________________
> >>> Waits 0 0/s
> >>> Current 0
> >>> Time acquiring
> >>> Total 0 ms
> >>> Average 0 ms
> >>> Max 0 ms
> >>>
> >>> __ InnoDB Data, Pages, Rows
> ____________________________________________
> >>> Data
> >>> Reads 96 0.1/s
> >>> Writes 12 0.0/s
> >>> fsync 11 0.0/s
> >>> Pending
> >>> Reads 0
> >>> Writes 0
> >>> fsync 0
> >>>
> >>> Pages
> >>> Created 0 0/s
> >>> Read 107 0.1/s
> >>> Written 3 0.0/s
> >>>
> >>> Rows
> >>> Deleted 0 0/s
> >>> Inserted 0 0/s
> >>> Read 20.98k 13.9/s
> >>> Updated 0 0/s
> >>> root@rider:~/tmp#
> >>>
> >>> and the mysqltuner.pl report :
> >>> ------------------------------------------------------------ -
> >>>
> >>> root@rider:~/tmp# perl mysqltuner.pl
> >>>
> >>> >> MySQLTuner 1.2.0 - Major Hayden
> >>> >> Bug reports, feature requests, and downloads at
> >>> http://mysqltuner.com/
> >>> >> Run with '--help' for additional options and output filtering
> >>> Please enter your MySQL administrative login: root
> >>> Please enter your MySQL administrative password:
> >>>
> >>> -------- 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: 7G (Tables: 2408)
> >>> [--] Data in InnoDB tables: 1M (Tables: 37)
> >>> [!!] Total fragmented tables: 49
> >>>
> >>> -------- Security Recommendations
> >>> -------------------------------------------
> >>> [OK] All database users have passwords assigned
> >>>
> >>> -------- Performance Metrics
> >>> -------------------------------------------------
> >>> [--] Up for: 26m 26s (54K q [34.593 qps], 2K conn, TX: 110M, RX: 13M)
> >>> [--] Reads / Writes: 90% / 10%
> >>> [--] Total buffers: 2.1G global + 2.6M per thread (100 max threads)
> >>> [OK] Maximum possible memory usage: 2.3G (19% of installed RAM)
> >>> [OK] Slow queries: 0% (69/54K)
> >>> [OK] Highest usage of available connections: 70% (70/100)
> >>> [OK] Key buffer size / total MyISAM indexes: 2.0G/9.2G
> >>> [OK] Key buffer hit rate: 99.8% (363M cached / 745K reads)
> >>> [OK] Query cache efficiency: 76.1% (36K cached / 47K selects)
> >>> [OK] Query cache prunes per day: 0
> >>> [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
> >>> [!!] Joins performed without indexes: 689
> >>> [!!] Temporary tables created on disk: 36% (1K on disk / 4K total)
> >>> [OK] Thread cache hit rate: 93% (198 created / 2K connections)
> >>> [!!] Table cache hit rate: 6% (1K open / 14K opened)
> >>> [!!] Open file limit used: 89% (1K/2K)
> >>> [OK] Table locks acquired immediately: 96% (14K immediate / 14K locks)
> >>> [OK] InnoDB data size / buffer pool: 1.2M/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
> >>> 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
> >>> Variables to adjust:
> >>> join_buffer_size (> 8.0K, or always use indexes with joins)
> >>> tmp_table_size (> 32M)
> >>> max_heap_table_size (> 32M)
> >>> table_cache (> 1024)
> >>> open_files_limit (> 2158)
> >>>
> >>> root@rider:~/tmp#
> >>>
> >>>
> >>>
> >>> On Thu, Oct 6, 2011 at 10:09 AM, Rik Wasmus wrote:
> >>> >> thanks for the response - but do not believe queries are the issue
> >>> >> because - Like I said - i have other websites doing the same exact
> >>> >> queries as I am doing on the site with the 9gig table.
> >>> >
> >>> > Contrary to popular believe, size DOES matter... And having a table
> >>> > large
> >>> > enough so it doesn't fit in memory could require another approach
> >>> > entirely for
> >>> > query optimization.
> >>> >
> >>> > Another good start would be to examine the output of mysqlreport, it
> >>> > will tel
> >>> > you a lot.
> >>> > --
> >>> > Rik Wasmus
> >>> >
> >>> > --
> >>> > MySQL General Mailing List
> >>> > For list archives: http://lists.mysql.com/mysql
> >>> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=mjh2000@gmail.com
> >>> >
> >>> >
> >>>
> >>> --
> >>> MySQL General Mailing List
> >>> For list archives: http://lists.mysql.com/mysql
> >>> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=eroomydna@gmail.com
> >>>
> >>
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mdykman@gmail.com
>
>


--
- michael dykman
- mdykman@gmail.com

May the Source be with you.

--0015175dd79c4c7fba04aea4430b--

Re: 4 minute slow on select count(*) from table - myisam type

--000e0cd51dc435f55e04aea47e3f
Content-Type: text/plain; charset=ISO-8859-1

Precisely my point Singer. There's a workload here that isn't friendly with
table level locking and I would hazard a guess that there's some fights over
IO due to load vs resources. The count is going to be queued as you
describe.

A

On Thu, Oct 6, 2011 at 6:09 PM, Singer X.J. Wang wrote:

> Okay, lets hold on for a minute here and go back. We're side tracking too
> much.
>
> Lets state the facts here:
>
> 1) MyISAM stores the row count internally, a 'select count(*) from table'
> DOES NOT DO A FULL TABLE SCAN
> 2) hell, a software RAID6 of 2 MFM drives could do a seek to the metadata
> faster then 4 minutes..
>
> But lets remember that if another thread is writing or updating the MyISAM
> table, the count(*) must wait..
>
> So I recommend this:
>
> run a select count(*) from the table that you see is long.. if it is taking
> a long time open another session, do a show processlist
>
> I bet you that you will see another process updating or deleting or
> inserting into the MyISAM table.
>
>
> On Thu, Oct 6, 2011 at 12:35, Joey L wrote:
>
>> i did google search - myisam is faster...i am not really doing any
>> transaction stuff.
>> thanks
>>
>> On Thu, Oct 6, 2011 at 12:16 PM, Andrew Moore
>> wrote:
>> > Sorry, hit send by accident there! *face palm*
>> > Just had a quick scan of the report. You've got 2 1GB disks in software
>> raid
>> > - RAID1 or RAID5? I can also see you're creating a lot of temporary
>> files on
>> > disk. I think in your previous email that your biggest table's index(s)
>> were
>> > larger then the keybuffer size. I would suspect that you're disk bound
>> with
>> > limited IO performance through 2 disks and effectively 1 if in a
>> mirrored
>> > configuration. The stats show that you're configured for MyISAM and that
>> > you're tables are taking reads and writes (read heavy though), MyISAM
>> > doesn't like high concurrency mixed workloads such as yours, it will
>> cause
>> > locking and maybe thats why your count has such a delay. Such activity
>> may
>> > be better suited to InnoDB engine (you must configure and tune for this,
>> not
>> > JUST change the engine).
>> > HTH
>> > Andy
>> >
>> >
>> > On Thu, Oct 6, 2011 at 5:05 PM, Andrew Moore
>> wrote:
>> >>
>> >> Joey, does your 'large' table get
>> >>
>> >> On Thu, Oct 6, 2011 at 3:22 PM, Joey L wrote:
>> >>>
>> >>> here is mysqlreport ---
>> >>> ------------------------------------------------
>> >>> root@rider:~/tmp# ./mysqlreport --user root --password barakobomb
>> >>> Use of uninitialized value $is in multiplication (*) at ./mysqlreport
>> >>> line 829.
>> >>> Use of uninitialized value in formline at ./mysqlreport line 1227.
>> >>> MySQL 5.1.49-3-log uptime 0 0:25:5 Thu Oct 6 10:20:49
>> 2011
>> >>>
>> >>> __ Key
>> ____________________________________________________________ _____
>> >>> Buffer used 727.43M of 2.00G %Used: 35.52
>> >>> Current 963.24M %Usage: 47.03
>> >>> Write hit 29.41%
>> >>> Read hit 99.79%
>> >>>
>> >>> __ Questions
>> ___________________________________________________________
>> >>> Total 50.20k 33.4/s
>> >>> QC Hits 32.56k 21.6/s %Total: 64.87
>> >>> DMS 12.28k 8.2/s 24.46
>> >>> Com_ 3.21k 2.1/s 6.39
>> >>> COM_QUIT 2.89k 1.9/s 5.76
>> >>> -Unknown 745 0.5/s 1.48
>> >>> Slow 10 s 68 0.0/s 0.14 %DMS: 0.55 Log:
>> OFF
>> >>> DMS 12.28k 8.2/s 24.46
>> >>> SELECT 11.09k 7.4/s 22.10 90.36
>> >>> UPDATE 539 0.4/s 1.07 4.39
>> >>> INSERT 384 0.3/s 0.77 3.13
>> >>> DELETE 260 0.2/s 0.52 2.12
>> >>> REPLACE 0 0/s 0.00 0.00
>> >>> Com_ 3.21k 2.1/s 6.39
>> >>> set_option 1.10k 0.7/s 2.20
>> >>> show_fields 1.03k 0.7/s 2.05
>> >>> admin_comma 707 0.5/s 1.41
>> >>>
>> >>> __ SELECT and Sort
>> _____________________________________________________
>> >>> Scan 1.65k 1.1/s %SELECT: 14.87
>> >>> Range 493 0.3/s 4.44
>> >>> Full join 310 0.2/s 2.79
>> >>> Range check 339 0.2/s 3.06
>> >>> Full rng join 0 0/s 0.00
>> >>> Sort scan 887 0.6/s
>> >>> Sort range 628 0.4/s
>> >>> Sort mrg pass 0 0/s
>> >>>
>> >>> __ Query Cache
>> _________________________________________________________
>> >>> Memory usage 5.96M of 16.00M %Used: 37.25
>> >>> Block Fragmnt 5.17%
>> >>> Hits 32.56k 21.6/s
>> >>> Inserts 5.66k 3.8/s
>> >>> Insrt:Prune 5.66k:1 3.8/s
>> >>> Hit:Insert 5.76:1
>> >>>
>> >>> __ Table Locks
>> _________________________________________________________
>> >>> Waited 513 0.3/s %Total: 3.62
>> >>> Immediate 13.65k 9.1/s
>> >>>
>> >>> __ Tables
>> ____________________________________________________________ __
>> >>> Open 1024 of 1024 %Cache: 100.00
>> >>> Opened 14.96k 9.9/s
>> >>>
>> >>> __ Connections
>> _________________________________________________________
>> >>> Max used 70 of 100 %Max: 70.00
>> >>> Total 2.89k 1.9/s
>> >>>
>> >>> __ Created Temp
>> ________________________________________________________
>> >>> Disk table 1.34k 0.9/s
>> >>> Table 2.35k 1.6/s Size: 32.0M
>> >>> File 5 0.0/s
>> >>>
>> >>> __ Threads
>> ____________________________________________________________ _
>> >>> Running 32 of 37
>> >>> Cached 0 of 8 %Hit: 93.26
>> >>> Created 195 0.1/s
>> >>> Slow 0 0/s
>> >>>
>> >>> __ Aborted
>> ____________________________________________________________ _
>> >>> Clients 0 0/s
>> >>> Connects 2 0.0/s
>> >>>
>> >>> __ Bytes
>> ____________________________________________________________ ___
>> >>> Sent 100.33M 66.7k/s
>> >>> Received 12.48M 8.3k/s
>> >>>
>> >>> __ InnoDB Buffer Pool
>> __________________________________________________
>> >>> Usage 1.67M of 8.00M %Used: 20.90
>> >>> Read hit 99.70%
>> >>> Pages
>> >>> Free 405 %Total: 79.10
>> >>> Data 107 20.90 %Drty: 0.00
>> >>> Misc 0 0.00
>> >>> Latched 0.00
>> >>> Reads 26.18k 17.4/s
>> >>> From file 78 0.1/s 0.30
>> >>> Ahead Rnd 2 0.0/s
>> >>> Ahead Sql 1 0.0/s
>> >>> Writes 3 0.0/s
>> >>> Flushes 3 0.0/s
>> >>> Wait Free 0 0/s
>> >>>
>> >>> __ InnoDB Lock
>> _________________________________________________________
>> >>> Waits 0 0/s
>> >>> Current 0
>> >>> Time acquiring
>> >>> Total 0 ms
>> >>> Average 0 ms
>> >>> Max 0 ms
>> >>>
>> >>> __ InnoDB Data, Pages, Rows
>> ____________________________________________
>> >>> Data
>> >>> Reads 96 0.1/s
>> >>> Writes 12 0.0/s
>> >>> fsync 11 0.0/s
>> >>> Pending
>> >>> Reads 0
>> >>> Writes 0
>> >>> fsync 0
>> >>>
>> >>> Pages
>> >>> Created 0 0/s
>> >>> Read 107 0.1/s
>> >>> Written 3 0.0/s
>> >>>
>> >>> Rows
>> >>> Deleted 0 0/s
>> >>> Inserted 0 0/s
>> >>> Read 20.98k 13.9/s
>> >>> Updated 0 0/s
>> >>> root@rider:~/tmp#
>> >>>
>> >>> and the mysqltuner.pl report :
>> >>> ------------------------------------------------------------ -
>> >>>
>> >>> root@rider:~/tmp# perl mysqltuner.pl
>> >>>
>> >>> >> MySQLTuner 1.2.0 - Major Hayden
>> >>> >> Bug reports, feature requests, and downloads at
>> >>> http://mysqltuner.com/
>> >>> >> Run with '--help' for additional options and output filtering
>> >>> Please enter your MySQL administrative login: root
>> >>> Please enter your MySQL administrative password:
>> >>>
>> >>> -------- 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: 7G (Tables: 2408)
>> >>> [--] Data in InnoDB tables: 1M (Tables: 37)
>> >>> [!!] Total fragmented tables: 49
>> >>>
>> >>> -------- Security Recommendations
>> >>> -------------------------------------------
>> >>> [OK] All database users have passwords assigned
>> >>>
>> >>> -------- Performance Metrics
>> >>> -------------------------------------------------
>> >>> [--] Up for: 26m 26s (54K q [34.593 qps], 2K conn, TX: 110M, RX: 13M)
>> >>> [--] Reads / Writes: 90% / 10%
>> >>> [--] Total buffers: 2.1G global + 2.6M per thread (100 max threads)
>> >>> [OK] Maximum possible memory usage: 2.3G (19% of installed RAM)
>> >>> [OK] Slow queries: 0% (69/54K)
>> >>> [OK] Highest usage of available connections: 70% (70/100)
>> >>> [OK] Key buffer size / total MyISAM indexes: 2.0G/9.2G
>> >>> [OK] Key buffer hit rate: 99.8% (363M cached / 745K reads)
>> >>> [OK] Query cache efficiency: 76.1% (36K cached / 47K selects)
>> >>> [OK] Query cache prunes per day: 0
>> >>> [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
>> >>> [!!] Joins performed without indexes: 689
>> >>> [!!] Temporary tables created on disk: 36% (1K on disk / 4K total)
>> >>> [OK] Thread cache hit rate: 93% (198 created / 2K connections)
>> >>> [!!] Table cache hit rate: 6% (1K open / 14K opened)
>> >>> [!!] Open file limit used: 89% (1K/2K)
>> >>> [OK] Table locks acquired immediately: 96% (14K immediate / 14K locks)
>> >>> [OK] InnoDB data size / buffer pool: 1.2M/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
>> >>> 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
>> >>> Variables to adjust:
>> >>> join_buffer_size (> 8.0K, or always use indexes with joins)
>> >>> tmp_table_size (> 32M)
>> >>> max_heap_table_size (> 32M)
>> >>> table_cache (> 1024)
>> >>> open_files_limit (> 2158)
>> >>>
>> >>> root@rider:~/tmp#
>> >>>
>> >>>
>> >>>
>> >>> On Thu, Oct 6, 2011 at 10:09 AM, Rik Wasmus wrote:
>> >>> >> thanks for the response - but do not believe queries are the issue
>> >>> >> because - Like I said - i have other websites doing the same exact
>> >>> >> queries as I am doing on the site with the 9gig table.
>> >>> >
>> >>> > Contrary to popular believe, size DOES matter... And having a table
>> >>> > large
>> >>> > enough so it doesn't fit in memory could require another approach
>> >>> > entirely for
>> >>> > query optimization.
>> >>> >
>> >>> > Another good start would be to examine the output of mysqlreport, it
>> >>> > will tel
>> >>> > you a lot.
>> >>> > --
>> >>> > Rik Wasmus
>> >>> >
>> >>> > --
>> >>> > MySQL General Mailing List
>> >>> > For list archives: http://lists.mysql.com/mysql
>> >>> > To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=mjh2000@gmail.com
>> >>> >
>> >>> >
>> >>>
>> >>> --
>> >>> MySQL General Mailing List
>> >>> For list archives: http://lists.mysql.com/mysql
>> >>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=eroomydna@gmail.com
>> >>>
>> >>
>> >
>> >
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=wang@singerwang.com
>>
>>
> --
> Pythian proud winner of Oracle North America Titan Award for Exadata Solution... Read more & see us at OpenWorld bit.ly/pythianoow11
>
>

--000e0cd51dc435f55e04aea47e3f--

Re: 4 minute slow on select count(*) from table - myisam type

oky..you guys are much more advanced then me!
I am glad i am asking for your help...here is show processlist of mysql bel=
ow.
One thing to point out - the locks are happening to the 9gig table
like i thought.
I would like to know what i can do - tuning wise to mysql to help this
locking issue.
It seems when i have the server up for an extended period of time like
3-6 hours - this locking starts to affect other sites/dbs.
I am using a cms and it has a database backend...i have multiple sites
running the same code but the site that has all the traffic is the one
with the biggest table - 9 gig. Can anyone suggest tuning parameters
for this locking issue ??
thanks
ysql> show processlist ;



mysql> show processlist ;
+------+----------+-----------+----------+---------+------+- -------------+-=
------------------------------------------------------------ ---------------=
--------------------------+
| Id | User | Host | db | Command | Time | State
| Info
|
+------+----------+-----------+----------+---------+------+- -------------+-=
------------------------------------------------------------ ---------------=
--------------------------+
| 103 | root | localhost | NULL | Query | 0 | NULL
| show processlist
|
| 2507 | p_092211 | localhost | p_092211 | Query | 5 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_community&Itemi |
| 2508 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| SELECT oldurl from w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_community&Itemid=3D484&lan |
| 2509 | p_092211 | localhost | p_092211 | Query | 5 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_community&Itemi |
| 2521 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_jfbconnect&lang |
| 2522 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_jfbconnect&lang |
| 2523 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_community&Itemi |
| 2529 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_community&Itemi |
| 2535 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_community&Itemi |
| 2536 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_community&Itemi |
| 2537 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| SELECT oldurl from w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_community&Itemid=3D484&lan |
| 2538 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_community&Itemi |
| 2556 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_limos&airportid |
| 2557 | p_092211 | localhost | p_092211 | Query | 5 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_community&Itemi |
| 2558 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_limos&city=3DCasc |
| 2567 | p_092211 | localhost | p_092211 | Query | 11 | Sending
data | select oldurl, newurl, id, dateadd from w6h8a_sh404sef_urls
where newurl <> "" AND soundex(oldurl) |
| 2568 | p_092211 | localhost | p_092211 | Query | 69 | Sending
data | select oldurl, newurl, id, dateadd from w6h8a_sh404sef_urls
where newurl <> "" AND soundex(oldurl) |
| 2569 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| SELECT oldurl from w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_community&Itemid=3D484&lan |
| 2572 | p_092211 | localhost | p_092211 | Query | 5 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_community&Itemi |
| 2576 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| select oldurl, newurl, id, dateadd from w6h8a_sh404sef_urls where
newurl <> "" AND soundex(oldurl) |
| 2577 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| select oldurl, newurl, id, dateadd from w6h8a_sh404sef_urls where
newurl <> "" AND soundex(oldurl) |
| 2578 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| select oldurl, newurl, id, dateadd from w6h8a_sh404sef_urls where
newurl <> "" AND soundex(oldurl) |
| 2579 | p_092211 | localhost | p_092211 | Query | 5 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_community&Itemi |
| 2580 | p_092211 | localhost | p_092211 | Query | 5 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_community&Itemi |
| 2581 | p_092211 | localhost | p_092211 | Query | 5 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_community&Itemi |
| 2582 | p_092211 | localhost | p_092211 | Query | 69 | Locked
| update `w6h8a_sh404sef_urls` set cpt=3D(cpt+1) where `oldurl` =3D
'Camargo-Illinois-Holiday_Light_Tour-H |
| 2583 | p_092211 | localhost | p_092211 | Query | 5 | Locked
| select oldurl, newurl, id, dateadd from w6h8a_sh404sef_urls where
newurl <> "" AND ( oldurl like ' |
| 2584 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_limos&city=3DMilt |
| 2585 | p_092211 | localhost | p_092211 | Query | 5 | Locked
| select oldurl, newurl, id, dateadd from w6h8a_sh404sef_urls where
newurl <> "" AND ( oldurl like ' |
| 2586 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| select oldurl, newurl, id, dateadd from w6h8a_sh404sef_urls where
newurl <> "" AND soundex(oldurl) |
| 2587 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| select oldurl, newurl, id, dateadd from w6h8a_sh404sef_urls where
newurl <> "" AND soundex(oldurl) |
| 2588 | p_092211 | localhost | p_092211 | Query | 5 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_community&Itemi |
| 2591 | p_092211 | localhost | p_092211 | Query | 5 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_community&Itemi |
| 2592 | p_092211 | localhost | p_092211 | Query | 5 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_community&Itemi |
| 2593 | p_092211 | localhost | p_092211 | Query | 5 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_community&Itemi |
| 2594 | p_092211 | localhost | p_092211 | Query | 4 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_community&Itemi |
| 2595 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_limos&city=3DMilt |
| 2596 | p_092211 | localhost | p_092211 | Query | 5 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_community&Itemi |
| 2598 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_limos&city=3DBoyn |
| 2599 | p_092211 | localhost | p_092211 | Query | 5 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_community&Itemi |
| 2600 | p_092211 | localhost | p_092211 | Query | 6 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_jfbconnect&lang |
| 2601 | p_092211 | localhost | p_092211 | Query | 5 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_limos&city=3DOxfo |
| 2602 | p_092211 | localhost | p_092211 | Query | 5 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_limos&city=3DMilt |
| 2603 | p_092211 | localhost | p_092211 | Query | 5 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_limos&city=3DShar |
| 2604 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| SELECT `oldurl`, `newurl`, `dateadd` FROM w6h8a_sh404sef_urls
WHERE `oldurl`=3D'O52/index.php/' ORDER |
| 2605 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| SELECT `oldurl`, `newurl`, `dateadd` FROM w6h8a_sh404sef_urls
WHERE `oldurl`=3D'MLS/index.php/' ORDER |
| 2606 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| SELECT * FROM w6h8a_sh404sef_urls WHERE `oldurl`=3D'MLD/index.php'
|
| 2607 | p_092211 | localhost | p_092211 | Query | 5 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_limos&city=3DMilt |
| 2608 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| SELECT oldurl from w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_limos&airportid=3D5892&lan |
| 2609 | p_092211 | localhost | p_092211 | Query | 6 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_limos&city=3DShar |
| 2610 | p_092211 | localhost | p_092211 | Query | 5 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_limos&city=3DFish |
| 2611 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| SELECT oldurl from w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_limos&airportid=3D7705&lan |
| 2612 | p_092211 | localhost | p_092211 | Query | 5 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_limos&city=3DFish |
| 2613 | p_092211 | localhost | p_092211 | Query | 6 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_limos&city=3DMilt |
| 2614 | p_092211 | localhost | p_092211 | Query | 6 | Locked
| SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl =3D
'index.php?option=3Dcom_limos&city=3DArmo |
| 2615 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| SELECT `oldurl`, `newurl`, `dateadd` FROM w6h8a_sh404sef_urls
WHERE `oldurl`=3D'MLF/index.php/' ORDER |
| 2616 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| SELECT * FROM w6h8a_sh404sef_urls WHERE `oldurl`=3D'MLF/index.php'
|
| 2617 | p_092211 | localhost | p_092211 | Query | 7 | Locked
| INSERT INTO `w6h8a_sh404sef_urls` (`cpt`, `rank`, `oldurl`,
`newurl`, `dateadd`) VALUES (1, 0, '2O1/ |
+------+----------+-----------+----------+---------+------+- -------------+-=
------------------------------------------------------------ ---------------=
--------------------------+
58 rows in set (0.00 sec)




On Thu, Oct 6, 2011 at 1:17 PM, Andrew Moore wrote:
> Precisely my point Singer. There's a workload here that isn't friendly wi=
th
> table level locking and I would hazard a guess that there's some fights o=
ver
> IO due to load vs resources. The count is going to be queued as you
> describe.
>
> A
>
> On Thu, Oct 6, 2011 at 6:09 PM, Singer X.J. Wang
> wrote:
>>
>> Okay, lets hold on for a minute here and go back. We're side tracking to=
o
>> much.
>>
>> Lets state the facts here:
>>
>> 1) MyISAM stores the row count internally, a 'select count(*) from table=
'
>> DOES NOT DO A FULL TABLE SCAN
>> 2) hell, a software RAID6 of 2 MFM drives could do a seek to the metadat=
a
>> faster then 4 minutes..
>>
>> But lets remember that if another thread is writing or updating the MyIS=
AM
>> table, the count(*) must wait..
>>
>> So I recommend this:
>>
>> run a select count(*) from the table that you see is long.. if it is
>> taking a long time open another session, do a show processlist
>>
>> I bet you that you will see another process updating or deleting or
>> inserting into the MyISAM table.
>>
>>
>> On Thu, Oct 6, 2011 at 12:35, Joey L wrote:
>>>
>>> i did google search - myisam is faster...i am not really doing any
>>> transaction stuff.
>>> thanks
>>>
>>> On Thu, Oct 6, 2011 at 12:16 PM, Andrew Moore
>>> wrote:
>>> > Sorry, hit send by accident there! *face palm*
>>> > Just had a quick scan of the report. You've got 2 1GB disks in softwa=
re
>>> > raid
>>> > - RAID1 or RAID5? I can also see you're creating a lot of temporary
>>> > files on
>>> > disk. I think in your previous email that your biggest table's index(=
s)
>>> > were
>>> > larger then the keybuffer size. I would suspect that you're disk boun=
d
>>> > with
>>> > limited IO performance through 2 disks and effectively 1 if in a
>>> > mirrored
>>> > configuration. The stats show that you're configured for MyISAM and
>>> > that
>>> > you're tables are taking reads and writes (read heavy though), MyISAM
>>> > doesn't like high concurrency mixed workloads such as yours, it will
>>> > cause
>>> > locking and maybe thats why your count has such a delay. Such activit=
y
>>> > may
>>> > be better suited to InnoDB engine (you must configure and tune for
>>> > this, not
>>> > JUST change the engine).
>>> > HTH
>>> > Andy
>>> >
>>> >
>>> > On Thu, Oct 6, 2011 at 5:05 PM, Andrew Moore
>>> > wrote:
>>> >>
>>> >> Joey, does your 'large' table get
>>> >>
>>> >> On Thu, Oct 6, 2011 at 3:22 PM, Joey L wrote:
>>> >>>
>>> >>> here is mysqlreport ---
>>> >>> ------------------------------------------------
>>> >>> root@rider:~/tmp# ./mysqlreport --user root --password barakobomb
>>> >>> Use of uninitialized value $is in multiplication (*) at ./mysqlrepo=
rt
>>> >>> line 829.
>>> >>> Use of uninitialized value in formline at ./mysqlreport line 1227.
>>> >>> MySQL 5.1.49-3-log =A0 =A0 =A0 uptime 0 0:25:5 =A0 =A0 =A0 =A0Thu O=
ct =A06 10:20:49
>>> >>> 2011
>>> >>>
>>> >>> __ Key
>>> >>> ____________________________________________________________ _____
>>> >>> Buffer used =A0 727.43M of =A0 2.00G =A0%Used: =A035.52
>>> >>> =A0Current =A0 =A0 963.24M =A0 =A0 =A0 =A0 =A0 =A0%Usage: =A047.03
>>> >>> Write hit =A0 =A0 =A029.41%
>>> >>> Read hit =A0 =A0 =A0 99.79%
>>> >>>
>>> >>> __ Questions
>>> >>> ___________________________________________________________
>>> >>> Total =A0 =A0 =A0 =A0 =A050.20k =A0 =A033.4/s
>>> >>> =A0QC Hits =A0 =A0 =A032.56k =A0 =A021.6/s =A0%Total: =A064.87
>>> >>> =A0DMS =A0 =A0 =A0 =A0 =A012.28k =A0 =A0 8.2/s =A0 =A0 =A0 =A0 =A0 =
24.46
>>> >>> =A0Com_ =A0 =A0 =A0 =A0 =A03.21k =A0 =A0 2.1/s =A0 =A0 =A0 =A0 =A0 =
=A06.39
>>> >>> =A0COM_QUIT =A0 =A0 =A02.89k =A0 =A0 1.9/s =A0 =A0 =A0 =A0 =A0 =A05=
..76
>>> >>> =A0-Unknown =A0 =A0 =A0 =A0745 =A0 =A0 0.5/s =A0 =A0 =A0 =A0 =A0 =
=A01.48
>>> >>> Slow 10 s =A0 =A0 =A0 =A0 =A068 =A0 =A0 0.0/s =A0 =A0 =A0 =A0 =A0 =
=A00.14 =A0%DMS: =A0 0.55 =A0Log:
>>> >>> OFF
>>> >>> DMS =A0 =A0 =A0 =A0 =A0 =A012.28k =A0 =A0 8.2/s =A0 =A0 =A0 =A0 =A0=
24.46
>>> >>> =A0SELECT =A0 =A0 =A0 11.09k =A0 =A0 7.4/s =A0 =A0 =A0 =A0 =A0 22.1=
0 =A0 =A0 =A0 =A0 90.36
>>> >>> =A0UPDATE =A0 =A0 =A0 =A0 =A0539 =A0 =A0 0.4/s =A0 =A0 =A0 =A0 =A0 =
=A01.07 =A0 =A0 =A0 =A0 =A04.39
>>> >>> =A0INSERT =A0 =A0 =A0 =A0 =A0384 =A0 =A0 0.3/s =A0 =A0 =A0 =A0 =A0 =
=A00.77 =A0 =A0 =A0 =A0 =A03.13
>>> >>> =A0DELETE =A0 =A0 =A0 =A0 =A0260 =A0 =A0 0.2/s =A0 =A0 =A0 =A0 =A0 =
=A00.52 =A0 =A0 =A0 =A0 =A02.12
>>> >>> =A0REPLACE =A0 =A0 =A0 =A0 =A0 0 =A0 =A0 =A0 0/s =A0 =A0 =A0 =A0 =
=A0 =A00.00 =A0 =A0 =A0 =A0 =A00.00
>>> >>> Com_ =A0 =A0 =A0 =A0 =A0 =A03.21k =A0 =A0 2.1/s =A0 =A0 =A0 =A0 =A0=
=A06.39
>>> >>> =A0set_option =A0 =A01.10k =A0 =A0 0.7/s =A0 =A0 =A0 =A0 =A0 =A02.2=
0
>>> >>> =A0show_fields =A0 1.03k =A0 =A0 0.7/s =A0 =A0 =A0 =A0 =A0 =A02.05
>>> >>> =A0admin_comma =A0 =A0 707 =A0 =A0 0.5/s =A0 =A0 =A0 =A0 =A0 =A01.4=
1
>>> >>>
>>> >>> __ SELECT and Sort
>>> >>> _____________________________________________________
>>> >>> Scan =A0 =A0 =A0 =A0 =A0 =A01.65k =A0 =A0 1.1/s %SELECT: =A014.87
>>> >>> Range =A0 =A0 =A0 =A0 =A0 =A0 493 =A0 =A0 0.3/s =A0 =A0 =A0 =A0 =A0=
=A04.44
>>> >>> Full join =A0 =A0 =A0 =A0 310 =A0 =A0 0.2/s =A0 =A0 =A0 =A0 =A0 =A0=
2.79
>>> >>> Range check =A0 =A0 =A0 339 =A0 =A0 0.2/s =A0 =A0 =A0 =A0 =A0 =A03.=
06
>>> >>> Full rng join =A0 =A0 =A0 0 =A0 =A0 =A0 0/s =A0 =A0 =A0 =A0 =A0 =A0=
0.00
>>> >>> Sort scan =A0 =A0 =A0 =A0 887 =A0 =A0 0.6/s
>>> >>> Sort range =A0 =A0 =A0 =A0628 =A0 =A0 0.4/s
>>> >>> Sort mrg pass =A0 =A0 =A0 0 =A0 =A0 =A0 0/s
>>> >>>
>>> >>> __ Query Cache
>>> >>> _________________________________________________________
>>> >>> Memory usage =A0 =A05.96M of =A016.00M =A0%Used: =A037.25
>>> >>> Block Fragmnt =A0 5.17%
>>> >>> Hits =A0 =A0 =A0 =A0 =A0 32.56k =A0 =A021.6/s
>>> >>> Inserts =A0 =A0 =A0 =A0 5.66k =A0 =A0 3.8/s
>>> >>> Insrt:Prune =A0 5.66k:1 =A0 =A0 3.8/s
>>> >>> Hit:Insert =A0 =A0 5.76:1
>>> >>>
>>> >>> __ Table Locks
>>> >>> _________________________________________________________
>>> >>> Waited =A0 =A0 =A0 =A0 =A0 =A0513 =A0 =A0 0.3/s =A0%Total: =A0 3.62
>>> >>> Immediate =A0 =A0 =A013.65k =A0 =A0 9.1/s
>>> >>>
>>> >>> __ Tables
>>> >>> ____________________________________________________________ __
>>> >>> Open =A0 =A0 =A0 =A0 =A0 =A0 1024 of 1024 =A0 =A0%Cache: 100.00
>>> >>> Opened =A0 =A0 =A0 =A0 14.96k =A0 =A0 9.9/s
>>> >>>
>>> >>> __ Connections
>>> >>> _________________________________________________________
>>> >>> Max used =A0 =A0 =A0 =A0 =A0 70 of =A0100 =A0 =A0 =A0%Max: =A070.00
>>> >>> Total =A0 =A0 =A0 =A0 =A0 2.89k =A0 =A0 1.9/s
>>> >>>
>>> >>> __ Created Temp
>>> >>> ________________________________________________________
>>> >>> Disk table =A0 =A0 =A01.34k =A0 =A0 0.9/s
>>> >>> Table =A0 =A0 =A0 =A0 =A0 2.35k =A0 =A0 1.6/s =A0 =A0Size: =A032.0M
>>> >>> File =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A05 =A0 =A0 0.0/s
>>> >>>
>>> >>> __ Threads
>>> >>> ____________________________________________________________ _
>>> >>> Running =A0 =A0 =A0 =A0 =A0 =A032 of =A0 37
>>> >>> Cached =A0 =A0 =A0 =A0 =A0 =A0 =A00 of =A0 =A08 =A0 =A0 =A0%Hit: =
=A093.26
>>> >>> Created =A0 =A0 =A0 =A0 =A0 195 =A0 =A0 0.1/s
>>> >>> Slow =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A00 =A0 =A0 =A0 0/s
>>> >>>
>>> >>> __ Aborted
>>> >>> ____________________________________________________________ _
>>> >>> Clients =A0 =A0 =A0 =A0 =A0 =A0 0 =A0 =A0 =A0 0/s
>>> >>> Connects =A0 =A0 =A0 =A0 =A0 =A02 =A0 =A0 0.0/s
>>> >>>
>>> >>> __ Bytes
>>> >>> ____________________________________________________________ ___
>>> >>> Sent =A0 =A0 =A0 =A0 =A0100.33M =A0 66.7k/s
>>> >>> Received =A0 =A0 =A0 12.48M =A0 =A08.3k/s
>>> >>>
>>> >>> __ InnoDB Buffer Pool
>>> >>> __________________________________________________
>>> >>> Usage =A0 =A0 =A0 =A0 =A0 1.67M of =A0 8.00M =A0%Used: =A020.90
>>> >>> Read hit =A0 =A0 =A0 99.70%
>>> >>> Pages
>>> >>> =A0Free =A0 =A0 =A0 =A0 =A0 =A0405 =A0 =A0 =A0 =A0 =A0 =A0%Total: =
=A079.10
>>> >>> =A0Data =A0 =A0 =A0 =A0 =A0 =A0107 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 20.90 %Drty: =A0 0.00
>>> >>> =A0Misc =A0 =A0 =A0 =A0 =A0 =A0 =A00 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0 =A00.00
>>> >>> =A0Latched =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A00.00
>>> >>> Reads =A0 =A0 =A0 =A0 =A026.18k =A0 =A017.4/s
>>> >>> =A0From file =A0 =A0 =A0 =A078 =A0 =A0 0.1/s =A0 =A0 =A0 =A0 =A0 =
=A00.30
>>> >>> =A0Ahead Rnd =A0 =A0 =A0 =A0 2 =A0 =A0 0.0/s
>>> >>> =A0Ahead Sql =A0 =A0 =A0 =A0 1 =A0 =A0 0.0/s
>>> >>> Writes =A0 =A0 =A0 =A0 =A0 =A0 =A03 =A0 =A0 0.0/s
>>> >>> Flushes =A0 =A0 =A0 =A0 =A0 =A0 3 =A0 =A0 0.0/s
>>> >>> Wait Free =A0 =A0 =A0 =A0 =A0 0 =A0 =A0 =A0 0/s
>>> >>>
>>> >>> __ InnoDB Lock
>>> >>> _________________________________________________________
>>> >>> Waits =A0 =A0 =A0 =A0 =A0 =A0 =A0 0 =A0 =A0 =A0 0/s
>>> >>> Current =A0 =A0 =A0 =A0 =A0 =A0 0
>>> >>> Time acquiring
>>> >>> =A0Total =A0 =A0 =A0 =A0 =A0 =A0 0 ms
>>> >>> =A0Average =A0 =A0 =A0 =A0 =A0 0 ms
>>> >>> =A0Max =A0 =A0 =A0 =A0 =A0 =A0 =A0 0 ms
>>> >>>
>>> >>> __ InnoDB Data, Pages, Rows
>>> >>> ____________________________________________
>>> >>> Data
>>> >>> =A0Reads =A0 =A0 =A0 =A0 =A0 =A096 =A0 =A0 0.1/s
>>> >>> =A0Writes =A0 =A0 =A0 =A0 =A0 12 =A0 =A0 0.0/s
>>> >>> =A0fsync =A0 =A0 =A0 =A0 =A0 =A011 =A0 =A0 0.0/s
>>> >>> =A0Pending
>>> >>> =A0 =A0Reads =A0 =A0 =A0 =A0 =A0 0
>>> >>> =A0 =A0Writes =A0 =A0 =A0 =A0 =A00
>>> >>> =A0 =A0fsync =A0 =A0 =A0 =A0 =A0 0
>>> >>>
>>> >>> Pages
>>> >>> =A0Created =A0 =A0 =A0 =A0 =A0 0 =A0 =A0 =A0 0/s
>>> >>> =A0Read =A0 =A0 =A0 =A0 =A0 =A0107 =A0 =A0 0.1/s
>>> >>> =A0Written =A0 =A0 =A0 =A0 =A0 3 =A0 =A0 0.0/s
>>> >>>
>>> >>> Rows
>>> >>> =A0Deleted =A0 =A0 =A0 =A0 =A0 0 =A0 =A0 =A0 0/s
>>> >>> =A0Inserted =A0 =A0 =A0 =A0 =A00 =A0 =A0 =A0 0/s
>>> >>> =A0Read =A0 =A0 =A0 =A0 20.98k =A0 =A013.9/s
>>> >>> =A0Updated =A0 =A0 =A0 =A0 =A0 0 =A0 =A0 =A0 0/s
>>> >>> root@rider:~/tmp#
>>> >>>
>>> >>> and the mysqltuner.pl report :
>>> >>> ------------------------------------------------------------ -
>>> >>>
>>> >>> root@rider:~/tmp# perl mysqltuner.pl
>>> >>>
>>> >>> =A0>> =A0MySQLTuner 1.2.0 - Major Hayden
>>> >>> =A0>> =A0Bug reports, feature requests, and downloads at
>>> >>> http://mysqltuner.com/
>>> >>> =A0>> =A0Run with '--help' for additional options and output filter=
ing
>>> >>> Please enter your MySQL administrative login: root
>>> >>> Please enter your MySQL administrative password:
>>> >>>
>>> >>> -------- 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: 7G (Tables: 2408)
>>> >>> [--] Data in InnoDB tables: 1M (Tables: 37)
>>> >>> [!!] Total fragmented tables: 49
>>> >>>
>>> >>> -------- Security Recommendations
>>> >>> =A0-------------------------------------------
>>> >>> [OK] All database users have passwords assigned
>>> >>>
>>> >>> -------- Performance Metrics
>>> >>> -------------------------------------------------
>>> >>> [--] Up for: 26m 26s (54K q [34.593 qps], 2K conn, TX: 110M, RX: 13=
M)
>>> >>> [--] Reads / Writes: 90% / 10%
>>> >>> [--] Total buffers: 2.1G global + 2.6M per thread (100 max threads)
>>> >>> [OK] Maximum possible memory usage: 2.3G (19% of installed RAM)
>>> >>> [OK] Slow queries: 0% (69/54K)
>>> >>> [OK] Highest usage of available connections: 70% (70/100)
>>> >>> [OK] Key buffer size / total MyISAM indexes: 2.0G/9.2G
>>> >>> [OK] Key buffer hit rate: 99.8% (363M cached / 745K reads)
>>> >>> [OK] Query cache efficiency: 76.1% (36K cached / 47K selects)
>>> >>> [OK] Query cache prunes per day: 0
>>> >>> [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
>>> >>> [!!] Joins performed without indexes: 689
>>> >>> [!!] Temporary tables created on disk: 36% (1K on disk / 4K total)
>>> >>> [OK] Thread cache hit rate: 93% (198 created / 2K connections)
>>> >>> [!!] Table cache hit rate: 6% (1K open / 14K opened)
>>> >>> [!!] Open file limit used: 89% (1K/2K)
>>> >>> [OK] Table locks acquired immediately: 96% (14K immediate / 14K
>>> >>> locks)
>>> >>> [OK] InnoDB data size / buffer pool: 1.2M/8.0M
>>> >>>
>>> >>> -------- Recommendations
>>> >>> -----------------------------------------------------
>>> >>> General recommendations:
>>> >>> =A0 =A0Run OPTIMIZE TABLE to defragment tables for better performan=
ce
>>> >>> =A0 =A0MySQL started within last 24 hours - recommendations may be
>>> >>> inaccurate
>>> >>> =A0 =A0Enable the slow query log to troubleshoot bad queries
>>> >>> =A0 =A0Adjust your join queries to always utilize indexes
>>> >>> =A0 =A0When making adjustments, make tmp_table_size/max_heap_table_=
size
>>> >>> equal
>>> >>> =A0 =A0Reduce your SELECT DISTINCT queries without LIMIT clauses
>>> >>> =A0 =A0Increase table_cache gradually to avoid file descriptor limi=
ts
>>> >>> Variables to adjust:
>>> >>> =A0 =A0join_buffer_size (> 8.0K, or always use indexes with joins)
>>> >>> =A0 =A0tmp_table_size (> 32M)
>>> >>> =A0 =A0max_heap_table_size (> 32M)
>>> >>> =A0 =A0table_cache (> 1024)
>>> >>> =A0 =A0open_files_limit (> 2158)
>>> >>>
>>> >>> root@rider:~/tmp#
>>> >>>
>>> >>>
>>> >>>
>>> >>> On Thu, Oct 6, 2011 at 10:09 AM, Rik Wasmus wrote:
>>> >>> >> thanks for the response - but do not believe queries are the iss=
ue
>>> >>> >> because - Like I said - i have other websites doing the same exa=
ct
>>> >>> >> queries as I am doing on the site with the 9gig table.
>>> >>> >
>>> >>> > Contrary to popular believe, size DOES matter... And having a tab=
le
>>> >>> > large
>>> >>> > enough so it doesn't fit in memory could require another approach
>>> >>> > entirely for
>>> >>> > query optimization.
>>> >>> >
>>> >>> > Another good start would be to examine the output of mysqlreport,
>>> >>> > it
>>> >>> > will tel
>>> >>> > you a lot.
>>> >>> > --
>>> >>> > Rik Wasmus
>>> >>> >
>>> >>> > --
>>> >>> > MySQL General Mailing List
>>> >>> > For list archives: http://lists.mysql.com/mysql
>>> >>> > To unsubscribe:
>>> >>> > =A0http://lists.mysql.com/mysql?unsub=3Dmjh2000@gmail.com
>>> >>> >
>>> >>> >
>>> >>>
>>> >>> --
>>> >>> MySQL General Mailing List
>>> >>> For list archives: http://lists.mysql.com/mysql
>>> >>> To unsubscribe:
>>> >>> =A0http://lists.mysql.com/mysql?unsub=3Deroomydna@gmail.com
>>> >>>
>>> >>
>>> >
>>> >
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dwang@singer=
wang.com
>>>
>>
>> --
>> Pythian proud winner of Oracle North America Titan Award for Exadata
>> Solution... Read more & see us at OpenWorld bit.ly/pythianoow11
>>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: 4 minute slow on select count(*) from table - myisam type

--0016e6ddff46bd868804aea7b56e
Content-Type: text/plain; charset=ISO-8859-1

I've sent this email a few times now, mysql list kept rejecting it due to
size, sorry for any duplicates....


I think you need to examine this query in particular:

| 2567 | p_092211 | localhost | p_092211 | Query | 11 | Sending
data | select oldurl, newurl, id, dateadd from w6h8a_sh404sef_urls
where newurl <> "" AND soundex(oldurl) |
| 2568 | p_092211 | localhost | p_092211 | Query | 69 | Sending
data | select oldurl, newurl, id, dateadd from w6h8a_sh404sef_urls
where newurl <> "" AND soundex(oldurl)
| 2582 | p_092211 | localhost | p_092211 | Query | 69 | Locked
| update `w6h8a_sh404sef_urls` set cpt=(cpt+1) where `oldurl` =
'Camargo-Illinois-Holiday_Light_Tour-H |

One of those has been running for over a minute. Doing a show full
processlist will give the entire query, you could then paste it into your
SQL editor prefixed with explain and see what the heck is taking so long.
Pretty sure it has to do with using a function on on oldurl in the where
clause -- can't use an index when you do this; therefore, the entire table
has to be scanned. Also, since this table doesn't fit into memory, its disk
bound. If you have the ability to modify the table structure and the
software, a column could be added to the table that is the result of
SOUNDEX(oldurl) and then an index added to that column. The where clause
could then use soundex_column=whatever instead and utilize the index.

I haven't used MYISAM in a long time, so i'm not sure about this but.. is
the INSERT locked due to the SELECT queries that have been running for so
long? And are the rest of the selects (with <8s running time) locked by the
INSERT?

-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--0016e6ddff46bd868804aea7b56e--

Re: 4 minute slow on select count(*) from table - myisam type

> From: Joey L
>=20
> i did google search - myisam is faster...i am not really doing any
> transaction stuff.

That's true for read-only. But if you have a mix of reads and writes, =
MYISAM locks tables during writes, which could be blocking reads.

----------------
In a museum in Havana, there are two skulls of Christopher Columbus; one =
when he was a boy and one when he was a man. -- Mark Twain
:::: Jan Steinman, EcoReality Co-op ::::


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: 4 minute slow on select count(*) from table - myisam type

Guys - I wanted to thank you all very much for your help!!!!
I found the offending code on the website !!!!!
thank you very very very much...
what did it for me was a combination of show processlist and show full
processlist.

I saw the full queries and the main thing was that it was doing a
query about 20 miles long.

thanks again!!!!
mjh

On Thu, Oct 6, 2011 at 7:48 PM, Jan Steinman wrote:
>> From: Joey L
>>
>> i did google search - myisam is faster...i am not really doing any
>> transaction stuff.
>
> That's true for read-only. But if you have a mix of reads and writes, MYI=
SAM locks tables during writes, which could be blocking reads.
>
> ----------------
> In a museum in Havana, there are two skulls of Christopher Columbus; one =
when he was a boy and one when he was a man. -- Mark Twain
> :::: Jan Steinman, EcoReality Co-op ::::
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmjh2000@gmail=
..com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: 4 minute slow on select count(*) from table - myisam type

--000e0cd51dc457401504aeaa77d6
Content-Type: text/plain; charset=ISO-8859-1

Glad you got to the bottom of it Joey.
On 7 Oct 2011 01:23, "Joey L" wrote:
> Guys - I wanted to thank you all very much for your help!!!!
> I found the offending code on the website !!!!!
> thank you very very very much...
> what did it for me was a combination of show processlist and show full
> processlist.
>
> I saw the full queries and the main thing was that it was doing a
> query about 20 miles long.
>
> thanks again!!!!
> mjh
>
> On Thu, Oct 6, 2011 at 7:48 PM, Jan Steinman wrote:
>>> From: Joey L
>>>
>>> i did google search - myisam is faster...i am not really doing any
>>> transaction stuff.
>>
>> That's true for read-only. But if you have a mix of reads and writes,
MYISAM locks tables during writes, which could be blocking reads.
>>
>> ----------------
>> In a museum in Havana, there are two skulls of Christopher Columbus; one
when he was a boy and one when he was a man. -- Mark Twain
>> :::: Jan Steinman, EcoReality Co-op ::::
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=mjh2000@gmail.com
>>
>>

--000e0cd51dc457401504aeaa77d6--

Re: 4 minute slow on select count(*) from table - myisam type

Good to see the issue has been solved. What I noticed in the mysqltuner output, is that you may want to enlarge your table_cache and open files limit before you run into problems there.

----- Original Message -----
> From: "Johnny Withers"
>
> I haven't used MYISAM in a long time, so i'm not sure about this
> but.. is the INSERT locked due to the SELECT queries that have been running
> for so long? And are the rest of the selects (with <8s running time) locked
> by the INSERT?

Yes, because MyISAM doesn't have a mechanism for keeping multiple concurrent consistent views on a table, SELECT also locks the table for writes. There is one important and occasionally useful exception, though: if there are no holes in the table (ie, if you haven't done any deletes) the engine will allow inserts to append to the table while selects are happening.


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org