| CI runs slow with Nginx + PHP 7.1 + MariaDB |
Problem
I am running a CentOS 7 machine with Nginx 1.10.2 + PHP 7.1.10 (php-fpm) + MariaDB 10.1.28. Using this machine I am serving the following applications:
Steps to debug/resolve Activated php-fpm's and mariadb's slowlog (>= 3 seconds): The results confirm that only my CI applications are causing entries in both slowlogs. After analyzing the entries I can group them in two categories:
www-slow.log (PHP-FPM) Code: [12-Nov-2017 23:08:02] [pool www] pid 23991slow.log (MariaDB) Code: # Time: 171112 23:08:05I also tried to switch the database driver in CI from PDO to Mysqli...this didn't change anything. Also I ran all those slow queries in Phpmyadmin, and they perform super fast...so finally in CI I switched session from DB to Session_files_driver causing the slowlog to print: Code: [12-Nov-2017 23:27:00] [pool www] pid 23991So I am kind of stuck here...I can't really determine the real cause for those slow requests. I first thought it might be related to some slow CURL/CRON requests, after ruling that out I though it is related to DB transactions, but after switching session to Session_files_driver, excluding database, the slow requests still occur. Any ideas on how to debug this further? I am lost...should I switch back to PHP 7.0 or PHP 5.x? Thanks in advance!!
Downgrading PHP can't possibly help, not just with this but with anything.
Looks like you're handling concurrent requests from the same user (AJAX-heavy website?), which when combined with sessions ... Well, it's explained in the manual. Also, that query explain plan shows that you didn't have an index on the `id` field (which you should, again per the same manual page) and that has slowed it further down.
I am still stuck here. I altered database table according to man page (added primary key) and also checked for extensive session/ajax use, which is not the case. Especially not on the overview/index page that I am using for reference.
For further testing I copied both CI apps to a PHP 7.0 environment and there I don't have these request delays. Is it possible that these issues are related to Third Party Add-Ons, or modules that use code that isn't supposed to work well with PHP 7.1+? I am having some slowlog entries without session files as well: Code: [14-Nov-2017 08:23:40] WARNING: [pool www] child 25464, script '/usr/share/nginx/www/ci/index.php' (request: "GET /index.php") executing too slow (6.277512 sec), loggingCode: [14-Nov-2017 08:23:40] [pool www] pid 25464Maybe it is a server issues, but the fact that I am running a lot of other applications on this PHP 7.1 server as well without any issues I assume it is related to CI in some way.
Any third-party extensions can certainly slow the app down. These logs even show eval() usage, which should be avoided for other reasons, but it's also a particularly slow function.
Okay...and is it possible that these third-party extensions/bad coding style (eval()) are having such a huge effect on PHP 7.1 while PHP 7.0 isn't complaining?
No difference between PHP 7.0 and 7.1 can cause such a regression. In fact, as far as I know, 7.1 should be even faster than 7.0. It must be some difference in the environments, such as extra or missing extensions.
In any case, asking more and more questions here won't get you anywhere. You need to follow that stack trace, dig through the code and find the bottleneck.
Turn on CodeIgniter's Profiler and see what is going on...
What did you Try? What did you Get? What did you Expect?
Joined CodeIgniter Community 2009. ( Skype: insitfx )
If someone is interested in the solution/cause of the issue:
After some more debugging I was able to identify the problem to be related to MySQL. CI and Wordpress are using InnoDB Engine while MODx and my other applications were using MyISAM. I converted one MODx installation to InnoDB and suddenly it started having exactly the same issues: ![[Image: jBd7X.png]](https://i.stack.imgur.com/jBd7X.png) As I mentioned earlier most slowlog entries were related to sessions, so I digged in a bit deeper, and I think the problem is InnoDB clustered indexes. In CI the PK of the default CI session table is likely to cause those issues when used in certain setups with InnoDB. Let me sum up the following post which explains it in more detail: From MySQL Docs: Quote:Every InnoDB table has a special index called the clustered index where the data for the rows is stored. Typically, the clustered index is synonymous with the primary key. To get the best performance from queries, inserts, and other database operations, you must understand how InnoDB uses the clustered index to optimize the most common lookup and DML operations for each table. From this SO answer: Quote:According to The Queen Of Indexing - Kimberly Tripp - what she looks for in a clustered index is primarily: In the case of CI session table this is not really the case:
Here's what can happen when a non-ever-increasing clustered index is used. Source Quote:If the clustering key is ever-increasing then new rows have a specific location where they can be placed. If that location is at the end of the table then the new row needs space allocated to it but it doesn't have to make space in the middle of the table. If a row is inserted to a location that doesn't have any room then room needs to be made (e.g. you insert based on last name then as rows come in space will need to be made where that name should be placed). If room needs to be made, it's made by SQL Server doing something called a split. Splits in SQL Server are 50/50 splits - simply put - 50% of the data stays and 50% of the data is moved. This keeps the index logically intact (the lowest level of an index - called the leaf level - is a douly-linked list) but not physically intact. When an index has a lot of splits then the index is said to be fragmented. Good examples of an index that is ever-increasing are IDENTITY columns (and they're also naturally unique, natural static and naturally narrow) or something that follows as many of these things as possible - like a datetime column (or since that's NOT very likely to be unique by itself datetime, identity). In my case with CI and other applications this lead to the following issue: Quote:When you are inserting a new row (the "random" hash guarantees that) it gets inserted in a random location of the index. This means that it sometimes will find no space there available to be inserted (note that InnoDB always leaves some space free in the index but when that free-available space is filled) there has to be some rearrangement of the index - and that takes time. The solutions for me is to either use a different session driver, or switch the session table to MyISAM. |
| Welcome Guest, Not a member yet? Register Sign In |
MENU
EXTRA MENU
ABOUT US
CodeIgniter is a powerful PHP framework with a very small footprint, built for developers who need a simple and elegant toolkit to create full-featured web applications.
