Layer7 API Management

We do get some issues related to the ibdata file size and I got this confusion regarding the same. From what I understand in mysql by design the ibdata file size never goes down and it can auto extend to set value based on the entry innodb_data_file_path=ibdata:100M:autoextend:max:<MAX_SIZE>  in /etc/my.conf

What happens when ibdata becomes the set MAX_SIZE and i delete the audit events and optimize the tables "audit_main, audit_admin, audit_detail, audit_detail_params, audit_message, audit_system"

Where does the freed up space go ? Does this mean mysql can still write some data= size of cleaned up logs even though the ibdata size shows maximum on the file system? If so can i just do delete audit events and continue using the gateways without downtime and then shrink ibdata on a scheduled time?

Regards,

Anand

Hi anand.rudran,

We have a KB article in the Support Portal on this topic of managing the ibdata file size. It is located at https://na32.salesforce.com/articles/Knowledge_Base/Shrinking-MySQL-ibdata-file?popup=true for your reference. You must be signed into the Support Portal to access this KB article.

To summarize the article, the only way to shrink the size of the ibdata file is to delete it and restore from a backup which will then create a new ibdata file size back in line with the current size of all the tables in the database. There is a lot more to it, but here is a snippet from the KB article on the actual shrinking portion of the instructions (but note there are steps to follow prior and afterwards to have a clean system):

Shrink the ibdata file

1. Stop the MySQL service on the primary Gateway: service mysql stop
2. Remove the existing ibdata files: rm -rf /var/lib/mysql/ib*
3. Start the MySQL service on the primary Gateway: service mysql start
4. Extract the contents of the primary Gateway database backup: gzip -d /root/ibdata-shrink-backup.sql.gz
5. Restore the primary Gateway database backup: mysql < /root/ibdata-shrink-backup.sql                                                                   
6. Start the Gateway service on the primary Gateway: service ssg start
7. Repeat the Shrink the ibdata file section on the secondary database node

I hope that helps. Please review the article noted in the link above and let me know if you have any further questions.

Sincerely,

Dustin Dauncey

Support Engineer, Global Customer Success

Email: API-Support@ca.com

Phone: +1 800 225 5224

Outside of North America - ca.com/us/worldwide.aspx

CA API Management Community: ca.com/talkapi

Hi Dustin,

I have seen this steps before on the CA knowledge base and also performed the same multiple times. My question is a bit different. Sometimes by mistake tracing stays enabled on gateway and due to high volume, it fills up very fast. But its difficult to do a periodic activity on a Production system. Instead of that i am thinking if we can just purge the audit logs and leave ibdata file as it is. My assumption is that the purging frees up some space in audit tables and the logs can continue writing to audit tables again, we do use audit purge scripts and I am thinking about customizing it to run more intelligently(rather than setting a age or number of records) based on the space consumed by audits. I am still not sure if other functionalities of gateway work.

Regards,

Anand

Hi Anand,

The ibdata file will remain the same size without growing if you clear up space within it. But if the ibdata file has reached its maximum size, then all processing that involves the database ceases at that point and you must shrink the ibdata file, and that is due to the configuration in the /etc/my.cnf file which dictates the maximum size the ibdata file can grow to before MySQL essentially stops processing any SQL transactions.

So your theory is definitely sound and makes sense, but in the event of a filled up ibdata file that's reached its maximum allowed size, the only way to make processing move forward again is by shrinking the ibdata file.

Sincerely,

Dustin Dauncey

Support Engineer, Global Customer Success

Email: API-Support@ca.com

Phone: +1 800 225 5224

Outside of North America - ca.com/us/worldwide.aspx

CA API Management Community: ca.com/talkapi

My assumption is that audits consume most percent of the space and If I could clean it more effectively, I should not face this issue frequently

Hi Anand,

If the audit_purge.sh script is run more frequently, it should prevent the ibdata file from growing too large, correct. The audits definitely do take up the vast majority the disk space for the database file. The basic premise of that script file is to keep the disk space usage to a minimum at least in terms of the audits. We have some customers who run it weekly, most daily, and some even hourly or more frequently than that. It truly depends on the traffic volumes and expected data and how much is actually being audited in your environment. You will need to test it out to see what is right for your environment.

How often do you run the audit_purge.sh script currently?

Sincerely,

Dustin Dauncey

Support Engineer, Global Customer Success

Email: API-Support@ca.com

Phone: +1 800 225 5224

Outside of North America - ca.com/us/worldwide.aspx

CA API Management Community: ca.com/talkapi

Dustin,

