Hello all! We have a master-master replication system. Both nodes run 5.0.54, the version compiled by centos, running on CentOS 5.1 x86_64. The boxen have 8 GB RAM, 1 GB swap, and the cpu is Xeon(R) CPU X3220 @ 2.40GHz (shows as 4 cpus to the system). db1 is serving the live site, db2 is used only for nightly dumps.
There are 2245 databases, the majority of those (2240 or so) have 195 tables, of which about 190 are InnoDB (with "innodb_file_per_table" set). That means that there is the default ibdata1 that is 1Gig, and 2 ib_logfile files, 128 Megs each, and then the ibd files for each table in those databases (current count is 415307). The /var/lib/mysql directory is on a 300GB Raid 1 mirror with SAS drives. Replication broke on db2, so I decided to start from scratch* and just mysqldump everything from db1 to db2. The way that I do the dump is get a list of databases, then call mysqldump for each database. The problem is that while I am running the script, the amount of memory that mysql on db1 uses gradually grows until it uses all RAM and swap and the kernel kills mysqld (it's not mysqldump, it's mysql itself). I have my settings very conservatively set, and by my estimate, there should be no more than about 4 GB being used by mysql in its entirety (less than 2 Gigs for mysql itself, and 2 Gigs for InnoDB). I have included my my.cnf at the bottom of this message. The problem is that InnoDB (I think) is using memory and not giving it back (memory fragmentation? It's a guess). I have flushed everything that can be flushed. Is there any way I can tell mysql to force innodb to flush some of that memory and give it back to the system? The only thing left for me to do otherwise is shut mysql down and start it back up. It's a live system, and a restart takes a few minutes, which results in downtime that we don't want. I'm trying to get my data back onto the second master, at which point I will have a much better feeling about the state of things. My trip-point is that the process of copying the data from db1 to db2 is causing db1 to run out of memory. I've seen posts where Kevin Burton mentions innodb_file_per_table and performance (and somewhere I read a comment on error recovery, but I can't find it now). We might be pushing our system a bit beyond any of his test cases with the large number of databases, and I think we might be RAM starved compared to his tests. Here is my current memory consumption according to SHOW INNODB STATUS: ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 6681154330; in additional pool allocated 1048576 Buffer pool size 131072 Free buffers 17 Database pages 128431 Modified db pages 14 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 1175284, created 489085, written 4557296 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 This is from SHOW STATUS: | Qcache_free_blocks | 203 | | Qcache_free_memory | 8300808 | | Qcache_hits | 272676719 | | Qcache_inserts | 61184474 | | Qcache_lowmem_prunes | 32835896 | | Qcache_not_cached | 19674714 | | Qcache_queries_in_cache | 17689 | | Qcache_total_blocks | 38769 | * Initially I did try rsyncing all data/tables from db1, but the sheer amount of errors in the mysqld.log complaining about mismatched id's when trying to start up was disconcerting. The first attempt to insert data caused an error 11, mysqld restarted, it repeated the entire process of reading in all the tables and complaining about mismatched id's, then it spit out an Assertion, along with this: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 203998 K bytes of memory so I am positive that my (MyISAM) settings are very conservative and shouldn't be using too much memory. USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND root 27482 0.0 0.0 63812 1204 ? S Nov01 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/mysql/mysql.sock --log-error=/var/log/mysqld.log --pid-file=/var/run/mysqld/mysqld.pid mysql 27541 3.0 85.5 7270500 6998988 ? Sl Nov01 1041:08 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --socket=/var/lib/mysql/mysql.sock /etc/my.cnf: [mysql] prompt=db1 (\\d)>\\_ [client] default-character-set=utf8 [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock default-character-set=utf8 default-table-type=innodb default-time-zone=UTC skip-external-locking set-variable = key_buffer=4M set-variable = max_allowed_packet=16M set-variable = max_connections=200 set-variable = read_buffer_size=512K set-variable = sort_buffer_size=1M set-variable = query_cache_size=32M set-variable = table_cache=4096 set-variable = thread_cache=32 set-variable = tmp_table_size=128M set-variable = wait_timeout=28800 innodb_file_per_table=1 innodb_flush_log_at_trx_commit=1 innodb_buffer_pool_size = 2048M innodb_log_file_size = 128M innodb_open_files=4000 innodb_flush_method=O_DIRECT server-id=2401 master-host=db2 master-user=replicate master-password=XXXXXXXXXXXXXXXXX master-connect-retry=30 log-bin expire_logs_days=20 log-slave-updates auto-increment-increment=2 auto-increment-offset=1 log-slow-queries = /var/lib/mysql/slow.log long_query_time = 5 log_queries_not_using_indexes = 1 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysqldump] quick -- Regards... Todd All truth passes through three stages. First, it is ridiculed. Second, it is violently opposed. Third, it is accepted as being self-evident. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
