Re: How would you send all queries to a secondary server

[Clint Alexander]

It's noteworthy to mention that I inherited a mess. Web scripts cannot be updated to separate selects and writes and I've been charged to find a way to alleviate stress in the database environment first - without changing anything that would require the web scripts to be changed (estimated over 3 million lines of poor program code and a CEO that demanded little features to be created 'on the fly' and requiring it in the "next few hours").

Even more noteworthy is that the only time (in the recent past) I've gotten the time to work on this was while I was at home during the week and over the weekend. So "learning the environment" has been very slow up until the end of this week so I'm still trying to figure things out.

I've got a Cluster solution that will push whatever tables they are joining together into a pure memory-only environment and with 7.2 supporting "join push-down", the nodes will do all the heavy lifting during joins as long as all the tables joined are in the cluster. But we're looking for a quick boost in performance sooner rather than later (as a perfectionist, I won't enable the cluster until its absolutely ready and redundancy is setup).


Rick James Wrote:
-------------------------------------------------------
> Reads? Or Writes? Which are you doing more of?

It's problem is within SELECT statements that are laced with subquery joins inside of a normalized schema. Some of these joins reach into tables that have 10-25million records.

Largest table is 10GB (10mil rows)
Next largest table is 5GB (26mil rows)
6 More tables ranging between 1GB-5GB (2mil - 20mil rows)
They exponentially fall down in size after that. (avg 500k rows and lower)

(ps -- its not a typo: 10gb is 10m rows while the 5gb is 26m rows)


Rick James Wrote:
-------------------------------------------------------
> If it is Reads, then use Master-Slave(s) setup and
> send the reads to the Slaves. "Critical reads"
> (those supporting writes) should continue to go to
> the Master.

(see above)


Rick James Wrote:
-------------------------------------------------------
> Can you do replication in the Cloud? I don't
> know. (Clouds are ephemeral, databases like to
> persist.)

They thought it wise to bring in a virtual environment, lift the database off of the hardware and just put it on the cloud and turn it on. Small stress tests were done as I understand it, but I don't know the details on just what was done (yet).

Now -- I'm anti-virtualization when it comes to database servers, but this is not within my power of authority and I "...must find a way to get it to work in the cloud. Other companies are doing it successfully after making some tweaks here and there..."


Rick James Wrote:
-------------------------------------------------------
> SHOW VARIABLES LIKE 'innodb%';
> (Some tunables can markedly help I/O.)

mysql> show variables like 'innodb%';
+---------------------------------+------------------------+
| Variable_name | Value |
+---------------------------------+------------------------+
| innodb_adaptive_flushing | ON |
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 8388608 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 21474836480 |
| innodb_change_buffering | all |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | ON |
| innodb_flush_log_at_trx_commit | 2 |
| innodb_flush_method | O_DIRECT |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_io_capacity | 200 |
| innodb_large_prefix | OFF |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 268435456 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 75 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 0 |
| innodb_open_files | 300 |
| innodb_purge_batch_size | 20 |
| innodb_purge_threads | 0 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_spin_wait_delay | 6 |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | ON |
| innodb_stats_sample_pages | 8 |
| innodb_strict_mode | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_use_native_aio | ON |
| innodb_use_sys_malloc | OFF |
| innodb_version | 1.1.8 |
| innodb_write_io_threads | 4 |
+---------------------------------+------------------------+
59 rows in set (0.01 sec)

NOTE: I just turned innodb_use_sys_malloc OFF to see if having innodb to manage its own extended memory would be helpful. I don't expect to see much of a difference on that though.


Rick James Wrote:
-------------------------------------------------------
> How much RAM do you have?

48GB

I am still not sure of the size of their "working set" yet.


Rick James Wrote:
-------------------------------------------------------
> What version of MySQL are you running?

v5.5


Rick James Wrote:
-------------------------------------------------------
> Let's see some of the common transactions.

I'm not going to share the SQL statements or anonymity for public display just yet. I'm after a different question/answer.


Rick James Wrote:
-------------------------------------------------------
> Is the database on a separate server from
> everything else? Sounds like it is busy enough to
> deserve its own machine.

Of course its on its own dedicated.. errr... virtual machine. ;)


Rick James Wrote:
-------------------------------------------------------
> (No, I do not see any reliable or practical way of
> doing what the Subject asks.)

bummer.



Edited 3 time(s). Last edit at 04/14/2013 08:29AM by Clint Alexander.