MySQL Forums
Forum List  »  Quality Assurance

weird behaviour inserting 900k with autocommit of
Posted by: Hans-Peter Sloot
Date: April 06, 2012 05:45AM

Hi,

This is about Server version: 5.1.52 on OEL6.

As an Oracle dba I am looking at mysql to expand my skills.
I want to move the sample schemas for Oracle to mysql.
To do this I have created a partitioned table in my sql for table SALES of the SH schema. After this I execute a script that does about 900K inserts with autocommet set to off.
After I commit I expect the rows to be visible to other sessions.
But that is not the case.
Only when I restart mysqld I see the new rows in other sessions.

Am I doing something wrong or can mysql not handle such an amount of rows?
Any advice would be appreciated.

mysql> SELECT table_name, partition_name, table_rows FROM information_schema.partitions WHERE TABLE_SCHEMA = 'sh' and table_name = 'SALES';
+------------+----------------+------------+
| table_name | partition_name | table_rows |
+------------+----------------+------------+
| SALES | SALES_1995 | 0 |
| SALES | SALES_1996 | 0 |
| SALES | SALES_H1_1997 | 0 |
| SALES | SALES_H2_1997 | 0 |
| SALES | SALES_Q1_1998 | 44082 |
| SALES | SALES_Q2_1998 | 35962 |
| SALES | SALES_Q3_1998 | 50753 |
| SALES | SALES_Q4_1998 | 49303 |
| SALES | SALES_Q1_1999 | 64383 |
| SALES | SALES_Q2_1999 | 54523 |
| SALES | SALES_Q3_1999 | 67573 |
| SALES | SALES_Q4_1999 | 62643 |
| SALES | SALES_Q1_2000 | 62353 |
| SALES | SALES_Q2_2000 | 55683 |
| SALES | SALES_Q3_2000 | 59163 |
| SALES | SALES_Q4_2000 | 52746 |
| SALES | SALES_Q1_2001 | 60903 |
| SALES | SALES_Q2_2001 | 63513 |
| SALES | SALES_Q3_2001 | 66123 |
| SALES | SALES_Q4_2001 | 70184 |
| SALES | SALES_Q1_2002 | 0 |
| SALES | SALES_Q2_2002 | 0 |
| SALES | SALES_Q3_2002 | 0 |
| SALES | SALES_Q4_2002 | 0 |
| SALES | SALES_Q1_2003 | 0 |
| SALES | SALES_Q2_2003 | 0 |
| SALES | SALES_Q3_2003 | 0 |
| SALES | SALES_Q4_2003 | 0 |
+------------+----------------+------------+
28 rows in set (0.01 sec)

mysql> select count(*) from SALES;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'SALES';
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+------------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+------------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+
| SALES | InnoDB | 10 | Compact | 919902 | 65 | 60227584 | 0 | 0 | 3611295744 | NULL | NULL | NULL | NULL | latin1_swedish_ci | NULL | partitioned | |
+-------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+------------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.01 sec)

When I do 'analyze table SALES; ' the rows become visible.
Also when I insert another row after the commit.


regards Hans-Peter

Options: ReplyQuote


Subject
Views
Written By
Posted
weird behaviour inserting 900k with autocommit of
1744
April 06, 2012 05:45AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.