Server Move - Optimization - Time to First Byte

[ABGenc]

Since PHP 5.5 was very close to its end of life and the VPS my site is running on had CentOS 5.11 with cPanel which did not support PHP 5.6, I had to move to a new one with newer OS .

The new server has CentOS 6.7 x64, 2GB 2 CPU, 60 GB disk, Apache 2.4.18  PHP 5.6.21 and MySQL 5.6.30 .

The previous server was checked by @ASTRAPI and although I tried to pass all of the configurations to the new one, I have seen that the Time to First Byte for New Content Stream is rather big. Here are the results of the latest test I have done.

Running mysqltuner.pl had only one warning which was to increase join_buffer_size like ;

join_buffer_size (> 6.0M, or always use indexes with joins)

I have started from 1M and now it always tells me to increase. Does this mean the queries within the suite uses joins that does not have indexes ? How much should I increase the size ?

Added to that, I have not installed any caching yet ( in previous version I have problems with memcache(d) )  Any advice on what to install for this configuration ? 

 

[RevengeFNF]

You should not increase join buffer size. 6Mb is already more than it should be. I set mine to 2Mb only.

If you need help configuring the server, i can give you a hand.

[ABGenc]

3 minutes ago, RevengeFNF said:

You should not increase join buffer size. 6Mb is already more than it should be. I set mine to 2Mb only.

If you need help configuring the server, i can give you a hand.

That is what I have read too but mysqltuner.pl keeps telling me to increase. I will share the information soon and we can discuss on it a bit ( and that way I can learn ) if that is not a problem for you. If we can not get it solved soon I will ask for your help  

[RevengeFNF]

Ok, no prob.

[ABGenc]

Here is the most recent result from mysqltuner.pl ( It seems I have raised the buffer size to 12M at some time of midnight )  

Use of implicit split to @_ is deprecated at mysqltuner.pl line 3243 (#1)
    (D deprecated, W syntax) It makes a lot of work for the compiler when you
    clobber a subroutine's argument list, so it's better if you assign the results
    of a split() explicitly to an array (or list).

 >>  MySQLTuner 1.6.12 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

[--] Skipped version check for MySQLTuner script
Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQ
Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQ
[OK] Currently running supported MySQL version 5.6.30
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA
[--] Data in MyISAM tables: 3G (Tables: 1076)
[--] Data in InnoDB tables: 512K (Tables: 15)
[!!] Total fragmented tables: 43

-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[--] There are 605 basic passwords in the list.

-------- CVE Security Recommendations --------------------------------------------------------------
[OK] NO SECURITY CVE FOUND FOR YOUR VERSION

-------- Performance Metrics -----------------------------------------------------------------------
[--] Up for: 15h 29m 22s (2M q [52.580 qps], 106K conn, TX: 32G, RX: 6G)
[--] Reads / Writes: 92% / 8%
[--] Binary logging is disabled
[--] Physical Memory     : 2.0G
[--] Max MySQL memory    : 1.0G
Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQ
[--] Other process memory: 775.9M
[--] Total buffers: 171.0M global + 13.6M per thread (35 max threads)
[--] P_S Max memory usage: 390M
[--] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 847.5M (41.38% of installed RAM)
[OK] Maximum possible memory usage: 1.0G (50.69% of installed RAM)
Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQ
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (2/2M)
[OK] Highest usage of available connections: 60% (21/35)
[OK] Aborted connections: 0.01%  (8/106986)
[OK] Query cache is disabled by default due to mutex contention on multiprocessor machines.
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 89K sorts)
[!!] Joins performed without indexes: 345
[OK] Temporary tables created on disk: 16% (5K on disk / 32K total)
[OK] Table cache hit rate: 89% (2K open / 2K opened)
[OK] Open file limit used: 30% (3K/10K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)

-------- ThreadPool Metrics ------------------------------------------------------------------------
[--] ThreadPool stat is disabled.

-------- Performance schema ------------------------------------------------------------------------
[--] Performance schema is enabled.
[--] Memory used by P_S: 390.3M
[--] Sys schema isn't installed.

