Feed Aggregator

Hi Phil,

This looks like you are using a 32-bit MySQL Binary:

[Warning] option ‘max_join_size’: unsigned value 18446744073709551615 adjusted to 4294967295

With 32-bit Binaries in theory you cannot address more than 4 Gbyte RAM (in fact it is about 3 Gbyte).

Are you still using MySQL 5.0 or older?

[ERROR] /usr/libexec/mysqld: unknown variable ’table_open_cache=3072’

This parameter table_open_cache is new with MySQL 5.1. You should NOT use MySQL 5.0. It is old and not supported anymore. With MySQL 5.0 this parameter is called table_cache.

Regards, Oli

This is what im running into whenever i go to install the db on the mounted drbd device:

[root@srvdb1 /]# mysql_install_db –datadir=/repdata/data/mysql –user=mysql Installing MySQL system tables… 110622 10:44:33 [Warning] option ‘max_join_size’: unsigned value 18446744073709551615 adjusted to 4294967295 110622 10:44:33 [Warning] option ‘max_join_size’: unsigned value 18446744073709551615 adjusted to 4294967295 110622 10:44:33 [ERROR] /usr/libexec/mysqld: unknown variable ’table_open_cache=3072’ Installation of system tables failed!

help…please?!

somebody should make benchmarks. if you can see a batter performance sombody can make a patch, so it would be hardcoded.

How would you add this logic to the script? Maybe pull out Qcache_free_blocks and Qcache_total_blocks and set them to variables?

Hi,

Hopefully this will help. As of 5.1.6 (iirc) the event_scheduler has been available. Simply enable it on the server you’re using in my.cnf (details can be found in the manual).

Then create something like this:

mysql> use mysql;

CREATE EVENT flush_q_cache ON SCHEDULE EVERY 60 MINUTE STARTS ‘2011-02-15 20:28:01’ ON COMPLETION NOT PRESERVE ENABLE DO FLUSH QUERY CACHE

This will create an event attached to the mysql system schema, which, from the start time/date, will run FLUSH QUERY CACHE every 60 minutes.

This eliminates the need for shell scripts, and passwords, and keeps everything within the realm of the DBA. The only caveat is the user will need appropriate rights to the system table so this will often be setup as the DB root user or similarly privileged account.

Hi Normann,

Yes, why not? Great idea!

Something like: If Qcache_free_blocks is bigger than 10% of Qcache_total_blocks then do a flush.

It just needs a little bit more logic in the script…

Oli

why not flush it after every N_th QC change!

Dear MySQL and MariaDB User,

With this Newsletter you receive the News about FromDual for Summer 2011.

Sie können diesen Newsletter auch auf deutsch lesen oder sich den deutschprachigen Newsletter. abonnieren.

Topics

• Basic- and Silver Support for MySQL and MariaDB
• FromDual Performance Monitor for MySQL with more InnoDB Graphs
• Summer vacation without a MySQL DBA?
• Upcomming Trainings and Workshops
• Technical Information
• German Speaking MySQL User Group founded
• News about MySQL

Basic- and Silver-Support for MySQL and MariaDB

End of last year Oracle/MySQL has dropped the low cost Basic- and Silver-Support contracts from their service portfolio. The official justification was: Minor demand from the customers.

Numerous MySQL users since then have inquired this Support offerings with FromDual. We can offer you now the following Support Services for MySQL:

• Our Best Effort Support (BES) for EUR 990.-/year as an alternative to MySQL Basic and
• Our Business Hour Support (5 x 9, BHS) for EUR 1'990.-/year as an …

i wonder why mysql cant flush the QC on its own. having a configuration parameter to flush every x seconds would be so much more convenient and everybody using QC would use it if reasonable defaults were set …

When we analyze our customers systems we see typically a high fragmentation of the query cache after a while. This leads to a less optimal use of the Query Cache than possible.

With the following Query you can see the values for your Query Cache:

mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 11328    |
| Qcache_free_memory      | 89442000 |
| Qcache_hits             | 6595644  |
| Qcache_inserts          | 1041831  |
| Qcache_lowmem_prunes    | 717896   |
| Qcache_not_cached       | 1040936  |
| Qcache_queries_in_cache | 17775    |
| Qcache_total_blocks     | 46990    |
+-------------------------+----------+

Watch out for the value of Qcache_free_blocks and Qcache_free_memory.

The MySQL documentation states: You can defragment the query cache to better utilize its memory with the FLUSH QUERY CACHE statement. The statement does not remove any queries from the cache.
[ …

We have just released v0.7.1 of the FromDual MySQL Performance Monitor. The new release can be downloaded from here.

In all editions some error messages have been cleaned-up, the fall-back data gather method mysql has been removed and the Maria SE template has been renamed to Aria.

In the edition dedicated to our customers most of the InnoDB graphs which are available with MEM v2.3.3 have been implemented now.

Please let us know, what you want to have monitored in addition and send us your feedback and requests for enhancements to contact@fromdual.com

To stay up-to-date follow us on

Some sample graphs you can find here:

Overview of available InnoDB Graphs:

InnoDB Adaptive Hash Index Searches:

InnoDB Buffer Pool Activity:

InnoDB Datafile I/O Usage:

InnoDB Transactions:

InnoDB OS File Access:

InnoDB Row Lock Details:

InnoDB Row Operations:

InnoDB Semaphores:

InnoDB Transaction History:

InnoDB Transaction Lock Memory:

I’ve seen this happen a lot when the master has crashed and sync_binlog isn’t turned on.

Thats why I rant against VMWare, Xen and so on \o/ They use to cache fsync’s. So I suggest the position was there but never synced. So with the crash it was gone. But the Slave already knew the position :)

