Showing entries 61 to 70 of 90
« 10 Newer Entries | 10 Older Entries »
Displaying posts with tag: configuration (reset)
Announcing mycheckpoint: lightweight, SQL oriented monitoring for MySQL

I’m proud to announce mycheckpoint, a monitoring utility for MySQL, with strong emphasis on user accessibility to monitored data.

mycheckpoint is a different kind of monitoring tool. It leaves the power in the user’s hand. It’s power is not with script-based calculations of recorded data. It’s with the creation of a view hierarchy, which allows the user to access computed metrics directly.

mycheckpoint is needed first, to deploy a monitoring schema. It may be needed next, so as to INSERT recorded data (GLOBAL STATUS, GLOBAL VARIABLES, MASTER STATUS, SLAVE STATUS) — but this is just a simple INSERT; anyone can do that, even another monitoring tool.

It is then that you do not need it anymore: everything is laid at your fingertips. Consider:

SELECT innodb_read_hit_percent, DML …
[Read more]
MySQL and hardware information

People often ask “what’s the best hardware to run a database on?” And the answer, of course, is “it depends”. With MySQL, though, you can get good performance out of almost any hardware.

If you need *great* performance, and you have active databases with a large data set, here are some statistics on real life databases — feel free to add your own.

We define “large data set” as over 100 Gb, mostly because smaller data sets have an easier time with the available memory on a machine (even if it’s only 8 Gb) and backups are less intrusive — InnoDB Hot Backup and Xtrabackup are not really “hot” backups, they are “warm” backups, because there is load on the machine to copy the data files, and on large, active servers we have found that this load impacts query performance. As for how active a database is, we’ve found that equates to a peak production load of over 3,000 queries per second on a transactional …

[Read more]
Configuring for large databases in MySQL Cluster

If you need to create a big database into MySQL Cluster with:

  • A lot of tables indexes, columns, and tables
  • A lot of records

there are a few things to think about:

  • If a table has > ~90M records, you have to create the table with MAX_ROWS=<amount of records in table anticipating growth>:
    CREATE TABLE t1(...) ENGINE=ndbcluster MAX_ROWS=200000000;
    This way the data node will allocate more partitions for the table, since there is a limitation in how many records that can be stored in one partition (and the limit is around 90M records).
  • Many tables / table objects --> Make sure you increase MaxNoOfTables (kernel limit is 20320 tables). This creates a table object pool of size MaxNoOfTables.
    • Each table you create will use one table object. …
[Read more]
MySQL related bookmark collection

I am publishing my MySQL related bookmark collection http://www.mysqlpreacher.com/bookmarks/.

Feel free to send me links you think might be good to add in order to help others.

Remember, SHARING IS CARING!!! …. we get so much for free, why shouldn’t we give some back?

Cheers,
Darren

Have you checked your MySQL error log today?

As a consultant I would be rich if I made money every time when asking “Have you checked the MySQL error log?”

Today’s special found in a 13GB MySQL server error log.

090819 22:49:37InnoDB: Warning: difficult to find free blocks from
InnoDB: the buffer pool (1101071 search iterations)! Consider
InnoDB: increasing the buffer pool size.
InnoDB: It is also possible that in your Unix version
InnoDB: fsync is very slow, or completely frozen inside
InnoDB: the OS kernel. Then upgrading to a newer version
InnoDB: of your operating system may help. Look at the
InnoDB: number of fsyncs in diagnostic info below.
InnoDB: Pending flushes (fsync) log: 0; buffer pool: 0
InnoDB: 167 OS file reads, 1 OS file writes, 1 OS fsyncs
InnoDB: Starting InnoDB Monitor to print further
InnoDB: diagnostics to the standard output.
090819 22:49:37InnoDB: Warning: difficult to find free blocks from
InnoDB: the buffer pool (1101051 search iterations)! Consider
InnoDB: increasing …
[Read more]
Character sets: latin1 vs. ascii

Unless specified otherwise, latin1 is the default character set in MySQL.

What I usually find in schemes are columns which are either utf8 or latin1. The utf8 columns being those which need to contain multilingual characters (user names, addresses, articles etc.), and latin1 column being all the rest (passwords, digests, email addresses, hard-coded values etc.)

I find latin1 to be improper for such purposes and suggest that ascii be used instead. The reason being that latin1 implies a European text (with swedish collation). It is unclear for an outsider, when finding a latin1 column, whether it should actually contain West European characters, or is it just being used for ascii text, utilizing the fact that a character in latin1 only requires 1 byte of storage. …

[Read more]
Reasons to use innodb_file_per_table

When working with InnoDB, you have two ways for managing the tablespace storage:

  1. Throw everything in one big file (optionally split).
  2. Have one file per table.

I will discuss the advantages and disadvantages of the two options, and will strive to convince that innodb_file_per_table is preferable.

A single tablespace

Having everything in one big file means all tables and indexes, from all schemes, are ‘mixed’ together in that file.

This allows for the following nice property: free space can be shared between different tables and different schemes. Thus, if I purge many rows from my log table, the now unused space can be occupied by new rows of any other table.

This same nice property also translates to a not so nice one: data can be greatly fragmented across the tablespace.

An annoying property of InnoDB’s …

[Read more]
The importance of report_host & report_port

The two server variables, report_host and report_port, are not commonly used. One of the reasons, I suspect, is that they do not appear in any of the standard .cnf files provided with a MySQL installation.

For a replication environment, I find these two variables to be very useful.

Where are these variables used?

Here’s a slightly modified exerpt from a MySQL master node I have, and which has two slaves:

mysql> SHOW SLAVE HOSTS;
+-----------+------------+------+-------------------+-----------+
| Server_id | Host       | Port | Rpl_recovery_rank | Master_id |
+-----------+------------+------+-------------------+-----------+
|        21 | webhost1   | …
[Read more]
More on variables ambiguities

Continuing Variables ambiguities in names and values, there are two more issues I wish to present.

First, I’ve shown that variable values may be ambiguous. Thus, 1 and ON are interchangeable. But also 1 and YES.

The important thing to note is that it’s not always like that. You can’t just swap 1 for ON or YES as you will: it depends on the variable. Thus, a specific variable (e.g. query_cache_type) may accept ON for 1 - but will not accept YES. Another may accept YES for 1 - bot not ON.

Confused? Here’s the second issue. This one is really a bug, as I see it. Take a look …

[Read more]
Variables ambiguities in names and values

Writing up some scripts, I see more and more ambiguities with regard to global variables.

For one thing, the names ambiguity between the hyphen (’-') and the underscore (’_'). So wait_timeout and wait-timeout are the same variable.

But just check out the many levels of inconsistency:

  • Command line arguments (e.g. run mysqld with option variables) use the hyphen convention
  • mysql –verbose –help shows the hyphen convention
  • SHOW GLOBAL VARIABLES uses the underscore convention
  • The MySQL supplied sample configuration files use both conventions interchangeably

Enough? Not quite: there are ambiguities in values, as well. For example, you may set query_cache_type to 1 or ON. These are equivalent. …

[Read more]
Showing entries 61 to 70 of 90
« 10 Newer Entries | 10 Older Entries »