GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

InnoDB, 1.800.000 rows and more than 1.500.000 users per day

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • InnoDB, 1.800.000 rows and more than 1.500.000 users per day

    Hi all!
    I'm new to this cool forum

    I have a DELL PowerEdge 1950, 2x 146GB SAS 15k RPM in hardware Raid1 and 4GB ram.

    This server is a mysql server only. Not other services on it.

    I have a database with an innodb table of more than 1.800.000 records growing up quickly and I have a website that has more than 1.500.000 page views per day.

    Each page view does a select on the innodb table.
    When simultaneus connection are low, performance are ok but in the afternoon, when connections grow up, queries took more then 15-20 seconds to be down and site is very very slow.

    How could I optimize this server? Should I do a cluster or a replication? I have 4-5 writes every 10-15 select.

  • #2
    Before you look into any heavy-handed solutions like replication, sharding, or clustering, you should make sure your web application is optimized. And I don't just mean optimizing each query, but also looking for places to put in some caching. MySQL's query cache flushing policy is fairly aggressive, so if you have a good number of writes, you're definitely better off trying to use some external caching like memcached.

    Going with replication, sharding or clustering will require you to make some changes in your application anyway, so starting with some good caching can buy you some time.

    Comment


    • #3
      tom123 wrote on Mon, 01 September 2008 14:57

      Hi all!
      I'm new to this cool forum

      I have a DELL PowerEdge 1950, 2x 146GB SAS 15k RPM in hardware Raid1 and 4GB ram.

      This server is a mysql server only. Not other services on it.

      I have a database with an innodb table of more than 1.800.000 records growing up quickly and I have a website that has more than 1.500.000 page views per day.

      Each page view does a select on the innodb table.
      When simultaneus connection are low, performance are ok but in the afternoon, when connections grow up, queries took more then 15-20 seconds to be down and site is very very slow.

      How could I optimize this server? Should I do a cluster or a replication? I have 4-5 writes every 10-15 select.


      What is your setting for innodb_buffer_pool_size? If you're running only InnoDB, you should optimally set it to use 70% or so of your RAM, so about 3 GB. At a minimum, make sure it's large enough to hold all the indices for your InnoDB tables.

      Comment

      Working...
      X