Recently , Our server provider told us there are many temp tables created by cart on disk , which cause Disk I/O problems. They need us to find the final solution for this either disable the function or fixed with otherw ways, otherwise our cart would be suspended. now it has already been suspended. but no luck to find the solution , it is nightmare for us .
[font=Verdana, Arial, Helvetica][size=2]below are samples.
-----
| Query | 613 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS products., descr1.product as product, MIN(prices.price) as price, descr1 |
| Query | 571 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS products., descr1.product as product, MIN(prices.price) as price, descr1 |
| Query | 567 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS products., descr1.product as product, MIN(prices.price) as price, descr1 |
| Query | 414 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS products., descr1.product as product, MIN(prices.price) as price, descr1 |
| Query | 128 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS products.*, descr1.product as product, MIN(prices.price) as price, descr1 |[/size][/font]
[font=Verdana, Arial, Helvetica][size=2]ANy ideas how to optimize these?[/size][/font]
[font=Verdana, Arial, Helvetica][size=2]thanks[/size][/font]
I replied in the other thread to you so I will paste here:
[quote name='martin1979' timestamp='1395673250' post='180013']
hi guys
our website was just suspended,
our host provider told us , There are many temp tables created on the DIsk.
We used csv files to import products about 100000
[/quote]
Is this a shared host? A VPS? CS Cart 3 can't run that many products on a shared host. You'll probably have to end up doing what I did and either increase the size of your /tmp or move the mysql tmp directory off of /tmp and put it somwhere else on the main disk partition (neither of which you can do on a shared host).
This is the same query I optimized. But I optimized speed not for temp table size on disk. To get it to stop using temporary tables there's some things to look at:
Temporary tables can be created under conditions such as these:
If there is an ORDER BY clause and a different GROUP BY clause, or if the ORDER BY or GROUP BY contains columns from tables other than the first table in the join queue, a temporary table is created.
DISTINCT combined with ORDER BY may require a temporary table.
If you use the SQL_SMALL_RESULT option, MySQL uses an in-memory temporary table, unless the query also contains elements (described later) that require on-disk storage.
In this case it is probably the order by and group by and there's not much you can do about having to use them. So I'm afraid your best option is to get a better server or use less products.
Thanks Atrix,
Currently we use VPS ( 4608MB Guaranteed RAM 120GB RAID-10 Disk Space 8x Priority 8+ CPUs 9000GB Premium Bandwidth
)
and Run cs cart 2.2.4 version , Maybe we need less the product since there is no good way to solve the problem. Also i am thinking to move the Database to some professional reomote Mysql Hosting . WIll it solve the problem? And will the cs cart 4 be available for large amount products?
Thanks
Hello martin1979,
Try to use Searchanize, it may help. This service was developed exactly for the cases when there are too many products and product features.
Thanks
[quote name='martin1979' timestamp='1395740792' post='180080']
Thanks Atrix,
Currently we use VPS ( 4608MB Guaranteed RAM 120GB RAID-10 Disk Space 8x Priority 8+ CPUs 9000GB Premium Bandwidth
)
and Run cs cart 2.2.4 version , Maybe we need less the product since there is no good way to solve the problem. Also i am thinking to move the Database to some professional reomote Mysql Hosting . WIll it solve the problem? And will the cs cart 4 be available for large amount products?
Thanks
[/quote]
The latest version of 4 supposedly handles lots of products ok.
You have a weird host, if you order the VPS you're supposed to be able to use as much I/O on it as you want I'd look for a better provider.
If you move the mysql to a remote server you face a hit in performance due to the data packets having to make remote connections and receive the large packets of data so I'd suggest staying away from it except as a last option.
What was the response from the helpdesk? The software should delete temporary tables when it is done with them.