solved MySQL performance

Our server setup is more than likely slightly different to most JamRoom setups in that we run a dedicated remote MySQL server (2GB RAM & SSD). This serves all our sites (20+) across 5 webservers.

The JamRoom site is on it's own dedicated server as it is the only php site we run (the rest are Ruby on Rails).

When we run the performance check we get quite a bad SQL score in the mid 44's, which gives us an overall score of 190ish (Quite bad).

On our test server which has it's own installation of MySQL, I followed the details on here https://www.jamroom.net/brian/documentation/guides/1265/optional-mysql-optimizations-for-larger-servers and immediately got a performance boost.

These same changes have been applied to the live server but have no effect because of the remote MySQL server.

I don't think the site is slow and we've had no complaints.

Just wondering if anyone else has any experience of a JamRoom setup with a remote MySQL server?

Cheers

Dave
updated by @white-agency: 02/24/16 08:40:36PM

44 seconds for the MySQL test is pretty long - even for a remote server. Is the MySQL server on the same network subnet as your Jamroom server? Ideally it hangs on the same switch. The test runs 12,000 queries, so if there is a few milliseconds of latency then it could cause the time to go way up even though single page performance will be acceptable.

The performance test doesn't take network latency into consideration, so in your case it could be giving you a low score even though it's OK. The idea with the test is to give you a general idea of the performance - if your JR "feels OK" to you, then it's fine. I've noticed that on sites where MySQL is on the same server and there is a very low score, Jamroom feels very slow as well.


--
Brian Johnson
Founder and Lead Developer - Jamroom
https://www.jamroom.net

why don't you run mysql on localhost unless its underpowered
a dedicated server for mysql is fine however 20 plus sites and only 2 gigs ram might be an issue too
i have mine on localhost
i have a dozen sites jr being the hugest
i have 16 gigs i think alocated to mysql (i use mariadb not mysql) buffers and caches

my highest performance test score was 3650
i often get over 3000 but my guess is 2700 would be the lower side of average
note i use mysqltuner.pl and tune it to perfection
low side i get a 3.4 ish on the db tests

those tips brian gave in the documentation is an ok starting point b7t after that run mysqltuner.pl ( perl mysqltuner.pl u might have to unstall it or update it 1st)
run that every 48 hours till u get real good results u might have to ignore the optimize tables and increase join buffer size (mariiadb will fix the join buffer issue)
but all other recomendations are good to follow as long as u give it 24-48 hours between tunups


--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities

Don't know about the sql server being on the same switch but it's in the same data centre ! All our other sites across the other servers are running fast; a couple of these servers are in a different data centre to the sql server and we have no latency issues with them.

We run a separate sql server for security and maintenance reasons. Easier to look after 1 rather than 5! It's specifically built as an SQL server by the hosting company ( and looked after by them ) who have a lot more experience than we do !

I've shown the site to other developers who are not familiar with it and they can't see a problem with the speed. I think because of the low score from the performance check it just got me wondering if there was a problem when in fact there isn't.

Cheers

Dave

run the mysql tuner script
the more databases you add that require more ram, more buffers and cache space etc the more important it is to do tuning

if all your sites are compatible upgrade to mariadb
the 1stseveral days you will think it was a mistake but after running the tuner a few times and folowing the suggestions you will have better performance


--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities

heres what i want you to do

login through ssh as root
type perl mysqltuner.pl copy and post the results here
if that says not found

paste this command


1 line at a time paste 1st line hit enter wait to complete then 2nd etc then 3rd the third will give you the tuner output and suggestions

after you do that ill give you tuneup suggestions i might need a couple other things fter i see the results'
like total number of tables etc
repeat the runs every 48 hours
in a week you should see a real improvement


--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities