RE: How MyISAM handle auto_increment
On Wed, 5 Oct 2011, Jerry Schwartz wrote: Can't you use CREATE TABLE LIKE and then reset the auto-increment value? Thanks. Since when does create table like exist? I was unaware of it, but I see it exists in mysql 5.1. The tricks I described worked since 3.x or thereabouts. -- Lucio Chiappetti - INAF/IASF - via Bassini 15 - I-20133 Milano (Italy) Citizens entrusted of public functions have the duty to accomplish them with discipline and honour [Art. 54 Constitution of the Italian Republic] For more info : http://www.iasf-milano.inaf.it/~lucio/personal.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
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.html # 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= 3306 socket = /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 = /var/run/mysqld/mysqld.sock nice= 0 [mysqld] # # * Basic Settings # user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /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= 127.0.0.1 # # * Fine Tuning # key_buffer = 2G key_buffer_size = 2G max_allowed_packet = 16M thread_stack= 192K thread_cache_size = 8 join_buffer_size= 128 # 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 max_heap_table_size = 32M tmp_table_size = 32M thread_concurrency = 10 # # * Query Cache Configuration # query_cache_limit = 2M query_cache_size= 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= /var/log/mysql/mysql.log general_log = 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 = /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 [mysqldump] quick quote-names max_allowed_packet = 64M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] key_buffer = 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 eric.ber...@gmail.com 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 mjh2...@gmail.com 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és Tello mr.crip...@gmail.com wrote: have you tried select count(yourindex) instead of select count(*) ? On Mon, Oct 3, 2011 at 7:53 AM, Joey L
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 mjh2...@gmail.com 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: # # 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.html # 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 = 3306 socket = /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 = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /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 = 127.0.0.1 # # * Fine Tuning # key_buffer = 2G key_buffer_size = 2G max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 join_buffer_size = 128 # 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 max_heap_table_size = 32M tmp_table_size = 32M thread_concurrency = 10 # # * Query Cache Configuration # query_cache_limit = 2M query_cache_size = 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 = /var/log/mysql/mysql.log general_log = 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 = /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 [mysqldump] quick quote-names max_allowed_packet = 64M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] key_buffer = 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 eric.ber...@gmail.com wrote: Can you run show processlist in another connection while the select
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 running against this table along with the explain output of each problem query. 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 mjh2...@gmail.com 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. thanks mjh On Thu, Oct 6, 2011 at 6:13 AM, Joey L mjh2...@gmail.com 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: # # 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.html # 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= 3306 socket = /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 = /var/run/mysqld/mysqld.sock nice= 0 [mysqld] # # * Basic Settings # user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /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= 127.0.0.1 # # * Fine Tuning # key_buffer = 2G key_buffer_size = 2G max_allowed_packet = 16M thread_stack= 192K thread_cache_size = 8 join_buffer_size= 128 # 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 max_heap_table_size = 32M tmp_table_size = 32M thread_concurrency = 10 # # * Query Cache Configuration # query_cache_limit = 2M query_cache_size= 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= /var/log/mysql/mysql.log general_log = 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 = /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 [mysqldump] quick quote-names max_allowed_packet = 64M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] key_buffer = 16M # # *
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,etcno luck. thanks mjh On Thu, Oct 6, 2011 at 9:15 AM, Johnny Withers joh...@pixelated.net 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 query. 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 mjh2...@gmail.com 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. thanks mjh On Thu, Oct 6, 2011 at 6:13 AM, Joey L mjh2...@gmail.com 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: # # 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.html # 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 = 3306 socket = /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 = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /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 = 127.0.0.1 # # * Fine Tuning # key_buffer = 2G key_buffer_size = 2G max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 join_buffer_size = 128 # 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 max_heap_table_size = 32M tmp_table_size = 32M thread_concurrency = 10 # # * Query Cache Configuration # query_cache_limit = 2M query_cache_size = 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 = /var/log/mysql/mysql.log general_log = 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 = /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
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 different 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 100-thousand records this is probably a non issue for the server, when the table has millions, well then it could be a problem. Maybe your indexes for this table don't fit into memory? Who knows? That is why you need to look at the problem queries. 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. Sent from my iPad On Oct 6, 2011, at 8:40 AM, Joey L mjh2...@gmail.com 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,etcno luck. thanks mjh On Thu, Oct 6, 2011 at 9:15 AM, Johnny Withers joh...@pixelated.net 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 query. 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 mjh2...@gmail.com 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. thanks mjh On Thu, Oct 6, 2011 at 6:13 AM, Joey L mjh2...@gmail.com 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: # # 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.html # 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= 3306 socket = /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 = /var/run/mysqld/mysqld.sock nice= 0 [mysqld] # # * Basic Settings # user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /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= 127.0.0.1 # # * Fine Tuning # key_buffer = 2G key_buffer_size = 2G max_allowed_packet = 16M thread_stack= 192K thread_cache_size = 8 join_buffer_size= 128 # 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 max_heap_table_size = 32M tmp_table_size = 32M thread_concurrency = 10 # # * Query Cache Configuration # query_cache_limit = 2M query_cache_size= 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= /var/log/mysql/mysql.log general_log = 2 # # Error logging goes to syslog due to
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=arch...@jab.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=arch...@jab.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:5Thu 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.20k33.4/s QC Hits 32.56k21.6/s %Total: 64.87 DMS 12.28k 8.2/s 24.46 Com_ 3.21k 2.1/s6.39 COM_QUIT 2.89k 1.9/s5.76 -Unknown745 0.5/s1.48 Slow 10 s 68 0.0/s0.14 %DMS: 0.55 Log: OFF DMS12.28k 8.2/s 24.46 SELECT 11.09k 7.4/s 22.10 90.36 UPDATE 539 0.4/s1.07 4.39 INSERT 384 0.3/s0.77 3.13 DELETE 260 0.2/s0.52 2.12 REPLACE 0 0/s0.00 0.00 Com_3.21k 2.1/s6.39 set_option1.10k 0.7/s2.20 show_fields 1.03k 0.7/s2.05 admin_comma 707 0.5/s1.41 __ SELECT and Sort _ Scan1.65k 1.1/s %SELECT: 14.87 Range 493 0.3/s4.44 Full join 310 0.2/s2.79 Range check 339 0.2/s3.06 Full rng join 0 0/s0.00 Sort scan 887 0.6/s Sort range628 0.4/s Sort mrg pass 0 0/s __ Query Cache _ Memory usage5.96M of 16.00M %Used: 37.25 Block Fragmnt 5.17% Hits 32.56k21.6/s Inserts 5.66k 3.8/s Insrt:Prune 5.66k:1 3.8/s Hit:Insert 5.76:1 __ Table Locks _ Waited513 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/sSize: 32.0M File5 0.0/s __ Threads _ Running32 of 37 Cached 0 of8 %Hit: 93.26 Created 195 0.1/s Slow0 0/s __ Aborted _ Clients 0 0/s Connects2 0.0/s __ Bytes ___ Sent 100.33M 66.7k/s Received 12.48M8.3k/s __ InnoDB Buffer Pool __ Usage 1.67M of 8.00M %Used: 20.90 Read hit 99.70% Pages Free405%Total: 79.10 Data107 20.90 %Drty: 0.00 Misc 0 0.00 Latched 0.00 Reads 26.18k17.4/s From file78 0.1/s0.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 Reads96 0.1/s Writes 12 0.0/s fsync11 0.0/s Pending Reads 0 Writes 0 fsync 0 Pages Created 0 0/s Read107 0.1/s Written 3 0.0/s Rows Deleted 0 0/s Inserted 0 0/s Read 20.98k13.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 ma...@mhtx.net Bug reports, feature requests, and downloads at http://mysqltuner.com/ Run with '--help' for additional options and output filtering
MySQL Indexes
Hi, Can anyone help and offer some advice with regards MySQL indexes. Basically we have a number of different tables all of which have the obviously primary keys. We then have some queries using JOIN statements that run slowly than we wanted. How many indexes are recommended per table ? For example should I have a index on all fields that will be used in a WHERE statement ? Should the indexes be created with multiple fields ? A example of two basic queries SELECT auto_id, name, score FROM test_table WHERE score 10 ORDER BY score DESC SELECT auto_id, name, score FROM test_table WHERE score 10 AND name Like '%Red%' ORDER BY score DESC How many indexes should be created for these two queries ? Thanks, Neil
Re: 4 minute slow on select count(*) from table - myisam type
Joey, does your 'large' table get On Thu, Oct 6, 2011 at 3:22 PM, Joey L mjh2...@gmail.com 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:5Thu 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.20k33.4/s QC Hits 32.56k21.6/s %Total: 64.87 DMS 12.28k 8.2/s 24.46 Com_ 3.21k 2.1/s6.39 COM_QUIT 2.89k 1.9/s5.76 -Unknown745 0.5/s1.48 Slow 10 s 68 0.0/s0.14 %DMS: 0.55 Log: OFF DMS12.28k 8.2/s 24.46 SELECT 11.09k 7.4/s 22.10 90.36 UPDATE 539 0.4/s1.07 4.39 INSERT 384 0.3/s0.77 3.13 DELETE 260 0.2/s0.52 2.12 REPLACE 0 0/s0.00 0.00 Com_3.21k 2.1/s6.39 set_option1.10k 0.7/s2.20 show_fields 1.03k 0.7/s2.05 admin_comma 707 0.5/s1.41 __ SELECT and Sort _ Scan1.65k 1.1/s %SELECT: 14.87 Range 493 0.3/s4.44 Full join 310 0.2/s2.79 Range check 339 0.2/s3.06 Full rng join 0 0/s0.00 Sort scan 887 0.6/s Sort range628 0.4/s Sort mrg pass 0 0/s __ Query Cache _ Memory usage5.96M of 16.00M %Used: 37.25 Block Fragmnt 5.17% Hits 32.56k21.6/s Inserts 5.66k 3.8/s Insrt:Prune 5.66k:1 3.8/s Hit:Insert 5.76:1 __ Table Locks _ Waited513 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/sSize: 32.0M File5 0.0/s __ Threads _ Running32 of 37 Cached 0 of8 %Hit: 93.26 Created 195 0.1/s Slow0 0/s __ Aborted _ Clients 0 0/s Connects2 0.0/s __ Bytes ___ Sent 100.33M 66.7k/s Received 12.48M8.3k/s __ InnoDB Buffer Pool __ Usage 1.67M of 8.00M %Used: 20.90 Read hit 99.70% Pages Free405%Total: 79.10 Data107 20.90 %Drty: 0.00 Misc 0 0.00 Latched 0.00 Reads 26.18k17.4/s From file78 0.1/s0.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 Reads96 0.1/s Writes 12 0.0/s fsync11 0.0/s Pending Reads 0 Writes 0 fsync 0 Pages Created 0 0/s Read107 0.1/s Written 3 0.0/s Rows Deleted 0 0/s Inserted 0 0/s Read 20.98k13.9/s Updated 0 0/s root@rider:~/tmp# and the mysqltuner.pl report : - root@rider:~/tmp# perl mysqltuner.pl MySQLTuner
Re: 4 minute slow on select count(*) from table - myisam type
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 eroomy...@gmail.com wrote: Joey, does your 'large' table get On Thu, Oct 6, 2011 at 3:22 PM, Joey L mjh2...@gmail.com 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:5Thu 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.20k33.4/s QC Hits 32.56k21.6/s %Total: 64.87 DMS 12.28k 8.2/s 24.46 Com_ 3.21k 2.1/s6.39 COM_QUIT 2.89k 1.9/s5.76 -Unknown745 0.5/s1.48 Slow 10 s 68 0.0/s0.14 %DMS: 0.55 Log: OFF DMS12.28k 8.2/s 24.46 SELECT 11.09k 7.4/s 22.10 90.36 UPDATE 539 0.4/s1.07 4.39 INSERT 384 0.3/s0.77 3.13 DELETE 260 0.2/s0.52 2.12 REPLACE 0 0/s0.00 0.00 Com_3.21k 2.1/s6.39 set_option1.10k 0.7/s2.20 show_fields 1.03k 0.7/s2.05 admin_comma 707 0.5/s1.41 __ SELECT and Sort _ Scan1.65k 1.1/s %SELECT: 14.87 Range 493 0.3/s4.44 Full join 310 0.2/s2.79 Range check 339 0.2/s3.06 Full rng join 0 0/s0.00 Sort scan 887 0.6/s Sort range628 0.4/s Sort mrg pass 0 0/s __ Query Cache _ Memory usage5.96M of 16.00M %Used: 37.25 Block Fragmnt 5.17% Hits 32.56k21.6/s Inserts 5.66k 3.8/s Insrt:Prune 5.66k:1 3.8/s Hit:Insert 5.76:1 __ Table Locks _ Waited513 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/sSize: 32.0M File5 0.0/s __ Threads _ Running32 of 37 Cached 0 of8 %Hit: 93.26 Created 195 0.1/s Slow0 0/s __ Aborted _ Clients 0 0/s Connects2 0.0/s __ Bytes ___ Sent 100.33M 66.7k/s Received 12.48M8.3k/s __ InnoDB Buffer Pool __ Usage 1.67M of 8.00M %Used: 20.90 Read hit 99.70% Pages Free405%Total: 79.10 Data107 20.90 %Drty: 0.00 Misc 0 0.00 Latched 0.00 Reads 26.18k17.4/s From file78 0.1/s0.30 Ahead Rnd 2 0.0/s Ahead Sql 1 0.0/s Writes 3 0.0/s Flushes 3 0.0/s
Re: MySQL Indexes
For the first query, the obvious index on score will give you optimal results. The second query is founded on this phrase: Like '%Red%' and no index will help you there. This is an anti-pattern, I am afraid. The only way your database can satisfy that expression is to test each and every record in the that database (the test itself being expensive as infix finding is iterative). Perhaps you should consider this approach instead: http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi, Can anyone help and offer some advice with regards MySQL indexes. Basically we have a number of different tables all of which have the obviously primary keys. We then have some queries using JOIN statements that run slowly than we wanted. How many indexes are recommended per table ? For example should I have a index on all fields that will be used in a WHERE statement ? Should the indexes be created with multiple fields ? A example of two basic queries SELECT auto_id, name, score FROM test_table WHERE score 10 ORDER BY score DESC SELECT auto_id, name, score FROM test_table WHERE score 10 AND name Like '%Red%' ORDER BY score DESC How many indexes should be created for these two queries ? Thanks, Neil -- - michael dykman - mdyk...@gmail.com May the Source be with you.
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 eroomy...@gmail.com 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 eroomy...@gmail.com wrote: Joey, does your 'large' table get On Thu, Oct 6, 2011 at 3:22 PM, Joey L mjh2...@gmail.com 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
mysqldiff resurrected and 0.43 released
Hi all, After a very long hiatus from maintainership (several years), I have finally released a new version of MySQL-Diff, the CPAN module suite which also contains mysqldiff, a CLI-based frontend tool for comparing the table schema of a pair of MySQL databases. Its output is a sequence of MySQL statements (CREATE/ALTER/DROP TABLE/COLUMN etc.), which if applied to the first database of the pair, will make its schema match that of the second. The web page is here: http://adamspiers.org/computing/mysqldiff/ and the manual page is here: http://search.cpan.org/dist/MySQL-Diff/bin/mysqldiff There are still a few open bugs in the tracker which need ironing out, mostly due to the evolution of MySQL itself since I first wrote this utility in 2000. Nevertheless I hope that some of you will already be able to benefit from the new life I'm trying to breathe into this little project. github forks are of course welcome too ;-) Cheers, Adam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 4 minute slow on select count(*) from table - myisam type
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 mjh2...@gmail.com 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 eroomy...@gmail.com 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 eroomy...@gmail.com wrote: Joey, does your 'large' table get On Thu, Oct 6, 2011 at 3:22 PM, Joey L mjh2...@gmail.com 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:5Thu 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.20k33.4/s QC Hits 32.56k21.6/s %Total: 64.87 DMS 12.28k 8.2/s 24.46 Com_ 3.21k 2.1/s6.39 COM_QUIT 2.89k 1.9/s5.76 -Unknown745 0.5/s1.48 Slow 10 s 68 0.0/s0.14 %DMS: 0.55 Log: OFF DMS12.28k 8.2/s 24.46 SELECT 11.09k 7.4/s 22.10 90.36 UPDATE 539 0.4/s1.07 4.39 INSERT 384 0.3/s0.77 3.13 DELETE 260 0.2/s0.52 2.12 REPLACE 0 0/s0.00 0.00 Com_3.21k 2.1/s6.39 set_option1.10k 0.7/s2.20 show_fields 1.03k 0.7/s2.05 admin_comma 707 0.5/s1.41 __ SELECT and Sort _ Scan1.65k 1.1/s %SELECT: 14.87 Range 493 0.3/s4.44 Full join 310 0.2/s2.79 Range check 339 0.2/s3.06 Full rng join 0 0/s0.00 Sort scan 887 0.6/s Sort range628 0.4/s Sort mrg pass 0 0/s __ Query Cache _ Memory usage5.96M of 16.00M %Used: 37.25 Block Fragmnt 5.17% Hits 32.56k21.6/s Inserts 5.66k 3.8/s Insrt:Prune 5.66k:1 3.8/s Hit:Insert 5.76:1 __ Table Locks _ Waited513 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/sSize: 32.0M File5 0.0/s __ Threads _ Running32 of 37 Cached 0 of8 %Hit: 93.26 Created 195 0.1/s Slow0 0/s __ Aborted _ Clients 0 0/s Connects2 0.0/s __ Bytes ___ Sent 100.33M 66.7k/s Received 12.48M8.3k/s
Re: 4 minute slow on select count(*) from table - myisam type
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 w...@singerwang.comwrote: 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 mjh2...@gmail.com 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 eroomy...@gmail.com 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 eroomy...@gmail.com wrote: Joey, does your 'large' table get On Thu, Oct 6, 2011 at 3:22 PM, Joey L mjh2...@gmail.com 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:5Thu 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.20k33.4/s QC Hits 32.56k21.6/s %Total: 64.87 DMS 12.28k 8.2/s 24.46 Com_ 3.21k 2.1/s6.39 COM_QUIT 2.89k 1.9/s5.76 -Unknown745 0.5/s1.48 Slow 10 s 68 0.0/s0.14 %DMS: 0.55 Log: OFF DMS12.28k 8.2/s 24.46 SELECT 11.09k 7.4/s 22.10 90.36 UPDATE 539 0.4/s1.07 4.39 INSERT 384 0.3/s0.77 3.13 DELETE 260 0.2/s0.52 2.12 REPLACE 0 0/s0.00 0.00 Com_3.21k 2.1/s6.39 set_option1.10k 0.7/s2.20 show_fields 1.03k 0.7/s2.05 admin_comma 707 0.5/s1.41 __ SELECT and Sort _ Scan1.65k 1.1/s %SELECT: 14.87 Range 493 0.3/s4.44 Full join 310 0.2/s2.79 Range check 339 0.2/s3.06 Full rng join 0 0/s0.00 Sort scan 887 0.6/s Sort range628 0.4/s Sort mrg pass 0 0/s __ Query Cache _ Memory usage5.96M of 16.00M %Used: 37.25 Block Fragmnt 5.17% Hits 32.56k21.6/s Inserts 5.66k 3.8/s Insrt:Prune 5.66k:1 3.8/s Hit:Insert 5.76:1 __ Table Locks _ Waited513 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 _
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 below. 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 = 'index.php?option=com_communityItemi | | 2508 | p_092211 | localhost | p_092211 | Query |7 | Locked | SELECT oldurl from w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_communityItemid=484lan | | 2509 | p_092211 | localhost | p_092211 | Query |5 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_communityItemi | | 2521 | p_092211 | localhost | p_092211 | Query |7 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_jfbconnectlang | | 2522 | p_092211 | localhost | p_092211 | Query |7 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_jfbconnectlang | | 2523 | p_092211 | localhost | p_092211 | Query |7 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_communityItemi | | 2529 | p_092211 | localhost | p_092211 | Query |7 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_communityItemi | | 2535 | p_092211 | localhost | p_092211 | Query |7 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_communityItemi | | 2536 | p_092211 | localhost | p_092211 | Query |7 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_communityItemi | | 2537 | p_092211 | localhost | p_092211 | Query |7 | Locked | SELECT oldurl from w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_communityItemid=484lan | | 2538 | p_092211 | localhost | p_092211 | Query |7 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_communityItemi | | 2556 | p_092211 | localhost | p_092211 | Query |7 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_limosairportid | | 2557 | p_092211 | localhost | p_092211 | Query |5 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_communityItemi | | 2558 | p_092211 | localhost | p_092211 | Query |7 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_limoscity=Casc | | 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 = 'index.php?option=com_communityItemid=484lan | | 2572 | p_092211 | localhost | p_092211 | Query |5 | Locked | SELECT oldurl, dateadd FROM w6h8a_sh404sef_urls WHERE newurl = 'index.php?option=com_communityItemi | | 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 |
Re: MySQL Indexes
Maybe that was a bad example. If the query was name = 'Red' what index should I create ? Should I create a index of all columns used in each query or have a index on individual column ? On 6 Oct 2011, at 17:28, Michael Dykman mdyk...@gmail.com wrote: For the first query, the obvious index on score will give you optimal results. The second query is founded on this phrase: Like '%Red%' and no index will help you there. This is an anti-pattern, I am afraid. The only way your database can satisfy that expression is to test each and every record in the that database (the test itself being expensive as infix finding is iterative). Perhaps you should consider this approach instead: http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil neil.tompk...@googlemail.com wrote: Hi, Can anyone help and offer some advice with regards MySQL indexes. Basically we have a number of different tables all of which have the obviously primary keys. We then have some queries using JOIN statements that run slowly than we wanted. How many indexes are recommended per table ? For example should I have a index on all fields that will be used in a WHERE statement ? Should the indexes be created with multiple fields ? A example of two basic queries SELECT auto_id, name, score FROM test_table WHERE score 10 ORDER BY score DESC SELECT auto_id, name, score FROM test_table WHERE score 10 AND name Like '%Red%' ORDER BY score DESC How many indexes should be created for these two queries ? Thanks, Neil -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Re: 4 minute slow on select count(*) from table - myisam type
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 joh...@pixelated.net
Re: MySQL Indexes
Only one index at a time can be used per query, so neither strategy is optimal. You need at look at the queries you intend to run against the system and construct indexes which support them. - md On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins neil.tompk...@googlemail.comwrote: Maybe that was a bad example. If the query was name = 'Red' what index should I create ? Should I create a index of all columns used in each query or have a index on individual column ? On 6 Oct 2011, at 17:28, Michael Dykman mdyk...@gmail.com wrote: For the first query, the obvious index on score will give you optimal results. The second query is founded on this phrase: Like '%Red%' and no index will help you there. This is an anti-pattern, I am afraid. The only way your database can satisfy that expression is to test each and every record in the that database (the test itself being expensive as infix finding is iterative). Perhaps you should consider this approach instead: http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil neil.tompk...@googlemail.com neil.tompk...@googlemail.com wrote: Hi, Can anyone help and offer some advice with regards MySQL indexes. Basically we have a number of different tables all of which have the obviously primary keys. We then have some queries using JOIN statements that run slowly than we wanted. How many indexes are recommended per table ? For example should I have a index on all fields that will be used in a WHERE statement ? Should the indexes be created with multiple fields ? A example of two basic queries SELECT auto_id, name, score FROM test_table WHERE score 10 ORDER BY score DESC SELECT auto_id, name, score FROM test_table WHERE score 10 AND name Like '%Red%' ORDER BY score DESC How many indexes should be created for these two queries ? Thanks, Neil -- - michael dykman - mdyk...@gmail.commdyk...@gmail.com May the Source be with you. -- - michael dykman - mdyk...@gmail.com May the Source be with you.
Re: MySQL Indexes
Neil, whenever you see multiple fields you'd like to index, you should consider, at least: * The frequency of each query; * The occurrences of the same field in multiple queries; * The cardinality of each field; There is a tool Index Analyzer that may give you some hints, and I think it's maatkit that has a tool to run a query log to find good candidates - I've seen it somewhere, I believe Just remember that idx_a(field_a,field_b) is not the same, and is not considered for use, the same way as idx_b(field_b,field_a). -NT Em 07-10-2011 00:22, Michael Dykman escreveu: Only one index at a time can be used per query, so neither strategy is optimal. You need at look at the queries you intend to run against the system and construct indexes which support them. - md On Thu, Oct 6, 2011 at 2:35 PM, Neil Tompkins neil.tompk...@googlemail.comwrote: Maybe that was a bad example. If the query was name = 'Red' what index should I create ? Should I create a index of all columns used in each query or have a index on individual column ? On 6 Oct 2011, at 17:28, Michael Dykman mdyk...@gmail.com wrote: For the first query, the obvious index on score will give you optimal results. The second query is founded on this phrase: Like '%Red%' and no index will help you there. This is an anti-pattern, I am afraid. The only way your database can satisfy that expression is to test each and every record in the that database (the test itself being expensive as infix finding is iterative). Perhaps you should consider this approach instead: http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html On Thu, Oct 6, 2011 at 10:59 AM, Tompkins Neil neil.tompk...@googlemail.com neil.tompk...@googlemail.com wrote: Hi, Can anyone help and offer some advice with regards MySQL indexes. Basically we have a number of different tables all of which have the obviously primary keys. We then have some queries using JOIN statements that run slowly than we wanted. How many indexes are recommended per table ? For example should I have a index on all fields that will be used in a WHERE statement ? Should the indexes be created with multiple fields ? A example of two basic queries SELECT auto_id, name, score FROM test_table WHERE score 10 ORDER BY score DESC SELECT auto_id, name, score FROM test_table WHERE score 10 AND name Like '%Red%' ORDER BY score DESC How many indexes should be created for these two queries ? Thanks, Neil -- - michael dykman - mdyk...@gmail.commdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 4 minute slow on select count(*) from table - myisam type
From: Joey L mjh2...@gmail.com 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=arch...@jab.org
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 j...@bytesmiths.com wrote: From: Joey L mjh2...@gmail.com 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=mjh2...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: 4 minute slow on select count(*) from table - myisam type
Glad you got to the bottom of it Joey. On 7 Oct 2011 01:23, Joey L mjh2...@gmail.com 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 j...@bytesmiths.com wrote: From: Joey L mjh2...@gmail.com 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=mjh2...@gmail.com
