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 <johnny@stripped> 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 <mjh2000@stripped> 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 <mjh2000@stripped> 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.bergen@stripped>
> 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 <mjh2000@stripped> 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.criptos@stripped> wrote:
>>>>>
>>>>>> have you tried
>>>>>>
>>>>>> select count(yourindex) instead of select count(*) ?
>>>>>>
>>>>>>
>>>>>> On Mon, Oct 3, 2011 at 7:53 AM, Joey L <mjh2000@stripped>
> 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
> <bferrell@stripped
>>>>>>>> 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=1
>>>>>>>>
>>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Eric Bergen
>>>> eric.bergen@stripped
>>>> http://www.ebergen.net
>>>>
>>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>>
>
