Оптимизация скорости обработки запросов к БД в CakePHP 2.x

Привет.

При работе над проектом VirCities нашей студии, мы столкнулись с неудовлетворительной скоростью обработки запросов к БД через CakePHP 2.6, на котором написан бек-сайд. После проведения нагрузочного тестирования и профилирования приложения, мы обнаружили наше самое «узкое место», им оказался ORM кейка.

В поисках возможных вариантов решения этой проблемы были проведены дополнительные эксперименты, в которых использовались сложные (2-3 уровня) запросы к базе:

• cakephp_orm с contain;
• cakephp_orm с recursive без contain;
• cakephp_orm с запросом через query;
• прямая работа с pdo.

Ниже я приведу маленький пример, как было ДО проведения профилирования, вариант кода для «cakephp_orm с contain»

	public function contain() {
		$this->out('Start');
		$result = $this->Company->find('all', array(
			'contain' => array(
				'User' => array(
					'City'
				)
			)
		));
		$this->out('Finish');
	}

Этот пример описывает, как проблемное место выглядит в коде проекта с несколькими исключениями:

• 'all' обычно не используется, в запросах есть либо limit, либо дополнительное условие
• есть перечисление нужных полей в 'fields'

И пример кода прямой работы с pdo:

	public function pdo(){
		$this->out('Start');
		$pdo = $this->Company->getDataSource()->getConnection();
		$stm = $pdo->prepare("SELECT * FROM companies");
		$stm->execute();
		$companies = $stm->fetchAll();
		$result = array();
		foreach ($companies as $company) {
			$stm = $pdo->prepare("SELECT * FROM users WHERE id = ?");
			$stm->execute(array($company['user_id']));
			$user = $stm->fetch();
			$stm = $pdo->prepare("SELECT * FROM cities WHERE id = ?");
			$stm->execute(array($user['city_id']));
			$city = $stm->fetch();
			$user['City'] = $this->clearResult($city);
			$company['User'] = $this->clearResult($user);
			$result[]['Company'] = $this->clearResult($company);
		}
		$this->out('Finish');
	}

Привожу тайминги по проведенным тестами:

~: time cake Ormtest contain

real 0m0.417s
user 0m0.216s
sys 0m0.044s

~: time cake Ormtest pdo

real 0m0.185s
user 0m0.100s
sys 0m0.012s

Эти цифры были получены с рабочей машины одного из разработчиков, на боевых серверах значения ниже с сохранением соотношения. Вариант с чистым PDO показал лучшие результаты, даже не смотря на то, что в тесте не использовались JOIN. В итоге мы решили оптимизировать поведение «ContainableBehavior».

Почему мы решили сделать именно так? На наше решение повлияли следующие факторы:

• Большая кодовая база с 4х летней историей;
• Фреймворк с очень жесткими связями между компонентами;
• Ограничение по времени и ресурсам.

Поэтому следующие варианты были исключены сразу:

• Замена фреймворка;
• Апгрейд CakePHP до версии 3 (особенно с учетом того, что она еще не вышла в релиз);
• Замена ORM;
• Модификация ядра CakePHP по работе с базой;
• Ручная переделка запросов в проекте.

На самом деле апгрейд до третьей версии был бы оптимальным решением, но мы ждем релизной версии и возможности высвободить ресурсы для данного перехода, так как проект немаленький. Поэтому сейчас нам пришлось искать «собственный путь».

Мы разделили оптимизацию на несколько этапов, первый из которых — модификация ContainableBehavior.

Новое поведение уложилось в 400 строк, приводить его тут кусками смысла не вижу. Все желающие могут ознакомиться на Github.

Краткое описание изменений:

• в beforeFind первый уровень связей — преобразуем в join и отдаем дальше ядру кейка, последующие уровни сохраняем для постобработки запроса;
• в afterFind — из сохраненных связей собираем и выполняем запросы к базе используя PDO, результаты аттачим к массиву отданному ядром кейка.

После внесения изменений было проведено тестирование на специально усложненном запросе (обкатывали belongsTo, hasOne, hasMany):

public function contain() {
		$this->out('Start');

		$this->Company->find('all', array(
			'contain' => array(
				'Manager',
				'CompanyWorker',
				'CompanyType' => array(
					'CompanyProductionType' => array(
						'ItemType' => array(
							'ItemTypeResource' => array(
								'ItemTypeMain'
							),
						),
					)
				),
				'CompanyReceipt' => array(
					'ItemType' => array(
						'ItemTypeResource' => array(
							'ItemTypeMain'
						),
					),
				),
				'CompanyProductionProgress',
				'CurrentProduction',
				'Corporation',
				'User'
			),
		));
		$this->out('Finish');
	}

И вот что мы получили.

Результаты тестирования оригинального поведения:

~:time cake Ormtest contain
real 1m21.964s
user 0m24.768s
sys 0m6.160s

А это результаты после внесения модифицикаций:

~:time cake Ormtest contain

real 0m5.849s
user 0m1.772s
sys 0m0.448s

Результирующие массивы — идентичны, за исключением сортировки ключей. Надеюсь, наше решение кому-нибудь пригодится, или натолкнет на «светлую» мысль.

С Уважением.