Here is a config diff that made mysql usable again. As the database
grew in size, buffer sizes in the config were increased to try to boost
mysql performance.
Unfortunately it didn't work as expected. As the config was tweaked,
mysql slowed down even more. Removing all settings from the my.cnf
restored performance.
So what was the setting below that was tanking mysql performance? I
suspect that "innodb_log_buffer_size=32M" was the culprit.
#skip-innodb
key_buffer = 2048M
max_allowed_packet = 1M
-table_cache = 1536
-sort_buffer_size = 256M
-net_buffer_length = 64K
-read_buffer_size = 256M
-read_rnd_buffer_size = 256M
-myisam_sort_buffer_size = 256M
+#table_cache = 1536
+#sort_buffer_size = 256M
+#net_buffer_length = 64K
+#read_buffer_size = 256M
+#read_rnd_buffer_size = 256M
+#myisam_sort_buffer_size = 256M
language = /usr/share/mysql/english
myisam_data_pointer_size = 6
# daves: this may not quite work ...
-join_buffer_size = 256M
+#join_buffer_size = 256M
long_query_time = 10
log-long-format
log-slow-queries
@@ -117,9 +117,9 @@
# the rest of the innodb config follows:
# don't eat too much memory, we're trying to be safe on 64Mb boxes.
# you might want to bump this up a bit on boxes with more RAM
-innodb_buffer_pool_size = 2048M
+innodb_buffer_pool_size = 6144M
# this is the default, increase if you have lots of tables
-innodb_additional_mem_pool_size = 32M
+innodb_additional_mem_pool_size = 4M
#
# i'd like to use /var/lib/mysql/innodb, but that is seen as a
database :-(
# and upstream wants things to be under /var/lib/mysql/, so that's the
route
@@ -134,7 +134,7 @@
# sensible values range from 1MB to
(1/innodb_log_files_in_group*innodb_buffer_pool_size)
innodb_log_file_size = 1G
# this is the default, increase if you have very large transactions.
-innodb_log_buffer_size = 32M
+innodb_log_buffer_size = 1M
# this is the default, and won't hurt you.
# you shouldn't need to tweak it.
set-variable = innodb_log_files_in_group=2
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
