HPOS 3.5 - Tuning MySQL parameters for HPOS

HP Output Server 3.5 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:
You can tune MySQL with defragmentation of databases as follows:
1. Stop HP Output Server servers.
2. Execute the following command to back up the databases from the mySQLinstallDir/bin directory:
./mysqldump --defaults-file=mySQLinstallDir/my.cnf --quote-names
--add-drop-table --quick --extended-insert --all-databases
--result-file=<dump_file>
3. Stop the MySQL server.
4. Make a backup of the data directory.
5. Start the MySQL server.
6. Create a script with the following commands, which will extract the database names and execute the drop
database command on all the existing databases except MySQL internal databases:
./bin/mysql --defaults-file=./my.cnf -e " show databases;"| \
sed 's/\|//g' | \
sed '/^+/d' | \
grep -v Database | \
grep domain name| \
while read db_name
do
./bin/mysql --defaults-file=./my.cnf -e "drop database \`$db_name\`"
done
7. Shut down the MySQL server.
8. Remove the ibdata and iblog files.
9. Append the following lines in the my.cnf file to tune the MySQL parameters:
table_cache=250
innodb_buffer_pool_size=150M
innodb_additional_mem_pool_size=22M
read_buffer_size=18M
read_rnd_buffer_size=18M
innodb_log_file_size=30M
innodb_log_buffer_size=20M
innodb_sync_spin_loops=120 *
innodb_thread_concurrency=18 *
Advantages • 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
Disadvantages • Re-creates the database
• Corrupts the database if the re-creation of the database fails