Laravel Slower Laravel Package

Getting Started

1. Install the package via Composer:

   composer require halilcosdu/laravel-slower
   

2. Publish and configure the config and migrations:

   php artisan vendor:publish --tag="slower-config"  
   php artisan vendor:publish --tag="slower-migrations"  
   php artisan migrate
   

3. Configure your environment:
   Set OPENAI_API_KEY and optionally OPENAI_ORGANIZATION in .env. Set SLOWER_ENABLED=true and SLOWER_THRESHOLD (in milliseconds) to define what counts as a “slow” query.
   By default, queries exceeding 10 seconds (10000ms) are logged.
4. First use case: Log slow queries in production by ensuring the database events are broadcasted, then schedule cleanup and analysis:

   // In app/Console/Kernel.php
   $schedule->command('slower:analyze')->daily();
   $schedule->command('slower:clean', ['--days' => 30])->daily();
   

Implementation Patterns

• Background analysis with caching: Run slower:analyze daily (or hourly) via Laravel Scheduler. It processes pending logs (is_analyzed = false) and uses OpenAI to generate optimization recommendations. Because the command runs ->runInBackground(), it doesn’t block the console worker.
• Manual ad-hoc analysis: For immediate insight into a known slow query, use:

  $log = SlowLog::where('time', '>', 5)->first();
  Slower::analyze($log);
  echo $log->recommendation;
  

• Custom thresholds per environment: Use env-based config (SLOWER_THRESHOLD) to set aggressive monitoring (e.g., SLOWER_THRESHOLD=5000) in staging and relax it in production (15000) to avoid noise.
• Integration with logging: Pair with Laravel’s existing query logging (e.g., via DB::listen()) or tools like Laravel Debugbar — this package doesn’t interfere; it consumes its own log table.
• Prevent overhead during dev: Set SLOWER_ENABLED=false locally to avoid logging minor fluctuations; keep it enabled only in non-local environments.

Gotchas and Tips

• AI cost control: ai_recommendation defaults to true. If OPENAI_API_KEY is missing, it throws an exception — handle this with a try/catch or disable AI via config if budget is a concern.
• EXPLAIN ANALYZE is critical: The v2.0+ prompt uses PostgreSQL’s EXPLAIN ANALYZE output (or MySQL’s EXPLAIN FORMAT=JSON if DB supports it) for better suggestions. Ensure your DB user has sufficient privileges to run EXPLAIN ANALYZE.
• Ignored queries by default: INSERT, UPDATE, DELETE, and queries with EXPLAIN prefix are skipped. Adjust via config if your workload includes long-running DML.
• Schema context matters: Recommendations depend on knowing table indexes and column types. The package fetches schema metadata at runtime — verify that the DB connection used for analysis has access to information_schema or equivalent.
• No auto-indexing: As of v2.0.5, indexing is advisory only. Do not auto-apply recommendations — always review and validate in staging first. The roadmap mentions “Auto Indexer (Premium Feature)”, but it’s not yet implemented.
• Prompt customization: The default prompt is verbose. Override it via SLOWER_PROMPT or use config('slower.prompt') to inject environment-specific context (e.g., highlight read replicas or sharding strategy).
• Migration safety: The SlowLog model uses longtext for sql, raw_sql, recommendation, etc. Ensure your DB supports that (all major ones do). If using SQLite for local dev, prefer text or mediumtext to avoid bloat.