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.

Curretly my main culprit of poor performance is MySQL and I'm not a DB savvy. So far, I've had little luck in optimizing mysql configs on my server, which has a Sandy Bridge processor with 16GB ram and SSD disks serving a Drupal 6 site running on top of Ubuntu. I have ~1300qps with 80/20 read/write ratio. MyISAM is the only engine that I use (My experience with InnoDB have been horrible, despite all buzz about it as being superior to MyISAM).

The original my.cnf shiped with MySQL 5.1 is abysmal and Debian's suggested configs for 'huge' servers seemed to be less than optimal for my case. After a lot of experimentations and tweaks I figured out that tuning-primer script is too generic to give sensible advices (if they are not outright misleading!). So regarding the plethora of parameters involved in an optimal mysql config, it would be great if some DB-informed drupalers could share their my.cnf for similar Server/case.

share|improve this question
1  
This is a really broad question. What performance issues are you specifically facing? Also, what was the problem you ran into with InnoDB? –  Derek Downey Jan 3 '12 at 15:48
    
The perfomance issue is persistent high load (~20-30) where mysql consuming ~700% of the CPU. With InnoDB the performance showed to be even more miserable. –  alfish Jan 3 '12 at 16:41
    
Did you say 700% ? ... –  ypercube Jan 3 '12 at 16:44
    
yes it takes > 700% of CPU load, reading from 'top' –  alfish Jan 3 '12 at 17:26
    
And during those high load periods, what is the output of SHOW FULL PROCESSLIST –  Derek Downey Jan 3 '12 at 17:40
show 1 more comment

migrated from drupal.stackexchange.com Jan 3 '12 at 13:53

This question came from our site for Drupal developers and administrators.

4 Answers

If you're running at that load on that hardware, you really should get some experts in to do this for you. Pythian and Percona are 2 consultancy/support organisations that I can think of: I have no link to either of them BTW.

Each install and load has different tweaks needed. The "huge" cases for my.cnf files I've seen are what you'd use a few years ago or on your mobile phone nowadays...

The default settings are amazingly low so if you've never changed them then there is your problem.

Edit: Have you already hacked your my.cnf? http://drupal.stackexchange.com/questions/12085/default-to-myisam-instead-of-innodb-in-drupal-7

share|improve this answer
add comment

You'll need to do some more debugging to see what your bottleneck is. A few tips to do this:

  • During periods of high load, run a SHOW FULL PROCESSLIST to see what's happening. My guess is there is some table locking going on.

  • Enable the slow query log with the option to log queries that don't use indexes turned on:

    To include queries that do not use indexes for row lookups in the statements written to the slow query log, enable the log_queries_not_using_indexes system variable.

Those should head you in the right direction. If your tables are improperly indexed, you will run into issues.

share|improve this answer
1  
+1 for Finding Diagnosis before Configuration !!! –  RolandoMySQLDBA Jan 3 '12 at 18:02
add comment

You may need to consider converting everything to InnoDB anyway in order to prevent table locking issues. However, here are a couple of things to think about:

FULLTEXT Indexing

At present, only MyISAM supports FULLTEXT indexing. FULLTEXT indexing in InnoDB is currently in the works for MySQL 5.6 but is not production-ready. If you have any Drupal tables using FULLTEXT indexes, they cannot be converted to InnoDB.

To locate those tables that have a FULLTEXT index, run this query:

SELECT table_schema,table_name FROM information_schema.statistics WHERE index_type='FULLTEXT';

If no rows come back, convert all InnoDB tables to your heart's content. I wrote an earlier post on how to convert all MyISAM tables to InnoDB using only mysql.

Replication

If you have a read-heavy environment, reads can go faster in MyISAM if you do the following:

  • Setup Master/Slave Replication
  • Create one or more Read Slaves under the Master
  • Add --skip-innodb in /etc/my.cnf on all Slaves (converts tables to MyISAM when loading data into the Slave)
  • Change the row format of all MyISAM tables on every Slave to FIXED by this command: ALTER TABLE tblname ROW_FORMAT=FIXED;
  • I posted something on this in the DBA StackExchange
  • The book MySQL Database Design and Tuning recommends using ROW_FORMAT=FIXED on pages 72,73. This will internally convert all VARCHAR fields to CHAR. It will make the MyISAM table larger, but executed SELECTs against it will be much faster. I can personally attest to this. I once had a table that was 1.9GB. I changed the format with ALTER TABLE tblname ROW_FORMAT=FIXED. The table ended up 3.7GB. The speed of the SELECTs against it was 20-25% faster without improving or changing anything else.

