Laravel Slower Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require halilcosdu/laravel-slower
   php artisan vendor:publish --tag="slower-config"
   php artisan vendor:publish --tag="slower-migrations"
   php artisan migrate
   

2. Enable in AppServiceProvider:

   use HalilCosdu\Slower\Slower;
   
   public function boot()
   {
       Slower::enable();
   }
   

3. Configure .env:

   SLOWER_ENABLED=true
   SLOWER_THRESHOLD=10000  # Milliseconds
   OPENAI_API_KEY=your_api_key
   

4. First Use Case: Trigger a slow query (e.g., unindexed WHERE on large tables) and check the slow_logs table for captured queries.

Implementation Patterns

Core Workflow

1. Query Capture:

   • Automatically logs queries exceeding SLOWER_THRESHOLD (default: 10ms).
   • Stores raw SQL, bindings, execution time, and connection details.

2. Analysis Pipeline:

   • Manual Trigger:

     $slowLog = \HalilCosdu\Slower\Models\SlowLog::first();
     \HalilCosdu\Slower\Facades\Slower::analyze($slowLog);
     

   • Scheduled Analysis (via app/Console/Kernel.php):

     $schedule->command(\HalilCosdu\Slower\Commands\AnalyzeQuery::class)
              ->daily()
              ->runInBackground();
     

3. AI Recommendations (if enabled):

   • Uses OpenAI to generate optimization suggestions (indexes, query rewrites, EXPLAIN ANALYZE insights).
   • Configurable via SLOWER_AI_RECOMMENDATION_MODEL (e.g., gpt-4).

4. Cleanup:

   • Schedule daily cleanup:

     $schedule->command(\HalilCosdu\Slower\Commands\SlowLogCleaner::class)
              ->daily()
              ->runInBackground();
     

Integration Tips

• Debugging Slow Endpoints: Add middleware to log slow requests:

  use HalilCosdu\Slower\Facades\Slower;
  
  public function handle($request, Closure $next)
  {
      $start = microtime(true);
      $response = $next($request);
      $duration = (microtime(true) - $start) * 1000;
  
      if ($duration > config('slower.threshold')) {
          Slower::logQuery('Request took ' . $duration . 'ms', ['duration' => $duration]);
      }
      return $response;
  }
  

• Custom Query Logging: Extend the Slower facade to log custom queries:

  Slower::logQuery('SELECT * FROM users WHERE email = ?', ['john@example.com'], 'pgsql');
  

• Batch Analysis: Process multiple slow logs in bulk:

  $slowLogs = \HalilCosdu\Slower\Models\SlowLog::where('is_analyzed', false)->limit(10)->get();
  foreach ($slowLogs as $log) {
      Slower::analyze($log);
  }
  

Gotchas and Tips

Pitfalls

1. AI Dependency:

   • Disabling AI (SLOWER_AI_RECOMMENDATION=false) removes optimization suggestions but retains query logging.
   • Cost Warning: OpenAI API calls incur costs (~$0.03–$0.06 per 1,000 tokens). Monitor usage via openai-php/laravel logs.

2. Performance Overhead:

   • Logging adds minimal overhead (~1–2ms per query). Test in staging first.
   • Exclude Queries: Use SLOWER_IGNORE_INSERT_QUERIES=true to skip INSERT/UPDATE logs.

3. Database-Specific Quirks:

   • PostgreSQL: Ensure EXPLAIN ANALYZE is supported (enabled by default in config).
   • MySQL: Add /*! EXPLAIN */ hints if EXPLAIN ANALYZE fails.

4. Schema Mismatches:

   • If slow_logs table schema changes, reset migrations or manually adjust the resources.table_name config.

Debugging Tips

• Verify Logs: Check slow_logs table for captured queries:

  SELECT * FROM slow_logs ORDER BY time DESC LIMIT 10;
  

• AI Debugging:

  • Validate OpenAI API keys in .env:

    OPENAI_API_KEY=sk-xxx
    OPENAI_ORGANIZATION=org-xxx
    

  • Test API connectivity with:

    use OpenAI\Laravel\Facades\OpenAI;
    OpenAI::completion()->create(['model' => 'gpt-4', 'prompt' => 'Test']);
    

• Query Analysis: Manually run EXPLAIN ANALYZE in your DB client to cross-validate AI suggestions:

  EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com';
  

Extension Points

1. Custom Prompts: Override the default prompt in config/slower.php:

   'prompt' => 'Focus on adding indexes for large tables only. Ignore small tables (<1000 rows).',
   

2. Post-Analysis Hooks: Extend the SlowLog model to trigger actions after analysis:

   protected static function booted()
   {
       static::saved(function ($model) {
           if ($model->wasChanged('recommendation')) {
               // Send Slack notification, etc.
           }
       });
   }
   

3. Filtering Queries: Use Eloquent scopes to filter slow logs:

   $slowLogs = \HalilCosdu\Slower\Models\SlowLog::where('time', '>', 5000)
       ->where('connection', 'pgsql')
       ->get();
   

4. Testing: Mock AI responses in tests:

   $this->mock(OpenAI::class)->shouldReceive('completion')
       ->andReturn(['choices' => [['text' => 'Add index on column `email`']]]);