Hi all, thanks for input.
A small script which shows clients and topics sorted by number of messages. Useful to find clients which are requesting persistent connections (cleansession=false) but disconnect after subscribing:
#!/bin/bash
mosquitto_db_dump /var/lib/mosquitto/mosquitto.db > mdump.txt
cat mdump.txt |grep -A 10 'DB_CHUNK_MSG_STORE:'|egrep 'Topic|Store ID'|tr -d '\n' |sed -e 's/Store/\nStore/g' > storeTopic.txt
echo ""
echo "Largest topics:"
cat storeTopic.txt | grep Topic | sed 's/.*Topic//'|uniq -c | sort -g|tail
echo ""
\rm brokerdata.db
echo 'create table storetopic (storeid,topic);'|sqlite3 brokerdata.db
echo "BEGIN;" > tmp1.sql
cat storeTopic.txt | awk '{printf("insert into storetopic(storeid,topic) values ('"'"'%s'"'"','"'"'%s'"'"');\n", $3,$5)}' >> tmp1.sql
echo "COMMIT;" >> tmp1.sql
cat tmp1.sql | sqlite3 brokerdata.db
cat mdump.txt |grep -A 9 'DB_CHUNK_CLIENT_MSG:'| tr -d '\n'| sed -e 's/DB/\nDB/g' > clientmsg.txt
echo 'create table clientmsg (clientid,storeid);'|sqlite3 brokerdata.db
echo "BEGIN;" > tmp1.sql
cat clientmsg.txt | awk '{printf("insert into clientmsg(clientid,storeid) values ('"'"'%s'"'"','"'"'%s'"'"');\n", $6,$9)}' >> tmp1.sql
echo "COMMIT;" >> tmp1.sql
cat tmp1.sql | sqlite3 brokerdata.db
echo "Messages stored in topics probably from persistent clients no longer listening:"
echo 'select topic,clientid from storetopic,clientmsg where storetopic.storeid = clientmsg.storeid;'|sqlite3 brokerdata.db |sort | uniq -c |sort -g