A customer rose a support case with a problem on his Master-Master set-up. The 2^nd Master claims to have a problem:

master2> SHOW SLAVE STATUS<br>G
                ...
    Master_Log_File: master1-bin.000014
Read_Master_Log_Pos: 97975045
   Slave_IO_Running: No
  Slave_SQL_Running: Yes
Exec_Master_Log_Pos: 97975045
      Last_IO_Errno: 1236
      Last_IO_Error: Got fatal error 1236 from master when reading data from
                     binary log: 'Client requested master to start replication
                     from impossible position'

What has happened?

When we look at the actual Masters binary logs we see the following situation:

master1> SHOW BINARY LOGS;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| master1-bin.000013 |  68757710 |
| master1-bin.000014 |  97973131 |
| master1-bin.000015 |    626440 |
+--------------------+-----------+

That looks really odd: The Slave wants some events above the size of the Masters binary log! …

From August 15^th to 17^th FromDual will have an Advanced MySQL Developer Workshop with Citrus in Helsinki (Finland). The workshop will be held in English. The workshop topics you can find here and the registration form is available here.

Today the German speaking MySQL User Group (DMySQLAG) was officially founded in Berlin. The association advocates:

• the information of the use, the handling and the experience with MySQL and development in the MySQL Eco-System as well as systems which use MySQL.
• the exchange of experience between MySQL users about MySQL and other systems.
• the Advice and Cooperation with Oracle and vendors of other systems.
• the submission of suggestions of the members to Oracle and vendors of other systems.

The intention of the German speaking MySQL User Group is to be a platform for MySQL User from Germany, Austria and Switzerland. To get a high enough weight for Austria and Switzerland we hope for a high number of registrations of these two countries. Who wants to be listed as a founding member, should asap (until Friday) send us a notice via e-Mail. A later membership is always possible.

Member fee

• Students: free
• Single-membership: EUR 100.-/a
• Companies up to 500 employees: EUR 200.-/a
• Companies above 500 employees: EUR …

MySQL 5.6

The focus of this release seems to be on Replication, Partitioning and InnoDB/Performance.

• New InnoDB-related INFORMATION_SCHEMA tables and more information in the PERFORMANCE_SCHEMA.
• Persistent Optimizer statistics for InnoDB tables.
• InnoDB table information can be limited when many tables are opened.
• Several internal performance enhancements for InnoDB.
• InnoDB deadlocks can be written to the error log.
• Delayed Replication
• ALTER TABLE ... EXCHANGE PARTITION.
• Binlog shipping with the mysqlbinlog utility.
• Row-based replication improved by row image control.
• Support for explicit selection of one or more partitions.
• Binary logs written and read by the MySQL Server are now crash-safe.
• Logging of master connection information and of slave relay log information to tables as well as files is possible.
• Old features were removed.

MySQL 5.5

• Improved scalability on multi-core CPUs, especially in InnoDB.
• InnoDB can make more effective use of available I/O capacity.
• Improved Diagnostic and Monitoring …

In our opinion remote Consulting is non optimal for the customer because tight interaction is difficult remotely.

Please visit Remote-DBA or Consulting…

In our last customer project we had around 600 Gbyte of data in a MySQL database. Because this database consumed a significant amount of our disk space and backups with the InnoDB backup tool took pretty long we wanted to find out if we could get rid of some of the tables.

This application was growing over the last 10 years and it was not clear if some tables are still in use or not.

But how to find out when a table was touched last? MySQL/InnoDB theoretically could know about but does not report this information.

Fortunately the operating system command stat knows when a file was last accessed (read) and modified (written).

For example stat on the InnoDB log file:

stat ib_logfile0
  File: `ib_logfile0'
  Size: 5242880         Blocks: 10240      IO Block: 4096   regular file
Device: 811h/2065d      Inode: 14689226    Links: 1
Access: (0660/-rw-rw----)  Uid: ( 1001/   mysql)   Gid: ( 1001/   mysql)
Access: 2011-05-29 11:53:30.787909003 +0200
Modify: 2011-05-29 11:59:33.697909059 +0200
Change: 2011-05-29 …

Workshop topics

Duration: 3 days, register here.

• Partitioning
  • Partitioning by time (temporal data)
  • Optimizer and Partitioning
  • Limitations
  • New 5.5 and 5.6 features of Partitioning
• InnoDB Architecture
  • Clustered PK
  • InnoDB Internals
  • InnoDB Online features
  • New Performance Features in 5.5 and 5.6
  • Operations of InnoDB
• Performance Tuning and Benchmarking
  • Basics, Latency and Throughput
  • Profiling
  • Benchmarking
  • Benchmarking Tools
• Memcached
  • Caching strategies
  • Operations
  • ding
• HA and replication solutions
  • Replication
  • Scale-Out
  • DRBD
• Blob streaming engine
• Handler Socket and Memcached-API