1

I have a Google Cloud Platform MySQL 2nd Gen 5.7 instance with two databases, one for testing and one for production.

There are about 20 or so tables. One of the tables accounts for 99% of the storage.

I added 8 columns to the largest table (it already has ~150 columns) of the testing db. 6 of the new columns are Integer and 2 of them are Boolean (or more accurately, TinyInt). No indexes of any kind added.

Below is a snapshot of the metrics from the migration operation. You can clearly see CPU and Storage peaks for each of the 8 columns.

snapshots

Weird part is, RAM usage goes up an stays up. Any insight on what I may have forgotten to do?

The columns did not have any data populated to them, all are Null for each row so far

0

How many GB in the big table?

What was the setting of innodb_file_per_table? Of innodb_buffer_pool_size? Please provide the ALTER statement(s).

What probably happened was that the table was copied over to produce the new version with the new columns. (8.0 can add columns without copying -- in some situations.)

If you had file_per_table OFF, then the copy would be in ibdata1, which won't shrink afterwards. (The freed up space will be reused, just not returned to the OS, which you are monitoring.)

The buffer_pool is in RAM. It grows as needed up to the configured limit, which I guess to be 5G. The ALTER caused the sudden growth. This is a "cache", so there is no 'permanent' harm. Again, no shrinkage will happen.

  • The big table has ~9 GB. the testing db is a copy of the prod one, although the test db has its data truncated. I am not sure by how much exactly, though. The ALTER statement comes from alembic (a python library for db migrations built alongside the SQLAlchemy ORM). As for those innodb.. settings, I am not sure. They are the default of the GCP Cloud SQL engine. So that extra RAM is allocated, but not necessarily being consumed? I ran the migration against the production database and the RAM usage did not grow appreciably above what is in the images above – Brian Leach Oct 31 '18 at 16:16
  • @BrianLeach - I would say your graphs are red herrings. On a Cloud version of MySQL, most of RAM will be in use (once you put enough data on it). If you have performance problems; let's look at the queries and the schema. The graphs (usually) won't provide any useful info. – Rick James Oct 31 '18 at 16:42
  • @BrianLeach - And... I'll predict that if mysqld is restarted, the memory usage will drop back to, say, 3GB, at least temporarily. – Rick James Oct 31 '18 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.