MySQL Forums
Forum List  »  Performance

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:

CREATE TABLE IF NOT EXISTS `pictures` (
`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,
PRIMARY KEY (`id`),
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.

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock

skip-name-resolve
skip-bdb
skip-networking
skip-locking

default-character-set=cp1251
character-set-client=cp1251
character_set_server=cp1251

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

log_slow_queries=/var/log/mysqld.slow.log
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
5393
November 17, 2009 09:08PM
2060
November 19, 2009 11:29PM
1776
November 25, 2009 12:47PM
1557
November 25, 2009 11:46PM
1550
November 26, 2009 04:33AM
1613
November 26, 2009 01:03PM
1461
November 26, 2009 01:41PM
1615
November 27, 2009 02:19PM
1549
November 27, 2009 04:09PM
1606
November 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.