Mysql – Tomcat and MySql on VPS

MySQLperformancetomcatvps

I am doing some experimentation with VPS before moving my application from private Tomcat hosting to cloud. It is a read intensive app built on Struts 2 + Spring + Hibernate + MySql. Its a moderately popular app in India with 1500 visitors and 10,000 pageviews per day. I have some basic questions about choosing a server configuration.

1) Would it be enough to have 256M RAM for running both Tomcat and Mysql. I wont be running anything else other than SSH. No apache, FTP etc. My current heap size is 190M. can i still set the heap size as 190M with 256M RAM? What are the pros and cons?

2) Is it better to have 2 256M servers one with Tomcat and one with Mysql? or 1 server with 512M running both MySql and Tomcat?

I am open to any suggestions. Thanks!

Best Answer

I think it could be done.. I've seen a similarly sized app running on 256MB Linux VPS.

However, you're leaving very little memory for MySQL, which will cause it to have to go to disk often. It could be quite slow.

One server is better than two. You have less to configure and you don't pay for the OS + virtual machine container overhead twice. Also, your app server and your database may not use equal memory, so being on separate machine could be an inefficient use of memory.

Related Solutions

Mysql – concatenate multiple MySQL rows into one field

You can use GROUP_CONCAT:

SELECT person_id,
   GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Ludwig stated in his comment, you can add the DISTINCT operator to avoid duplicates:

SELECT person_id,
   GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY:

SELECT person_id, 
       GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:

SET group_concat_max_len = 2048;

Of course, you can change 2048 according to your needs. To calculate and assign the value:

SET group_concat_max_len = CAST(
                     (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
                           FROM peoples_hobbies
                           GROUP BY person_id) AS UNSIGNED);

Mysql – Which MySQL data type to use for storing boolean values

For MySQL 5.0.3 and higher, you can use BIT. The manual says:

As of MySQL 5.0.3, the BIT data type is used to store bit-field values. A type of BIT(M) enables storage of M-bit values. M can range from 1 to 64.

Otherwise, according to the MySQL manual you can use BOOL or BOOLEAN, which are at the moment aliases of tinyint(1):

Bool, Boolean: These types are synonyms for TINYINT(1). A value of zero is considered false. Non-zero values are considered true.

MySQL also states that:

We intend to implement full boolean type handling, in accordance with standard SQL, in a future MySQL release.

References: http://dev.mysql.com/doc/refman/5.5/en/numeric-type-overview.html

Best Answer

Related Solutions

Mysql – concatenate multiple MySQL rows into one field

Mysql – Which MySQL data type to use for storing boolean values

Related Topic