Tables with 30mln entries are slow. Optimize MySQL or switch to mongodb?

Question

I have a simple mysql db running on one server with two tables: products and reviews. products table has about 10 million entries and reviews table has about 30 million entries.

The whole Db is about 30Gb. I feel like it's getting slow and I'm wondering what should I do about it. I created indexes, but it didn't help. For example, products table have category field and when I do a simple select * from products where category=2 - it is just slow.

Will switching to mongodb help me in this situation or I can solve this just by optimizing Mysql somehow? In this case should I do sharding or size of tables is not that big and it's possible to optimize the other way?

Tables and my.cnf

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL auto_increment,
  `product_title` varchar(1000) NOT NULL,
  `product_id` varchar(100) NOT NULL,
  `title` varchar(1000) NOT NULL,
  `image` varchar(1000) NOT NULL,
  `url` varchar(1000) NOT NULL,
  `price` varchar(100) NOT NULL,
  `reviews` int(11) NOT NULL,
  `stars` float NOT NULL,
  `BrowseNodeID` int(11) NOT NULL,
  `status` varchar(100) NOT NULL,
  `started_at` int(15) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `id_index` (`BrowseNodeID`),
  KEY `status_index` (`status`),
  KEY `started_index` (`started_at`),
  KEY `id_ind` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13743335 ;


CREATE TABLE IF NOT EXISTS `reviews` (
  `id` int(11) NOT NULL auto_increment,
  `product_id` varchar(100) NOT NULL,
  `product_title` varchar(1000) NOT NULL,
  `review_title` varchar(1000) NOT NULL,
  `content` varchar(5000) NOT NULL,
  `author` varchar(255) NOT NULL,
  `author_profile` varchar(1000) NOT NULL,
  `stars` float NOT NULL,
  `owner` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `product_id` (`product_id`),
  KEY `id_index` (`product_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=48129737 ;

Some info from my.cnf

set-variable    = query_cache_size=1512M
set-variable    = thread_cache_size=8
thread_concurrency = 8
skip-innodb
low-priority-updates
delay-key-write=ALL

key_buffer_size = 100M
max_allowed_packet = 1M
#table_open_cache = 4048
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 50M
set-variable    = table_cache=256
set-variable    = query_cache_limit=1024M
set-variable    = query_cache_size=1024M

What's "slow"? No detail to the tables involved, the indexes or the query/queries involved -- sounds more like you want justification to switch to mongodb. — OMG Ponies, Jan 20 '11 at 01:34
What is the selectivity of the category column? Perhaps an index won't help that select * at all .... — Mitch Wheat, Jan 20 '11 at 01:36
@Chris Henry the whole my.cnf? It's big. Any particular variables? — Arty, Jan 20 '11 at 01:38
Buffer pool sizes would be most helpful, depending on whether you're using MyISAM or InnoDB — Chris Henry, Jan 20 '11 at 01:42
You have duplicate key on product_id column. Drop one and change the varchar(100) to INT if appropriate. This will reduce the index size and gain performance. — shantanuo, Jan 20 '11 at 08:10

score 3 · Answer 1 · answered Jan 20 '11 at 03:13

3

Based on your my.cnf, it looks as though your key_buffer_size is way too small, so you're going to disk for every read. Ideally, that value should be set larger than the total size of your MyISAM indexes.

Before you go changing DB technologies, you may also want to consider changing your table type to InnoDB. Your my.cnf has it disabled, right now. I've gotten pretty stellar performance out of a 300M row table with smart indexes and enough memory. InnoDB will also give you some leeway with longer running reads, as they won't lock your entire table.

answered Jan 20 '11 at 03:13

Chris Henry

11,914
3
30
31

Thanks you for your advice. 300M? That's impressive! All at the same server and in a single table? No sharding? How much RAM did you have? Were you able to make simple queries with WHERE or order? – Arty Jan 20 '11 at 04:25
1

@Arty - http://stackoverflow.com/questions/4419499/mysql-nosql-help-me-to-choose-the-right-one-on-a/4421601#4421601 (my desktop) – Jon Black Jan 20 '11 at 12:37

Tables with 30mln entries are slow. Optimize MySQL or switch to mongodb?

1 Answers1