Crystal DBA - IT Press Tour #60 Jan. 2025

Transcript

1. AI-Powered PostgreSQL Expertise for Everyone
2. 05 Johann Schleier-Smith • 300 million social network members (hi5.com, Tagged.com, etc.) • Oracle, PostgreSQL, MySQL • On-prem and cloud infrastructure • 110 technical staff • AI supplier to 25% of the Fortune 50 • Leader in computer vision for automotive, retail, and other applications • Systems, databases, programming languages, machine learning • Exams in both Computer Science (1st in class) and Physics CEO & Founder at Crystal DBA Ex-CTO and Founder at if(we) Board Member at Sama PhD at UC Berkeley and Stanford
3. Key trends for 2025 ✕
4. Developer Experience Edge Computing Systems PostgreSQL is thriving AlloyDB Cloud Tools Azure SQL Hybrid
5. Chatbot Teammate Copilot Agent AI is taking many forms
6. Crystal DBA: Your AI Teammate for Operational Excellence Crystal DBA
7. What is operational excellence? Availability, data protection, trusted as system of record. Consistently low latency, avoiding wasted resources. People stay focused on projects that move the business forward. Reliability and security Performance and efficiency Team effectiveness
8. Challenges to operational excellence “Adapting to fluctuating workloads and ensuring scalability and peak-time performance is a persistent struggle.” “Routine database tasks like tuning and bottleneck resolution require considerable effort and are fraught with manual errors.” Scalability Performance Optimization
9. Challenges to operational excellence “We face a knowledge gap in deep database expertise, challenging our ability to manage high transaction volumes effectively.” “Multi-tenancy limits our visibility and impacts our ability to diagnose and resolve database issues promptly.” Management and Maintenance Multi-Tenancy and Microservices
10. Managed Services Make You Do Too Much Management Seamless major version upgrades Query plan tuning Configuration selection Scalability planning Database parameter tuning ✗ Index tuning Availability planning Disaster recovery planning ✗ ✗ ✗ ✗ ✗ ✗ ✗
11. DBaaS Only Crystal DBA and DBaaS Seamless major version upgrades Query plan tuning Configuration selection Scalability planning Database parameter tuning ✗ Index tuning Availability planning Disaster recovery planning ✗ ✗ ✗ ✗ ✗ ✗ ✗ ✓ ✓ ✓ ✓ ✓ ✓ ✓ ✓ Backups and failover Installation and patching ✓ ✓ ✓ ✓
12. Crystal DBA works with your PostgreSQL, wherever you run it today AWS, Azure, or GCP Managed PostgreSQL Crystal DBA On-Prem PostgreSQL Slack Teams Web Terminal
13. Crystal DBA never sleeps: Save time and run PostgreSQL confidently Achieve optimal application performance by tuning configuration, indexing, and queries Ensure high availability with resilient configuration and 24x7 analysis Actionable, proactive, and simplified monitoring through predictive analysis Scale with confidence using advanced workload and growth models
14. Tuning PostgreSQL for your workload
15. Over 300 settings in postgres.conf Cache and Buffer Management Write-Ahead Log Vacuum Query Tuning Connections and Authentication Lock Management Error Handling Replication Statistics Others 37 38 13 48 25 5 4 24 12 132 shared_buffers, max_stack_depth wal_buffers, wal_compression autovacuum_vacuum_threshold, autovacuum_naptime cpu_tuple_cost, effective_cache_size max_connections, password_encryption deadlock_timeout, max_pred_locks_per_page data_sync_retry, restart_after_crash vacuum_defer_cleanup_age, max_replication_flush_lag track_io_timing, log_planner_stats block_size, data_checksums AREA NUMBER EXAMPLES
16. PostgreSQL Autovacuum Configuration analyze_scale_factor analyze_threshold freeze_max_age max_workers multixact_freeze_max_age naptime vacuum_cost_delay vacuum_cost_limit vacuum_insert_scale_factor vacuum_insert_threshold vacuum_scale_factor vacuum_threshold work_mem Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples. Minimum number of tuple inserts, updates, or deletes prior to analyze. Age at which to autovacuum a table to prevent transaction ID wraparound. Sets the maximum number of simultaneously running autovacuum worker processes. Multixact age at which to autovacuum a table to prevent multixact wraparound. Time to sleep between autovacuum runs. Vacuum cost delay in milliseconds, for autovacuum. Vacuum cost amount available before napping, for autovacuum. Number of tuple inserts prior to vacuum as a fraction of reltuples. Minimum number of tuple inserts prior to vacuum, or -1 to disable insert vacuums. Number of tuple updates or deletes prior to vacuum as a fraction of reltuples. Minimum number of tuple updates or deletes prior to vacuum. Sets the maximum memory to be used by each autovacuum worker process. CONFIGURATION PARAMETER DESCRIPTION
17. Objective Configuration 1. Performance Objective Best Cost Best Latency
18. Objective Configuration 1. Performance objective Best Cost Best Latency 2. Availability objective Standard Availability (99.95% SLA) High Availability (99.995% SLA)
19. Demo
20. Crystal DBA Summary Crystal DBA never sleeps: Save time and run PostgreSQL confidently Puts expertise at the fingertips of every member of your team Support by PostgreSQL experts—your partners in operational excellence
21. Thank You www.crystaldba.ai Crystal DBA