FAQ Search Today's Posts Mark Forums Read
» Video Reviews

» Linux Archive

Linux-archive is a website aiming to archive linux email lists and to make them easily accessible for linux users/developers.


» Sponsor

» Partners

» Sponsor

Go Back   Linux Archive > CentOS > CentOS

 
 
LinkBack Thread Tools
 
Old 01-27-2010, 04:28 AM
JohnS
 
Default Centos/Linux Disk Caching, might be OT in some ways

On Tue, 2010-01-26 at 09:48 -0500, Ross Walker wrote:

> > Great things started to happen with mysql @ version 5 >. Now it's
> > just
> > probally going to wither away. Who really knows?
>
> Some really nice things are happening with postgresql as well, you
> should check it out.
>
> -Ross
>
---
Yes, I have been having some thoughts for the past few weeks of tearing
into it with all that is happening with MySQL.

John

_______________________________________________
CentOS mailing list
CentOS@centos.org
http://lists.centos.org/mailman/listinfo/centos
 
Old 01-27-2010, 08:07 AM
Noob Centos Admin
 
Default Centos/Linux Disk Caching, might be OT in some ways

Hi,

> Split the TEXT/BLOB data out of the primary table into tables of their
> own indexed to the primary table by it's key column.

This is part of what I was planning to do, there are a lot of stuff I
am planning to split out into their own tables with reference key. The
problem is I'm unsure whether the added overheads of joins would
negate the IO benefits hence trying to figure out more about how
Centos/Linux does the caching.

> Think about distributing the parts to different boxes as necessary.
> You can start with the DBMS which is the logical candidate.

Eventually I figured that would probably have to be done but I don't
know enough at this point. So I'm taking the approach of optimizing
stage by stage starting with things I'm more familiar with and less
likely to muck up totally, i.e.from the app/script side first. Then
after getting more familiar with the setup, experiment with the
hardware based solutions.


> On the DBMS backend, give it plenty of memory, good storage for the
> workload and good networking.

Again problem is old server so memory is maxed, drives controller is
probably not helping.

> On the Apache/PHP side, look for a good DBMS inter-connect and some
> PHP caching module and of course enough CPU for the PHP code and
> network for Apache+DBMS inter-connect.
>
> If you wanted to split it up even more you could look into some sort
> of PHP distributed cache/processing system and have PHP processed
> behind Apache.

Thanks for the heads up, I didn't realize it was possible to separate
the PHP processing from Apache itself. However, for the time being,
I'm probably still limited to a single server situation so will keep
this in mind for future.
_______________________________________________
CentOS mailing list
CentOS@centos.org
http://lists.centos.org/mailman/listinfo/centos
 
Old 01-27-2010, 08:11 AM
Noob Centos Admin
 
Default Centos/Linux Disk Caching, might be OT in some ways

Hi,

>>>
>>> I believe the OP said he was running postgresql.
>>>
>>
>> Quoted from OPs previous mail hes not sure lol....
>>
>> """The web application is written in PHP and runs off MySQL and/or
>> Postgresql."""
>
> Ah, well #1 on his list then is to figure out what he is running!

LOL, I know it sounds quite noobish, coming across like I've no idea
what DBMS it is running on. The system currently runs on MySQL but
part of my update requirement was to decouple the DBMS so that we can
make an eventual switch to postgresql.

Hence the solution cannot be dependent on some specific MySQL functionality.
_______________________________________________
CentOS mailing list
CentOS@centos.org
http://lists.centos.org/mailman/listinfo/centos
 
Old 01-27-2010, 11:30 AM
Chan Chung Hang Christopher
 
Default Centos/Linux Disk Caching, might be OT in some ways

>> Ah, well #1 on his list then is to figure out what he is running!
>
> LOL, I know it sounds quite noobish, coming across like I've no idea
> what DBMS it is running on. The system currently runs on MySQL but
> part of my update requirement was to decouple the DBMS so that we can
> make an eventual switch to postgresql.
>
> Hence the solution cannot be dependent on some specific MySQL functionality.


mysql's isam tables have a reputation for surviving just about anything
and great builtin replication support...

postgresql less so (I suspect due to fake fsync/fsyncdata in the days
before barriers) but maybe things have improved a lot nowadays.

Why are you switching?
_______________________________________________
CentOS mailing list
CentOS@centos.org
http://lists.centos.org/mailman/listinfo/centos
 
Old 01-27-2010, 11:51 AM
Noob Centos Admin
 
Default Centos/Linux Disk Caching, might be OT in some ways

MySQL's acquisition was one of the factor, the client wants to keep
everything on the opensource side as far as possible.

On the technical side, all tables are using the InnoDB engine because
myISAM doesn't support either. Also previously during development, it
was discovered that on some particular application/function, MyISAM
caused a heavy load that went away after switching to InnoDB.

