Re: InnoDB vs. other storage engines
Hi! Manuel == Manuel Arostegui man...@tuenti.com writes: Manuel 2012/9/19 Mark Haney ma...@abemblem.com I hope this doesn't end in some kind of flame war. I'm looking to optimize my tables (and performance in general) of the DB my web app is using. I'm tweaking things a little at a time, but I'm curious as to what the rest of the MySQL list thinks about changing my storage engine from InnoDB to something else so I can optimize the tables on a regular basis. Is it worth the effort? Any caveats? Manuel Hi Mark, Manuel I would depend on what your workload would be. Mostly writes, mostly reads, Manuel how many writes/reads do you expect etc. Manuel The best approach, from my point of view, would be, firstly, tune your Manuel MySQL server (if you've not done it yet) before getting into engine/tables Manuel optimizations which can be more complicated. InnoDB is a great engine, but not suitable for everything. Depending on your usage, moving some tables to another engine may help. Here is some suggestions (in no particular order): - If you want to have small footprint but don't need commit, foreign keys or explicite rollback then ARIA is an option. http://kb.askmonty.org/en/aria-formerly-known-as-maria/ - Duplicating some data in the MEMORY engine may also be beneficially. - If your problem is a lot of write, then you should take a look at Tokutek. It's an engine that is optimized for a lot of inserts. http://www.tokutek.com/products/tokudb-for-mysql/ - If you want to utilize a lot of computers to analyze BIG data then ScaleDB (http://www.scaledb.com) or InfiniDB (http://infinidb.org/) may be an option. Good luck and please post/blog about your experiences! Regards, Monty Creator of MySQL and MariaDB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Risks involved in MyISAM to Innodb
Am 21.09.2012 18:18, schrieb Rick James: (Apologies to the rare bottom-poster.) bullshit on mostly egvery mailing-list there are guidlines that you NOT should top-post, try it out on the postfix-list as example and wait what Wietse wille xplain you about both * your top-posting * and your stupid reply all resulting in get answers twice the case where you should be clever enough to do so is when there is a answer below and you post on top in stupidity to surround the question on both sides with answers to make it unable for anybody to read a thread anymore it is suitable if EVERY answer is on top but plain stupid after a answer at the bottom ___ AND YES my business and private communication is ALWAYS top-posting but i was smart enough to accept the it is a guidline at virtual all mailing-lists not do it there signature.asc Description: OpenPGP digital signature
RE: Aggregate
2012/09/04 11:43 -0700, Rick James int(1) does not mean what you think. Probably you want TINYINT UNSIGNED. Yeap, a real misfeature of MySQL. It is also one of the ways wherin MySQL puts in C and takes PL1 away. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Mysql cluster installation error
Hello, I am trying to install MySQL cluster on three physical machines. Management Node on one machine. Data Node on two machines. SQL node on the same machine as Management Node. Management node started Data Nodes started *SQL node started but not connected to Management NOde and it gives no error * C:\mysql\binndb_mgm -e show Connected to Management Server at: localhost:1186 Cluster Configuration - [ndbd(NDB)] 2 node(s) id=8@172.16.56.8 (mysql-5.5.25 ndb-7.2.7, Nodegroup: 0, Master) id=9@172.16.56.9 (mysql-5.5.25 ndb-7.2.7, Nodegroup: 0) [ndb_mgmd(MGM)] 1 node(s) id=6@172.16.56.7 (mysql-5.5.25 ndb-7.2.7) [mysqld(API)] 1 node(s) id=7 (not connected, accepting connect from 172.16.56.7) *config.ini* [ndbd default] # Options affecting ndbd processes on all data nodes: NoOfReplicas=2# Number of replicas DataDir=C:/mysql/bin/cluster-data # Directory for each data node's data files DataMemory=80M# Memory allocated to data storage IndexMemory=18M # Memory allocated to index storage [ndb_mgmd] # Management process options: HostName=172.16.56.7# Hostname or IP address of management node DataDir=C:/mysql/bin/cluster-logs # Directory for management node log files NodeId=5 [ndbd] # Options for data node A: HostName=172.16.56.8 # Hostname or IP address NodeId=8 MaxNoOfOrderedIndexes=1024 MaxNoOfAttributes=3000 # added 2012.8.08 [ndbd] # Options for data node B: HostName=172.16.56.9 # Hostname or IP address NodeId=9 MaxNoOfOrderedIndexes=1024 MaxNoOfAttributes=3000 # added 2012.8.08 [mysqld] # SQL node options: HostName=172.16.56.7 # Hostname or IP address NodeId=7 *my.ini* * * [mysql_cluster] # Options for management node process config-file=c:/mysql/bin/config.ini configdir=c:/mysql/bin/cluster-cache/ [mysqld] # Options for mysqld process: ndbcluster # run NDB storage engine ndb-connectstring=172.16.56.7 # location of management server ndb-nodeid=7 server-id=7 default-storage-engine=ndbcluster Could anyone help to identify/ Aastha Gupta
Re: Partitioning on a Substring of Varchar Column in Mysql
On Fri, Sep 21, 2012 at 9:43 PM, Rick James rja...@yahoo-inc.com wrote:
Some statements are incorrect...
If you PARTITION on (`x`), then `x` must be included in every UNIQUE key.
That includes the PRIMARY KEY.
You do not need to PARTITION on the PK.
Yes true, but my application requires primary key on ( `id` varchar(255)
NOT NULL, ) column. I cannot alter the schema.
So if i need to partition on created column then i need to put primary key
as ( id,created_time ) that is not accepted because this causes duplicate
records of id under different created times.
i.e i ask in my first can we partition on substring from id column.
An AUTO_INCREMENT value, say `id` must be the _first_ field in _some_ key.
`id` does not have to be UNIQUE, nor does it have to be the PK. (However,
if you do not have UNIQUE(`id`), by itself, it is possible to
_explicitly_ create duplicate ids. This is not likely in normal practice.)
So, if you are partitioning on dt_dtamp, and you have id AUTO_INCREMENT,
then PRIMARY KEY ( 'id','dt_dtamp') is sufficient. UNIQUE ('id') is
unnecessary (and disallowed).
I don't have auto increment column in my table , my primary key is ( `id`
varchar(255) NOT NULL, )
If `id` is a string, then you _could_ (in 5.5.x?) PARTITION BY RANGE(id)
and specify VALUES LESS THAN...
Is the below function works in 5.5
PARTITION BY RANGE ( substring(id,9,6) ) (
- PARTITION c0 VALUES LESS THAN (
substring('014-120412124227546-o-C@1469',9,6) ),
- PARTITION c1 VALUES LESS THAN (
substring('014-120512124227546-o@1469',9,6) ),
Rt now not supported in 5.1.5
WHY do you want to use PARTITIONing? I ask because I find a lot of people
think they want to PARTITION, yet they have not determined that there will
be any benefit.
I need partitioning because three tables in my database are populated in
seconds , right now size 40 GB. We don't need data older than 15 days ,
right now we are deleting ( purging ) older records manually that causes
fragmentation and a lot of manual effort.PFA schema of a table. So I
decided to partiton on created_time column purge records by deleting
partitions but I cannot change the primary key (id) to primary key (
id,created_time) column i.e the only concern i have.
All is working fine as the proper indexes guided by you but I cannot use
partitioning to solve this issue if i need to change primary key i.e i m
moving to partition on substring of id column if feasible.
Any other way i can solve this issue. Thanks for your link also , it is
very helpfull.
Thanks
See tips in
http://mysql.rjweb.org/doc.php/ricksrots
-Original Message-
From: Adarsh Sharma [mailto:eddy.ada...@gmail.com]
Sent: Friday, September 21, 2012 5:09 AM
To: mysql@lists.mysql.com
Subject: Re: Partitioning on a Substring of Varchar Column in Mysql
Just update the string needed function substring(id,9,6).
On Fri, Sep 21, 2012 at 5:18 PM, Adarsh Sharma
eddy.ada...@gmail.comwrote:
Hi all,
I created a partition on a timstamp column ( dt_dtamp ) in
mysql-5.1.58 testing table.But as all of us know that partitioned
column need to be primary key.
I have already id column as primary key but my application cann't
afford primary key ('id','dt_stamp') in a table.
I thought tried PRIMARY KEY ( 'id','dt_dtamp') UNIQUE ('id')
will
work but below error came :
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the
table's partitioning function
as it is clearly mentioned in
http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-
partit
ioning-keys-unique-keys.html
.
Is it possible i can create partitioning on a substring of
varchar(255) ( id ) column. Below are the some contents of my id
column :-
0038000-120614070130414-sudoie-sudoi-C@86 |
| 0038000-120614070130414-sudoie-sudoi-C@87 |
| 0038000-120614070130414-sudoie-sudoi-C@88 |
| 0038000-120614070130414-sudoie-sudoi-C@89 |
| 0038000-120614070130414-sudoie-sudoi-C@90 |
| 0038000-120614070130414-sudoie-sudoi-C@91 |
| 0038000-120614070130414-sudoie-sudoi-C@92 |
| 0038000-120614070130414-sudoie-sudoi-C@93 |
| 0038000-120614070130414-sudoie-sudoi-C@94 |
| 0038000-120614070130414-sudoie-sudoi-C@95 |
| 0038000-120614070130414-sudoie-sudoi-C@96 |
It includes date in it. Can i make my partitioning on string after
first hyphen : 120614
Is it possible in mysql5.1.58 or may be higher versions so that i
have
only primary key ('id') on my table. Need a crack for it.
Thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
