HPOS 3.5 - Tuning MySQL parameters for HPOS

HP Output Server 3.5 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,” and “Parameters that affect CPU.”
NOTE: If the system does not have sufficient memory, the MySQL server will not start.
4. Start the MySQL server.
5. Start HP Output Server servers.
MySQL tunable parameters
The following sections describe the MySQL tunable parameters.
Parameters that affect memory
• innodb_buffer_pool_size
This is the size of the memory chunk used by InnoDB to store the index and related data in memory. It should be
large enough for all operations on InnoDB tables.
This parameter is dependent on the size of the biggest table in MySQL server databases.
• innodb_log_buffer_size
This is the size of the buffer that InnoDB uses to write to the log files on disk. Values typically range from 1 MB
to half the combined size of log files. A large log buffer allows large transactions to run without a need to write
the log to disk until the transaction is committed. Therefore, a value of 25 to 30 percent of buffer pool size is
required.
• innodb_additional_mem_pool_size
This parameter is used to hold the internal structure and the hash table. It must be approximately 50 percent of
the innodb_buffer_pool_size.
• read_buffer_size
This parameter helps to load a huge amount of data from the disk for operations such as pdq and pdls.
Increase the value by 20 to 50 percent of 128 KB (the current value). This parameter helps in table scans.