Re: Error importing databases in mysql: ERROR 1005 (HY000) (errno:-1)
Posted by: Rick James
Date: May 17, 2014 09:37AM

Partial analysis:

( (key_buffer_size / 0.20 + innodb_buffer_pool_size / 0.70) / _ram ) = (16M / 0.20 + 128M / 0.70) / 8192M = 3.2% -- Most of available ram should be made available for caching.
-- http://mysql.rjweb.org/doc.php/memory

( open_files_limit ) = 1,024 -- ulimit -n
-- To allow more files, change ulimit or /etc/security/limits.conf or in sysctl.conf (kern.maxfiles & kern.maxfilesperproc) or something else (OS dependent)

( 1 - Innodb_buffer_pool_read_ahead_evicted / Innodb_buffer_pool_read_ahead ) = 1 - 0 / 60 = 100.0% -- Utility of read_ahead.
-- Turn off read-ahead

( innodb_log_file_size * innodb_log_files_in_group / innodb_buffer_pool_size ) = 5M * 2 / 128M = 7.8% -- 50% recommended.
-- Changing innodb_log_file_size is complex.

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 29,451,264 / (981 / 3600) / 2 / 5M = 10.3 -- Ratio
--

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 981 / 60 * 5M / 29451264 = 2.91 -- Minutes between InnoDB log rotations
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size.

( innodb_file_per_table ) = OFF -- Put each file in its own tablespace
-- (Mildly recommended, especially for large tables)

( innodb_rollback_on_timeout ) = OFF
-- Probably wise to have it ON, for data integrity.

( (Com_show_create_table + Com_show_fields) / Questions ) = (0 + 2866) / 48187 = 5.9% -- Naughty framework -- spending a lot of effort rediscovering the schema.
-- Complain to the 3rd party vendor.

( local_infile ) = ON
-- local_infile = ON is a potential security issue

( Qcache_hits / Qcache_inserts ) = 2,901 / 10003 = 0.29 -- Hit to insert ratio -- high is good
-- Consider turning off the query cache.

( Qcache_hits / (Qcache_hits + Com_select) ) = 2,901 / (2901 + 10646) = 21.4% -- Hit ratio -- SELECTs that used QC
-- Consider turning off the query cache.

( (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache / query_alloc_block_size ) = (16M - 3032) / 5366 / 8192 = 0.382 -- query_alloc_block_size vs formula
-- Adjust query_alloc_block_size

( Created_tmp_disk_tables / (Created_tmp_disk_tables + Created_tmp_tables) ) = 186 / (186 + 627) = 22.9% -- Percent of temp tables that spilled to disk
-- maybe increase tmp_table_size and max_heap_table_size; avoid blobs, etc.

( Com_rollback / Com_commit ) = 143 / 110 = 1.3 -- Rollback : Commit ratio
-- Rollbacks are costly; change app logic

( Select_scan / Com_select ) = 974 / 10646 = 9.1% -- % of selects doing full table scan. (May be fooled by Stored Routines.)
-- Add indexes / optimize queries

( sort_buffer_size ) = 2M -- One per thread, malloced at full size until 5.6.4, so keep low; after that bigger is ok.
-- This may be eating into available RAM; recommend no more than 2M.

( binlog_format ) = STATEMENT -- STATEMENT/ROW/MIXED. ROW is preferred

( log_slow_queries ) = OFF -- Whether to log slow queries.

( slow_query_log ) = OFF -- Whether to log slow queries.

( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2

( Uptime_since_flush_status ) = 981 -- How long (in seconds) since FLUSH STATUS (or server startup).
-- GLOBAL STATUS has not been gathered long enough to get reliable suggestions for many of the issues. Fix what you can, then come back in a few hours.

( Uptime ) = 981 -- How long (in seconds) the server has been running.
-- The system has not been up long enough to get reliable suggestions for many of the issues. Fix what you can, then come back with fresh values after the system has been running a few hours.

( Threads_created / Connections ) = 5 / 49 = 10.2% -- Rapidity of process creation
-- Increase thread_cache_size (non-Windows)


More later.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.