Feed Aggregator

[http://www.blogger.com/profile/11195863756524022642 Shinguz] said…

Andrew pointed me to the following blog entry of Brian: [http://krow.livejournal.com/481313.html Daemon Example (deamon plugin)]

Thanks!

[http://www.blogger.com/profile/11195863756524022642 Shinguz] said…

Hi Björn,

Thanks a lot for you post. Much better than mine! :)

Shinguz

[http://www.blogger.com/profile/12606038649636876830 arathorn2005] said…

Nice approach!

Try this:

SELECT IF(TIMESTAMPDIFF(HOUR , UTC_TIMESTAMP(), SYSDATE()) = 2, ‘summer time’, ‘winter time’) AS TIME;

The keywords are longer, but all in all this maybe slightly easier to read.

Björn

Recently a colleague at Sun was asking me if MySQL can tell him to determine if we are currently in summer time or winter time. He was doing some data analysis of his house where he has installed solar panels.

I am not aware of what he wants to do exactly, but possibly he wants all the data in solar time. So UTC could help him because UTC does not change much over time.

Next thing which came to my mind is, that possibly the good place to do such mathematical calculations is the application code and not the database.

But nevertheless I was interested in how to solve this IN the database.

By default your MySQL server relies on your servers time zone.
[ 1
]

So if your server is set-up correctly you should be capable to determine if you are in summer time or winter time by your current time, UTC time and the offset you have to UTC.

SELECT IF(ROUND(TIME_TO_SEC(SUBTIME(TIME(SYSDATE()), UTC_TIME())) / 3600, 0) = 2
        , 'summer time', 'winter time') AS time;

Have fun calculating how much power is produced by …

[http://www.blogger.com/profile/11195863756524022642 Shinguz] said…

A colleague pointed out that I was missing some information:

The operative worklog task is WL#2360 and its dependencies, WL#4034 is a raw-idea bin item of no current significance.

The complete specification of the performance schema can be found here:

[http://forge.mysql.com/worklog/task.php?id=2333 WL#2333: SHOW ENGINE … LOCK STATUS] [http://forge.mysql.com/worklog/task.php?id=2360 WL#2360: Performance Schema] [http://forge.mysql.com/worklog/task.php?id=2515 WL#2515: Performance statements] [http://forge.mysql.com/worklog/task.php?id=3249 WL#3249: SHOW PROCESSLIST should show memory] [http://forge.mysql.com/worklog/task.php?id=4674 WL#4674: PERFORMANCE_SCHEMA Setup For Actors] [http://forge.mysql.com/worklog/task.php?id=4678 WL#4678: PERFORMANCE_SCHEMA Instrumenting File IO] [http://forge.mysql.com/worklog/task.php?id=4813 WL#4813: PERFORMANCE_SCHEMA Instrumenting Stages] [http://forge.mysql.com/worklog/task.php?id=4816 …

[http://www.blogger.com/profile/07820955267400574921 Morgan] said…

I just read what I wrote, sorry for not making sense. What I meant:

The ‘‘‘default’’’ INNODB STATUS method of showing statistics is for anywhere between 0 seconds to 60 seconds makes it difficult. 0-10 seconds is usually useless because it averages too much, and it’s entirely unpredictable what interval InnoDB uses.

Your script allows me to just get the current value (not an average). Brilliant!

[http://www.blogger.com/profile/11195863756524022642 Shinguz] said…

Hi Morgan,

Do not get this. With the script read_process_memory.sh you can sample at a specific interval with a timestamp in the record and write a *.csv. That must be ideal for Cacti or any other monitoring/graphing tool, is it not? You could even extend it to write directly to a database…

Please elaborate a bit more your concern.

As you probably have experienced yet MySQL does not always provide all internal information as you might want to have them and as you are used to have from other RDBMS.

MySQL plans to improve this implementing the/a performance schema and its probably already partly done in MySQL 5.4. But who knows when this will be finished and what it contains at all…

What is not provided to me I want to gather myself… But how? Other RDBMS provide interfaces to attach applications directly to their memory to retrieve information. But MySQL does not. So I was looking for a way to read an other process memory.

I have no clue about programming and thus changing MySQL code was out of focus. Further I am looking for a solution you can use immediately on a running systems at consulting gigs. Some tries to read /proc/<pid>/mem with a little PHP script failed.

An article by Domas M. helped me. I do not have to write something myself I can use a tool already existing to do the work. But gdb is not installed on …

[http://www.blogger.com/profile/07820955267400574921 Morgan] said…

Very nice! I can see the real use with some of the InnoDB stats. The random sampling interval makes it very difficult to get good numbers to use for cacti/RRDTool graphs.

[http://www.blogger.com/profile/07511835531671315487 Daniel] said…

knetknight

I’m not to hot on MySQL but I would have thought 5. repair table would be avoided except as a last result?

If there is corrupted data would it not be better to restore from a backup and apply logs?

[http://www.blogger.com/profile/07820955267400574921 Morgan] said…

You might want to add that Community Edition has partitioning.

The following summary shows my personal understanding of MySQL 5.1 licenses, packages and products. It does not necessarily reflect 100% the way MySQL understands it. But after all the discussions I hope it is as close as possible to the reality:

MySQL Embedded Database Server

(Download → OEM Software)

Classic  (OEM license, -MEM -InnoDB)
Pro      (= Classic +InnoDB)
Advanced (= Pro +Partitioning)

MySQL Community Sever

(Download → Download)

Community (GPL, -NDB)

MySQL Enterprise Server

(Download → Enterprise Software)

Pro      (GPL or commercial, -NDB +InnoDB +MEM, Basic + Silver customer, MRU + QSP)
Advanced (= Pro +Partitioning, Gold + Platinum customer)

MySQL Cluster

(Download)

Community Edition (GPL, all features)
Com (ex CGE?)     (OEM or commercial, -InnoDB +NDB)
Com-Pro           (Com, all features)
Standard Edition  (= Com, -NDB-API -Cluster-Repl, -LDAP)

Upgrade

EP customer should follow the QSP trail unless it is critical for them to install an MRU to get a quick bugfix to hold them over until …

[http://www.blogger.com/profile/02551781456999098405 Jonas Oreland] said…

FYI: I also added a new config parameter in 6.3.19, that makes the redo-files to be fully initialized:

InitFragmentLogFiles=full

[http://www.blogger.com/profile/11195863756524022642 Shinguz] said…

Hey guys,

Have a look at the addendum. I have updated it.

Regards, Oli

Anonymous said…

Thanks for sharing this. This is a very good read!

Hope the patch made it in the production code

Anonymous said…

Will this performance improvement patch made it into normal releases or will it be available for Enterprise customers only?

This week we had a very interesting problem at a customer: They complained, that their MySQL Cluster takes about 1 hour for a --initial start-up. After some tuning on the hardware and the config.ini they brought it down to around 40 minutes. But this is still unacceptable long…

This sounds a little strange to me. But let us have a look at their config.ini first. It looked more or less like the following (which is already tuned!):

#
# config.ini
#

[NDB_MGMD DEFAULT]
DataDir                      = /localdisk/cluster

[NDBD DEFAULT]
DataDir                      = /localdisk/cluster
NoOfReplicas                 =     2
LockPagesInMainMemory        =     1
DataMemory                   = 12000M
IndexMemory                  =  2000M

FragmentLogFileSize          =   256M
NoOfFragmentLogFiles         =    42

DiskCheckpointSpeedInRestart =   100M
ODirect                      =     1

BackupMaxWriteSize           =     1M
BackupDataBufferSize         =    16M
BackupLogBufferSize          =     4M
BackupMemory …

[http://www.blogger.com/profile/11195863756524022642 Shinguz] said…

Hi Baron

Hmmm. Did not know about it. But to be honest: I did not yet have time to look at all the nice stuff from your Maatkit and all the cool Google Patches and OurDelta etc… :-(

But thanks for the hint!

[http://www.blogger.com/profile/11195863756524022642 Shinguz] said…

Hi Willy

Yes. MySQL circular replication usually works fine (technically). I hope you have a good concept cleaning it up again when it some how messes up!

We usually recommend customers to be very very careful with this kind of replication!

Regards Oli

[http://www.blogger.com/profile/02327079024123812197 willy] said…

I have 4 servers, I need 4 servers share changes made on each one, my solution was configure them in ring schema, this is: server1 is master of server2 and slave of server4, server2 is master of server3 and slave of server1, and so on. This works fine. All server updates on same schema. Hope to be useful.