Support Forums

optimizing mysql queries

This is a discussion on optimizing mysql queries within the Technical Support forums, part of the Technical Support Forums category; What version of 68 Classifieds are you running? Example: V4.1.10 Dveloper What template are you using? Default Please describe in ...


Go Back   68 Classifieds Forums > Technical Support Forums > Technical Support

Reply
 
Thread Tools Display Modes
Old 09-25-2010, 11:44 AM   #1
Customer
 
Join Date: Jun 2008
Location: Maldives
Posts: 134
Rep Power: 16
business is on a distinguished road
Default optimizing mysql queries

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?
__________________
v 4.2.0 Developer - Default Template
business is offline   Reply With Quote
Old 09-26-2010, 08:19 AM   #2
curmudgeon
 
Join Date: Mar 2006
Posts: 5,413
Rep Power: 138
Lhotch is a glorious beacon of light Lhotch is a glorious beacon of light Lhotch is a glorious beacon of light
Default

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

Knowledge learned is more valuable than knowledge given.
Lhotch is offline   Reply With Quote
Old 09-26-2010, 03:11 PM   #3
Customer
 
Join Date: Jun 2008
Location: Maldives
Posts: 134
Rep Power: 16
business is on a distinguished road
Default

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
__________________
v 4.2.0 Developer - Default Template
business is offline   Reply With Quote
Old 09-26-2010, 07:08 PM   #4
Genius At Work
 
bowers01's Avatar
 
Join Date: May 2008
Location: Geelong, Victoria, Australia
Posts: 1,089
Rep Power: 35
bowers01 is on a distinguished road
Default

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
__________________
Nick Bowers
68c v4.1.10 Developer Custom Template
bowers01 is offline   Reply With Quote
Old 09-27-2010, 08:31 AM   #5
curmudgeon
 
Join Date: Mar 2006
Posts: 5,413
Rep Power: 138
Lhotch is a glorious beacon of light Lhotch is a glorious beacon of light Lhotch is a glorious beacon of light
Default

Quote:
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.
__________________
Larry

Knowledge learned is more valuable than knowledge given.
Lhotch is offline   Reply With Quote
Old 09-27-2010, 09:42 AM   #6
Customer
 
Join Date: Jun 2008
Location: Maldives
Posts: 134
Rep Power: 16
business is on a distinguished road
Default

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>
__________________
v 4.2.0 Developer - Default Template
business is offline   Reply With Quote
Old 09-27-2010, 10:10 AM   #7
68 Classifieds Staff
 
Eric Barnes's Avatar
 
Join Date: Mar 2006
Location: Belmont, NC
Posts: 5,489
Rep Power: 135
Eric Barnes is just really nice Eric Barnes is just really nice
Default

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.
__________________
Eric Barnes
68 Classifieds Developer
Customer Area | Issue Tracker | Documentation | 68C Mods | 68 @ Twitter | My Modules
Eric Barnes is offline   Reply With Quote
Old 09-27-2010, 10:12 AM   #8
curmudgeon
 
Join Date: Mar 2006
Posts: 5,413
Rep Power: 138
Lhotch is a glorious beacon of light Lhotch is a glorious beacon of light Lhotch is a glorious beacon of light
Default

Quote:
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.
__________________
Larry

Knowledge learned is more valuable than knowledge given.
Lhotch is offline   Reply With Quote
Old 09-27-2010, 10:51 PM   #9
Genius At Work
 
bowers01's Avatar
 
Join Date: May 2008
Location: Geelong, Victoria, Australia
Posts: 1,089
Rep Power: 35
bowers01 is on a distinguished road
Default

�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
__________________
Nick Bowers
68c v4.1.10 Developer Custom Template
bowers01 is offline   Reply With Quote
Old 09-28-2010, 09:24 AM   #10
curmudgeon
 
Join Date: Mar 2006
Posts: 5,413
Rep Power: 138
Lhotch is a glorious beacon of light Lhotch is a glorious beacon of light Lhotch is a glorious beacon of light
Default

Quote:
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.
__________________
Larry

Knowledge learned is more valuable than knowledge given.

Last edited by Lhotch; 09-28-2010 at 09:39 AM.
Lhotch is offline   Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
2 Simple Queries - Perhaps? Unregistered Pre Sales Questions 3 07-08-2009 08:07 AM
pre sale customisation queries Unregistered Pre Sales Questions 4 04-30-2009 10:42 AM
memory Usage & Queries marktudor Technical Support 3 03-13-2009 08:27 AM
are site search queries listed anywhere? UME Technical Support 3 10-01-2008 07:17 PM


All times are GMT -4. The time now is 07:19 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2011, Jelsoft Enterprises Ltd.
Search Engine Friendly URLs by vBSEO 3.2.0