Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a Drupal site with nearly 2000 pages running on WAMP server. The pages also contain images, and flash videos embedded into them. So, they are taking some time to load under normal settings.

Nearly 200-300 users are expected to use the system concurrently (the number is expected to grow later).

The dev server (on our company intranet) has 16 GB RAM, and runs Windows7 - 64 bit OS. I can see that in wamp/bin/mysql/mysql5.5.16/ has files

  1. my.ini
  2. my-huge.ini
  3. my-innodb-heavy-4G.ini

I had a look into both of them, and I am not sure what is the purpose of each one of them and which one is used by my the WAMP server currently. Also, I needed to know the difference in using my-huge.ini and my-innodb-heavy-4G.ini. I have also configured Apache SOLR for indexing the search (I don't know if this is relevant).

P.S : Drupal stores pages (along with the link to images and videos) into the database and images and other media are stored in folder under the site root.

share|improve this question
    
I think that you can store the media to hard disks,and link to them. –  Jasper Ma Jun 4 '12 at 12:30
    
could you be more specific? –  Ajit S Jun 4 '12 at 12:50

1 Answer 1

up vote 4 down vote accepted
+50

The differences have to do with the expected capacity mysqld is requested to handle

my_huge.ini

  • does not have max_connections set
  • has innodb settings commented out
  • values preset
    • sort_buffer_size = 2M
    • read_buffer_size = 2M
    • read_rnd_buffer_size = 8M
    • myisam_sort_buffer_size = 64M

my-innodb-heavy-4G.ini

  • has max_connections set at 100
  • has innodb settings enabled
  • values preset
    • max_allowed_packet = 16M
    • binlog_cache_size = 1M
    • max_heap_table_size = 64M
    • read_buffer_size = 2M
    • read_rnd_buffer_size = 16M
    • sort_buffer_size = 8M
    • join_buffer_size = 8M
    • thread_cache_size = 8
    • thread_concurrency = 8
    • query_cache_size = 64M
    • query_cache_limit = 2M

Default values are used if not specified. Whichever one you want to use, you must copy it over my.ini and restart mysqld to take effect.

  • If using my-huge.ini, you must shutdown mysqld, copy my-innodb-heavy-4G.ini to my.ini then startup mysqld.
  • If using my-innodb-heavy-4G.ini, you must shutdown mysqld, delete the innodb log files (ib_logfile0,ib_logfile1), copy my-innodb-heavy-4G.ini to my.ini then startup mysqld.

Since you said the number of DB Connections is expected to grow, you must raise max_connections to higher values but you must balance the usage of RAM. The best way to balance it would to run some diagnostic program like mysqltuner.pl and get recommendations adjusting settings per connection and per instance.

Since you are running MySQL in a WAMP environment, I would not surpass 25% of RAM for total memory because you must give Windows, Apache, and PHP (or Perl or Python) their due in terms of RAM. (I am glad you are using Apache and not IIS, otherwise this would be a WIMP environment !!!)

share|improve this answer
1  
+1 for "WIMP" (-: –  KM. Jun 5 '12 at 15:33
    
thank you.. I had a look at it. This was because I have a responsibility to tune the settings for the complete site (development, design, database settings etc.), and being from a development background it becomes difficult to know the minute settings. I'll keep the question posted, and I suppose the bounty will automatically go to you (: –  Ajit S Jun 6 '12 at 6:02
    
You have to manually award the bounty. You can do so now or wait the 5 days out and award it then. If you do nothing, I would be awarded only half at the end of the 5 days + 1 day grace period. –  RolandoMySQLDBA Jun 6 '12 at 18:41
    
@RolandoMySQLDBA I'll do it for sure. Thank you for your answer.. –  Ajit S Jun 7 '12 at 6:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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