Forums

MySQL my.cnf

Does a my.cnf file exist for MySQL databases run on PA?

@get: I never use MySQL, so I can't answer that for you, but I still wanted to welcome you to our PA community. I hope you have a super duper awesome time here!!!

@a2j: Haha, thanks but this isn't my first PA rodeo. My other paid account was one of the first 10 paid accounts I believe. I have the personalized mug to prove it. :)

Anyways, any info on the existence of a my.cnf file would be great. I have a hunch that it doesn't exist (or isn't available for the user to edit). If that's the case, is there no way to modify the default config settings for a MySQL database on PA? The reason I ask is I need to adjust some settings that are preventing me from inserting a large database from a dump file.

We don't provide access to the MySQL cnf file. What is it that you want to change? It may be that the settings you require are useful to others. If they are we can implement them for everyone.

@glenn: My use case that I ran into errors was trying to load a dump file (~1.1GB) into a database on PA. In order to get it to work, the settings I need changed and the reason for the change are the following:

Change #1

SET: tmp_table_size=2G, max_heap_table_size=2G

REASON: Prevent MySQL error 1114 (table full)

Change #2

SET: innodb_data_file_path = ibdata1:10M:autoextend, innodb_force_recovery = 0

REASON: Prevent MySQL from crashing while loading large dump file into a database.

I'm not sure that loading a 1GB dump file into MySQL is a particularly good idea - it's not the most stable piece of software and I've had issues with smaller dumps than that before.

Is it important that the data is loaded in a single transaction? If not it wouldn't be particularly hard to write a quick Python script which executed the transactions in chunks, committing after each one. If you want it in a single transaction then frankly I'm not sure MySQL will support that particularly gracefully either - that'd be one heck of a transaction! Also, table dumps tend to contain DDL statements which cannot be rolled back in any case.

While these settings might get the dump to work, I wonder if they might be open to abuse, at least if left permanently. For example, a tmp_table_size of 2GB presumably means someone could trivially consume up to 2GB RAM on the MySQL server - doing that with several free accounts concurrently could constitute a DoS attack on the database.

Still, that's just my opinion, and that's not the one that counts! (^_^) Perhaps a compromise might be to temporarily increase these limits for a 24 period or something.

Right, well I wasn't suggesting my config settings to be universally acceptable/optimal, but these are the optimized settings I need for the database I use. For the past few months, I've been running this database and other large databases like it on my own servers (granted, they are very fast and powerful dedicated machines), and I was just checking to see if I could get away with hosting the database on my PA account instead to free up resources on my servers.

I don't think a 1GB dump file is all that large, which I why I thought I might be able to squeeze it in on PA.

We'll give some thought to and investigate whether those settings are a good idea. But we probably won't get to this before the middle of next week. If you want to try it sooner, I'd suggest folllowing Cartroo's advice and chunking the import.