We run it every 2 hours, age is set as 7 days, this does not solve the problem, say if somebody accidentally turns tracing or if there are any errors due to which the transaction fails and writes more data than usual(we usually does not write audits for success transactions), What we are thinking is, we should also do a check on the size of audit records and based on that, limit the age of logs(dynamically step it down from 7 days to may be 3 days or just 1 day or even few hours) to have a better control on the ibdata size growth. I also think that we can add more checks on the script to alert if there are tracing left on, I can check the published_service table for that.

Thanks,

Anand

Hi Anand,

Just for clarification, you are now wanting to expand upon the audit_purge.sh script to include checks for the size of the records / tables and run it automatically when a certain threshold is reached, is that correct? I would not know how to do that, unfortunately, and something like that definitely hasn't been tested nor QA'd, so CA Services (a paid service) would be your best bet as CA Support would not support customizations to that script at this time. Alternatively I hope that somebody very familiar with scripting and that script in particular may be able to offer some assistance on what needs to be changed in the script for that to happen.

For what it's worth though, the following line is what I use in CA Support to determine the sizes of all the tables in the ssg database, and you may be able to use this as a starting point, or alternatively have some monitoring software run it and report back to you when the size breaches a threshold. The line that I run to find out the table sizes in the ssg database is this one: mysql -e 'select table_name, round(((data_length + index_length) / (1024*1024)),2) as "size in megs" from information_schema.tables where table_schema = "ssg";'

Sincerely,

Dustin Dauncey

Support Engineer, Global Customer Success

Email: API-Support@ca.com

Phone: +1 800 225 5224

Outside of North America - ca.com/us/worldwide.aspx

CA API Management Community: ca.com/talkapi

Thanks Dustin, I think I will be able to modify the script or write something similar.

Hi anand.rudran,

Just out of curiosity, since I'm revisiting this community thread and saw that you believed you could write a script to add the detection of table sizes and trigger its execution depending on the size... did you successfully implement that? If so, would you mind sharing out what you did for everyone coming across this in the CA Community?

Sincerely,

Dustin Dauncey

Support Engineer, Global Customer Success

Email: API-Support@ca.com

Phone: +1 800 225 5224

Outside of North America - ca.com/us/worldwide.aspx

CA API Management Community: ca.com/talkapi

Hi DustinDauncey

Honestly I did not test or implemented this, but this is what I had in mind.

# Calculate the size of audit tables

AUDIT_DB_SIZE=`mysql -N -e 'select sum(round(((data_length + index_length) / (1024*1024)),2)) as "Total Size" from information_schema.tables where table_schema = "ssg" and table_name like "audit%";'`

# Remove the float

SIZE=${AUDIT_DB_SIZE%%.*}

# Set limits based on /var/lib/mysql size

LIMIT1=15000

LIMIT2=7000

LIMIT3=5000

# Compare size vs limits and set AGE for audit_purge.sh

if [[ $SIZE -gt $LIMIT1 ]] ; then

  echo "AGE=1 days"

elif [[ $SIZE -gt $LIMIT2 ]] ; then

  echo "AGE=3 days"

elif [[ $SIZE -gt $LIMIT3 ]] ; then

  echo "AGE=5 days"

else

  echo "AGE=7 days"

fi

I was thinking about adding this into audit_purge.sh.

Thanks,

Anand

Hi Dustin,

  Running audit_purge.sh script does not seem to have any effect on reducing the ibdata size. Moreover the audit_purge.sh deletes data from audit_main however even audit_detail and audit_detail_params also seem to be occupying good amount of space. Is there any reason why only audit_admin data is deleted and not from other tables? Also any reason why this does not reduce ibdata.

Regards

Trinath

Hi mtrinath,

Focusing on why the ibdata file never shrinks in size... absolutely, I can explain why the ibdata file never shrinks in size. The ibdata file continuously grows without ever shrinking, and that's per design (by Sun/Oracle, not CA). For a different point of view though, according to this report for a feature request to maintain the sizing of an ibdata file in MySQL, some people view this as a lack of resizing functionality that should be implemented or improved upon in a future version. However, that looks unlikely to change anytime soon, as that report / feature request is over 12 years old now, and still needing to be triaged.

