Every so often, but Threads Install bogs down. I have my own server (leased) and sometimes, the pages start loading very slowly. When I log in as root and do "Top" command it appears that MySQL is using lots of memory and cause most of the load. If I reboot the server it usually clears this up. I'm just wondering if anyone has had similar problems and if you know what might be changed in MySQL config to make things more robust?

Basil

Instead of doing that log into MySQL via command line and run this command: show processlist; Copy that and paste it into word or notepad and post it here. Let's find out what it's bogging down on instead of just rebooting it. That will not make the problem go away.

You can also get that from phpmyadmin by the way. You'll want to be logged in as MySQL's root operator in order to get the full list.

Welll, its not having a problem at the moment, but when I do that commnand, I get this:

mysql> show processlist;
+--------+-----------+-----------+----------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+-----------+-----------+----------+---------+------+-------+------------------+
| 2621 | ccc_xxxx | localhost | xx_lore | Sleep | 2573 | | NULL |
| 2797 | ccc_xxxx | localhost | xx_lore | Sleep | 5960 | | NULL |
| 2814 | ccc_xxxx | localhost | xx_lore | Sleep | 3838 | | NULL |
| 2821 | ccc_xxxx | localhost | xx_lore | Sleep | 3838 | | NULL |
| 2877 | ccc_xxxx | localhost | xx_lore | Sleep | 4292 | | NULL |
| 3030 | ccc_xxxx | localhost | xx_lore | Sleep | 2159 | | NULL |
| 8637 | ccc_xxxx | localhost | xx_lore | Sleep | 3867 | | NULL |
| 17311 | ccc_xxxx | localhost | xx_lore | Sleep | 4139 | | NULL |
| 26708 | ccc_xxxx | localhost | xx_lore | Sleep | 3884 | | NULL |
| 27464 | ccc_xxxx | localhost | xx_lore | Sleep | 9490 | | NULL |
| 428940 | ccc_xxxx | localhost | NULL | Query | 0 | NULL | show processlist |
+--------+-----------+-----------+----------+---------+------+-------+------------------+


(I've changed the user name. The Lore database is a knowledge base that doesn't get used much. What I guess I don't under stand is what I
m not seeing and processed related to my threads DB? GUess I don't understand what this is showing me?

Threads caches data outputted by queries.

Just for curiosity, I clicked on "Run Time Information" in My PhPAdmin and saw some of the stats listed in red (which I assume means not good)? Keep in mind that Threads is not the only Database running on my site. The items that show is red are specifically:

Slow_queries 537 - The number of queries that have taken more than long_query_time seconds.

Handler_read_rnd 2,582 k - The number of requests to read a row based on a fixed position.

Handler_read_rnd_next 338 M - The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

Qcache_lowmem_prunes 18 k The number of queries that have been removed from the cache to free up memory for caching new queries. This information can help you tune the query cache size. The query cache uses a least recently used (LRU) strategy to decide which queries to remove from the cache.

Created_tmp_disk_tables 117 The number of temporary tables on disk created automatically by the server while executing statements. If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based.

Select_full_join 8,248 The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.

Sort_merge_passes 23 The number of merge passes the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.

Opened_tables 3,166 The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.

Table_locks_waited 14 k The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.

Any of this suggest different setting in MySQL?

The number of queries in show processlist when nothing is going wrong is what I'd expect. None.

It's when things are not going right that the list is important. We can see the query that's running long and probably has a table locked, backing up the whole works.

Logging slow queries would help too, you've got a few there.

14 thousand table_locks_waited is really not good.

try this script, run it from command line and see what it tells you, it's written by a MySQL developer and is a great help.

http://www.day32.com/MySQL/

The tuning primer script, top line.

Out of curiosity I ran the same thing to compare...

Slow_queries 642
Handler_read_rnd 30 M
Handler_read_rnd_next 913 M
Qcache_lowmem_prunes 141 k
Slow_launch_threads 4 - The number of threads that have taken more than slow_launch_time seconds to create
Created_tmp_disk_tables 13 k
Select_full_join 1,418
Select_range_check 4 - The number of joins without keys that check for key usage after each row. (If this is not 0, you should carefully check the indexes of your tables.)
Opened_tables 23 k
Table_locks_waited 3,349

I only have one active database, on a shared server. Dumb question, but is this server-wide, or just me? Some of them seem out of control!

I'd think it was the entire server.

Ok, the next time the site starts bogging down, I'll SSH in and try it again.