# Munin reporting strange memory behavior



## cedivad (Jan 14, 2012)

MySQL (mariadb) is configured to use up to 100GB of ram. I don't understand what's happening, is this a problem? Can I debug it? Thanks.


----------



## cedivad (Jan 14, 2012)

```
Jan 14 12:22:53 root last message repeated 2 times
Jan 14 12:22:53 root kernel: swap_pager_getswapspace(4): failed
Jan 14 12:22:53 root kernel: swap_pager_getswapspace(2): failed
Jan 14 12:22:53 root kernel: swap_pager_getswapspace(2): failed
Jan 14 12:22:53 root kernel: swap_pager_getswapspace(4): failed
Jan 14 12:23:04 root kernel: pid 79933 (mysqld), uid 88, was killed: out of swap space
```

Here is my bet: mysql is configured to use 100GB of ram. Unlike mysql under CentOS, for example, it doesn't preallocate all the ram it needs. So when it reaches what could be the limit for the ram, it doesn't stop and it swaps to the disk. Than the error.


----------



## gkontos (Jan 14, 2012)

It might sound stupid but how exactly are you allocating 100GB of RAM to my.cnf?


----------



## cedivad (Jan 14, 2012)

gkontos said:
			
		

> It might sound stupid but how exactly are you allocating 100GB of RAM to my.cnf?



This is my my.cnf:


```
[mysqld]
plugin-load=ha_xtradb.so

datadir=/w/mysql
innodb_buffer_pool_size = 120G
innodb_log_file_size = 2G
innodb_file_per_table=1
max_connections = 500
```


----------



## cedivad (Jan 14, 2012)

I now see that it's 120GB, not 100GB.
It doesn't' change nothing, i had the same problem even with a innodb buffer size of 60GB.


----------



## gkontos (Jan 14, 2012)

These numbers are way out of my league! 

Just for your reference here is what I am using on a server that I don't want mysql to consume more than 1GB


```
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
```
 
So far, this is woking great. Here is an top output of a relative quite day:


```
PID USERNAME  THR PRI NICE   SIZE    RES STATE   C   TIME   WCPU COMMAND
 1466 mysql      19  20    0   824M   253M uwait   1   0:26  0.00% mysqld
```


----------



## da1 (Jan 15, 2012)

Maybe you have something else consuming the RAM. What FS do you have (zfs by any chance?) ? You could also try using the memory by process munin plugin to see exactly how much memory mariadb is taking.
What's your swap size btw? Maybe it's worth increasing it for the time being? What about the OS release? Which one is it? Do you have any exotic settings in /boot/loader.conf and/or /etc/make.conf?
And the hardware? What's the total amount of RAM you have?


----------



## cedivad (Jan 16, 2012)

