1

I have a MySQL server (5.5.31) with 450 databases of some 200 tables each. (Yes, 90,000 tables)

When we're under high load, there's over 100 concurrent queries, of which 70% are either opening or closing tables. They're all only open for small queries.

The machine is heavily swapped, so obviously I can throw some RAM at it.

But before that, is there anything in the config I should be looking at? We've used various tuning tools, but they seem heavily geared to a more normal database setup where you might realistically have all tables open at any one time. Last time we changed our settings to match those recommended by a tuning tool, the entire system came to a halt.

What particular tuning should I be looking to to be able to get maximum performance before we throw RAM at it?

1

Generally throw RAM at it - especially given that you do not even say how much RAM you have. DAtabases love WARM and unless you are in the way to change the number of tables (90k is QUITE a lot) then basically thow RAM at it.

At least until you reach the low end of a low end database serer - which at these days would be 32gb memory (full memory loadout for a low end single processor Xeon).

  • The point here would be to eliminate swapping. Swapping bad. Especially for heavy traffic databases. RAM = cheap, bad performance = expensive, fumbling with options = expensive. – Max Vernon Dec 10 '13 at 15:04
  • 1
    Pretty much. It depends on the RAM already there. If that would be a 512gb server I would not look for more ram, but if that is what I think it is - then anything else just wont work. Tuning is nice, but tuning a 0.7 liter car engine to get 600hp just never works ;) Sometimes you throw some low cost things at it, and RAM is cheap. Databases should never ever ever ever swap. – TomTom Dec 10 '13 at 15:29
  • I'd bet a decent amount of my lunch money they don't have anywhere near 512GB. – Max Vernon Dec 10 '13 at 21:13
  • Yeah. I bet they are on a smallish VM or outdated server. Something that I would not use as a workstation - RAM wise. The question points to that. – TomTom Dec 10 '13 at 21:23
0

Which database management software is being used?

My recommendation is turning Profiler on - to find most heavily used queries on each database. From this queries - find out index plan that is being used, to see if there is index optimization that can be performed for the database.

You should also review if there is any disk I/O contention. Most DBMS has features to allow database composed of different group of disks.

  • MySQL .. per the tag. I've added it to the text. – RickMeasham Dec 10 '13 at 6:41
0

You have 90,000 tables in your DB server. Increasing RAM certainly helps you to overcome from this situation. Using Disks, swapping and open/close tables are negative points for DB performance. When you see this issue in your DB server, please execute show open tables; statement and list down the tables. You can repeat it multiple times and compare the list to find out which tables are repeatedly open. After this, you can analyze the reasons behind this.

Only increasing hardware is not necessary, its full utilization is also an important factor which is totally depends upon the software and its configuration. Regarding hardware, you have never mentioned configuration of your Database server. Regarding software, you are using almost updated version of MySQL. Now, you have to consider on tuning the MySQL configuration parameters. Before starting parameters tweaking of your MySQL, Please refer below URL:http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html. Almost every parameters are important while some of them are very important for performance improvement. To know the values of all parameters, please execute SHOW VARIABLES; statement. Below are the few important variables:

innodb_buffer_pool_size,innodb_flush_log_at_trx_commit,innodb_open_files,innodb_table_locks,innodb_thread_concurrency,join_buffer_size,key_buffer_size,max_connections,max_delayed_threads,max_heap_table_size,max_tmp_tables,thread_cache_size,tmp_table_size,wait_timeout,myisam_sort_buffer_size

In the same way, you must know the Global status variables of MySQL. Please refer URL: http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html. To check the values of these variable value, please run show global status; statement. Also, note that values of status variable vary time to time. You must note down the value of some status variables during peak time and analyze how much it is different than usual hour. Few of them are below:

Threads_cached,Threads_connected,Threads_created,Threads_running,Select_scan,Table_locks_immediate,Table_locks_waited,Opened_tables,Qcache_free_blocks,Qcache_free_memory,Qcache_hits,Qcache_inserts,Key_read_requests,Key_reads,Key_write_requests,Key_writes,Max_used_connections,Open_files,Open_tables,Opened_files,Innodb_row_lock_time,Innodb_row_lock_time_avg,Innodb_row_lock_time_max,Innodb_row_lock_waits,Innodb_rows_deleted,Innodb_rows_inserted,Innodb_rows_read,Innodb_rows_updated

There are multiple factors behind this likewise

Your hardware resources - RAM, CPU, Disks
Database engine of your tables - MyISAM, InnoDB
How much Index created in your tables?

You have to find out how many tables are using InnoDB engine and how much are using MyISAM. In the same ratio, you should divide the RAM. MySQL uses different-different parameters for both of the engines. These variables are prefix with Myisam and innodb respectively.

Disk I/O should be reduced as much as we can. By better allocation of RAM resources, we can do this. It may also be possible that Queries involved in the application are not utilizing indexes which tends to full table scan. By creating indexes in proper way, designing the database schema and write proper queries are very helpful in the improvement of system performance.

Hope above information would help...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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