Download to read offline
![PDO – Broken by Design
<?php
$pdo = new PDO(“mysql:host=localhost;dbname=test”,
“user”, “password”);
$query = $pdo->prepare(
“SELECT id FROM table LIMT ?, ?”);
$query->bindValue(1, $_GET[“offset”]);
$query->bindValue(2, $_GET[“limit”]);
$query->execute();](https://image.slidesharecdn.com/ipcmysqlphp-101101071508-phpapp01/85/Ipc-mysql-php-21-320.jpg)
![“The mysqli extension, or as it is sometimes known,
the MySQL improved extension, was developed to
take advantage of new features found in MySQL
systems versions 4.1.3 and newer. […] If you are
using MySQL versions 4.1.3 or later it is strongly
recommended that you use this extension.”
http://php.net/mysqli.overview
What The PHP Manual Is Saying
Extended(!) support for
MySQL 4.0(!) ended 2008-09-30](https://image.slidesharecdn.com/ipcmysqlphp-101101071508-phpapp01/85/Ipc-mysql-php-22-320.jpg)
![PDO – Broken by Design
<?php
$pdo = new PDO(“mysql:host=localhost;dbname=test”,
“user”, “password”);
$query = $pdo->prepare(
“SELECT id FROM table LIMT ?, ?”);
$query->bindValue(1, $_GET[“offset”]);
$query->bindValue(2, $_GET[“limit”]);
$query->execute();](https://image.slidesharecdn.com/ipcmysqlphp-101101071508-phpapp01/75/Ipc-mysql-php-21-2048.jpg)
![“The mysqli extension, or as it is sometimes known,
the MySQL improved extension, was developed to
take advantage of new features found in MySQL
systems versions 4.1.3 and newer. […] If you are
using MySQL versions 4.1.3 or later it is strongly
recommended that you use this extension.”
http://php.net/mysqli.overview
What The PHP Manual Is Saying
Extended(!) support for
MySQL 4.0(!) ended 2008-09-30](https://image.slidesharecdn.com/ipcmysqlphp-101101071508-phpapp01/75/Ipc-mysql-php-22-2048.jpg)
More Related Content
Ipc mysql php
- 2. <Insert Picture Here> PHPand MySQL – The Current State Johannes Schlüter MySQL Engineering: Connectors & Client Connectivity
- 3. The following isintended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.
- 4. Oracle’s Strategy: Complete. Open.Integrated. • Built together • Tested together • Managed together • Serviced together • Based on open standards • Lower cost • Lower risk • More reliable
- 5. Oracle’s Investment inOpen Source • Supported popular open source projects for many years • Part of Oracle’s Complete, Open, Integrated strategy • Speed up time-to-innovation • Expand the developer community
- 6. • Oracle neversettles for being second best at any level of the stack • “Complete” means we meet most customer requirements at every level That’s why MySQL matters to Oracle and Oracle customers
- 7. Industry’s Most CompleteLAMP Stack –Oracle Enterprise Linux –Oracle VM (Xen-based) –Apache –MySQL –PHP, Perl, Python MySQL Apache Eclipse NetBeans Apps PHP
- 8. Investment in MySQL •Make MySQL a Better MySQL – #1 Open Source Database for Web Applications • Develop, Promote and Support MySQL – Improve engineering, consulting and support – Leverage 24x7, World-Class Oracle Support • MySQL Community Edition – Source and binary releases – GPL license
- 9. Investment in MySQL •MySQL Focus Areas – Web, Embedded & Telecom – LAMP – Windows • Oracle + MySQL Customers – Oracle Enterprise Manager – Oracle Secure Backup – Oracle Audit Vault
- 10. InnoDB will becomedefault • ACID Transactions, FKs, Crash Recovery Improved Availability • Semi-synchronous Replication • Replication Heartbeat Improved Usability • SIGNAL/RESIGNAL • More Partitioning Options • PERFORMANCE_SCHEMA Better Instrumentation/Diagnostics • InnoDB stats in 5.5 PERFORMANCE_SCHEMA MySQL 5.5 RC
- 11. InnoDB Performance improvements •Multiple Buffer Pool Instances • Multiple Rollback Segments • Extended Change Buffering (with delete buffering, purge buffering) • Improved Purge Scheduling • Improved Log Sys mutex • Separate Flush List mutex MySQL Performance Improvements • Better Metadata Locking within Transactions • Split LOCK_open mutex • Eliminated LOCK_alarm mutex as bottleneck • Eliminated LOCK_thread_count as bottleneck • Improved Performance/Scale on Win32, 64 More than 10x improvement in recovery times MySQL 5.5 is Faster! RC
- 12. MySQL 5.5 SysBenchBenchmarks Linux Intel Xeon X7460 x86_64 4 CPU x 6 Cores/CPU 2.66 GHz, 32GB RAM Fedora 10 MySQL 5.1.50 (InnoDB built-in) MySQL 5.1.50 (InnoDB Plug-in) MySQL 5.5.6 (New InnoDB) 200% performance gain for MySQL 5.5 over 5.1.50; at scale RC
- 13. MySQL 5.5 SysBenchBenchmarks Linux MySQL 5.1.50 (InnoDB built-in) MySQL 5.1.50 (InnoDB Plug-in) MySQL 5.5.6 (New InnoDB) Intel Xeon X7460 x86_64 4 CPU x 6 Cores/CPU 2.66 GHz, 32GB RAM Fedora 10 369% performance gain for MySQL 5.5 over 5.1.50; at scale RC
- 15. PHP Extensions forMySQL PDO_mysql ext/mysql mysqli PHP
- 16. ext/mysql • One ofthe first PHP extensions • Actively maintained with PHP 4 – No new features in PHP 5 • Exception: Added mysqlnd support with PHP 5.3 – Bug fixing only • Best documented database extension – Tons of books, tutorials, … • Missing support for many MySQL features – Prepared statements, Queries with multiple result sets (stored procedures), compression, encryption, full charset support, …
- 17. PDO_mysql • “The PHPData Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP.” http://php.net/intro.pdo • Lowest common denominator • PHPish API • Broken by Design™
- 18. $ php --rfPDO::sqliteCreateFunction Exception: Method PDO::sqliteCreateFunction() does not exist PDO – Broken by Design
- 19. Intermezzo: Prepared Statements ClientServer SELECT foo FROM bar WHERE id = 42 •Create Execution plan •Query database Resultset(s) query()
- 20. Intermezzo: Prepared Statements ClientServer SELECT foo FROM bar WHERE id = ? •Query database Resultset(s) Handle Handle Param 1: 42 •Create Execution plan prepare() execute()
- 21. PDO – Brokenby Design <?php $pdo = new PDO(“mysql:host=localhost;dbname=test”, “user”, “password”); $query = $pdo->prepare( “SELECT id FROM table LIMT ?, ?”); $query->bindValue(1, $_GET[“offset”]); $query->bindValue(2, $_GET[“limit”]); $query->execute();
- 22. “The mysqli extension,or as it is sometimes known, the MySQL improved extension, was developed to take advantage of new features found in MySQL systems versions 4.1.3 and newer. […] If you are using MySQL versions 4.1.3 or later it is strongly recommended that you use this extension.” http://php.net/mysqli.overview What The PHP Manual Is Saying Extended(!) support for MySQL 4.0(!) ended 2008-09-30
- 23. mysqli The Improved MySQLExtension • Full support for all MySQL features – Stored Procedures – Prepared Statements – Encryption (SSL) – Compression – Charsets – … • Actively developed, maintained and supported by Oracle
- 24. PHP and mysqlnd PHP PHPMemory IO: PHP StreamsInfrastructure ext/mysql PDO_mysqlmysqli mysqlnd – MySQL native driver for PHP MySQL Server
- 25. libmysql vs. mysqlnd MySQLServer MySQL Server mysqlnd libmysql PHP PHP PHP Memory libmysql Memory PHP Memory PHP Memory copy copy usedirectly copy
- 26. Building PHP withmysqlnd • ./configure --with-mysql=mysqlnd --with-mysqli=msqlnd --with-pdo-mysql=mysqlnd • Default on Windows and some distributions
- 27. mysqlnd Statistics • Around150 statistic values collected • mysqli_get_client_stats (), mysqli_get_connection_stats()
- 28. Asynchronous Queries /* Dosomething */ PHP Script MySQL query result query poll result $conn = new MySQLi(...); $conn->query( "SELECT * FROM t WHERE ....", MYSQLI_ASYNC); /* Process query results */ mysqli_poll($links, $errors, $reject, 1);
- 29.
- 30. Sharding foreach ($all_links as$link) $link->query("SELECT 'test' ", MYSQLI_ASYNC); $processed = 0; do { $links = $all_links; if (!mysqli_poll($links, $errors, $reject, 1)) continue; /* TIMEOUT */ foreach ($links as $link) { if ($result = $link->reap_async_query()) { print_r($result->fetch_row()); mysqli_free_result($result); $processed++; } } } while ($processed < count($all_links));
- 31. mysqlnd plugins Plugin Hook mysqlndQuery mysqli::query()mysql_query() PDO::query() Wire Protocol Plugin Hook Network
- 32. mysqlnd Plugins • “mysqlndclient proxy” – Load Balancing • Read / Write splitting • Failover – Monitoring • Query Logging • Query Auditing – Performance • Caching • Sharding
- 33. The Database IsThe Bottleneck
- 34.
- 35. # pecl installmysqlnd_qc-beta … and you are done! Almost at least ;-)
- 36. mysqlnd Query cache •For more: • Ulf's presentation → Tomorrow 16:30 (German)
- 37. Experimental Extensions • ByOracle: – mysqlnd_sip – mysqlnd_mc – mysqlnd_ms – mysqlnd_pscache • By Community: – mysqlnd_uh (David Soria Parra / Mayflower GmbH)
- 38. mysqlnd_uh class ConnProxy extendsMysqlndUHConnection { public function query($conn, $query) { if ($query == “SELECT 1”) { $query = “SELECT 2”; } return parent::query($conn, $query); } } mysqlnd_uh_set_connection_proxy(new ConnProxy());
- 39. Key Takeaways • MySQLis important to Oracle and our customers – Part of our Complete, Open, Integrated strategy • Oracle is making MySQL better today – Major Feature, Performance, Scalability enhancements – 24x7, Global support in 145 countries Download Now http://dev.mysql.com/downloads Download Now http://dev.mysql.com/downloads