Page 1 of 1

MySQL makes a server load and crashes

Posted: Tue Jan 24, 2006 12:00 pm
by Ozz
This is the TOP stats from my vps:
http://img28.imageshack.us/img28/3528/c ... d014rg.gif

As you see server load is too much and its getting more then mysql service stops and everything is normal. This happens for 2 days, hosting company made a Linux kernel update maybe that can be the reason.

My VPS load was always too high and there is only 1 site got 10.000 unique visitors. I did so many optimizations on the mysql queries and also php. I cant afford a dedicated server at this time and I dont think it's not so much traffic.

What do you suggest?

Posted: Tue Jan 24, 2006 12:09 pm
by josh
You are over 95% idle, however can you check the mysql process list and see if there are queries running for longer then they should?

Posted: Tue Jan 24, 2006 12:42 pm
by Ozz
Is there a way to see mysql processes from command line.
I use Putty to make SSH connection.
WHM gives this:
+-----+-----------+-----------+---------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-----------+-----------+---------+---------+------+-------+------------------+
| 231 | db_user_name | localhost | db_name | Sleep | 14 | | |
| 238 | root | localhost | | Query | 0 | | show processlist |
+-----+-----------+-----------+---------+---------+------+-------+------------------+

Posted: Tue Jan 24, 2006 1:12 pm
by raghavan20
you can use slow query logs to find out queries that are running for more than an expected time duration.
The time duration is usually five seconds but you can bring it down to one second for optimization.
look here for mysql documentation. Make sure you have indexes on all search columns.

Look at my post where I was talking about enabling slow query log.

If you have large queries, post here, let me see whether I can help you optimize them.

Posted: Tue Jan 24, 2006 1:44 pm
by Ozz
I already logging slow queries
There are tonz of hit update queries with LOW_PRIORITY options. This queries executing every 15 minutes with cron reading from a text file.
# Query_time: 11 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
UPDATE LOW_PRIORITY links SET hit = hit+1 WHERE id = '91941'
----------------------------------------------------------
And some search queries like:
# Query_time: 8 Lock_time: 0 Rows_sent: 1 Rows_examined: 43336
SELECT id, name, url, descr FROM links WHERE (name LIKE '%KEYWORD%' OR url LIKE '%KEYWORD%' OR descr LIKE '%KEYWORD%'

I did necessary indexes and also made my.ini optimizations.
I got this load problem for months and I can get rid of it.

Posted: Tue Jan 24, 2006 1:54 pm
by raghavan20
Ozz wrote:I already logging slow queries
There are tonz of hit update queries with LOW_PRIORITY options. This queries executing every 15 minutes with cron reading from a text file.
# Query_time: 11 Lock_time: 0 Rows_sent: 0 Rows_examined: 0
UPDATE LOW_PRIORITY links SET hit = hit+1 WHERE id = '91941'
I do not understand why is it taking 11 seconds to do a update and I hope id is a primary key index.
----------------------------------------------------------
Ozz wrote: And some search queries like:
# Query_time: 8 Lock_time: 0 Rows_sent: 1 Rows_examined: 43336
SELECT id, name, url, descr FROM links WHERE (name LIKE '%KEYWORD%' OR url LIKE '%KEYWORD%' OR descr LIKE '%KEYWORD%'
I hope you have an index like this index(`name`, `url`, `decr`) for hte last query. Look at this number
" Rows_examined: 43336", I think you do not have proper index. you should think of using a fulltext index search for this, if not, atleast a boolean fulltext search would do well.

Posted: Tue Jan 24, 2006 2:17 pm
by Ozz
id is indexed as UNIQUE now I changed as PRIMARY.
and make index(name,url,descr)
And yes it is strange take so long that LOW_PRIORITY query.
And also I was changed MyISAM tables as InnoDB.
Because of MyISAM tables have some table-locking problems.
Thanks

Posted: Tue Jan 24, 2006 3:09 pm
by raghavan20
remember unique is only a constraint not an index
primary key automatically creates an index on a column
How much time does it take after you changed to primary key and added new indexes?
Mysql manual wrote: FULLTEXT indexes can be used only with MyISAM tables.
Think about switching from MyIsam to Innodb...also read about full text searches...

Posted: Thu Jan 26, 2006 11:31 am
by Ozz
I did index changes but mysql server going on to stop after 10-15 minutes.
Maybe something wrong with mycnf
Take a look at it:

Code: Select all

[mysqld]
set-variable = max_connections=500
safe-show-database
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
log-slow-queries = /var/log/sql_slow.log
long_query_time = 5
#log=/var/log/mysqld.log
[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M


[myisamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M

Posted: Thu Jan 26, 2006 12:22 pm
by raghavan20
This is my interactive timeout setting mysql 5

Code: Select all

mysql> show variables like '%interac%';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
+---------------------+-------+
1 row in set (0.59 sec)