« Stored Procedures in MySQL | Main | Nagging Thoughts about MySQL »

April 16, 2004

MySQL and PHP: Best Practices

Jim Winstead speaks about good practice for MySQL and PHP at MySQL 2004. Looking down this it appears that I've just duplicated Jim's slides, not sure how much value that adds if you can just go an get the slides yourself.

Security:
- do not run as root
- do not run as same user as apache
- set a root password
- all the usual password and account security practices (random password, know accounts)
- restrict connections to localhost or ip addresses (unless you know you can trust your DNS)
- skip-networking on mysqld if not needed
- bind-address = 10.1.10.5 (if you have an internal network)
- look at SSL, SSH tunneling and IP level restrictions (firewall, kernel packet filtering)

Don't Trust User Data - don't trust user data
- sql injection - use prepared statements
- XSS (cross-site scripting) - use htmlspecialchars() to get rid of html in form fields
- CSRF (cross-site request forgeries) - turn off register_globals, use $_POST and form keys

Optimization - Things to Benchmark
- overall page load time
- individual functions (use a profiler like dbg)
- specific queries (use slow query)
- benchmark as often as possible - know how each change has made a difference

Optimization - Compiler Cache
- APC in PEAR
- Turck MMCache
- IonCube Accelerator
- BWare Afterbuerner
- Zend Performance Suite

Optimization - Database
- don't use * in MySQL selects
- use mysql_unbuffered_query() - stream the results as soon as something is available
- use mysqli
- use the right storage engine
- help the optimizer
- use EXPLAIN
- tune buffers
- consider replication
- use query_cache - SHOW STATUS LIKE 'Qcache%'

Important to consider needs of the application when choosing a storage engines
- MyISAM - inserts that append to table do not lock
- InnoDB - ACID, row-level locking
- DBD - page level locking
- HEAP - (memory)
- NDB (cluster)

Non-Tips (not performance isues)
- mysql_pconnect doesn't work much faster and might introduce other issues
- echo vs print
- moving in and out of php

Use CSS templates to send less data and allow future flexibility

Posted by mike at April 16, 2004 3:14 PM

Hard Drive Recovery Group offers hard disk data recovery services for RAID, laptops and servers. Complete clean room and hard drive repair service.

Trackback Pings

TrackBack URL for this entry:
http://mike.kruckenberg.com/mt/mt-tb.cgi/560