Calculating Sphinx Memory Usage

Even when you are using on-disk indexes, Sphinx can require a surprising large amount of memory as it has to keep all the index files (except the .spd and .spp ones) in memory. If you have more than a couple of indexes it can be a hassle to calculate the RAM usage manually , hence:

1: Estimate via command line (credits to this presentation)

    ls -la /##data dir##/|egrep "spa|spi"|awk '{ SUM += $5} END { print SUM/1024/1024/1024 }'
    # 36.978 (result is in GB)

2: Via the following script

			$mysqli = new mysqli('', '', '', '', 9307);
			$ramBytes = 0;
			foreach ($mysqli->query('SHOW TABLES')->fetch_all(MYSQLI_ASSOC) as $row) {
				if ($row['Type'] !== 'local') {
				foreach ($mysqli->query("SHOW INDEX {$row['Index']} STATUS")->fetch_all(MYSQLI_ASSOC) as $rowStatus) {
					if ($rowStatus['Variable_name'] === 'ram_bytes') {
						$ramBytes += $rowStatus['Value'];
			echo $ramBytes,'B',PHP_EOL,number_format($ramBytes/pow(1024,2)),'MB',PHP_EOL;
		}catch( Exception $e ){
			print_r( $e );
		# Put the script somewhere
		# You may need to change the port
		nano /tmp/calc_sphinx_mem.php
		# Run it
		php /tmp/calc_sphinx_mem.php

I wrote this based on the recommendation in this official blog post to use the 'ram_bytes' output from the SHOW INDEX STATUS output. It simply loops through all the local indexes which a SHOW TABLES responds with and runs a STATUS, keeping a sum of all the ram_bytes counters returned.

Load Comments...