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 <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 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 <mjh2...@gmail.com> 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 <bferr...@baywinds.org >>>>>>>> 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=<some value> >>>>>>>> >>>>>>>> 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=mjh2...@gmail.com >>>>>>>> >>>>>>>> >>>>>>> >>>>>> >>>>>> >>>>> >>>> >>>> >>>> >>>> -- >>>> Eric Bergen >>>> eric.ber...@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=joh...@pixelated.net >> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
