MySQL Forums :: Performance :: Very Slow Simple Query

Advanced Search

Very Slow Simple Query
Posted by: Martin Tsvetanov ()
Date: November 17, 2009 09:08PM

Hi guys,

I've got a big issue here. I have a Dual Core 1GB RAM Box running web server and mysql.
The site, that's being hosted on that server, isn't really big. I get 4 to 6k visits per day (200 to 300 000 impressions). My main table, which is causing the problem is around 200 to 300 000 rows, so it's not so big at all. Here's its architecture:

`id` mediumint(7) NOT NULL auto_increment,
`filename` char(80) collate cp1251_bulgarian_ci NOT NULL,
`cat` tinyint(2) NOT NULL default '0',
`uploader` varchar(25) collate cp1251_bulgarian_ci NOT NULL,
`uploader_id` mediumint(8) NOT NULL default '2',
`commented` tinyint(2) default NULL,
`times_downloaded` mediumint(6) NOT NULL default '0',
`vote` tinyint(1) default NULL,
`date` char(10) collate cp1251_bulgarian_ci NOT NULL,
`edited_by` varchar(25) collate cp1251_bulgarian_ci default NULL,
`rating` smallint(5) NOT NULL default '0',
`rating_order` smallint(5) default NULL,
`rated_by` char(255) collate cp1251_bulgarian_ci default NULL,
KEY `uploader_id` (`uploader_id`),
KEY `cat` (`cat`),
KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251 COLLATE=cp1251_bulgarian_ci AUTO_INCREMENT=351077 ;

I optimized it as much as I can. The data that is stored is the least that's possible.

When I run the query

SELECT id, filename FROM pictures WHERE cat = 14 ORDER BY id DESC LIMIT 7938, 42;

the MySQL server processes the request for 0.09 sec, which is very slow. And I ran the query when there wasn't anybody in the website. Normally there are around 100 - 120 people online and 200+ in rush hours which causes av. load to jump up to 5! When that happens the queries take even longer time to execute (~10secs each)!

Here's what the slow log normally shows:

# Time: 091118 3:16:46
# Query_time: 4 Lock_time: 0 Rows_sent: 42 Rows_examined: 27889

The query is simple as possible, the table has indexes on the correct column and uses InnoDB, but still ..
I tuned the my.cnf file.

port = 3306
socket = /var/lib/mysql/mysql.sock



query_cache_type = 1
query_cache_limit = 16M
query_cache_size = 16M
max_connections = 200
max_allowed_packet = 16M
max_connect_errors = 10
interactive_timeout = 25
wait_timeout = 15
connect_timeout = 10
table_cache = 1024
thread_cache_size = 256
tmp_table_size = 8M
max_heap_table_size = 8M
key_buffer_size = 256M
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 1M
join_buffer_size = 1M
myisam_sort_buffer_size = 8M
thread_concurrency = 8

long_query_time = 2

innodb_buffer_pool_size = 300M
innodb_additional_mem_pool_size = 20M

innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 50

innodb_thread_concurrency = 8

I tried about a 100 different combinations, but still no effect! Turned on the log-queries-not-using-indexes, but these queries are faster than others (0.00)!

Would you help me solve my problem, please?
Thanks in advance!

Options: ReplyQuote

Subject Views Written By Posted
Very Slow Simple Query 4994 Martin Tsvetanov 11/17/2009 09:08PM
Re: Very Slow Simple Query 1960 Rick James 11/19/2009 11:29PM
Re: Very Slow Simple Query 1724 Martin Tsvetanov 11/25/2009 12:47PM
Re: Very Slow Simple Query 1517 Rick James 11/25/2009 11:46PM
Re: Very Slow Simple Query 1516 Martin Tsvetanov 11/26/2009 04:33AM
Re: Very Slow Simple Query 1574 Rick James 11/26/2009 01:03PM
Re: Very Slow Simple Query 1438 Martin Tsvetanov 11/26/2009 01:41PM
Re: Very Slow Simple Query 1580 Rick James 11/27/2009 02:19PM
Re: Very Slow Simple Query 1507 Martin Tsvetanov 11/27/2009 04:09PM
Re: Very Slow Simple Query 1560 Rick James 11/27/2009 11:38PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.