optimizing mysql queries

[business +]

What version of 68 Classifieds are you running?
Example: V4.1.10 Dveloper

What template are you using?
Default


Please describe in detail the issue you are having:
On running mysql optimizer tool, the following suggestions were given:

++++++++++++++++++
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
++++++++++++++++++++

It was also highlighted that "many queries are copying to tmp table on disk. This will also affect the performance of the server"

Any suggestions on getting this sorted out?

[Lhotch +]

Are you on shared hosting or do you manage and have control of your own server?

[business +]

I have a dedicated server with following specs:
Xeon 3450(4x2.66GHz,8MB)
8GB DDR3 RAM
500GBs SATA-II

Yes, I have full control of the server. thanks

[bowers01 +]

if you are having issues with speed, mainly sql it is because of the sata drives, you need to move to sas or ssd.
I am suprised a hi spec server like that came with sata.

Nick

[Lhotch +]

Originally Posted by business

I have a dedicated server with following specs:
Xeon 3450(4x2.66GHz,8MB)
8GB DDR3 RAM
500GBs SATA-II

Yes, I have full control of the server. thanks

Your best bet is to tune the OS, mysql and get faster drives. SSD (expensive) would be the best.

[business +]

Here's some more info I got from host. What do you about the temp table suggestin:

=======
The most correct way to deal with this would be to check/rewrite your database queries and tables structure so temp tables won't be created. For example, this query:

SELECT p.id, p.owner, p.title, p.featured, p.section, p.shortDescription, p.description, p.price, p.dateadded, p.expiration, p.display, p.pHighlighted, p.pBold, p.hitcount, p.url, u.username, u.state, u.city, u.country, u.extra, u.extra2, u.extra3 ,store.sTitle,store.sDescription,store.sImage FROM trade_listings AS p LEFT JOIN trade_users AS u ON p.owner = u.id LEFT JOIN trade_store AS store ON (p.owner = store.sUserId) WHERE 1=1 AND p.price BETWEEN 0 AND 99999999999 AND p.expiration > NOW() AND p.display = "Y" GROUP BY p.id, p.owner, p.title, p.featured, p.section, p.description, p.dateadded, p.expiration, u.state, u.city, u.country ORDER BY dateadded desc, dateadded DESC LIMIT 160, 40;


needs to be re-done as it currently forces MySQL server to create a temporary table, copy data there and perform a file based data sorting:

mysql> desc SELECT p.id, p.owner, p.title, p.featured, p.section, p.shortDescription, p.description, p.price, p.dateadded, p.expiration, p.display, p.pHighlighted, p.pBold, p.hitcount, p.url, u.username, u.state, u.city, u.country, u.extra, u.extra2, u.extra3 ,store.sTitle,store.sDescription,store.sImage FROM trade_listings AS p LEFT JOIN trade_users AS u ON p.owner = u.id LEFT JOIN trade_store AS store ON (p.owner = store.sUserId) WHERE 1=1 AND p.price BETWEEN 0 AND 99999999999 AND p.expiration > NOW() AND p.display = "Y" GROUP BY p.id, p.owner, p.title, p.featured, p.section, p.description, p.dateadded, p.expiration, u.state, u.city, u.country ORDER BY dateadded desc, dateadded DESC LIMIT 160, 40;
+----+-------------+-------+--------+--------------------------+---------+---------+---------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+--------------------------+---------+---------+---------------------------+-------+----------------------------------------------+
| 1 | SIMPLE | store | system | NULL | NULL | NULL | NULL | 0 | const row not found |
| 1 | SIMPLE | p | ref | display,price,expiration | display | 4 | const | 13175 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | u | eq_ref | PRIMARY | PRIMARY | 4 | business_b2btrade.p.owner | 1 | |
+----+-------------+-------+--------+--------------------------+---------+---------+---------------------------+-------+----------------------------------------------+
3 rows in set (0.18 sec)

mysql>

[Eric Barnes]

