We are an email marketing company and recently switched our setup to MySQL. We need to configure mysql (my.cnf) for extreme performance.

We have tried to configure my.cnf but heavy queries can get stuck for many many hours without any result and same queries take one or two hours to complete on sqlserver (8GB Ram).

It's a dedicated MySQL server.

  • OS = ubuntu
  • Installed Ram = 512GB
  • Table type = Myisam

We have very large tables, (few tables having more then 70 million rows), almost every time new tables to match so indexes wont help that much, although there exist necessary indexes.

We do not have any application to connect our database, just 2 users who writes ad hoc queries directly.

Below are some variables we have changed from the default values.

key_buffer_size= 250G
tmp_table_size = 5G
sort_buffer_size = 2G
innodb_buffer_pool_size = 2G
query_cache_limit       = 10M
query_cache_size        = 100M
max_connections        = 20

Below is the mysqltuner recommendation

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.29-0ubuntu0.12.04.2
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1909G (Tables: 940)
[--] Data in InnoDB tables: 2G (Tables: 3)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 3

-------- Performance Metrics -------------------------------------------------
[--] Up for: 6m 59s (9 q [0.021 qps], 6 conn, TX: 10K, RX: 625)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 252.1G global + 4.1G per thread (20 max threads)
[OK] Maximum possible memory usage: 333.6G (66% of installed RAM)
[OK] Slow queries: 0% (0/9)
[OK] Highest usage of available connections: 5% (1/20)
[OK] Key buffer size / total MyISAM indexes: 250.0G/131.6G
[!!] Query cache efficiency: 0.0% (0 cached / 4 selects)
[OK] Query cache prunes per day: 0
[OK] Temporary tables created on disk: 0% (0 on disk / 2 total)
[OK] Thread cache hit rate: 83% (1 created / 6 connections)
[OK] Table cache hit rate: 78% (26 open / 33 opened)
[OK] Open file limit used: 1% (18/1K)
[OK] Table locks acquired immediately: 100% (36 immediate / 36 locks)
[!!] Connections aborted: 16%
[!!] InnoDB data size / buffer pool: 2.2G/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Your applications are not closing MySQL connections properly
Variables to adjust:
    query_cache_limit (> 10M, or use smaller result sets)
    innodb_buffer_pool_size (>= 2G)

Any help would be greatly appreciated.

  • This question is probably a bit too broad to answer here. You will probably get more useful answers by choosing a single query to optimise and including both that and the table definition(s) in your question. – Ladadadada Apr 11 '13 at 15:44
  • You should be looking at a columnar storage engine. If you are married to MySQL check out Infobright. Cheers – HTTP500 Apr 12 '13 at 1:48

Going with Ladadadada's edit, 70 million rows is not a lot. mysqltuner.pl seems to think you have 943 tables - this is still ridiculously high.

But since your total data is only around 4 times your physical memory, it should be obvious that the poor performance has little to do with physical I/O.

You seriously need to reduce the number of tables in the database.

After that, you should switch your MyISAM tables to Innodb (it won't go faster but it should eliminate most of the blocking) then you should increase the innodb_buffer_pool_size to around 80% of your available (possibly more) - however I'm rather surprised that you've got a machine with half a terrabyte of physical memory - really? Where did you get such a beast?

| improve this answer | |
  • Correction: There are few tables having more then 70 Million rows, I have edited the issue as well... – Umair Apr 11 '13 at 17:19
  • hey symcbean, most of the tables are unused they are small temp tables and can be removed but we mostly play around few tables, what mostly we do is import new table (may be of few thousand records) match them back to the Million rows table and produce the result, what i mean to say is most of the tables are unused, there is no use of installing that much ram if we are not getting optimal performance, we have few queries running simultaneously so blocking is not the issue. – Umair Apr 11 '13 at 17:42
  • Your lacking of housekeeping is impacting the performance of your system already. Letting it get worse just means more work to do later. – symcbean Apr 11 '13 at 23:11

very large tables more then 70Million to query

If you have more than 70 million tables then you need to get a big stick and apply it repeatedly to the person responsible for this.

There's lots of things you do to improve the performance a database (and ignoring this line, some small changes could massively increase the performance of a sensible schema) - but until you sort the number of tables you are wasting your time.

| improve this answer | |
  • I read that as "70 million rows" and edited the question to reflect my reading but if I'm wrong and he actually meant 70 million tables, that would certainly be causing problems. – Ladadadada Apr 11 '13 at 16:11
  • Sorry Ladadadada, i mean few tables having more then 70M rows. – Umair Apr 11 '13 at 17:22

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