Thanks for the munin plugin suggestion and yes, zfs used to much ram. I limited it to 10GB, i will now check what happens (cause of i think there should be others problems, since that limiting zfs's arc caused problems anyway). So let's see...


----------



## gkontos (Jan 16, 2012)

If you are using ZFS with innodb databases with this kind of memory requirements, you really need to do some tuning.

sysutils/zfs-stats is a good place to start for monitoring your ARC efficiency. IMHO 10GB is very low for your system and can lead to deadlocks.

FYI http://blogs.oracle.com/realneel/entry/mysql_innodb_zfs_best_practices


----------



## cedivad (Jan 16, 2012)

I might try to erase that setting (the 10gb limit for zfs on the boot config file) and let the system decide by itself how much ram to use - since that i found the other main user for the ram, a bad memory management on a php process.

This is the output of zfs-stats:

```
------------------------------------------------------------------------
ZFS Subsystem Report				Mon Jan 16 19:35:41 2012
------------------------------------------------------------------------

System Information:

	Kernel Version:				900044 (osreldate)
	Hardware Platform:			amd64
	Processor Architecture:			amd64

	ZFS Storage pool Version:		28
	ZFS Filesystem Version:			5

FreeBSD 9.0-RELEASE #0: Tue Jan 3 07:46:30 UTC 2012 root
 7:35PM  up  5:01, 1 user, load averages: 6.61, 7.67, 7.67

------------------------------------------------------------------------

System Memory:

	25.09%	35.04	GiB Active,	0.50%	718.98	MiB Inact
	2.51%	3.50	GiB Wired,	0.00%	160.00	KiB Cache
	71.90%	100.40	GiB Free,	0.00%	648.00	KiB Gap

	Real Installed:				160.00	GiB
	Real Available:			89.95%	143.92	GiB
	Real Managed:			97.03%	139.64	GiB

	Logical Total:				160.00	GiB
	Logical Used:			36.81%	58.89	GiB
	Logical Free:			63.19%	101.11	GiB

Kernel Memory:					1.55	GiB
	Data:				98.80%	1.53	GiB
	Text:				1.20%	19.01	MiB

Kernel Memory Map:				1.43	GiB
	Size:				76.54%	1.09	GiB
	Free:				23.46%	343.65	MiB

------------------------------------------------------------------------

ARC Summary: (HEALTHY)
	Memory Throttle Count:			0

ARC Misc:
	Deleted:				24.04m
	Recycle Misses:				37.76m
	Mutex Misses:				51.31k
	Evict Skips:				51.31k

ARC Size:				107.06%	1.00	GiB
	Target Size: (Adaptive)		12.50%	120.00	MiB
	Min Size (Hard Limit):		12.50%	120.00	MiB
	Max Size (High Water):		8:1	960.00	MiB

ARC Size Breakdown:
	Recently Used Cache Size:	10.94%	112.44	MiB
	Frequently Used Cache Size:	89.06%	915.32	MiB

ARC Hash Breakdown:
	Elements Max:				716.30k
	Elements Current:		99.81%	714.95k
	Collisions:				3.75m
	Chain Max:				5
	Chains:					56.05k

------------------------------------------------------------------------

ARC Efficiency:					97.00m
	Cache Hit Ratio:		78.76%	76.40m
	Cache Miss Ratio:		21.24%	20.60m
	Actual Hit Ratio:		62.56%	60.69m

	Data Demand Efficiency:		79.68%	14.56m
	Data Prefetch Efficiency:	8.12%	271.51k

	CACHE HITS BY CACHE LIST:
	  Anonymously Used:		16.11%	12.31m
	  Most Recently Used:		23.37%	17.85m
	  Most Frequently Used:		56.06%	42.83m
	  Most Recently Used Ghost:	1.77%	1.35m
	  Most Frequently Used Ghost:	2.70%	2.06m

	CACHE HITS BY DATA TYPE:
	  Demand Data:			15.19%	11.60m
	  Prefetch Data:		0.03%	22.05k
	  Demand Metadata:		64.20%	49.05m
	  Prefetch Metadata:		20.58%	15.73m

	CACHE MISSES BY DATA TYPE:
	  Demand Data:			14.37%	2.96m
	  Prefetch Data:		1.21%	249.46k
	  Demand Metadata:		38.72%	7.98m
	  Prefetch Metadata:		45.70%	9.41m

------------------------------------------------------------------------

L2 ARC Summary: (HEALTHY)
	Passed Headroom:			15.96k
	Tried Lock Failures:			7.60k
	IO In Progress:				102.03k
	Low Memory Aborts:			90
	Free on Write:				41.43k
	Writes While Full:			2.70k
	R/W Clashes:				479
	Bad Checksums:				0
	IO Errors:				0
	SPA Mismatch:				12.30m

L2 ARC Size: (Adaptive)				24.76	GiB
	Header Size:			0.59%	148.75	MiB

L2 ARC Breakdown:				20.60m
	Hit Ratio:			9.70%	2.00m
	Miss Ratio:			90.30%	18.60m
	Feeds:					21.00k

L2 ARC Buffer:
	Bytes Scanned:				720.40	GiB
	Buffer Iterations:			21.00k
	List Iterations:			1.31m
	NULL List Iterations:			535.35k

L2 ARC Writes:
	Writes Sent:			100.00%	16.47k

------------------------------------------------------------------------

File-Level Prefetch: (HEALTHY)

DMU Efficiency:					513.75m
	Hit Ratio:			62.72%	322.22m
	Miss Ratio:			37.28%	191.53m

	Colinear:				191.53m
	  Hit Ratio:			0.00%	5.44k
	  Miss Ratio:			100.00%	191.52m

	Stride:					319.89m
	  Hit Ratio:			99.83%	319.34m
	  Miss Ratio:			0.17%	540.57k

DMU Misc:
	Reclaim:				191.52m
	  Successes:			0.21%	410.13k
	  Failures:			99.79%	191.11m

	Streams:				2.51m
	  +Resets:			2.00%	50.21k
	  -Resets:			98.00%	2.46m
	  Bogus:				0

------------------------------------------------------------------------

VDEV cache is disabled

------------------------------------------------------------------------

ZFS Tunables (sysctl):
	kern.maxusers                           384
	vm.kmem_size                            1610612736
	vm.kmem_size_scale                      1
	vm.kmem_size_min                        0
	vm.kmem_size_max                        12884901888
	vfs.zfs.l2c_only_size                   26483158016
	vfs.zfs.mfu_ghost_data_lsize            29888512
	vfs.zfs.mfu_ghost_metadata_lsize        94576128
	vfs.zfs.mfu_ghost_size                  124464640
	vfs.zfs.mfu_data_lsize                  393216
	vfs.zfs.mfu_metadata_lsize              1761280
	vfs.zfs.mfu_size                        3121152
	vfs.zfs.mru_ghost_data_lsize            0
	vfs.zfs.mru_ghost_metadata_lsize        485376
	vfs.zfs.mru_ghost_size                  485376
	vfs.zfs.mru_data_lsize                  0
	vfs.zfs.mru_metadata_lsize              9388032
	vfs.zfs.mru_size                        522625024
	vfs.zfs.anon_data_lsize                 0
	vfs.zfs.anon_metadata_lsize             0
	vfs.zfs.anon_size                       57574400
	vfs.zfs.l2arc_norw                      1
	vfs.zfs.l2arc_feed_again                1
	vfs.zfs.l2arc_noprefetch                1
	vfs.zfs.l2arc_feed_min_ms               200
	vfs.zfs.l2arc_feed_secs                 1
	vfs.zfs.l2arc_headroom                  2
	vfs.zfs.l2arc_write_boost               8388608
	vfs.zfs.l2arc_write_max                 8388608
	vfs.zfs.arc_meta_limit                  251658240
	vfs.zfs.arc_meta_used                   1020801168
	vfs.zfs.arc_min                         125829120
	vfs.zfs.arc_max                         1006632960
	vfs.zfs.dedup.prefetch                  1
	vfs.zfs.mdcomp_disable                  0
	vfs.zfs.write_limit_override            0
	vfs.zfs.write_limit_inflated            463607169024
	vfs.zfs.write_limit_max                 19316965376
	vfs.zfs.write_limit_min                 33554432
	vfs.zfs.write_limit_shift               3
	vfs.zfs.no_write_throttle               0
	vfs.zfs.zfetch.array_rd_sz              1048576
	vfs.zfs.zfetch.block_cap                256
	vfs.zfs.zfetch.min_sec_reap             2
	vfs.zfs.zfetch.max_streams              8
	vfs.zfs.prefetch_disable                0
	vfs.zfs.mg_alloc_failures               36
	vfs.zfs.check_hostid                    1
	vfs.zfs.recover                         0
	vfs.zfs.txg.synctime_ms                 1000
	vfs.zfs.txg.timeout                     5
	vfs.zfs.scrub_limit                     10
	vfs.zfs.vdev.cache.bshift               16
	vfs.zfs.vdev.cache.size                 0
	vfs.zfs.vdev.cache.max                  16384
	vfs.zfs.vdev.write_gap_limit            4096
	vfs.zfs.vdev.read_gap_limit             32768
	vfs.zfs.vdev.aggregation_limit          131072
	vfs.zfs.vdev.ramp_rate                  2
	vfs.zfs.vdev.time_shift                 6
	vfs.zfs.vdev.min_pending                4
	vfs.zfs.vdev.max_pending                10
	vfs.zfs.vdev.bio_flush_disable          0
	vfs.zfs.cache_flush_disable             0
	vfs.zfs.zil_replay_disable              0
	vfs.zfs.zio.use_uma                     0
	vfs.zfs.version.zpl                     5
	vfs.zfs.version.spa                     28
	vfs.zfs.version.acl                     1
	vfs.zfs.debug                           0
	vfs.zfs.super_owner                     0

------------------------------------------------------------------------
```


----------



## gkontos (Jan 16, 2012)

You seem to be limiting you ARC size to less than a Gigabyte!



			
				cedivad said:
			
		

> ```
> vfs.zfs.arc_max                         1006632960
> ```


----------



## da1 (Jan 17, 2012)

cedivad said:
			
		

> I might try to erase that setting (the 10gb limit for zfs on the boot config file) and let the system decide by itself how much ram to use



I wouldn;t do that because ZFS is RAM-hungry this can cause it to use more and more RAM, leading to mariadb having not enough RAM. I would suggest setting the InnoDB buf pool size to a value and then subtract that value from the total amount of ram and the result can be safely allocated to ZFS via vfs.zfs.arc_max.


----------