-------- MyISAM Metrics ----------------------------------------------------------------------------
[OK] Key buffer used: 100.0% (33M used / 33M cache)
[OK] Key buffer size / total MyISAM indexes: 32.0M/1.4G
[OK] Read Key buffer hit rate: 99.9% (168M cached / 89K reads)
[!!] Write Key buffer hit rate: 48.8% (1M cached / 577K writes)

-------- AriaDB Metrics ----------------------------------------------------------------------------
[--] AriaDB is disabled.

-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 59.0M/512.0K
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 12.87% (486 used/ 3776 total)
[OK] InnoDB Read buffer efficiency: 97.91% (22781 hits/ 23267 total)
[!!] InnoDB Write Log efficiency: 0% (1 hits/ 0 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1 writes)

-------- TokuDB Metrics ----------------------------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ----------------------------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -----------------------------------------------------------------------
[--] Galera Synchronous replication: NO
[--] No replication slave(s) for this server.
[--] This is a standalone server.

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
Variables to adjust:
    join_buffer_size (> 12.0M, or always use indexes with joins)

and this is my.cnf

[mysqld]
default-storage-engine=MyISAM

key_buffer_size=32M
sort_buffer_size=1M
join_buffer_size=12M
read_buffer_size=128K

query_cache_size=32M
query_cache_limit=4M
thread_cache_size=32
table_open_cache=2048

tmp_table_size=32M

interactive_timeout=20
connect_timeout=20
wait_timeout=30

open_files_limit=10000
max_allowed_packet=268435456
max_connections=35
max_connect_errors=20
max_heap_table_size=32M

innodb_file_per_table=1
innodb_buffer_pool_instances=1
innodb_buffer_pool_size=61865984

[mysqldump]
quick
max_allowed_packet= 1024M

 

[ASTRAPI]

Never transfer the settings from one server to another !

 

[ABGenc]

7 hours ago, ASTRAPI said:

Never transfer the settings from one server to another !

Well that was not meant to be copying the conf files from one to another, just getting some hints  Anyway you are right, every server should be considered seperately and that is what I have done last night ;

To get some hints on how to optimize MySQL, I was using mysqltuner.pl but I thought another program might be helpful which really has been. I have installed tuning-primer.sh and it directly told me that ;

key_buffer_size=32M

  was too small. It was clearly seen in mysqltuner.pl that I was using %100 of it but did not display any warning but tuning-primer was telling me that the buffer could be raised up to 1/4 of the physical memory which is what I have done . Raised it to 512M and now watching how much it will be used in 48 hours.

The second warning was "long_query_time" not set. The default was 10s and system only considered queries that last longer than 10 seconds as "slow" and that could be misleading. I have set it to 3s and now monitoring for slow queries.

The third warning was  for ;

query_cache_size=32M

being it too large. I have lowered it to 4M and it still says it is high.

The last change I have done was  to lower join_buffer_size to 2M as it was too high which @RevengeFNF had also told.

The last thing I have done was to enable opcache on the server. 

Doing all of these changes and enabling opcache, I was able to get a better overall performance. The change can be easily seen in the comparison below.

http://www.webpagetest.org/video/compare.php?tests=160518_2B_AWD,160517_6Z_GQJ

I am planning to enable memcache after a few days of test and monitoring of the current config as I have seen a few times that the duo was best for IPS ;

 

[ASTRAPI]

You have a lot more to optimize for your database

Memcache will help a lot for IPS4 and not for IPS3 ...

[ABGenc]

6 minutes ago, ASTRAPI said:

You have a lot more to optimize for your database

Memcache will help a lot for IPS4 and not for IPS3 ...

I do not have any IPS3 installation on the server. I have two sites and they are both IPS4 so I believe memcache will be helpful.

Any hint for the optimization points?  

[ASTRAPI]

Yes memcache will help a lot then

I have to deep into the server to give some accurate recommendations ....

[ABGenc]

Just now, ASTRAPI said:

Yes memcache will help a lot then

I have to deep into the server to give some accurate recommendations ....

You are right, I thought you had some idea looking into the results I have submitted that is why I asked  

[ASTRAPI]

A few basics are:

That's a lot for your server:

13.6M per thread

You should decrease buffers a bit,,,,

