Jump to content

MariaDB with XtraDB

sobrenome

By sobrenome
in Technical Problems

Recommended Posts

  • Replies 132
  • Created
  • Last Reply
Makoto Veteran

Makoto

Posted
Posted

So mysqltuner also works for MariaDB?

​Yes, it's not optimized for some of the extra features MariaDB/XtraDB provides, but it works perfectly fine with MariaDB.

You'll want to wait at least 48 hours after MariaDB has been running in production to get usable results though.

On top of that, here's a good article on deciding the optimal value for your InnoDB buffer pool as a start,
http://dba.stackexchange.com/a/27341

Edit: The above RIBS query does not actually account for how much memory you have available. Please keep that in mind. The second query you run a week after will give you an accurate display of how much data you're actually utilizing in your InnoDB buffer pool. The main thing is you don't want to set your InnoDB buffer pool too low, it can severely hamper performance if you do.

You need to accommodate for how much free memory your server has first. How much memory your web server can consume under peak loads, how much memory you have dedicated to any other processes on your server (i.e. Sphinx). Once you know this, you can tune MySQL accordingly. InnoDB is one of the few settings you can pretty much throw as much memory into as you want, as long as you know you have enough free memory available on your server to handle it. (With other MySQL parameters, you want to be much more careful. Don't just increase or change things at random. It will easily degrade your database servers performance if you don't know what you're doing. mysqltuner is pretty good in this regard, it can for the most part recommend safe optimizations to you based on your servers actual load and available hardware.)

Link to comment
Share on other sites
RevengeFNF Experienced

RevengeFNF

Posted
Posted

He can also run the scrip Tuning Primer: https://launchpad.net/mysql-tuning-primer

It will tell him how much Data and Indexes in Innodb(Mysqltuner only shows Data) and it will tell the free space in buffer pool.

One of my servers for example:


INNODB STATUS
Current InnoDB index space = 682 M
Current InnoDB data space = 1.67 G
Current InnoDB buffer pool free = 29 %
Current innodb_buffer_pool_size = 2.73 G
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

Link to comment
Share on other sites
Makoto Veteran

Makoto

Posted
Posted

Yeah, and 2/3rd system memory is also popular baseline configuration. You just have to be sure that you accommodate for how much memory other processes on your server need. Most people don't do this, and if you're not careful it can easily lead to out-of-memory situations that will crash your entire server, or worse, send you into the depths of swap hell.

I'll also try and provide some more general configuration tips for MariaDB/XtraDB specifically later tonight. There are some safe general configuration changes that can be made on just about any configuration for improving performance.

There are a lot of complexities out there in regards to database optimization though. So giving general advice can be difficult for some things.

Link to comment
Share on other sites
Skipy7 Explorer

Skipy7

Posted
Posted

After spending hours looking into this today it looks like if you want to change to innodb after the fact (install) and you selected to use utf8mb4 you have to change every varchar(250) on columns that are indexed to something like varchar(190). then change the table to innodb. 

I'll keep looking into this in my free time. (wish IPS gave you the option to use the innodb engine and not force the default on install as the createTable method handles all column sizing)

 

EDIT: after reinstalling my test install using an edited db.php to force innodb with utf8mb4, I have found what it does to solve the key issue it sets a size on the index itself. @sobrenome If I find time tomorrow I'll make a script that will run through all the tables and set the right index sizes and perform the engine change.

Link to comment
Share on other sites
sobrenome Veteran

sobrenome

Posted
  • Author
Posted

For those that want to alter the engine to InnoDB, here is the easiest way that I have found:

Run this SQL statement (in the mysql client, phpMyAdmin, or wherever) to retrieve all the MyISAM tables in your database.

Replace value of the name_of_your_db variable with your database name.

SET @DATABASE_NAME = 'name_of_your_db';

SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC;

Then, copy the output and run as a new SQL query.

http://stackoverflow.com/questions/3856435/how-to-convert-all-tables-from-myisam-into-innodb

Link to comment
Share on other sites
Skipy7 Explorer

Skipy7

Posted
Posted

If you're doing a fresh install and your MySQL/MariaDB "default storage engine = MyISAM" and you want to use (Xtra/Inno)DB (I recommend using it only on MySQL 5.6 or MariaDB 10+.

before you install open system/Db/Db.php 

replace 

	/**
	 * Find out the default storage engine
	 *
	 * @return	string
	 */
	public function defaultEngine()
	{
		$result = $this->forceQuery( "SHOW ENGINES" );

		while( $engine = $result->fetch_assoc() )
		{
			if( \strtoupper( $engine['Support'] ) == 'DEFAULT' )
			{
				return $engine['Engine'];
			}
		}

		if( \IPS\Db::i()->server_version < 50600 )
		{
			return 'MyISAM';
		}
		else
		{
			return 'InnoDB';
		}
	}

with

	/**
	 * Find out the default storage engine
	 *
	 * @return	string
	 */
	public function defaultEngine()
	{
		/* $result = $this->forceQuery( "SHOW ENGINES" );

		while( $engine = $result->fetch_assoc() )
		{
			if( \strtoupper( $engine['Support'] ) == 'DEFAULT' )
			{
				return $engine['Engine'];
			}
		} */

		if( \IPS\Db::i()->server_version < 50600 )
		{
			return 'MyISAM';
		}
		else
		{
			return 'InnoDB';
		}
	}

 

Link to comment
Share on other sites
sobrenome Veteran

sobrenome

Posted
  • Author
Posted

/etc/my.cnf:

[mysqld]
tmp_table_size=64M
tmpdir="/mysqltmp"
log-error
#log-slow-queries="/var/lib/mysql/slow.log"
max_connections=252
innodb_file_per_table=1
open_files_limit=10000
query_cache_size=64M
thread_cache_size=4
#table_cache=64
max_allowed_packet=268435456
max_heap_table_size=64M
default-storage-engine=InnoDB
innodb_buffer_pool_size=6G

Mysqltuner advice:

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[!!] Your MySQL version 10.0.16-MariaDB is EOL software!  Upgrade soon!
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 3G (Tables: 1059)
[--] Data in CSV tables: 0B (Tables: 2)
[--] Data in InnoDB tables: 6G (Tables: 685)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[--] Data in MEMORY tables: 0B (Tables: 2)
[!!] Total fragmented tables: 209

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned
Use of uninitialized value in addition (+) at ./mysqltuner.pl line 567 (#1)
    (W uninitialized) An undefined value was used as if it were already
    defined.  It was interpreted as a "" or a 0, but maybe it was a mistake.
    To suppress this warning assign a defined value to your variables.
    
    To help you figure out what was undefined, perl will try to tell you the
    name of the variable (if any) that was undefined. In some cases it cannot
    do this, so it also tells you what operation you used the undefined value
    in.  Note, however, that perl optimizes your program and the operation
    displayed in the warning may not necessarily appear literally in your
    program.  For example, "that $foo" is usually optimized into "that "
    . $foo, and the warning will refer to the concatenation (.) operator,
    even though there is no . in your program.
    

-------- Performance Metrics -------------------------------------------------
[--] Up for: 7h 28m 12s (868K q [32.298 qps], 36K conn, TX: 19B, RX: 3B)
[--] Reads / Writes: 70% / 30%
[--] Total buffers: 6.3G global + 416.0K per thread (252 max threads)
[OK] Maximum possible memory usage: 6.4G (40% of installed RAM)
[OK] Slow queries: 0% (16/868K)
[OK] Highest usage of available connections: 21% (54/252)
[OK] Key buffer size / total MyISAM indexes: 128.0M/2.3G
[OK] Key buffer hit rate: 97.3% (12M cached / 333K reads)
[OK] Sorts requiring temporary tables: 0% (5 temp sorts / 29K sorts)
[OK] Temporary tables created on disk: 16% (4K on disk / 26K total)
[OK] Thread cache hit rate: 99% (272 created / 36K connections)
[!!] Table cache hit rate: 4% (400 open / 8K opened)
Use of uninitialized value $myvar{"table_cache"} in concatenation (.) or string
	at ./mysqltuner.pl line 851 (#1)
[OK] Open file limit used: 1% (142/10K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[!!] InnoDB data size / buffer pool: 6.7G/6.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Upgrade MySQL to version 4+ to utilize query caching
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    table_cache (> )
    innodb_buffer_pool_size (>= 6G)

 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing

  • Recently Browsing   0 members

    • No registered users viewing this page.
Invision Community © 2024 IPS, Inc.
×
×
  • Create New...