Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Is that the entire my.cnf ??? –  RolandoMySQLDBA Dec 12 '12 at 7:50
    
no, I update it edit: it is now all. –  m3hdi Dec 12 '12 at 8:01
    
How many tables did you convert from MyISAM to InnoDB ??? –  RolandoMySQLDBA Dec 12 '12 at 8:04
    
I converted all tables to innodb but when I get same result I changed back to myisam. –  m3hdi Dec 12 '12 at 8:15
    
What version of mysql are you using? Please run SELECT VERSION(); and tell me –  RolandoMySQLDBA Dec 12 '12 at 8:18
show 2 more comments

1 Answer

up vote 0 down vote accepted

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

share|improve this answer
    
thank you Ronaldo, do you have any solution for myisam so I do not convert it to innodb? –  m3hdi Dec 12 '12 at 8:55
    
Please run this query : SELECT SUM(index_length) RKBS FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql'); and tell me what RKBS is –  RolandoMySQLDBA Dec 12 '12 at 8:58
    
RKBS | +---------+ | 7582720 –  m3hdi Dec 12 '12 at 9:01
    
That's 7.5MB. You should be fine. RKBS stands for Recommended Key Buffer Size. you already have 32M which is fine. –  RolandoMySQLDBA Dec 12 '12 at 9:04
    
so I can stay with myisam? I also should note that I had a wordpress database that had similar problem (website didnt came up and got high load) and I moved it to another hosting, it is now working fine on new host! totaly confused what is the problem. –  m3hdi Dec 12 '12 at 9:09
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.