Package Data | |
---|---|
Maintainer Username: | halilcosdu |
Maintainer Contact: | halilcosdu@gmail.com (Halil Cosdu) |
Package Create Date: | 2024-05-03 |
Package Last Update: | 2024-11-17 |
Home Page: | https://laravel-slower.com |
Language: | PHP |
License: | MIT |
Last Refreshed: | 2024-12-17 15:00:06 |
Package Statistics | |
---|---|
Total Downloads: | 6,558 |
Monthly Downloads: | 1,186 |
Daily Downloads: | 67 |
Total Stars: | 339 |
Total Watchers: | 4 |
Total Forks: | 18 |
Total Open Issues: | 0 |
Laravel Slower is a powerful package designed for Laravel developers who want to enhance the performance of their applications. It intelligently identifies slow database queries and leverages AI to suggest optimal indexing strategies and other performance improvements. Whether you're debugging or routinely monitoring your application, Laravel Slower provides actionable insights to streamline database interactions.
You can install the package via composer:
composer require halilcosdu/laravel-slower
You can publish the config file with:
php artisan vendor:publish --tag="slower-config"
This is the contents of the published config file:
You can disable AI recommendations by setting the ai_recommendation
key to false
in the config file. If you disable AI recommendations, the package will not make any API requests to OpenAI.
<?php
// config for HalilCosdu/Slower
use HalilCosdu\Slower\Models\SlowLog;
return [
'enabled' => env('SLOWER_ENABLED', true),
'threshold' => env('SLOWER_THRESHOLD', 10000),
'resources' => [
'table_name' => (new SlowLog)->getTable(),
'model' => SlowLog::class,
],
'ai_recommendation' => env('SLOWER_AI_RECOMMENDATION', true),
'recommendation_model' => env('SLOWER_AI_RECOMMENDATION_MODEL', 'gpt-4'),
'recommendation_use_explain' => env('SLOWER_AI_RECOMMENDATION_USE_EXPLAIN', true),
'ignore_explain_queries' => env('SLOWER_IGNORE_EXPLAIN_QUERIES', true),
'ignore_insert_queries' => env('SLOWER_IGNORE_INSERT_QUERIES', true),
'open_ai' => [
'api_key' => env('OPENAI_API_KEY'),
'organization' => env('OPENAI_ORGANIZATION'),
'request_timeout' => env('OPENAI_TIMEOUT'),
],
'prompt' => env('SLOWER_PROMPT', 'As a distinguished database optimization expert, your expertise is invaluable for refining SQL queries to achieve maximum efficiency. Schema json provide list of indexes and column definitions for each table in query. Also analyse the output of EXPLAIN ANALYSE and provide recommendations to optimize query. Please examine the SQL statement provided below including EXPLAIN ANALYSE query plan. Based on your analysis, could you recommend sophisticated indexing techniques or query modifications that could significantly improve performance and scalability?'),
];
You can publish and run the migrations with:
php artisan vendor:publish --tag="slower-migrations"
php artisan migrate
public function up()
{
Schema::create(config('slower.resources.table_name'), function (Blueprint $table) {
$table->id();
$table->boolean('is_analyzed')->default(false)->index();
$table->longtext('bindings');
$table->longtext('sql');
$table->float('time')->nullable()->index();
$table->string('connection');
$table->string('connection_name')->nullable();
$table->longtext('raw_sql');
$table->longtext('recommendation')->nullable();
$table->timestamps();
});
}
public function down(): void
{
Schema::dropIfExists(config('slower.resources.table_name'));
}
You can register the commands with your scheduler.
php artisan slower:clean /*{days=15} Delete records older than 15 days.*/
php artisan slower:analyze /*Analyze the records where is_analyzed=false*/
use HalilCosdu\Slower\Commands\AnalyzeQuery;
use HalilCosdu\Slower\Commands\SlowLogCleaner;
protected $commands = [
AnalyzeQuery::class,
SlowLogCleaner::class,
];
/**
* Define the application's command schedule.
*/
protected function schedule(Schedule $schedule): void
{
$schedule->command(AnalyzeQuery::class)->runInBackground()->daily();
$schedule->command(SlowLogCleaner::class)->runInBackground()->daily();
}
$model = \HalilCosdu\Slower\Models\SlowLog::first();
\HalilCosdu\Slower\Facades\Slower::analyze($model): Model;
dd($model->raw_sql); /*select count(*) as aggregate from "product_prices" where "product_id" = '1' and "price" = '0' and "discount_total" > '0'*/
dd($model->recommendation);
In order to improve database performance and scalability, here are some suggestions below:
product_id
, price
, and discount_total
. This index would work well because the where clause covers all these columns.CREATE INDEX idx_product_prices
ON product_prices (product_id, price, discount_total);
(Note: The order of the columns in the index might depend on the selectivity of the columns and the data distribution. Therefore, you might have to reorder them depending on your specific situation.)
product_id
, price
, and discount_total
which are likely numerical columns. Remove the quotes for these where clause conditions.Updated Query:
SELECT COUNT(*) AS aggregate
FROM product_prices
WHERE product_id = 1
AND price = 0
AND discount_total > 0;
ANALYZE
command. This command collects statistics about the contents of tables in the database, and stores the results in the pg_statistic system catalog. Subsequently, the query planner uses these statistics to help determine the most efficient execution plans for queries.ANALYZE product_prices;
Remember to periodically maintain your index to keep up with the CRUD operations that could lead to index fragmentation. Depending on your DBMS, you might want to REBUILD or REORGANIZE your indices.
composer test
Please see CHANGELOG for more information on what has changed recently.
Please see CONTRIBUTING for details.
Please review our security policy on how to report security vulnerabilities.
The MIT License (MIT). Please see License File for more information.