Also, as part of my idea was to subsequently put the tables on
different disks for better improvement. Postgresql supports that while
MySQL appears to require all the tables remain on the same filesystem.

There were other considerations that was discussed internally
previously but without digging up docs, off hand, these are the key
factors I can recall that drove the decision to eventually replace
MySQL with Postgresql.


On 1/27/10, Chan Chung Hang Christopher
<christopher.chan@bradbury.edu.hk> wrote:
>
>>> Ah, well #1 on his list then is to figure out what he is running!
>>
>> LOL, I know it sounds quite noobish, coming across like I've no idea
>> what DBMS it is running on. The system currently runs on MySQL but
>> part of my update requirement was to decouple the DBMS so that we can
>> make an eventual switch to postgresql.
>>
>> Hence the solution cannot be dependent on some specific MySQL
>> functionality.
>
>
> mysql's isam tables have a reputation for surviving just about anything
> and great builtin replication support...
>
> postgresql less so (I suspect due to fake fsync/fsyncdata in the days
> before barriers) but maybe things have improved a lot nowadays.
>
> Why are you switching?
> _______________________________________________
> CentOS mailing list
> CentOS@centos.org
> http://lists.centos.org/mailman/listinfo/centos
>
_______________________________________________
CentOS mailing list
CentOS@centos.org
http://lists.centos.org/mailman/listinfo/centos
 
Old 01-27-2010, 12:26 PM
Les Mikesell
 
Default Centos/Linux Disk Caching, might be OT in some ways

Chan Chung Hang Christopher wrote:
>>> Ah, well #1 on his list then is to figure out what he is running!
>> LOL, I know it sounds quite noobish, coming across like I've no idea
>> what DBMS it is running on. The system currently runs on MySQL but
>> part of my update requirement was to decouple the DBMS so that we can
>> make an eventual switch to postgresql.
>>
>> Hence the solution cannot be dependent on some specific MySQL functionality.
>
>
> mysql's isam tables have a reputation for surviving just about anything
>

...or people just didn't notice the loss of unflushed data since there weren't
any relational constraints to break.

--
Les Mikesell
lesmikesell@gmail.com

_______________________________________________
CentOS mailing list
CentOS@centos.org
http://lists.centos.org/mailman/listinfo/centos
 
Old 01-27-2010, 01:30 PM
Ross Walker
 
Default Centos/Linux Disk Caching, might be OT in some ways

On Jan 27, 2010, at 4:07 AM, Noob Centos Admin
<centos.admin@gmail.com> wrote:

> Hi,
>
>> Split the TEXT/BLOB data out of the primary table into tables of
>> their
>> own indexed to the primary table by it's key column.
>
> This is part of what I was planning to do, there are a lot of stuff I
> am planning to split out into their own tables with reference key. The
> problem is I'm unsure whether the added overheads of joins would
> negate the IO benefits hence trying to figure out more about how
> Centos/Linux does the caching.

The idea behind it is you don't need to execute a join if you don't
need the extra data.

>> Think about distributing the parts to different boxes as necessary.
>> You can start with the DBMS which is the logical candidate.
>
> Eventually I figured that would probably have to be done but I don't
> know enough at this point. So I'm taking the approach of optimizing
> stage by stage starting with things I'm more familiar with and less
> likely to muck up totally, i.e.from the app/script side first. Then
> after getting more familiar with the setup, experiment with the
> hardware based solutions.

Good approach, take a look at the queries and indexes first, you can
get a lot of optimization out of tuning queries and/or adding/re-
indexing indexes

>> On the DBMS backend, give it plenty of memory, good storage for the
>> workload and good networking.
>
> Again problem is old server so memory is maxed, drives controller is
> probably not helping.
>
>> On the Apache/PHP side, look for a good DBMS inter-connect and some
>> PHP caching module and of course enough CPU for the PHP code and
>> network for Apache+DBMS inter-connect.
>>
>> If you wanted to split it up even more you could look into some sort
>> of PHP distributed cache/processing system and have PHP processed
>> behind Apache.
>
> Thanks for the heads up, I didn't realize it was possible to separate
> the PHP processing from Apache itself. However, for the time being,
> I'm probably still limited to a single server situation so will keep
> this in mind for future.

I was actually thinking of distributing the caching of the data rather
then the PHP processing, but you can have multiple PHP front-end
servers, one or two mid-line caching (and possibly pre-processing)
servers and then a couple of backend DB servers (replicas) for reads
and a master for writes.

You could even have all this within a single piece of highly redundant
hardware running ESXi or even Xen with PV domains.

If you get a second piece of highly redundant hardware you can then
look at vmotion or live migration to distribute the load between boxes
and setup fail-over, etc.

-Ross

