Custom DB config on a dedicated server

[specky-brian]

We had a lot of sagepay issues since moving servers 3 weeks ago and after a lot of investigation it turned out to be memcache enabled causing the problem. There are one or two other sagepay failed payments so I looked at the database as research led me there. We have query caching off (probably as we were using memcache before) but before enabling it I wanted to know what settings you would advise and what to put in the my.cnf file

We are running a dedicated server with 16GB RAM and RAID SSD's

Not being able to run memcache I think we will use FScache instead. I've read also about APC but I don't know about that (we have a freelance systems admin we use who can install things we need). Do you recommend anything else?

Reading the systems admin guide to prestashop I was going to SET GLOBAL query_cache_size = 268435456; in MySQL and then

[mysqld]
innodb_file_per_table=1
local-infile=0
query_cache_size=268435456
query_cache_type=1
query_cache_limit=1048576

but I have also seen settings much higher than that.

 

Any advice would be welcome.

[phrasespot]

Without an idea of the traffic you receive and the size of your tables it is difficult to suggest database parameters.

​

16GB RAM with SSDs is pretty decent and I am surprised you also need caching of any type, unless of course you have a gargantuan catalog. Caching has its own downfalls as you've found out. Are you satisfied native or external caching is required?

 

Query caching off can be a good thing depending on other factors. Before you start tinkering with MySQL parameters, are you satisfied that the frequency of visits/orders/registrations warrant query caching?

[specky-brian]

Query caching has definitely helped however so far all other caching has either made problems or slowed down the site. The sys admin has recommended we implement APC caching to try and get down the time to first byte which is currently around 1s.

 

We only have around 1500 products and some customisation. Our visits are not huge but can peak quite high (11,000 visits) and the database is 850MB but I think half of that is uncleaned bot connection data.

[phrasespot]

Before attempting any changes turn on the PS profiling and see where there are bottlenecks.

850MB data is normal even for a relatively mid-range shop but it does not tie in with 11K peak rate unless you turned off all PS stat modules. At the peak rate you are quoting the DB should be much larger.

In any case it would benefit from cleaning various visitor and connection data. I am on the side of leaving visitor stat collection to server stat programs or Google Analytics instead of using PS.

[bellini13]

I concur, I would truncate those connection tables and get your stores database size to something more manageable, like 100mb or less. 

 

You could also have a table that is not indexed properly, so profiling or enabling mysql slow log would help identify slow/long running queries. 

 

I would not suggest tuning mysql out of the door, however it likely does need to be tweaked.  the convention here is to install mysql tuner scripts and run it after a week of straight up time.  the tuner script will analyze and suggest how to better tune mysql.

 

but you need to take is slow and small changes.  make a small change, wait another week and re-run the tuner and see how it effected the performance. 

[Dh42]

@phrasespot Awesome to see you back. 

 

 

@specky-brian What you can do totally depends on the performance that you want. Here are my general recommendations for setting up a speedy shop.

 

1. Use apc

 

2. Disable modules that are not used. Depending on how your theme is written an some are written very shoddy, you might have a bunch of modules loading that are not used. Some developers will do a simple display:none on a module for a specific page. That hides the module, but everything still loads. 

 

3. Combine and compress everything. More than the simple CCC in Prestashop. Turn on CCC in prestashop, then go through a tool like pingdom and see how many requests your site is loading. Again, there are shoddy themes and modules that add css and js files directly to the theme and those files are not combined or compress. I would add them to the global.css file and delete the reference. Also I would take a good hard look at the images and see what could be made into sprites. See a browser only has 8 download channels at a time. If you are loading 200 requests, it clogs the channels. 

 

4. I would consider offloading your image, css, and js files to a service like maxcdn or metacdn. It will take the stress off of your server for having to serve the files. 

 

5. If that does not make your site faster, I would start running debug profiling and see how many queries are hitting your database. Some modules do not use caching very well. For instance some menu modules query the database on every page load and build the menu of categories from that. If you have say 500 categories that will be a slow query. One thing I have done in the past is just to write a menu module in straight html. It is pain to update, but it is very fast. 

 

6. I would also look into a caching module. They are pretty good these days. Xtendify makes a pretty good one that I have used before. 

[phrasespot]

@Dh42 Thanks.  We should catch up on the gossip sometime