The blunt fact of the matter is this: the ibdata file always will grow in size, never shrink in size, at least in terms of disk space usage. However, if you purged a bunch of information from the database stored in the ibdata file, you will find that new data being added won't expand the size of the ibdata file until it fills out to the size the ibdata file left off at before it continues to grow again. For example: Let's say you have an ibdata file at 5 GB in size on your hard disk, and you then purge 1 GB worth of data from the database written inside the ibdata file. That means that the ibdata file now stores just 4 GB of data, however it still takes up 5 GB of disk space (because remember, it never shrinks per design), and thus has an extra 1 GB now that it can contain before the ibdata file grows any further past the 5 GB in size it was sitting at. So in other words, if you remove data (i.e. via the audit_purge.sh script), it will stop the ibdata file from growing temporarily as it frees up space inside of the ibdata file, even though that is not reflected in actual disk space consumption.

If you run the command which I noted earlier in a comment above in this very same community thread, both before and after the audit_purge.sh script, you should see a difference in sizing of the ssg database. Again for quick reference though, below is the command to run to find the actual size of the ssg database itself:

mysql -e 'select table_name, round(((data_length + index_length) / (1024*1024)),2) as "size in megs" from information_schema.tables where table_schema = "ssg";'

If the ibdata file size gets out of hand and starts to consume all your available disk space in the volume / partition, or there is a large difference between the output of the command above and the actual size of the ibdata file, then the solution is to 1) take a full MySQL dump, 2) remove the ibdata file from the disk, then 3) import the backup into MySQL again where it will create a new ibdata file at just the size of the database itself to begin with, which helps save all the space that was wasted over time during periods of peak activity where it might shoot the ibdata file size quite high before the purge script runs, for example. We actually have a KB article (must be signed in to the Layer 7 Support Portal to view it though) with detailed instructions on how to shrink the ibdata file. I had also pasted a snippet of the instructions in a comment earlier up in this very same community thread to shrink the ibdata file.

This is ultimately an odd quirk in the design of the ibdata storage engine within MySQL, outside the control of CA Technologies. For performance reasons, we had chosen to go with the ibdata storage engine. That is always subject to change in the future if we find a much more efficient storage engine that we believe will improve the CA API Management product suite MySQL databases. But until such time, the ibdata storage engine is the better one we find for our products, and that is why we provide the necessary tools and instructions to keeping the file size to a minimum as we are aware of the quirk in the innodb storage engine design in MySQL.

I hope the above answers your question on why the ibdata file does not shrink and what can be done about it.

Sincerely,

Dustin Dauncey

Support Engineer, Global Customer Success

Email: API-Support@ca.com

Phone: +1 800 225 5224

Outside of North America - ca.com/us/worldwide.aspx

CA API Management Community: ca.com/talkapi

Hi Dustin,

  Thanks a lot for a very elaborate explanation of why the ibdata does not seem to shrink in spite of purging. Your command for finding the size had been very useful earlier and I came to know from it after purging the audits I still had 1.5GB of audit_detail_params. I shrank the ibdata file over the weekend and its now all healthy.

   There is one unanswered question though. audit_purge only purges audit_main and does not purge other audit_* tables. Any reason for the same? Or is it something that is yet to be built? (I can volunteer).

Regards,

Trinath

Hi mtrinath,

The audit_purge.sh script makes edits to the audit_main table, but the table is set up in such a way that it has a cascading effect to the other audit tables, so in effect it makes changes to the other audit-related tables as well. There is a very sizable difference between the amount of data stored in the audit_main table vs the audit_detail table, for example. The sizes of these tables will always be very far apart from each other, so it is perfectly normal for the audit_main to hold just a few MBs of data, where-as the audit_detail can contain a few GBs of data.

For reference (as you asked about this during our Office Hours today), I've just very quickly summed up some of the information held in some of the primary audit-related tables below:

• audit_main contains information on the GOID (a unique value generated for each transaction), the node ID (which node in the cluster served the request), time (what time the request came in at), audit_level (i.e. severe, error, warning, etc.), message, IP address (the IP address of the requestor, this may be the IP of a load balancer if it doesn't pass on the original requestor IP), and more.
• audit_detail contains the matching GOID from audit_main, as well as the time, components involved, a unique message ID, exception messages, and more.
• audit_message contains the matching GOID from audit_main, the status, the service GOID, whether they authenticated, request length, response length, response status, routing latency, and more.

To learn a bit more, you can also run the following command in MySQL to show what fields are held in the table: describe <tableName>;

<tableName> of course should be replaced with a name of a table in the SSG database, such as audit_main, audit_detail, etc.

I hope the above helps.

Sincerely,

Dustin Dauncey
Support Engineer, Global Customer Success
Email: API-Support@ca.com
Phone: +1 800 225 5224
Outside of North America - ca.com/us/worldwide.aspx
CA API Management Community: ca.com/talkapi