Configuration

You will need to allocate enough RAM for InnoDB data, InnoDB indexes, and MyISAM indexes (MySQL Does Not Cache MyISAM data). You will need to computer the sum of the InnoDB Data Pages and Index Pages to size up innodb_buffer_pool_size. You will also need to compute the sum of the MyISAM index pages to size up key_buffer_size.

Once your main data is InnoDB, you should upgrade to MySQL 5.5 so that you can take advantage of the new settings to engage multiple CPUs in accessing InnoDB data. Regardless of the version of MySQL, you are totally at the mercy of whatever version of MySQL is running if you do not configure InnoDB properly. There are documented cases of older versions of MySQL running better that newer versions given the same level playing field.

The only thing left to your discretion is making your application aware of separate read slaves and performing all writes (INSERTs, UPDATEs, DELETEs) at the InnoDB-based Replication Masters.

UPDATE 2012-01-03 14:25 EDT

To Mass Convert all MyISAM tables to have ROW_FORMAT='Fixed' here are the steps

mysql -uroot -ppassword -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ROW_FORMAT=Fixed;') FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql') AND engine = 'MyISAM' ORDER BY data_length" > MyISAMRowFormatConversionToFixed.sql
mysql -uroot -ppassword -A < MyISAMRowFormatConversionToFixed.sql

If you want to try this out by converting the 5 smallest tables, add LIMIT 5 to the query:

mysql -uroot -ppassword -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ROW_FORMAT=Fixed;') FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql') AND engine = 'MyISAM' ORDER BY data_length LIMIT 5" > MyISAMRowFormatConversionToFixed.sql
mysql -uroot -ppassword -A < MyISAMRowFormatConversionToFixed.sql
share|improve this answer
    
Rolando, thanks for your suggestions, but regarding the read-heavy nature of the database I'd prefer to stick with MyISAM and improve the config instead of changing the engine. In fact the very same DB used to work reasonably well with MyISAM and a previous config that I'v lost. –  alfish Jan 3 '12 at 18:51
    
How about a compromise ??? Convert the ROW_FORMAT of all your MyISAM tables to FIXED on your Master. This will increase the query speed 20-25% in exchange for 80-100% increase in overall table size. I explained this in bulletpoint #6 under Replication. –  RolandoMySQLDBA Jan 3 '12 at 18:57
    
Well, if there is a way to execute the command on several dozen tables, I'd like to give it a try. BTW, I don't use master/slave. There is only one DB. –  alfish Jan 3 '12 at 19:12
    
I wrote a two-line script to mass convert all MyISAM tables. I also wrote a two-line script to convert the 5 smallest MyISAM tables. Give them a Try !!! –  RolandoMySQLDBA Jan 3 '12 at 22:06
add comment

You may want to check out the MySQL Configuration wizard from Percona - these guys really know what they are talking about... - and like @gbn I have no affiliation to them either - shame really!!! :(

http://www.mysqlperformanceblog.com/2011/12/23/online-mysql-configuration-wizard-from-percona/

I know it's not a real "answer", but going through the wizard may help you understand where you might be able to get some performance tuning improvements without going to a great deal of effort in testing all the different areas. Run the wizard, and compare the output with your own my.cnf.

Hope that helps :)

Dave

share|improve this answer
    
I tired their generated my.cnf for both MyISAM and InnoDB for MySQL 5.1 but funnily enough, using the generated files mysql hanged at restart. However there are some good insights there like query_cache_size = 0, which is contrary to tuning-primer.sh suggestion which always suggests more and more query_cache. –  alfish Jan 4 '12 at 2:31
    
Yeah, it certainly isn't perfect, but it is a good start, with some good insights... –  Dave Rix Jan 4 '12 at 9:17
add comment

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.