| Performance problem or reached the limit of MySQL? [message #5] |
Fri, 11 August 2006 06:22  |
 Nacho
Messages: 1
Registered: August 2006 |
Junior Member |
|
|
Hi all!
First of all, congratulations for the Web :)
I think I'm having performance problems with a very small MyIsam table but I'm not sure if it's a configuration/design/hardware problem or have I reached the limit of MySQL.
The table is very simple and small (about 20000 rows). It contains the price and a short description of some products, and a timestamp indicating the moment of the insert:
CREATE TABLE products (
id int(11) NOT NULL default '0',
timestamp int(11) NOT NULL default '0',
price int(11) NOT NULL default '0',
description varchar(256) NOT NULL default '',
KEY `idx_products_id` (`id`),
KEY `idx_products_timestamp` (`timestamp`),
KEY `idx_products_price` (`price`)
) ENGINE=MyISAM
The database host is Intel Xeon 2.80GHz-4Gb RAM-Linux (kernel 2.6.15) using my-huge.cnf, and I'm querying the database through a separate Apache+PHP host using persistent connections.
The query is also very simple:
SELECT * FROM products WHERE price > $value1 AND an_precio < $value2 ORDER BY (timestamp) DESC LIMIT 100;
With random $value1 and $value2, mytop shows only 50 qps (no inserts or updates simultaneously, just this query).
After reading some useful posts on the Web, I removed idx_products_price index ( IGNORE INDEX(idx_products_price) ). This modification increases the qps to 350 (is normal that increase with only ignoring that index??), but I think the value of 350 qps is still low. My question is if is it a performance problem or have I reached the limit of this MySQL configuration and 350 qps is a good value?
Thanks in advance for your comments.
Regards,
Nacho.
|
|
|
| Re: Performance problem or reached the limit of MySQL? [message #7 is a reply to message #5 ] |
Fri, 11 August 2006 06:49  |
Peter
Messages: 405
Registered: August 2006 |
Senior Member
Super Guru |
|
|
Hi,
This is not MySQL taken to its limit. 20.000 rows table is very small.
First you probably did not convert query fully:
SELECT * FROM products WHERE price > $value1 AND an_precio < $value2 ORDER BY (timestamp) DESC LIMIT 100;
There is no an_precio column in the table so is it price ?
If so MySQL has to options running this query - first is to use range on price index do full sort on the range and return rows. This works well if range is small.
Second to retrieve rows in sorted order using index on timestamp - this works well if range is large so it is easy to find rows from this range doing reverse index scan.
Sometimes neither of this could be effective enough.
One solution is to use fixed price ranges, for example#
1-100$
100-400$ etc and pass price_range=5 rather than real price.
In his case (price_range,timestamp) index would be very efficient.
I blogged yesterday about very similar problem:
http://www.mysqlperformanceblog.com/2006/08/10/using-union-t o-implement-loose-index-scan-to-mysql/
You also can use similar approach to use number of unios with order by and apply extra price filtering clause if you want partial pre-computed ranges.
I know this is ugly...
Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/
|
|
|
Members
Search
Help
Register
Login
Home
