So as you probably know SPM 2.x requires the use of MySql as the backend database to store all the SPM structure and data.
By default MySql creates a single file on the server for the database. Not bad if you are on Unix but if you are on windows (and with SPM you are probably on Windows) then when SPM gets large the single database file gets large.
Having a single file on windows that is over 2GB is a bad idea (in my opinion anyway and especially if you are on 32 bit windows and of course i you are on SPM 2.x you are on 32 bit windows)
One of the other challenges with MySQL is unlike some other databases it just seems to grow and grow and never reclaims any of its internal space. In fact the only way to reclaim space ans shrink the database file is to export the entire database, delete the database, recreate it and reimport the exported file. Time consuming and fraught with risk.
However there is an answer…. (well a suggestion anyway)
You can tell MySql to create a number of smaller files to effectively partition the database files. How I hear you ask…..
Well in the my.ini for the database added these lines:
innodb_data_home_dir=drive:/dir1/dir2/dir3/ibdata
innodb_data_file_path=ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M;ibdata5:2000M;ibdata6:2000M;ibdata7:2000M;ibdata8:2000M;ibdata9:2000M;ibdata10:2000M;ibdata11:2000M;ibdata12:2000M;ibdata13:2000M;ibdata14:2000M;ibdata15:2000M;ibdata16:2000M:autoextend
innodb_log_group_home_dir=drive:/dir1/dir2/dir3/iblogs
So the first line says where tostore the db files.
The second line says to create 16 2GB files and then automatically extend the last one to infinity (and beyond 
if the MySql database needs to go over 32 GB.
One downside is that MySql will create all the files when it starts up, so you will suddenyl have consumed 32GB of disk. You might want to tune the number of files based on your environment.
Lastly before you do this you will need to backup your MySql instance, stop the Mysql service, remove the old MySql DB files and then restart MySql, which will recreate the DB files with the new partitonedversons automagically.
Then reimport the backup and wahoo you are away (plus ypu have just regorg’d the db and reclaim any lost space as well).
And of course you will ask, yes we asked SAS Tech Support and yes they ‘indicated’ this was not unsupported whichmeans it is supported.
Partition away!
Recent Comments