mysql freeze/crash on JOIN here is query:

SELECT nw.news_id, nw.title, nw.description, nw.url, nw.date, nw.image,
       nw.status, src.title as src_title, src.keyword as src_keyword,
       src.url as src_url, src.icon as src_icon, cat.title as cat_title
FROM news as nw
INNER JOIN sources as src ON nw.source=src.source_id
INNER JOIN categories as cat ON nw.category=cat.category_id
WHERE nw.status=1
ORDER BY nw.news_id DESC LIMIT 0,140;


news table size is around 30MB. server RAM is 1GB.

my.cnf:

[mysqld]
max_connections = 120
key_buffer = 32M
myisam_sort_buffer_size = 32M
join_buffer_size = 8M
read_buffer_size = 8M
sort_buffer_size = 32M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1000
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 8K
query_cache_size = 32K
query_cache_type = 1
tmp_table_size = 16M

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M
local-infile=0


it was myisam I converted to innodb for better performance but nothing changed so I get back to myisam.

query works correctly on localhost but when ran in server, get high load and eat all RAM until crash mysql. just one of above query get me highload! something like that mysql could not finish it. table has around 50000 records.

I tried to fix issue by changing my.cnf parammeters with no luck.
QUERY

With regard to the query, please make this index

ALTER TABLE news ADD INDEX status_news_id_index (status,news_id);


and refactor the query as follows:

SELECT nw.news_id, nw.title, nw.description, nw.url, nw.date, nw.image,
       nw.status, src.title as src_title, src.keyword as src_keyword,
       src.url as src_url, src.icon as src_icon, cat.title as cat_title
FROM
(
    SELECT * FROM news
    WHERE status=1
    ORDER BY news_id DESC LIMIT 0,140
) as nw
LEFT JOIN sources as src ON nw.source=src.source_id
LEFT JOIN categories as cat ON nw.category=cat.category_id;


STORAGE ENGINE

Here is the problem with your InnoDB : You did not configure anything for it.

Each storage engine caches differently. MyISAM caches index pages from the .MYI files into the key buffer. InnoDB caches data and index pages in the InnoDB Buffer Pool which you did not configure. The default is 8MB.

There is a default set of innodb files located in /var/lib/mysql


ibdata1
ib_logfile0
ib_logfile1


These must be resized.

Since you are using MySQL 5.1, please do the following:

STEP 01) Convert all InnoDB tables back to MyISAM

STEP 02) service mysql stop

STEP 03) Add the following lines to my.cnf after tmp_table_size = 16M

innodb_file_per_table
innodb_buffer_pool_size=512M
innodb_log_file_size=128M
innodb_fast_shutdown=0
innodb_thread_concurrency=0


STEP 04) Erase InnoDB Files

rm -f /var/lib/mysql/ib*


STEP 05) service mysql start

On startup, mysqld will do the following


create /var/lib/mysql/ibdata1 as a 10MB file, run some stuff, and end up 18MB
create /var/lib/mysql/ib_logfile0 128M
create /var/lib/mysql/ib_logfile1 128M


STEP 06) Convert all MyISAM tables back to InnoDB