How big is your DB in Mb?

What are your my.cnf settings?

Can you also run a:
SHOW GLOBAL STATUS;
and post the output here.

As it looks like is that MySQL is spending a lot of time trying to open tables.
This means that something is either locking the tables or that you have a very low setting of table_cache compared to the amount of connections that you have.

Current my.cnf:

[mysqld]
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=4M
query_cache_type=1
max_connections=900
wait_timeout=10
interactive_timeout=90
connect_timeout=7
thread_cache_size=100
key_buffer_size=24M
join_buffer=1M
max_allowed_packet=16M
table_cache=768
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K
max_connect_errors=10
thread_concurrency=8
myisam_sort_buffer_size=64M
tmp_table_size=768M
low_priority_updates=1
#log-bin
server-id=1
log-slow-queries
long_query_time = 1
max_user_connections=20


One database is about 100-200 MB of size (about 20.000-25.000 rows per database). This is output of SHOW GLOBAL STATUS;

mysql> SHOW GLOBAL STATUS;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'STATUS' at line 1

If I run SHOW GLOBAL STATUS (without ; at the end), nothing is displayed then.

P.S. 200 apache requests and 500 active mysql proccesses (with SELECT commands) at the moment (

Sorry, previous MySQL version 5 the status was always global so you should just write this:
SHOW STATUS;

Do you have any queries in the slow query log?

If you do, try to find one that seems to take a long time and occurs often and run an EXPLAIN on tha query.


Also post the output from:
SHOW CREATE TABLE keywords;

Because a lot of the queries in your processlist is accessing keywords.

Hi, this is output from show status:


| Aborted_clients | 124 |
| Aborted_connects | 8256 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 86361549 |
| Bytes_sent | 4207237726 |
| Com_admin_commands | 9 |
| Com_alter_db | 0 |
| Com_alter_table | 6 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 70376 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 37 |
| Com_dealloc_sql | 0 |
| Com_delete | 5321 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 13 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 1 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 657 |
| Com_grant | 6 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 66914 |
| Com_insert_select | 8 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 1096 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 164 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 210045 |
| Com_set_option | 7051 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 2863 |
| Com_show_databases | 339 |
| Com_show_errors | 0 |
| Com_show_fields | 3186 |
| Com_show_grants | 605 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_ndb_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_privileges | 0 |
| Com_show_processlist | 95 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 1 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 5194 |
| Com_show_variables | 332 |
| Com_show_warnings | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_stmt_close | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 14 |
| Com_unlock_tables | 1206 |
| Com_update | 104639 |
| Com_update_multi | 471 |
| Connections | 51398 |
| Created_tmp_disk_tables | 408 |
| Created_tmp_files | 776 |
| Created_tmp_tables | 3098 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 7219 |
| Handler_discover | 0 |
| Handler_read_first | 47937 |
| Handler_read_key | 8668209 |
| Handler_read_next | 142388566 |
| Handler_read_prev | 46443293 |
| Handler_read_rnd | 139251 |
| Handler_read_rnd_next | 299901178 |
| Handler_rollback | 0 |
| Handler_update | 5115859 |
| Handler_write | 103648 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 21806 |
| Key_read_requests | 17744600 |
| Key_reads | 89178 |
| Key_write_requests | 179800 |
| Key_writes | 117077 |
| Max_used_connections | 105 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 1446 |
| Open_streams | 0 |
| Open_tables | 768 |
| Opened_tables | 6424 |
| Qcache_free_blocks | 141 |
| Qcache_free_memory | 595552 |
| Qcache_hits | 157206 |
| Qcache_inserts | 198905 |
| Qcache_lowmem_prunes | 126811 |
| Qcache_not_cached | 6667 |
| Qcache_queries_in_cache | 423 |
| Qcache_total_blocks | 1426 |
| Questions | 683011 |
| Rpl_status | NULL |
| Select_full_join | 194 |
| Select_full_range_join | 0 |
| Select_range | 9985 |
| Select_range_check | 0 |
| Select_scan | 90984 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 3003 |
| Sort_merge_passes | 388 |
| Sort_range | 18907 |
| Sort_rows | 345227 |
| Sort_scan | 6553 |
| Table_locks_immediate | 410541 |
| Table_locks_waited | 135 |
| Threads_cached | 47 |
| Threads_connected | 58 |
| Threads_created | 105 |
| Threads_running | 1 |
| Uptime | 28298 |


Output from SHOW CREATE TABLE keywords;

keywords | CREATE TABLE `keywords` (
`id` int(11) NOT NULL auto_increment,
`keyword` varchar(255) NOT NULL default '',
`yahoo_body` text NOT NULL,
`markov` text NOT NULL,
`whois` tinyint(4) NOT NULL default '0',
`meta_keywords` varchar(255) NOT NULL default '',
`meta_description` varchar(255) NOT NULL default '',
`meta_title` varchar(255) NOT NULL default '',
`featured` tinyint(4) NOT NULL default '0',
`featured_body` text NOT NULL,
`published` tinyint(4) NOT NULL default '0',
`tb_direct` tinyint(4) NOT NULL default '0',
`tb_direct_google` tinyint(4) NOT NULL default '0',
`coppermine` tinyint(4) NOT NULL default '0',
`social` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `keyword` (`keyword`)
) ENGINE=MyISAM AUTO_INCREMENT=22894 DEFAULT CHARSET=latin1


Thanks for any suggestions.

Looking at you table you basically don't have any indexes.
Only two of the columns id (primary key) and keyword is indexed.

But if you look at the queries in your processlist a lot of them are of the kind:

SELECT ... FROM keywords WHERE social = 0 LIMIT 1;... FROM keywords WHERE published = 1 ORDER BY id DESC LIMIT 5;


And:

SELECT ... FROM trackback_direct WHERE posted = 0;


So here are some suggestions:
1. Create some indexes that target the queries above:

ALTER TABLE keywords ADD INDEX kw_ix_published_id (published, id);ALTER TABLE keywords ADD INDEX kw_ix_social (social);ALTER TABLE trackback_direct ADD INDEX tb_ix_posted(posted);


2.
Increase sort_buffer_size 1MB is very small (it's actually smaller than the default of 2MB which is a bit odd since you seem to have a pretty hefty server). Set it to about 5MB instead.
Try increasing the query cache variables:
query_cache_size=40MB
query_cache_limit=2MB


Try those to begin with and we shall see what happens.

Thanks, I tweaked my.cnf now and will try to add indexes to these columns.

Actually, I had index on "published" column previously, but after running EXPLAIN, I decided to remove index. Just can't remember what was wrong with it )

Ok, I just added index to published column, and attached image with EXPLAIN output (1st query is without index, and 2nd query is with published index added). As you may see, 2nd query displays "Using where; Using filesort" and I read somewhere that "Using filesort" indicates slower query?

You didn't run the ALTER TABLE statement that I gave you. Shame on you!

If you look at it I am creating a combined index with the columns (published, id).
And it is only this combined index that makes it possible to both retrieve the appropriate records and in the right order without a filesort.

Your first explain does not have a filesort on it because mysql choose to make an index scan, which means that it goes thru all rows in the primary index trying to find matching rows.

In your second explain it finds the matching rows by using the index but then has to sort them to deliver them in the right order.

You must not stare yourself blind on the last part of the explain. It is all of it that is interresting.
For instance if you have a query where you only have 4 rows left before the sorting it will still be faster than having to perform a table scan.

For example in your case your first explain reports that mysql has to examin 14804 rows while in you second explain it only has 1243 rows left after using the index.
So you see the index does make a difference.

But to speed it up even more, drop the index "published" that you created and create my combined index instead.

Oh, I really forgot about your queries. Shame on me, you are absolutely right about this

Going to run your queries on all databases, will let you know how it's going then

Added all these indexes on all websites hosted on server, but still no luck... Tons of same queries in active mysql proccesses list. I have no more ideas what causes it )

Some more opinions:
1.
Some general important questions:

What is actually your server doing during this high load?

Is it high CPU load or disk load?

How much of the RAM memory is used?

What OS are you running on?

Is the server a strict mysql DB server or are you running Apache or any other software on the same server?

Are the DB files located on a locally connected harddrive?

What kind of disk is it?


2.
I think some of your my.cnf options are very odd. And unless you know why they are set as they are then I have some suggestions:

Start by increasing this value:
key_buffer_size=256M
Your setting of 24M seems _very_ low.

Decrease this:
tmp_table_size=5M
Your 768M looks rediculously big and that memory can be used much better.

Then you have this setting:
max_connections=900
that looks awfully large compared to this:
table_cache=768
Since mysql is multithreaded and each thread that wants to read data from a MyISAM table needs a separate file handler the table_cache should be nr of connections times tables part of the query. But the strange part in that is that your status variables didn't indicate that you had many opened tables. Which contradict this.

All my settings are configured according to mysql tuning primer ( http://forge.mysql.com/projects/view.php?id=44 ) - launched it multiple times and software recommended these values to be used on server.

It's high CPU load. Memory usage is normal, about 20-40% of memory is used total. I'm running CentOS 4.4, and it's standard webserver hosting many different sites (it's not dedicated mysql server). Server has SATA disk drives and server just starts making tons of mysql proccesses every X minutes. But the traffic is the same all the time, so it looks strange for me - same number of visitors, but sometims load goes high, and sometimes it doesn't.

I'll tweak my.cnf according to your recommendations now.


UPDATE: Server is still crazy. I was looking at server for about 1 hour and it was ok. Many apache requests, load was normal. Suddenly server started making hundreds of mysql proccesses (like you saw in my previous attachment) and was overloaded, while number of apache requests is the same.

Turn on your slow query log and find the problem ones.

Tried already without success. Log file displays all these queries (displayed in 1st screenshot). Of course, it happens not all the time, only every X minutes (when load is skyrocketed) all queries are marked as slow.