Large table

--------------040305060009010007000509
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

All,

I have a very large table. It has about 1 billion rows. Initially
everything was fine. But now the table is a bit slow. Loaded takes a lot
of time. I usually load in chunks of 22 million rows.

Is the size of the table any way related to the performance? I am not
sure about this.

Will splitting the table and having a "view" from multiple table
increase the performance?

Thanks in advance.

--
Thanks,
Sairam Krishnamurthy
+1 612 859 8161


--------------040305060009010007000509--

Re: Large table

--20cf3054a475e7b03e0499c95ee2
Content-Type: text/plain; charset=ISO-8859-1

What is the table type for Table?

Firstly check with queries and index if required. Check if queries using
this table can be fine tuned. Check if table getting locked.

If size of table is problem and if the table type is innodb check for
innodb_file_per_table options. Also have a look for portioning.


On Fri, Jan 14, 2011 at 4:18 AM, Sairam Krishnamurthy
wrote:

> All,
>
> I have a very large table. It has about 1 billion rows. Initially
> everything was fine. But now the table is a bit slow. Loaded takes a lot of
> time. I usually load in chunks of 22 million rows.
>
> Is the size of the table any way related to the performance? I am not sure
> about this.
>
> Will splitting the table and having a "view" from multiple table increase
> the performance?
>
> Thanks in advance.
>
> --
> Thanks,
> Sairam Krishnamurthy
> +1 612 859 8161
>
>

--20cf3054a475e7b03e0499c95ee2--

Re: Large table

--001636c5be7c2698420499cb9286
Content-Type: text/plain; charset=ISO-8859-1

partitioning will help u

Krishna

On Fri, Jan 14, 2011 at 4:18 AM, Sairam Krishnamurthy
wrote:

> All,
>
> I have a very large table. It has about 1 billion rows. Initially
> everything was fine. But now the table is a bit slow. Loaded takes a lot of
> time. I usually load in chunks of 22 million rows.
>
> Is the size of the table any way related to the performance? I am not sure
> about this.
>
> Will splitting the table and having a "view" from multiple table increase
> the performance?
>
> Thanks in advance.
>
> --
> Thanks,
> Sairam Krishnamurthy
> +1 612 859 8161
>
>

--001636c5be7c2698420499cb9286--

Re: Large table

--------------040407040402070304080805
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Yogesh,

The type is MyISAM. So i guess size is not a matter for now. I am well
over the limit for MyISAM. I will partition the table and check.

Thanks,
Sairam Krishnamurthy
+1 612 859 8161


On 01/14/2011 01:27 AM, Yogesh Kore wrote:
> What is the table type for Table?
>
> Firstly check with queries and index if required. Check if queries
> using this table can be fine tuned. Check if table getting locked.
>
> If size of table is problem and if the table type is innodb check for
> innodb_file_per_table options. Also have a look for portioning.
>
>
> On Fri, Jan 14, 2011 at 4:18 AM, Sairam Krishnamurthy
> > wrote:
>
> All,
>
> I have a very large table. It has about 1 billion rows. Initially
> everything was fine. But now the table is a bit slow. Loaded takes
> a lot of time. I usually load in chunks of 22 million rows.
>
> Is the size of the table any way related to the performance? I am
> not sure about this.
>
> Will splitting the table and having a "view" from multiple table
> increase the performance?
>
> Thanks in advance.
>
> --
> Thanks,
> Sairam Krishnamurthy
> +1 612 859 8161
>
>

--------------040407040402070304080805--

Re: Large table

All,

I have another problem with the partition. Once I create a partition,
the first table contains 700 million rows and I started adding new
values to the second table. And I have a View with the following
definition:

CREATE VIEW view_name AS SELECT * FROM table_1 UNION SELECT * from table_2;

Now, the problem is, when I query the view, MySql is creating a temp
table from these two tables and queries the temp table. Because of the
size of the original tables, the temp table creation is very slow. I
looked at the processing algorithms for views and I not sure about the
difference between MERGE, and TEMPTABLE.

Can you guys please advise on what is the best way to do this?

Thanks
Sairam