_______________________________________________
CentOS mailing list
CentOS@centos.org
http://lists.centos.org/mailman/listinfo/centos
 
Old 01-27-2010, 01:30 PM
Ross Walker
 
Default Centos/Linux Disk Caching, might be OT in some ways

On Jan 27, 2010, at 4:07 AM, Noob Centos Admin
<centos.admin@gmail.com> wrote:

> Hi,
>
>> Split the TEXT/BLOB data out of the primary table into tables of
>> their
>> own indexed to the primary table by it's key column.
>
> This is part of what I was planning to do, there are a lot of stuff I
> am planning to split out into their own tables with reference key. The
> problem is I'm unsure whether the added overheads of joins would
> negate the IO benefits hence trying to figure out more about how
> Centos/Linux does the caching.

The idea behind it is you don't need to execute a join if you don't
need the extra data.

>> Think about distributing the parts to different boxes as necessary.
>> You can start with the DBMS which is the logical candidate.
>
> Eventually I figured that would probably have to be done but I don't
> know enough at this point. So I'm taking the approach of optimizing
> stage by stage starting with things I'm more familiar with and less
> likely to muck up totally, i.e.from the app/script side first. Then
> after getting more familiar with the setup, experiment with the
> hardware based solutions.

Good approach, take a look at the queries and indexes first, you can
get a lot of optimization out of tuning queries and/or adding/re-
indexing indexes

>> On the DBMS backend, give it plenty of memory, good storage for the
>> workload and good networking.
>
> Again problem is old server so memory is maxed, drives controller is
> probably not helping.
>
>> On the Apache/PHP side, look for a good DBMS inter-connect and some
>> PHP caching module and of course enough CPU for the PHP code and
>> network for Apache+DBMS inter-connect.
>>
>> If you wanted to split it up even more you could look into some sort
>> of PHP distributed cache/processing system and have PHP processed
>> behind Apache.
>
> Thanks for the heads up, I didn't realize it was possible to separate
> the PHP processing from Apache itself. However, for the time being,
> I'm probably still limited to a single server situation so will keep
> this in mind for future.

I was actually thinking of distributing the caching of the data rather
then the PHP processing, but you can have multiple PHP front-end
servers, one or two mid-line caching (and possibly pre-processing)
servers and then a couple of backend DB servers (replicas) for reads
and a master for writes.

You could even have all this within a single piece of highly redundant
hardware running ESXi or even Xen with PV domains.

If you get a second piece of highly redundant hardware you can then
look at vmotion or live migration to distribute the load between boxes
and setup fail-over, etc.

-Ross

_______________________________________________
CentOS mailing list
CentOS@centos.org
http://lists.centos.org/mailman/listinfo/centos
 
Old 01-27-2010, 01:38 PM
Ross Walker
 
Default Centos/Linux Disk Caching, might be OT in some ways

On Jan 27, 2010, at 7:30 AM, Chan Chung Hang Christopher <christopher.chan@bradbury.edu.hk
> wrote:

>
> mysql's isam tables have a reputation for surviving just about
> anything
> and great builtin replication support...
>
> postgresql less so (I suspect due to fake fsync/fsyncdata in the days
> before barriers) but maybe things have improved a lot nowadays.

It has changed a lot since then, where it depended on the OS' fsync
routine to assure data integrity. Now it uses barriers (where
supported).

But if your doing mysql on top of LVM your basically doing the same,
cause LVM (other then current kernels) doesn't support barriers.

Still if you have a battery backed write-caching controller that
negates the fsync risk, LVM or not, mysql or postgresql.

-Ross

_______________________________________________
CentOS mailing list
CentOS@centos.org
http://lists.centos.org/mailman/listinfo/centos
 
Old 01-27-2010, 02:20 PM
Noob Centos Admin
 
Default Centos/Linux Disk Caching, might be OT in some ways

Hi,

On 1/27/10, Ross Walker <rswwalker@gmail.com> wrote:
>
> But if your doing mysql on top of LVM your basically doing the same,
> cause LVM (other then current kernels) doesn't support barriers.
>
> Still if you have a battery backed write-caching controller that
> negates the fsync risk, LVM or not, mysql or postgresql.

This is a bit of a surpise. Am I understanding correctly that running
postgresql or mysql on top of LVM negates any data reliability
measures the DBMS might have in the event of an unexpected shutdown?

I have several servers configured to run LVM on top of MD1 for the
convenience of being able to add more space to a volume in the future.
I didn't realize this was a reliability risk.
_______________________________________________
CentOS mailing list
CentOS@centos.org
http://lists.centos.org/mailman/listinfo/centos
 

Thread Tools




All times are GMT. The time now is 11:42 PM.

VBulletin, Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Content Relevant URLs by vBSEO ©2007, Crawlability, Inc.
Copyright 2007 - 2008, www.linux-archive.org