maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #09575
Re: InnoDB blob for primary key
Hi Sergei!
As i told you i was prototyping for hash table
It is done around 90% apart from one thing when hash is same
how to get record from .myd file when i have offset of record
so currently i am skipping it
But it is very fast i do not know why this is so fast here are
results of employee database
salary table definition
CREATE TABLE salaries (
emp_no INT NOT NULL,
salary blob NOT NULL,
from_date DATE NOT NULL,
to_date DATE NOT NULL,
FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE,
PRIMARY KEY (emp_no, from_date)
)
;
And query is
MariaDB [employees]> select distinct salary from salaries;
Result with out using hash table
+--------+
85814 rows in set (2 min 24.76 sec)
Result with using hash table
| 39420 |
+--------+
85809 rows in set (6.24 sec)
( number of rows are not equal but this can be solved if i get record by
offset)
I am sure there is something wrong.The whole hash table is in memory like
wise the
b tree of hash is in memory but why there is so much improvement. Please
sir check the
prototype and tell if i am wrong .thanks
Regards
sachin
On Mon, May 2, 2016 at 11:43 AM, Sergei Golubchik <serg@xxxxxxxxxxx> wrote:
> Hi, Sachin!
>
> On May 02, Sachin Setia wrote:
> > I am sorry sir Currently my exam are going on
> > But i am working on prototype of second project. Will be done by tommorow
> > Regards
> > sachin
>
> Sure thing, that's totally fine!
>
> Still, in the future, if you plan to go silent for a while (exam or you
> just want to relax for a few days or something else :) - please drop me
> a short email and then I will know that you didn't disappear from the
> project. Thanks!
>
> Regards,
> Sergei
> Chief Architect MariaDB
> and security@xxxxxxxxxxx
>
diff --git a/storage/maria/ma_hash_table.h b/storage/maria/ma_hash_table.h
new file mode 100644
index 0000000..c8e4578
--- /dev/null
+++ b/storage/maria/ma_hash_table.h
@@ -0,0 +1,45 @@
+#include"../../mysys/my_malloc.c"
+#include"../../include/my_global.h"
+typedef struct ma_hash_table_element{
+ unsigned int hash_code;
+ unsigned int record_offset;
+ struct ma_hash_table * next; //we will use single link list because no delete operations
+} ma_hash_table_element;
+
+typedef struct ma_hash_table{
+ unsigned int size;
+ ma_hash_table_element * h_t_e;
+}ma_hash_table;
+ma_hash_table * ma_create_hash_table(int size){
+ ma_hash_table_element * _ht = (ma_hash_table *)my_malloc(sizeof(ma_hash_table)*size,MYF(MY_WME));
+ ma_hash_table_element * temp=_ht;
+ for(int i=0;i<size;i++){
+ temp->next=NULL;
+ temp++;
+ }
+ ma_hash_table *temp_hash_table = (ma_hash_table *)my_malloc(sizeof(ma_hash_table),MYF(MY_WME));
+ temp_hash_table->size =size;
+ temp_hash_table->h_t_e = _ht;
+ return temp_hash_table;
+}
+
+int add_key_to_hash_table(ma_hash_table *_ht,unsigned int key , unsigned int offset){
+ int hash_position = key % _ht->size; //just a simple logic thinking of upgrading it may we whatever
+ ma_hash_table_element *tmp= _ht->h_t_e;
+ tmp = tmp+hash_position;
+ ma_hash_table_element *parent;
+ //transverse the whole list
+ while(tmp!=NULL){
+ if(tmp->hash_code==key)
+ return 1;
+ parent=tmp;
+ tmp=tmp->next;
+ }
+ ma_hash_table_element *temp_hash = (ma_hash_table_element *)my_malloc(sizeof(ma_hash_table_element),MYF(MY_WME));
+ temp_hash->hash_code=key;
+ temp_hash->record_offset=offset;
+ temp_hash->next=NULL;
+ parent->next=temp_hash;
+ return 0;
+}
+
diff --git a/storage/maria/ma_state.h b/storage/maria/ma_state.h
index 2903986..d89324f 100644
--- a/storage/maria/ma_state.h
+++ b/storage/maria/ma_state.h
@@ -14,7 +14,7 @@
Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA */
/* Struct to store tables in use by one transaction */
-
+//#include"ma_hash_table.h"
typedef struct st_maria_status_info
{
ha_rows records; /* Rows in table */
@@ -24,6 +24,7 @@ typedef struct st_maria_status_info
my_off_t key_file_length;
my_off_t data_file_length;
ha_checksum checksum;
+ void * hash_table;
uint32 changed:1, /* Set if table was changed */
no_transid:1; /* Set if no transid was set on rows */
} MARIA_STATUS_INFO;
diff --git a/storage/maria/ma_write.c b/storage/maria/ma_write.c
index f57c462..b31f5a7 100644
--- a/storage/maria/ma_write.c
+++ b/storage/maria/ma_write.c
@@ -21,7 +21,7 @@
#include "trnman.h"
#include "ma_key_recover.h"
#include "ma_blockrec.h"
-
+#include "ma_hash_table.h"
/* Functions declared in this file */
static int w_search(MARIA_HA *info, uint32 comp_flag,
@@ -93,6 +93,7 @@ int maria_write(MARIA_HA *info, uchar *record)
my_bool lock_tree= share->lock_key_trees;
my_bool fatal_error;
MARIA_KEYDEF *keyinfo;
+ ma_hash_table *ht;
DBUG_ENTER("maria_write");
DBUG_PRINT("enter",("index_file: %d data_file: %d",
share->kfile.file, info->dfile.file));
@@ -121,26 +122,35 @@ int maria_write(MARIA_HA *info, uchar *record)
}
if (_ma_mark_file_changed(share))
goto err2;
-
+
/* Calculate and check all unique constraints */
+ if(!info->state->hash_table){
+ info->state->hash_table = ma_create_hash_table(100000);
+ }
+ ha_checksum unique_hash= _ma_unique_hash(share->uniqueinfo,record);
+ if(add_key_to_hash_table((ma_hash_table*)info->state->hash_table,unique_hash,info->state->records)){
+ //here check for duplicates
+ //i really do not know to get record by record offset
+ goto err2;
+ }
+// if (share->state.header.uniques)
+// {
+// for (i=0 ; i < share->state.header.uniques ; i++)
+// {
+// MARIA_UNIQUEDEF *def= share->uniqueinfo + i;
+// ha_checksum unique_hash= _ma_unique_hash(share->uniqueinfo+i,record);
+// if (maria_is_key_active(share->state.key_map, def->key))
+// {
+// if (_ma_check_unique(info, def, record,
+// unique_hash, HA_OFFSET_ERROR))
+// goto err2;
+// }
+// else
+// maria_unique_store(record+ share->keyinfo[def->key].seg->start,
+// unique_hash);
+// }
+// }
- if (share->state.header.uniques)
- {
- for (i=0 ; i < share->state.header.uniques ; i++)
- {
- MARIA_UNIQUEDEF *def= share->uniqueinfo + i;
- ha_checksum unique_hash= _ma_unique_hash(share->uniqueinfo+i,record);
- if (maria_is_key_active(share->state.key_map, def->key))
- {
- if (_ma_check_unique(info, def, record,
- unique_hash, HA_OFFSET_ERROR))
- goto err2;
- }
- else
- maria_unique_store(record+ share->keyinfo[def->key].seg->start,
- unique_hash);
- }
- }
/* Ensure we don't try to restore auto_increment if it doesn't change */
info->last_auto_increment= ~(ulonglong) 0;
@@ -160,122 +170,122 @@ int maria_write(MARIA_HA *info, uchar *record)
/* Write all keys to indextree */
buff= info->lastkey_buff2;
- for (i=0, keyinfo= share->keyinfo ; i < share->base.keys ; i++, keyinfo++)
- {
- MARIA_KEY int_key;
- if (maria_is_key_active(share->state.key_map, i))
- {
- my_bool local_lock_tree= (lock_tree &&
- !(info->bulk_insert &&
- is_tree_inited(&info->bulk_insert[i])));
- if (local_lock_tree)
- {
- mysql_rwlock_wrlock(&keyinfo->root_lock);
- keyinfo->version++;
- }
- if (keyinfo->flag & HA_FULLTEXT )
- {
- if (_ma_ft_add(info,i, buff,record,filepos))
- {
- if (local_lock_tree)
- mysql_rwlock_unlock(&keyinfo->root_lock);
- DBUG_PRINT("error",("Got error: %d on write",my_errno));
- goto err;
- }
- }
- else
- {
- while (keyinfo->ck_insert(info,
- (*keyinfo->make_key)(info, &int_key, i,
- buff, record, filepos,
- info->trn->trid)))
- {
- TRN *blocker;
- DBUG_PRINT("error",("Got error: %d on write",my_errno));
- /*
- explicit check to filter out temp tables, they aren't
- transactional and don't have a proper TRN so the code
- below doesn't work for them.
- Also, filter out non-thread maria use, and table modified in
- the same transaction.
- At last, filter out non-dup-unique errors.
- */
- if (!local_lock_tree)
- goto err;
- if (info->dup_key_trid == info->trn->trid ||
- my_errno != HA_ERR_FOUND_DUPP_KEY)
- {
- mysql_rwlock_unlock(&keyinfo->root_lock);
- goto err;
- }
- /* Different TrIDs: table must be transactional */
- DBUG_ASSERT(share->base.born_transactional);
- /*
- If transactions are disabled, and dup_key_trid is different from
- our TrID, it must be ALTER TABLE with dup_key_trid==0 (no
- transaction). ALTER TABLE does have MARIA_HA::TRN not dummy but
- puts TrID=0 in rows/keys.
- */
- DBUG_ASSERT(share->now_transactional ||
- (info->dup_key_trid == 0));
- blocker= trnman_trid_to_trn(info->trn, info->dup_key_trid);
- /*
- if blocker TRN was not found, it means that the conflicting
- transaction was committed long time ago. It could not be
- aborted, as it would have to wait on the key tree lock
- to remove the conflicting key it has inserted.
- */
- if (!blocker || blocker->commit_trid != ~(TrID)0)
- { /* committed */
- if (blocker)
- mysql_mutex_unlock(& blocker->state_lock);
- mysql_rwlock_unlock(&keyinfo->root_lock);
- goto err;
- }
- mysql_rwlock_unlock(&keyinfo->root_lock);
- {
- /* running. now we wait */
- WT_RESOURCE_ID rc;
- int res;
- PSI_stage_info old_stage_info;
-
- rc.type= &ma_rc_dup_unique;
- /* TODO savepoint id when we'll have them */
- rc.value= (intptr)blocker;
- res= wt_thd_will_wait_for(info->trn->wt, blocker->wt, & rc);
- if (res != WT_OK)
- {
- mysql_mutex_unlock(& blocker->state_lock);
- my_errno= HA_ERR_LOCK_DEADLOCK;
- goto err;
- }
- proc_info_hook(0, &stage_waiting_for_a_resource, &old_stage_info,
- __func__, __FILE__, __LINE__);
- res= wt_thd_cond_timedwait(info->trn->wt, & blocker->state_lock);
- proc_info_hook(0, &old_stage_info, 0, __func__, __FILE__, __LINE__);
-
- mysql_mutex_unlock(& blocker->state_lock);
- if (res != WT_OK)
- {
- my_errno= res == WT_TIMEOUT ? HA_ERR_LOCK_WAIT_TIMEOUT
- : HA_ERR_LOCK_DEADLOCK;
- goto err;
- }
- }
- mysql_rwlock_wrlock(&keyinfo->root_lock);
-#ifndef MARIA_CANNOT_ROLLBACK
- keyinfo->version++;
-#endif
- }
- }
-
- /* The above changed info->lastkey2. Inform maria_rnext_same(). */
- info->update&= ~HA_STATE_RNEXT_SAME;
-
- if (local_lock_tree)
- mysql_rwlock_unlock(&keyinfo->root_lock);
- }
- }
+// for (i=0, keyinfo= share->keyinfo ; i < share->base.keys ; i++, keyinfo++)
+// {
+// MARIA_KEY int_key;
+// if (maria_is_key_active(share->state.key_map, i))
+// {
+// my_bool local_lock_tree= (lock_tree &&
+// !(info->bulk_insert &&
+// is_tree_inited(&info->bulk_insert[i])));
+// if (local_lock_tree)
+// {
+// mysql_rwlock_wrlock(&keyinfo->root_lock);
+// keyinfo->version++;
+// }
+// if (keyinfo->flag & HA_FULLTEXT )
+// {
+// if (_ma_ft_add(info,i, buff,record,filepos))
+// {
+// if (local_lock_tree)
+// mysql_rwlock_unlock(&keyinfo->root_lock);
+// DBUG_PRINT("error",("Got error: %d on write",my_errno));
+// goto err;
+// }
+// }
+// else
+// {
+// while (keyinfo->ck_insert(info,
+// (*keyinfo->make_key)(info, &int_key, i,
+// buff, record, filepos,
+// info->trn->trid)))
+// {
+// TRN *blocker;
+// DBUG_PRINT("error",("Got error: %d on write",my_errno));
+// /*
+// explicit check to filter out temp tables, they aren't
+// transactional and don't have a proper TRN so the code
+// below doesn't work for them.
+// Also, filter out non-thread maria use, and table modified in
+// the same transaction.
+// At last, filter out non-dup-unique errors.
+// */
+// if (!local_lock_tree)
+// goto err;
+// if (info->dup_key_trid == info->trn->trid ||
+// my_errno != HA_ERR_FOUND_DUPP_KEY)
+// {
+// mysql_rwlock_unlock(&keyinfo->root_lock);
+// goto err;
+// }
+// /* Different TrIDs: table must be transactional */
+// DBUG_ASSERT(share->base.born_transactional);
+// /*
+// If transactions are disabled, and dup_key_trid is different from
+// our TrID, it must be ALTER TABLE with dup_key_trid==0 (no
+// transaction). ALTER TABLE does have MARIA_HA::TRN not dummy but
+// puts TrID=0 in rows/keys.
+// */
+// DBUG_ASSERT(share->now_transactional ||
+// (info->dup_key_trid == 0));
+// blocker= trnman_trid_to_trn(info->trn, info->dup_key_trid);
+// /*
+// if blocker TRN was not found, it means that the conflicting
+// transaction was committed long time ago. It could not be
+// aborted, as it would have to wait on the key tree lock
+// to remove the conflicting key it has inserted.
+// */
+// if (!blocker || blocker->commit_trid != ~(TrID)0)
+// { /* committed */
+// if (blocker)
+// mysql_mutex_unlock(& blocker->state_lock);
+// mysql_rwlock_unlock(&keyinfo->root_lock);
+// goto err;
+// }
+// mysql_rwlock_unlock(&keyinfo->root_lock);
+// {
+// /* running. now we wait */
+// WT_RESOURCE_ID rc;
+// int res;
+// PSI_stage_info old_stage_info;
+//
+// rc.type= &ma_rc_dup_unique;
+// /* TODO savepoint id when we'll have them */
+// rc.value= (intptr)blocker;
+// res= wt_thd_will_wait_for(info->trn->wt, blocker->wt, & rc);
+// if (res != WT_OK)
+// {
+// mysql_mutex_unlock(& blocker->state_lock);
+// my_errno= HA_ERR_LOCK_DEADLOCK;
+// goto err;
+// }
+// proc_info_hook(0, &stage_waiting_for_a_resource, &old_stage_info,
+// __func__, __FILE__, __LINE__);
+// res= wt_thd_cond_timedwait(info->trn->wt, & blocker->state_lock);
+// proc_info_hook(0, &old_stage_info, 0, __func__, __FILE__, __LINE__);
+//
+// mysql_mutex_unlock(& blocker->state_lock);
+// if (res != WT_OK)
+// {
+// my_errno= res == WT_TIMEOUT ? HA_ERR_LOCK_WAIT_TIMEOUT
+// : HA_ERR_LOCK_DEADLOCK;
+// goto err;
+// }
+// }
+// mysql_rwlock_wrlock(&keyinfo->root_lock);
+//#ifndef MARIA_CANNOT_ROLLBACK
+// keyinfo->version++;
+//#endif
+// }
+// }
+//
+// /* The above changed info->lastkey2. Inform maria_rnext_same(). */
+// info->update&= ~HA_STATE_RNEXT_SAME;
+//
+// if (local_lock_tree)
+// mysql_rwlock_unlock(&keyinfo->root_lock);
+// }
+// }
if (share->calc_write_checksum)
info->cur_row.checksum= (*share->calc_write_checksum)(info,record);
if (filepos != HA_OFFSET_ERROR)
@@ -407,12 +417,12 @@ int maria_write(MARIA_HA *info, uchar *record)
info->update= (HA_STATE_CHANGED | HA_STATE_WRITTEN | HA_STATE_ROW_CHANGED);
my_errno=save_errno;
err2:
- save_errno=my_errno;
- DBUG_ASSERT(save_errno);
- if (!save_errno)
- save_errno= HA_ERR_INTERNAL_ERROR; /* Should never happen */
- DBUG_PRINT("error", ("got error: %d", save_errno));
- _ma_writeinfo(info,WRITEINFO_UPDATE_KEYFILE);
+ save_errno=my_errno=141;
+// DBUG_ASSERT(save_errno);
+// if (!save_errno)
+// save_errno= HA_ERR_INTERNAL_ERROR; /* Should never happen */
+// DBUG_PRINT("error", ("got error: %d", save_errno));
+// _ma_writeinfo(info,WRITEINFO_UPDATE_KEYFILE);
DBUG_RETURN(my_errno=save_errno);
} /* maria_write */
@@ -471,7 +481,7 @@ static my_bool _ma_ck_write_btree(MARIA_HA *info, MARIA_KEY *key)
*/
static my_bool _ma_ck_write_btree_with_log(MARIA_HA *info, MARIA_KEY *key,
- my_off_t *root, uint32 comp_flag)
+ my_off_t *root, uint32 comp_flag)//work
{
MARIA_SHARE *share= info->s;
LSN lsn= LSN_IMPOSSIBLE;
@@ -518,7 +528,7 @@ static my_bool _ma_ck_write_btree_with_log(MARIA_HA *info, MARIA_KEY *key,
*/
my_bool _ma_ck_real_write_btree(MARIA_HA *info, MARIA_KEY *key, my_off_t *root,
- uint32 comp_flag)
+ uint32 comp_flag)//work
{
int error;
DBUG_ENTER("_ma_ck_real_write_btree");
@@ -777,7 +787,7 @@ static int w_search(register MARIA_HA *info, uint32 comp_flag, MARIA_KEY *key,
int _ma_insert(register MARIA_HA *info, MARIA_KEY *key,
MARIA_PAGE *anc_page, uchar *key_pos, uchar *key_buff,
MARIA_PAGE *father_page, uchar *father_key_pos,
- my_bool insert_last)
+ my_bool insert_last)//work
{
uint a_length, nod_flag, org_anc_length;
int t_length;
diff --git a/storage/myisam/mi_search.c b/storage/myisam/mi_search.c
index 60a34c6..710a3e4 100644
--- a/storage/myisam/mi_search.c
+++ b/storage/myisam/mi_search.c
@@ -763,7 +763,7 @@ uint _mi_get_static_key(register MI_KEYDEF *keyinfo, uint nod_flag,
/*
- get key witch is packed against previous key or key with a NULL column.
+ get key which is packed against previous key or key with a NULL column.
SYNOPSIS
_mi_get_pack_key()
diff --git a/storage/myisam/mi_write.c b/storage/myisam/mi_write.c
index ff96ee8..77549b0 100644
--- a/storage/myisam/mi_write.c
+++ b/storage/myisam/mi_write.c
@@ -122,7 +122,7 @@ int mi_write(MI_INFO *info, uchar *record)
}
else
{
- if (share->keyinfo[i].ck_insert(info,i,buff,
+ if (share->keyinfo[i].ck_insert(info,i,buff, //work
_mi_make_key(info,i,buff,record,filepos)))
{
if (local_lock_tree)
@@ -258,7 +258,7 @@ int _mi_ck_write(MI_INFO *info, uint keynr, uchar *key, uint key_length)
**********************************************************************/
int _mi_ck_write_btree(register MI_INFO *info, uint keynr, uchar *key,
- uint key_length)
+ uint key_length)//work
{
int error;
uint comp_flag;
@@ -335,7 +335,7 @@ int _mi_enlarge_root(MI_INFO *info, MI_KEYDEF *keyinfo, uchar *key,
0 = ok
1 = key should be stored in higher tree
*/
-
+//work
static int w_search(register MI_INFO *info, register MI_KEYDEF *keyinfo,
uint comp_flag, uchar *key, uint key_length, my_off_t page,
uchar *father_buff, uchar *father_keypos,
Follow ups
References