On Fri, Jan 14, 2011 at 2:08 PM, Sairam Krishnamurthy
wrote:
> Yogesh,
>
> The type is MyISAM. So i guess size is not a matter for now. I am well over
> the limit for MyISAM. I will partition the table and check.
>
> Thanks,
> Sairam Krishnamurthy
> +1 612 859 8161
>
> On 01/14/2011 01:27 AM, Yogesh Kore wrote:
>
> What is the table type for Table?
>
> Firstly check with queries and index if required. Check if queries using
> this table can be fine tuned. Check if table getting locked.
>
> If size of table is problem and if the table type is innodb check for
> innodb_file_per_table options. Also have a look for portioning.
>
>
> On Fri, Jan 14, 2011 at 4:18 AM, Sairam Krishnamurthy
> wrote:
>>
>> All,
>>
>> I have a very large table. It has about 1 billion rows. Initially
>> everything was fine. But now the table is a bit slow. Loaded takes a lot of
>> time. I usually load in chunks of 22 million rows.
>>
>> Is the size of the table any way related to the performance? I am not sure
>> about this.
>>
>> Will splitting the table and having a "view" from multiple table increase
>> the performance?
>>
>> Thanks in advance.
>>
>> --
>> Thanks,
>> Sairam Krishnamurthy
>> +1 612 859 8161
>>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Large table

The VIEW is pretty much forced to behave this way.. it's really just
a run-time SELECT which is amended with your additional query
parameters.. what you want is a MERGE table

http://dev.mysql.com/doc/refman/5.1/en/merge-storage-engine. html

This allow your collection of identically structured MyISAM tables to
remain distinct, storage-wise, while acting as an aggregate at query
time. This will give you the behaviour you are expecting.

A TEMP table is just that.. it is never visible to any except the
connection that creates it and it disappears when that connection is
broken. They need to be build from scratch within the connection
life-cycle. They have their uses but, I suspect, they have little to
do with the functionality you seek.

- michael dykman


On Tue, Jan 18, 2011 at 12:42 PM, Sairam Krishnamurthy
wrote:
> All,
>
> I have another problem with the partition. Once I create a partition,
> the first table contains 700 million rows and I started adding new
> values to the second table. And I have a View with the following
> definition:
>
> CREATE VIEW view_name AS SELECT * FROM table_1 UNION SELECT * from table_=
2;
>
> Now, the problem is, when I query the view, MySql is creating a temp
> table from these two tables and queries the temp table. Because of the
> size of the original tables, the temp table creation is very slow. I
> looked at the processing algorithms for views and I not sure about the
> difference between MERGE, and TEMPTABLE.
>
> Can you guys please advise on what is the best way to do this?
>
> Thanks
> Sairam
>
>
> On Fri, Jan 14, 2011 at 2:08 PM, Sairam Krishnamurthy
> wrote:
>> Yogesh,
>>
>> The type is MyISAM. So i guess size is not a matter for now. I am well o=
ver
>> the limit for MyISAM. I will partition the table and check.
>>
>> Thanks,
>> Sairam Krishnamurthy
>> +1 612 859 8161
>>
>> On 01/14/2011 01:27 AM, Yogesh Kore wrote:
>>
>> What is the table type for Table?
>>
>> Firstly check with queries and index if required. Check if queries using
>> this table can be fine tuned. Check if table getting locked.
>>
>> If size of table is problem and if the table type is innodb check for
>> innodb_file_per_table options. Also have a look for portioning.
>>
>>
>> On Fri, Jan 14, 2011 at 4:18 AM, Sairam Krishnamurthy om>
>> wrote:
>>>
>>> All,
>>>
>>> I have a very large table. It has about 1 billion rows. Initially
>>> everything was fine. But now the table is a bit slow. Loaded takes a lo=
t of
>>> time. I usually load in chunks of 22 million rows.
>>>
>>> Is the size of the table any way related to the performance? I am not s=
ure
>>> about this.
>>>
>>> Will splitting the table and having a "view" from multiple table increa=
se
>>> the performance?
>>>
>>> Thanks in advance.
>>>
>>> --
>>> Thanks,
>>> Sairam Krishnamurthy
>>> +1 612 859 8161
>>>
>>
>>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dmdykman@gmail=
..com
>
>



--=20
=A0- michael dykman
=A0- mdykman@gmail.com

=A0May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg