Show Notes for AlternaGeek Episode 4

AlternaGeek.com Podcast, Episode 4 for July 3, 2007
Featuring Linuxchic & the Code Ninja

Databases, Tips & Tools

• MySQL
  
• Detailed WordPress instructions with MySQL and phpMyAdmin:
  
• phpMyAdmin
  
• MySQL Query Browser
• MySQL Admin
  
• Stored Procedures in MySQL
  
• Using SQL With Gnumeric
  
• Postgresql
• EnterpriseDB
  
• Visual Case <
  
• SQLite
  
• OpenOffice Database
  
• Dia ERD Editor and DDL Generator

MySQL Tuning

mysqlreport: Script allowing you to see your MySQL status, usage and more. A great howto for using it can be found here.
The settings referenced here can be found in the my.cnf file

What the settings mean in my.cnf:

key_buffer = 48M
max_allowed_packet = 8M
table_cache = 128
sort_buffer_size = 48M
net_buffer_length = 8M
thread_cache_size = 4
query_cache_type = 1
query_cache_size = 4M

query_cache – this caches repeated SQL queries.
key_buffer – used for caching primary key indexes.
table_cache – tells mysql how many table files handles to keep open simultaneously.
thread_cache_size – this tells mysql to keep worker threads around which are expensive to start up, but cheap to maintain
sort_buffer_size – this value is used during queries to hold results in memory otherwise it creates temporary result tables on disk
net_buffer_length – should help on larger network based queries to improve throughput

Disable DNS lookups (a must for security as well as speed)

Add the –skip-name-resolve switch into your my.cnf config file (on most linux distros this is /etc/mysql/my.cnf) simply by putting this line in the file:

skip-name-resolve

Save and close the file, then restart MySQL (on linux usually by /etc/init.d/mysql restart)

More tuning links:

• MySQL Tuning Server Params
• InnoDB Tuning Tips
• Sample Configs For MySQL with Low Memory
• Optimizing MySQL (very informative presentation)
• MySQL Optimizations
• Tips from Drupal.org

---

Web Servers & Such

• Lighttpd (pronounced “lighty”)
  
• Apache Web Server
  
• THTTPD
  
• FastCGI
  
• FastCGI Support in Lighttpd
• Enabling FastCGI with Lighttpd and PHP
• Enabling FastCGI in Apache

---

A few more items from the show

Pixel Image Editor: Open source image manipulation software with support for MANY operating systems.

The Gabby Geek: Podcast with health and fitness tips for geeks (or anyone else that sits at a desk all day).

---

Housekeeping

Wikipedia on the History of SQL and SEQUEL

The first version of SQL was developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s. This version, initially called SEQUEL, was designed to manipulate and retrieve data stored in IBM’s original relational database product, System R. The SQL language was later formally standardized by the American National Standards Institute (ANSI) in 1986. Subsequent versions of the SQL standard have been released as International Organization for Standardization (ISO) standards.

Reference to Premature Optimization is the Root of All Evil

Donald Knuth said, paraphrasing Hoare, (many references, one is : http://www.acm.org/ubiquity/views/v7i24_fallacy.html)
* “We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil.” (Code Complete, Page 594)

More posts on AlternaGeek.com to follow containing more configuration tips for web servers…

---