I believe you should be able to add an index on the sUserId in the store table and then an index on the "display,price,expiration" in the listings table. But keep in mind this is the search query so different things could be searched. But those are generally always used.

[Lhotch +]

Originally Posted by business

Here's some more info I got from host. What do you about the temp table suggestin:

=======
The most correct way to deal with this would be to check/rewrite your database queries and tables structure so temp tables won't be created. For example, this query:

SELECT p.id, p.owner, p.title, p.featured, p.section, p.shortDescription, p.description, p.price, p.dateadded, p.expiration, p.display, p.pHighlighted, p.pBold, p.hitcount, p.url, u.username, u.state, u.city, u.country, u.extra, u.extra2, u.extra3 ,store.sTitle,store.sDescription,store.sImage FROM trade_listings AS p LEFT JOIN trade_users AS u ON p.owner = u.id LEFT JOIN trade_store AS store ON (p.owner = store.sUserId) WHERE 1=1 AND p.price BETWEEN 0 AND 99999999999 AND p.expiration > NOW() AND p.display = "Y" GROUP BY p.id, p.owner, p.title, p.featured, p.section, p.description, p.dateadded, p.expiration, u.state, u.city, u.country ORDER BY dateadded desc, dateadded DESC LIMIT 160, 40;


needs to be re-done as it currently forces MySQL server to create a temporary table, copy data there and perform a file based data sorting:

Im not sure what you are after here. Are you looking to completely redesign the database tables and all the queries? If thats something you are capable of doing yourself and dont mind spending 10's of hours on it, knock yourself out. Thats even assuming that its possible to redesign the queries AND retain current functionality.

The fact of the matter is some inefficient queries exist because there is no better way to do them. As I have already explained once, the nature of extra fields and their configurablity makes them a bit tricky and resource intensive. If your going to hire someone to go through the 68c code and try and clean up queries and modify the tables it could literally cost you thousands if not more in man hours. And even then, they may not be able to make it any more efficient.


So that leaves you other options like throwing more memory in your server, faster disk drives (sata is junk designed for archival data that rarely gets used by the way) and tweak the OS kernel and mysql server config to get the best performance.

[bowers01 +]

�SATA: 7200 RPM, 4 ms latency, 9 ms random read, 10 ms random write, 70 mb/sec sustained xfer rate

�SAS: 15000 RPM, 2 ms latency, 3.5 ms random read, 4 ms random write, 125 mb/sec sustained xfer rate

So for database queries its going to be twice as fast.

You should be able to get the database to load fully into the ram, how big is the db? How much ram is actually being used? Are you using a control panel?

Nick

[Lhotch +]

Originally Posted by bowers01

•SATA: 7200 RPM, 4 ms latency, 9 ms random read, 10 ms random write, 70 mb/sec sustained xfer rate

•SAS: 15000 RPM, 2 ms latency, 3.5 ms random read, 4 ms random write, 125 mb/sec sustained xfer rate

So for database queries its going to be twice as fast.

You should be able to get the database to load fully into the ram, how big is the db? How much ram is actually being used? Are you using a control panel?

Nick

Those specs are a bit misleading.

SAS and SATA refee to the interface or how data is tranferred on the BUS. Drive RPM is the speed at which the platters spin, the faster a disk spins the faster the head can get to a spot on the platter to read/write data. Rotational disk speed is not dependent/related to interface type.

SAS uses full duplex communication and the SCSI communication protocol. SATA is half duplex and uses the ATA communication protocol.

SAS, typically runs at 3GBsec or 6GBsec and is point to point in nature so EACH device on the SAS bus has access to the full speed (ie 3GBs/6GBs) in both directions (ie to and from the drive).

SATA devices all share the same bandwidth on the bus.

SATA drives are best used for archival data or simple desktop systems. In mutli user environments they simply are inefficient due to the communication architecture.

SAS also supports multipathing which can under some OS's take advantage of load balancing etc over multiple adapters.