HPOS 3.5 - Tuning MySQL parameters for HPOS

ManualsBrandsHP ManualsSoftwareHP Output Management Services Software

Table Of Contents

Tuning MySQL parameters for HP Output Server

HP Output Server 3.5 1

Technical Note

Tuning MySQL parameters for HP Output

Server

This technical note provides information about how to tune various MySQL server parameters for HP Output Server.

Platforms affected

All platforms

Problem

The external physical destination hangs when attribute modification fails at the end of job processing.

Solution

HP Output Server 3.5 is an InnoDB-oriented application. You should tune the default values of MySQL server

tunable parameters for better performance, stability, and reliability.

Tuning with or without defragmentation of databases

HP recommends tuning MySQL with defragmentation of databases because it helps to improve performance. Before

tuning MySQL, compare the value of the innodb_data_file_path ibdata1: 250M: autoextend

parameter in the my.cnf file, which indicates the currently set size of the ibdata1 file, with the actual size of the

ibdata1 file.

If the actual size of the ibdata1 file is larger than the value set in the my.cnf file, then the database is

defragmented. Proceed with “MySQL tuning with defragmentation of databases” on page 2.

If your database is not defragmented, follow the instructions in “MySQL tuning without defragmentation of

databases” on page 3.

NOTE: With MySQL tuning, if you change the value of the

-database-client-max-connections attribute to 25 on delivery servers and

the CM server, performance should improve significantly.

Summary of content (6 pages)

PAGE 1
Technical Note Tuning MySQL parameters for HP Output Server This technical note provides information about how to tune various MySQL server parameters for HP Output Server. Platforms affected All platforms Problem The external physical destination hangs when attribute modification fails at the end of job processing. Solution HP Output Server 3.5 is an InnoDB-oriented application. You should tune the default values of MySQL server tunable parameters for better performance, stability, and reliability.
PAGE 2
Tuning MySQL parameters for HP Output Server Solution MySQL tuning with defragmentation of databases The advantages and disadvantages of tuning with defragmentation of databases are as follows: Advantages Disadvantages • Increases the performance of reads per second • Decreases the response time • Configures indexes • Increases the query performance • Cleans temporary data in the datafiles and undo segments • Re-creates the database • Corrupts the database if the re-creation of the database
PAGE 3
Tuning MySQL parameters for HP Output Server Solution query_cache_size=800K innodb_data_file_path ibdata1: 250M: autoextend innodb_autoextend_increment=25 NOTE: Execute the following command to get the values of the parameters marked *: innodb_sync_spin_loops= 10 * Number of CPUs innodb_thread_concurrency=2 * (Number of CPUs + Number of disks in use for MySQL) For more information, see the following topics at the end of this technical note: “Parameters that affect memory,” “Parameters that affect disk,” an
PAGE 4
Tuning MySQL parameters for HP Output Server Solution innodb_log_buffer_size=20M innodb_sync_spin_loops=120 * innodb_thread_concurrency=18 * query_cache_size=800K NOTE: Execute the following command to get the values of the parameters marked *: innodb_sync_spin_loops=10 * Number of CPUs innodb_thread_concurrency=2 * (Number of CPUs + Number of disks in use for MySQL) For more information, see the following topics in the current technical note: “Parameters that affect memory,” “Parameters that affect disk,”
PAGE 5
Tuning MySQL parameters for HP Output Server Solution • read_rnd_buffer_size This parameter helps in pdls queries. During pdls queries, data is selected randomly and not through sequential queries. Increase the value by 20 to 50 percent of 256 KB (the current value). Parameters that affect CPU • innodb_sync_spin_loops This parameter balances the CPU time wasted while running spin locks with unnecessary context switches. If the spin lock setting is high, the CPU time wasted will increase.
PAGE 6
Tuning MySQL parameters for HP Output Server Solution HP Output Server 3.