Increase the max connections to:

max_connections = 150

 

As you don't have a lot of resources change also this:

innodb_buffer_pool_size= 2M

 

Restart mysql and check

 

It will be better but again you will need much more than that

[ABGenc]

I have not utilized innodb from the beginning and that is a point I should consider someday but it has a reason.

During the conversion from SMF to IPB I realised that the conversion was very very slow. Looking into the details I realised that default engine was set to Innodb and the new record creation was what was slowing it. Since the time was limited and I did not have much knowledge on Innodb optimization, I had to set it back to MyISAM and go on but I have read several posts telling InnoDB would perform better. That can be planned later, transition to InnoDB and performance tuning.

The idea of decreasing the max_connections was that a possible burst would exceed the available memory within the current buffer sizes ( as possible memory usage was related to buffer sizes x max_connections ) so I monitored the connections and they did not seem to go beyond 30 for some time so I set it to 35. Still monitoring and if I see a usage around %80 I will increase and if I increase that as you advised I have to decrease the buffer size.

I am willing to wait for 48 hours to be passed with these setting and see what the outcome is and then I will try your suggestions and will report back. Thanks a lot @ASTRAPI for your comments. 

[ASTRAPI]

That's why i ask you to decrease buffers so you can get more connections as 35 connections is very low and i am sure 100% that with a few traffic you will reach it...

For innnodb i recommend you also to get a 4GB ram server with a fast ssd

It is more easy to optimize it than Myisam if you know what to do

[ABGenc]

28 minutes ago, ASTRAPI said:

That's why i ask you to decrease buffers so you can get more connections as 35 connections is very low and i am sure 100% that with a few traffic you will reach it...

For innnodb i recommend you also to get a 4GB ram server with a fast ssd

It is more easy to optimize it than Myisam if you know what to do

Thanks for the tip.  That means it is best to stay with MyISAM at the moment cause I dont have the budget to move to a 4 GB / SSD disk server at the moment

[RevengeFNF]

InnoDB is still faster even if you don't have the Ram to put all your Database in the buffer. InnoDB is smart enough to put the most used pages in the buffer.

Imagine people with Databases with 300 or 500 Gb in size. They don't necessarily need to have 500Gb of Ram...

[ASTRAPI]

I didn't say that he will need equal ram as database size.

But using Innodb he will need more ram than using Myisam and as he don't have much ram that's why i recommend him to get more ram first.....

[RevengeFNF]

If he dedicates 1Gb to InnoDB buffer, i believe it will be enough to be faster than MyISAM and more secure. Just the fact that InnoDB don't have Table Locks, is a enough reason to change to it.

Off course the ideal situation, it would be to have more Ram, but not everyone have the funds to have it.

[ASTRAPI]

i believe it will be enough 

Don't thing so but he can try it

[ABGenc]

1 minute ago, ASTRAPI said:

i believe it will be enough 

Don't thing so but he can try it

I have created enough down-time last week for the server change so I will not risk myself for an unclear advanture  

[Zen Geek]

Any resolution to load times?

[ayuki]

someone have test  mod_socache_memcache in apache 2.4.20

[ABGenc]

On 31.05.2016 at 9:48 PM, Zen Geek said:

Any resolution to load times?

As I have stated, after increasing the key buffer size and installing opcache, the load times dramatically decreased. Monitoring the system and getting more hints from tuning-primer.sh I see that the performance is now much more better from the point I have started.

[The Old Man]

Hi,

Interesting topic. Is there a way to check if you have Op Cache and Memcache installed on your VPS? 

What is the difference, please?

How do you bench test performance, with GT Metrix?

Many thanks.

 

[ABGenc]

2 hours ago, The Old Man said:

Hi,

Interesting topic. Is there a way to check if you have Op Cache and Memcache installed on your VPS? 

What is the difference, please?

How do you bench test performance, with GT Metrix?

Many thanks.

 

Actually  I have WHM/cPanel on VPS and it has modules that I can check if they are installed or not. Opcache is for PHP caching as far as I get but memcache is for SQL which I have not implemented yet.

I test the performance differences using following site ;

http://www.webpagetest.org/