Improving Performance

We run a Magento 1.9 online store. It contains 110,000 products. We are running into issue around long page load times for category pages and product pages.

Dedicated WebApp server specs: – Apache 2.4 – PHP 5.6 – PHP.ini heavily tweaked, along with an 8GB Redis cache – 6 CPU Cores – 16GB Memory – SSD Storage

Dedicated Database Server: – MySQL 5.6 (tuned with MySQL Tuner). – 6 CPU Cores – 16GB Memory – SSD Storage

The site hasn’t launched yet as we are trying to fix these performance issues.

Seems to me the bottleneck is the database. I’ve read nearly every article I could find on improving performance but we are still at 6-11s load times.

[mysqld]  innodb_buffer_pool_instances = 11 innodb_log_file_size = 1024M innodb_buffer_pool_size = 12000M query_cache_size = 64M query_cache_type = 0 query_cache_limit = 2M key_buffer_size = 36M max_heap_table_size = 16M tmp_table_size = 256M join_buffer_size = 8M max_allowed_packet = 16M innodb_lock_wait_timeout=300 innodb_read_io_threads = 8 innodb_write_io_threads = 8 innodb_thread_concurrency = 12 max_connections = 500 thread_cache_size = 32 thread_concurrency = 12 innodb_flush_log_at_trx_commit = 2 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES  [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid 

Actual server resource usage is low. I’d love any recommendations on how we can improve the load speed and increase server resource usage of CPU and Memory.