HP-UX OSRA for Web Services 2.5 Blueprint and Configuration Guide

4 MySQL Tools and Tips
This chapter describes additional information on MySQL related configurations, products, and
services. This chapter addresses the following topics:
• “MySQL Performance Tuning Tips” (page 35)
• “MySQL High Availability Using HP Serviceguard” (page 35)
• “Backing Up MySQL Using HP Data Protector” (page 38)
MySQL Performance Tuning Tips
The following tuning tips can enhance MySQL performance:
• Set the file system buffer size to 50% of main memory for ideal I/O performance. In HP-UX,
file system buffer caching can be set as a minimum of 1% up to a maximum of 90%.
• Export thread and thread cache based environment variables.
The thread variables PTHREAD_FORCE_SCOPE_SYSTEM and PERF_ENABLE control the
contention scope without modifying the application source, and improves the performance
of a thread application with multiple thread models.
The thread cache variable _M_CACHE_OPTS improves the application speed by reducing
mutex contention among threads and by deferring coalescence of blocks. The three parameters
for this variable are the average number of blocks per bucket, number of buckets, and minutes
to retain the unused cache.
To export these variables, enter the following commands before starting mysqld:
#export PTHREAD_FORCE_SCOPE_SYSTEM=1
#export PERF_ENABLE=1
#export _M_CACHE_OPTS=150:20:0
MySQL High Availability Using HP Serviceguard
This section describes how allow for high availability in MySQL by using HP Serviceguard.
HP Serviceguard
HP Serviceguard is specialized software for protecting mission-critical applications from a wide
variety of hardware and software failures. A high availability computer system allows application
services to continue even in the event of a hardware or software failure. High availability systems
protect users from software failures and from the failure of a system processing unit (SPU), disk,
or local area network (LAN) component. If one component fails, the redundant component takes
over. HP Serviceguard and other high availability subsystems coordinate the transfer between
components.
MySQL Toolkit
The MySQL toolkit for HP Serviceguard consists of a set of shell scripts used by a package control
script to start, stop, and monitor the MySQL server package. The toolkit simplifies the effort of
making MySQL highly available by integrating MySQL with HP Serviceguard for HP-UX. The
architecture of MySQL High Availability using HP Serviceguard is displayed in Figure 4-1
(page 36)
MySQL Performance Tuning Tips 35