First Byte Time for the opencart ecommerce site is huge

Question

I checked the site stats on http://www.webpagetest.org/ for the ecommerce site : It gave the following report ----

Document Complete   Fully Loaded
Load Time   First Byte  Start Render    Speed Index DOM Elements    Time    Requests    Bytes In        Time    Requests    Bytes In
First View  20.995s 15.718s 16.492s 19760   1177    20.995s 117 2,357 KB    22.904s 161 2,827 KB
Repeat View 9.804s  8.200s  8.623s  9596    1177    9.804s  10  351 KB  11.276s 37  476 KB

I checked my server with topcommand & checked that mysql is taking 99% CPU% time and 214:26.79 in TIME+.

Earlier the mysql tables were not optimized so, I created the indexes of those table. After that, the loading time has reduced considerably. Now I am facing problem with the First Byte time. Following is the mysql variables :

mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_flush_log_at_trx_commit';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_open_files';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| innodb_open_files | 300   |
+-------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_table_locks';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_table_locks | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'innodb_thread_concurrency';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_thread_concurrency | 0     |
+---------------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'join_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| join_buffer_size | 131072 |
+------------------+--------+
1 row in set (0.00 sec)

mysql> show variables like 'key_buffer_size';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| key_buffer_size | 16777216 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'max_delayed_threads';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| max_delayed_threads | 20    |
+---------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'max_heap_table_size';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
+---------------------+----------+
1 row in set (0.00 sec)

mysql> show variables like 'max_tmp_tables';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| max_tmp_tables | 32    |
+----------------+-------+
1 row in set (0.01 sec)

mysql> show variables like 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 8     |
+-------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'tmp_table_size';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+
1 row in set (0.00 sec)

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'myisam_sort_buffer_size';
+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| myisam_sort_buffer_size | 8388608 |
+-------------------------+---------+
1 row in set (0.00 sec)

I think the First Byte delay is mainly due to the mysql configuration or opencart framework.I have already taken steps to optimize nginx running the site. How can I reduce this first byte time ? Please help.

Answer 1

I solved the issue. The problem was with the opencart databse indexing system. Because they have not indexed the tables properly - some queries related to product_tags & url_alias used to take a long period to execute. And since they lock those tables, they are inaccessible to other queries in the sequence.Creating a considerable amount of delay for the first-byte to be recived at the client side.

The solution is to create indexes to all these table for which the queries are taking considerably long time. My delay time has reduced to 1/10th of what it was. (FOr further informtation & details, check the opencart forums - this is a very common issue with OC users)