MySQL Performance: MySQL => 2 minutes to kill server

Members

Help

Help

Register

Login

Home

Home » Performance » MySQL » 2 minutes to kill server

Show: Today's Messages :: Show Polls :: Message Navigator

Switch to threaded view of this topic

Create a new topic

Submit Reply

2 minutes to kill server [message #1156]

Wed, 25 April 2007 17:53

Thorton is currently offline

Thorton
Messages: 19
Registered: March 2007

Junior Member

Hello,

I have extreme problems with mysql on 2xdual Opteron + 2GB RAM server which is more than enough to handle many requests, I believe. I don't know if my queries are not optimized (but I think they are ok). When I start mysql, it takes 2 minutes to kill server because and sites become not accessible. While Apache is processing about 100-200 requests at the same time, there are hundreds of mysql proccesses for unknown reasons. I'm attaching screenshot how everything looks. Screenshot also displays what queries are used, may be queries are wrong?

Any help is more than welcome, because all my sites are down about 22-23 hours per day due to heavy sql load and I can't do anything Sad

Sad

Attachment: sql.gif
(Size: 107.94KB, Downloaded 289 time(s))

Report message to a moderator

Send a private message to this user

Re: 2 minutes to kill server [message #1158 is a reply to message #1156 ]

Thu, 26 April 2007 09:55

sterin is currently offline

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

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.

Report message to a moderator

Send a private message to this user

Re: 2 minutes to kill server [message #1160 is a reply to message #1156 ]

Thu, 26 April 2007 15:01

Thorton is currently offline

Thorton
Messages: 19
Registered: March 2007

Junior Member

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 Sad

Sad

[Updated on: Thu, 26 April 2007 15:38]

Report message to a moderator

Send a private message to this user

Re: 2 minutes to kill server [message #1161 is a reply to message #1160 ]

Thu, 26 April 2007 19:35

sterin is currently offline

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

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.

[Updated on: Thu, 26 April 2007 20:05]

Report message to a moderator

Send a private message to this user

Re: 2 minutes to kill server [message #1163 is a reply to message #1156 ]

Fri, 27 April 2007 02:06

Thorton is currently offline

Thorton
Messages: 19
Registered: March 2007

Junior Member

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.

Report message to a moderator

Send a private message to this user

Re: 2 minutes to kill server [message #1164 is a reply to message #1156 ]

Fri, 27 April 2007 04:29

sterin is currently offline

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

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.

Report message to a moderator

Send a private message to this user

Re: 2 minutes to kill server [message #1165 is a reply to message #1156 ]

Fri, 27 April 2007 04:57

Thorton is currently offline

Thorton
Messages: 19
Registered: March 2007

Junior Member

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 Smile

Smile

Report message to a moderator

Send a private message to this user

Re: 2 minutes to kill server [message #1172 is a reply to message #1156 ]

Mon, 30 April 2007 16:31

Thorton is currently offline

Thorton
Messages: 19
Registered: March 2007

Junior Member

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?

Attachment: query.gif
(Size: 12.61KB, Downloaded 221 time(s))

Report message to a moderator

Send a private message to this user

Re: 2 minutes to kill server [message #1173 is a reply to message #1156 ]

Mon, 30 April 2007 17:24

sterin is currently offline

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

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

Wink

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.

Report message to a moderator

Send a private message to this user

Re: 2 minutes to kill server [message #1175 is a reply to message #1156 ]

Mon, 30 April 2007 17:31

Thorton is currently offline

Thorton
Messages: 19
Registered: March 2007

Junior Member

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

Wink

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

Report message to a moderator

Send a private message to this user

Re: 2 minutes to kill server [message #1178 is a reply to message #1156 ]

Tue, 01 May 2007 07:22

Thorton is currently offline

Thorton
Messages: 19
Registered: March 2007

Junior Member

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 Smile

Smile

Report message to a moderator

Send a private message to this user

Re: 2 minutes to kill server [message #1179 is a reply to message #1156 ]

Tue, 01 May 2007 18:10

sterin is currently offline

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

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.

Report message to a moderator

Send a private message to this user

Re: 2 minutes to kill server [message #1188 is a reply to message #1156 ]

Wed, 02 May 2007 17:41

Thorton is currently offline

Thorton
Messages: 19
Registered: March 2007

Junior Member

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.

[Updated on: Wed, 02 May 2007 20:17]

Report message to a moderator

Send a private message to this user

Re: 2 minutes to kill server [message #1194 is a reply to message #1188 ]

Thu, 03 May 2007 20:31

linuxrunner is currently offline

linuxrunner
Messages: 15
Registered: February 2007
Location: NYC

Junior Member

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

Report message to a moderator

Send a private message to this user

Re: 2 minutes to kill server [message #1201 is a reply to message #1156 ]

Fri, 04 May 2007 16:13

Thorton is currently offline

Thorton
Messages: 19
Registered: March 2007

Junior Member

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.

Report message to a moderator

Send a private message to this user

Re: 2 minutes to kill server [message #1202 is a reply to message #1156 ]

Fri, 04 May 2007 16:26

sterin is currently offline

sterin
Messages: 324
Registered: March 2007
Location: Sweden

Senior Member

How regular is the "only every X minutes"?

Do you have any cron jobs or does the application has any re-indexing jobs that it runs regularily?

And when you get it the next time, run the SHOW PROCESSLIST command and read thru _all_ the rows. Or better yet post them her also in an attachment. And please make it a text file since it is a much easier format to filter.

Because you have something that is taking a lot of CPU and it isn't the amount of processes since most of them was waiting for opening a table, I think that it is just a secondary effect since they can't open the table they want to and then the mysql threads are delayed and that is when they start to pile up.

Report message to a moderator

Send a private message to this user

Re: 2 minutes to kill server [message #1203 is a reply to message #1156 ]

Fri, 04 May 2007 16:43

Thorton is currently offline

Thorton
Messages: 19
Registered: March 2007

Junior Member

Yes, there are some cronjobs, but they just insert some rows (up to 100 rows per run, usually about 20-50 rows) to database and exit. I'll check these cronjobs, but they are really simple - a few lines of code to insert several rows of data.

Report message to a moderator

Send a private message to this user

Re: 2 minutes to kill server [message #1205 is a reply to message #1203 ]

Fri, 04 May 2007 19:06

linuxrunner is currently offline

linuxrunner
Messages: 15
Registered: February 2007
Location: NYC

Junior Member

This sounds like an IO bottleneck, are you running off of SAN disks?... nm just saw you posted its SATA drives...

[Updated on: Fri, 04 May 2007 19:07]

Report message to a moderator

Send a private message to this user

Switch to threaded view of this topic

Create a new topic

Submit Reply

Previous Topic:	Lost connection to MySQL server during query
Next Topic:	Injecting large amounts of random data

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Sun Jul 5 15:37:48 EDT 2009

Total time taken to generate the page: 0.02176 seconds

.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.