Oracle change initialization parameter with “deferred” keyword

When you see Modifiable you should read MEMORY Modifiable. For ALTER SYSTEM then this would mean can the parameter be modified in memory for the system. The Database Reference document you site includes part of this information:

Modifiable Specifies whether the parameter can be changed for the current session (by an ALTER SESSION statement) or for all sessions in the current instance (by an ALTER SYSTEM statement):

ALTER SYSTEM can be used to change the value in the server parameter file (SPFILE) of any initialization parameter. Such a change takes effect only in subsequent instances. The parameter descriptions indicate only those parameters that can be modified for the current instance.

To understand what will happen in entirety you have to understand the SCOPE parameter and what happens when no SCOPE is specified. From the SQL Language Reference we can see that the ALTER SYSTEM statement can have three different SCOPE specifiers or none at all.

SCOPE The SCOPE clause lets you specify when the change takes effect. Scope depends on whether you started up the database using a traditional plain-text parameter file (pfile) or server parameter file (spfile).

MEMORY indicates that the change is made in memory, takes effect immediately, and persists until the database is shut down. If you started up the database using a parameter file (pfile), then this is the only scope you can specify.

SPFILE indicates that the change is made in the server parameter file. The new setting takes effect when the database is next shut down and started up again. You must specify SPFILE when changing the value of a static parameter that is described as not modifiable in Oracle Database Reference.

BOTH indicates that the change is made in memory and in the server parameter file. The new setting takes effect immediately and persists after the database is shut down and started up again.

If a server parameter file was used to start up the database, then BOTH is the default. If a parameter file was used to start up the database, then MEMORY is the default, as well as the only scope you can specify.

So, since you were able to specify a scope of SPFILE, we can infer that you are using an SPFILE, so the default for the first ALTER SYSTEM when you did not specify a scope was BOTH which would fail because the parameter is not MEMORY modifiable. You can prove this by repeating your first ALTER SYSTEM and adding SCOPE=BOTH or SCOPE=MEMORY both of which will also fail.

I would avoid placing MySQL and PostgreSQL on the same server. They compete for the same resources. If you can, port everything to one RDBMS. My obvious choice would be PostgreSQL.

Then you can set shared_buffers to something like 500 MB and effective_cache_size to something like 1.5 GB. Be sure to read hints in the manual.

But I would also recommend to add more physical RAM. 2 GB is not much. Hardly enough for good performance with millions of rows. A few more GB of RAM shouldn't cost much.

If you have to stick with your setup 250 MB for Postgres seems reasonable. If MySQL has three times as much traffic, less might be better overall. Like 128 MB.

Basics for performance optimization in the Postgres Wiki.