Geekzone: technology news, blogs, forums
Guest
Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.

View this topic in a long page with up to 500 replies per page Create new topic
1 | 2 | 3
501 posts

Ultimate Geek
+1 received by user: 14


  Reply # 990486 19-Feb-2014 14:20 Send private message

Since people are suggesting a laptop, what about installing a linux distribution with MySQL on a Chromebook?




#include <standard.disclaimer>

845 posts

Ultimate Geek
+1 received by user: 261


  Reply # 990487 19-Feb-2014 14:22 One person supports this post Send private message

What about your internet connection? it might be a significant bottleneck itself.



18 posts

Geek
+1 received by user: 1


  Reply # 990519 19-Feb-2014 14:55 Send private message

andrewNZ: What about your internet connection? it might be a significant bottleneck itself.


The 6 seconds I mentioned is the time taken locally at 100Mbps for the server to respond. Once the page starts loading it loads instantly. It's the "Waiting for..." that takes 6 seconds.

My connection speed is 8 down 1 up and 12ms to Auckland. Sadly I'm outside the UFB upgrade area so that'll be my speed for all eternity. But I don't let that stop me.
http://www.speedtest.net/result/3034560010.png

I compress any pictures I put on pages so that they can be uploaded in a second or less at 1Mbps.
1Mbps is about 125KB/s so I keep pictures to about 40KB using lightroom and they load instantly.

I've accessed the site using 4G from abroad and don't notice any difference in how long the page takes to load. It's the "Waiting for..." that takes all the time. I've watched the CPU usage using the top command and the CPU is maxed out for 6 seconds during the "Waiting for..." and then the page loads in a blink.

Voice Engineer @ Orcon
1794 posts

Uber Geek
+1 received by user: 387

Trusted
Orcon
Subscriber

  Reply # 990521 19-Feb-2014 15:00 Send private message

Oh and it's Wordpress right? So the first place to look (after moving your DB to SQLite) should be plugins... some of these suckers are very inefficient and slow things down a lot.  Killing some plugins got my site from an 11s page load time down to about 3s.



18 posts

Geek
+1 received by user: 1


  Reply # 990525 19-Feb-2014 15:04 Send private message

ubergeeknz: Oh and it's Wordpress right? So the first place to look (after moving your DB to SQLite) should be plugins... some of these suckers are very inefficient and slow things down a lot.  Killing some plugins got my site from an 11s page load time down to about 3s.


It has taken that long to load before I started using any plugins but if you want to know I'm currently using:

Akismet
Who Hit The Page - Hit Counter
WordPress Hit Counter

Can you run through what I lose in mysql lite?



18 posts

Geek
+1 received by user: 1


  Reply # 990538 19-Feb-2014 15:12 Send private message

The process maxing out the CPU is www-data, mysql process is hardly using the CPU it's only hogging memory.

Voice Engineer @ Orcon
1794 posts

Uber Geek
+1 received by user: 387

Trusted
Orcon
Subscriber

  Reply # 990539 19-Feb-2014 15:13 Send private message

KiwiPower:
ubergeeknz: Oh and it's Wordpress right? So the first place to look (after moving your DB to SQLite) should be plugins... some of these suckers are very inefficient and slow things down a lot.  Killing some plugins got my site from an 11s page load time down to about 3s.


It has taken that long to load before I started using any plugins but if you want to know I'm currently using:

Akismet
Who Hit The Page - Hit Counter
WordPress Hit Counter

Can you run through what I lose in mysql lite?


There's a plugin here  - as far as I can tell you lose nothing.  SQLite is simply much leaner than MySQL so it will use less resource on your Pi.  Looks like you'll need to build a new instance with this plugin in play (read the instructions at the plugin page itself) then move your content across.

810 posts

Ultimate Geek
+1 received by user: 59


  Reply # 990666 19-Feb-2014 18:09 Send private message

ubergeeknz:
KiwiPower:
ubergeeknz: Seriously though, why do you want to host it yourself, when there's plenty of cheap and reliable web hosting out there with redundant connectivity, power, etc. and fully managed?


Where's the fun in that?


You have a point.  However I'd look into optimising things a bit before shelling for new hardware, sort out why MySQL is using so much RAM and whether you can reduce it, why page renders are taking so long, see what fat you can trim in terms of other things running on the box, etc.  You'd be surprised (especially with low powered devices) how much you can squeeze out with some optimisation.  The suggestion of ditching MySQL and going to SQLite may be a good place to start.  Oh and something like cloudflare to offload anything not needing dynamic updates.


MySQL isn't really the issue I don't think - given the memory foot print.  Your current device appears by the description to be I/O constrained - I suspect better performance might be achieved by replacing the  HD with an SSD and run a larger swap file.  On top of this one can increase the memory aperture (its a technical way of saying out more ram in).

The query engine in a database is not really that much of an overhead as such - more so I would suggest that the main tuning factor is "distance to the data".  A mechanical drive has a latency way higher than an SSD and assuming one is using Sata III, a higher number of IOPs.  

Hence my feeling is probably an SSD to see what kind of improvement can be gained and at least that can be taken to any new system.
 
I don't know what the size of the database is, but you could consider an experiment using either an SDHC card or a USB memory stick (in a USB slot) since both are effectively SSDs (just in a slightly slower package). I don't know anything about the PI, but I am wondering if it is possible to install Linux on a USB memory stick and store your DB on an SDHC card - giving the ability to remove the normal HD from the system - at least it might reduce power a bit.







TwoSeven

310 posts

Ultimate Geek
+1 received by user: 49


  Reply # 990746 19-Feb-2014 21:16 Send private message

TwoSeven:
MySQL isn't really the issue I don't think - given the memory foot print.  Your current device appears by the description to be I/O constrained - I suspect better performance might be achieved by replacing the  HD with an SSD and run a larger swap file.  On top of this one can increase the memory aperture (its a technical way of saying out more ram in).

The query engine in a database is not really that much of an overhead as such - more so I would suggest that the main tuning factor is "distance to the data".  A mechanical drive has a latency way higher than an SSD and assuming one is using Sata III, a higher number of IOPs.  

Hence my feeling is probably an SSD to see what kind of improvement can be gained and at least that can be taken to any new system.
 
I don't know what the size of the database is, but you could consider an experiment using either an SDHC card or a USB memory stick (in a USB slot) since both are effectively SSDs (just in a slightly slower package). I don't know anything about the PI, but I am wondering if it is possible to install Linux on a USB memory stick and store your DB on an SDHC card - giving the ability to remove the normal HD from the system - at least it might reduce power a bit.



Yes, SD cards and USB sticks are SSDs.  But they often have terrible performance.  Just because you can write a photo to one quickly (a nice big sequential write) doesn't mean using it as your OS drive, let along the backing store for a database, will be a good idea.  Try a USB hard drive, or better still, a "real" SSD in a USB enclosure.

810 posts

Ultimate Geek
+1 received by user: 59


  Reply # 990889 20-Feb-2014 08:46 Send private message

deadlyllama:
TwoSeven:
MySQL isn't really the issue I don't think - given the memory foot print.  Your current device appears by the description to be I/O constrained - I suspect better performance might be achieved by replacing the  HD with an SSD and run a larger swap file.  On top of this one can increase the memory aperture (its a technical way of saying out more ram in).

The query engine in a database is not really that much of an overhead as such - more so I would suggest that the main tuning factor is "distance to the data".  A mechanical drive has a latency way higher than an SSD and assuming one is using Sata III, a higher number of IOPs.  

Hence my feeling is probably an SSD to see what kind of improvement can be gained and at least that can be taken to any new system.
 
I don't know what the size of the database is, but you could consider an experiment using either an SDHC card or a USB memory stick (in a USB slot) since both are effectively SSDs (just in a slightly slower package). I don't know anything about the PI, but I am wondering if it is possible to install Linux on a USB memory stick and store your DB on an SDHC card - giving the ability to remove the normal HD from the system - at least it might reduce power a bit.



Yes, SD cards and USB sticks are SSDs.  But they often have terrible performance.  Just because you can write a photo to one quickly (a nice big sequential write) doesn't mean using it as your OS drive, let along the backing store for a database, will be a good idea.  Try a USB hard drive, or better still, a "real" SSD in a USB enclosure.


The original premise is to reduce power consumption and eek out more performance.  Using the mico-drives is the lowest cost way of achieving this (to see if it works) before committing money to buying the uprated hardware, considering many people have an USB drive/SD card already.  Basically what I am suggesting is test the architecture first before scaling it up.





TwoSeven

310 posts

Ultimate Geek
+1 received by user: 49


  Reply # 990938 20-Feb-2014 09:33 Send private message

TwoSeven:
deadlyllama:
TwoSeven:
MySQL isn't really the issue I don't think - given the memory foot print.  Your current device appears by the description to be I/O constrained - I suspect better performance might be achieved by replacing the  HD with an SSD and run a larger swap file.  On top of this one can increase the memory aperture (its a technical way of saying out more ram in).

The query engine in a database is not really that much of an overhead as such - more so I would suggest that the main tuning factor is "distance to the data".  A mechanical drive has a latency way higher than an SSD and assuming one is using Sata III, a higher number of IOPs.  

Hence my feeling is probably an SSD to see what kind of improvement can be gained and at least that can be taken to any new system.
 
I don't know what the size of the database is, but you could consider an experiment using either an SDHC card or a USB memory stick (in a USB slot) since both are effectively SSDs (just in a slightly slower package). I don't know anything about the PI, but I am wondering if it is possible to install Linux on a USB memory stick and store your DB on an SDHC card - giving the ability to remove the normal HD from the system - at least it might reduce power a bit.



Yes, SD cards and USB sticks are SSDs.  But they often have terrible performance.  Just because you can write a photo to one quickly (a nice big sequential write) doesn't mean using it as your OS drive, let along the backing store for a database, will be a good idea.  Try a USB hard drive, or better still, a "real" SSD in a USB enclosure.


The original premise is to reduce power consumption and eek out more performance.  Using the mico-drives is the lowest cost way of achieving this (to see if it works) before committing money to buying the uprated hardware, considering many people have an USB drive/SD card already.  Basically what I am suggesting is test the architecture first before scaling it up.



But most USB flash sticks and SD cards have terrible performance -- in fact, KiwiPower's current setup is probably using a slow SD card as his filesystem.  Swapping one slow drive for another will not eke out any more performance, all it will do is mean you own two slow flash devices rather than one.

Here's a helpful article on the [url=performance characteristics of cheap flash drives  The Raspberry Pi people themselves have run into this problem, which is why the NOOBS SD cards they sell are specially selected to have good random write performance, even though they're "class 4".  The Samsung SD cards are supposed to be better than most for this sort of thing but they're hard to impossible to buy in NZ.

Voice Engineer @ Orcon
1794 posts

Uber Geek
+1 received by user: 387

Trusted
Orcon
Subscriber

  Reply # 990971 20-Feb-2014 10:16 Send private message

Can attest to this, I have a couple of Samsung 32Gb microSD class 6 and the write performance is phenomenal.  You can buy them from dx.com... I haven't tested them with a Pi though

Also it seems like the Pi is being CPU constrained; but have you looked in top to see what the io wait % is?



18 posts

Geek
+1 received by user: 1


  Reply # 991482 20-Feb-2014 23:14 Send private message



But most USB flash sticks and SD cards have terrible performance -- in fact, KiwiPower's current setup is probably using a slow SD card as his filesystem.  Swapping one slow drive for another will not eke out any more performance, all it will do is mean you own two slow flash devices rather than one.

Here's a helpful article on the [url=performance characteristics of cheap flash drives  The Raspberry Pi people themselves have run into this problem, which is why the NOOBS SD cards they sell are specially selected to have good random write performance, even though they're "class 4".  The Samsung SD cards are supposed to be better than most for this sort of thing but they're hard to impossible to buy in NZ.


You're right I'm using a SD card (this is still an educational experiment not super high-tech) but the swap usage is 0B of about 100MB. So all I can gather from that is that everything it needs must be on the RAM? Or am I wrong in thinking that? If not then what is in that 320MB lol I always thought that was the whole database and it just writes back changes to the SD card.



18 posts

Geek
+1 received by user: 1


  Reply # 991494 20-Feb-2014 23:26 Send private message

ubergeeknz: Can attest to this, I have a couple of Samsung 32Gb microSD class 6 and the write performance is phenomenal.  You can buy them from dx.com... I haven't tested them with a Pi though

Also it seems like the Pi is being CPU constrained; but have you looked in top to see what the io wait % is?


I have had a look at the top command. The www-data process (command apache2) uses about 97% of the CPU during the long "Waiting for..." then it instantly drops back to nothing. So the CPU is definitely under powered for php, and mysql websites. That's why I was wondering what I could build that uses a little more power but has a lot more power and there has been some great suggestions on this!

The 320MB mysql process is using a lot of memory but the system swap usage is still 0B so it an't broke yet so I won't fix that at the moment. I'm probably more likely to break it fixing it. I read that there are some plugins that are broken with mysql lite on WordPress. I don't think any of my plugins are one of them but Murphy's law I'll find the best plugin on this earth and find it incompatible with mysql lite. If in the future it starts digging into the swap, then I'll do the switch. The biggest issue is the CPU time to process hits which is the www-data process not mysql. The memory usage is just a worry but not a problem at this stage. 

810 posts

Ultimate Geek
+1 received by user: 59


  Reply # 991604 21-Feb-2014 09:02 Send private message


I am not a MySQL person, but most databases are ISAM systems - that is, they are just a file divided into blocks with a separate index pointing to the data in the blocks. In that regard, it is unusual for the database to be loaded into memory, instead reads and writes at the fibre (part of a thread) level are made.

So in answer to the question, what is loaded in memory, I suspect that it is likely to be things like the execution engine, partial index, cached data, configuration entries and other such things.

Using an MS Windows analogy, the 100% CPU usage in task manager doesn't always mean that the CPU is actually busy doing processing work, it tends to mean that the CPU is busy waiting for the I/O system to return the data. Typically in windows, one will see an I/O spike shortly after the CPU goes 100%.

From a database tuning perspective we need to be thinking - for each request for data, how long will the I/O operation take and where does it need to go to get it. When we say "distance to data" we are saying how long does it take the electrical signal of the I/O operation to get to the storage (execute any mechanical operations involved if a HD is used) and return. If its L1 cache, L2 cache, L3 cache, L4 Cache (RAM) or L5 Cache (hard disk) the response times will take longer the further the data is away. Add on top of this the processing requirements of the database to identify, compile and execute the query.

For in-depth analysis I would suggest that one needs to know the following values. The time at which the HTTP request was received from the browser, the time the data request is sent to the database, the time it was received back, and the time the response is sent back to the browser. This would given an indication as to whether the bottleneck is within the request processing (CPU) or data processing (database).

Just my thoughts.




TwoSeven

1 | 2 | 3
View this topic in a long page with up to 500 replies per page Create new topic




Twitter »
Follow us to receive Twitter updates when new discussions are posted in our forums:



Follow us to receive Twitter updates when news items and blogs are posted in our frontpage:



Follow us to receive Twitter updates when tech item prices are listed in our price comparison site:




News »

Trending now »
Hot discussions in our forums right now:

Warning: Rage Ahead - Campbell Live and childhood poverty
Created by kawaii, last reply by sir1963 on 3-Sep-2014 11:55 (146 replies)
Pages... 8 9 10


Does NZ need better gun laws?
Created by mattwnz, last reply by heylinb4nz on 3-Sep-2014 11:54 (121 replies)
Pages... 7 8 9


What tyre brand/model to look at ?
Created by Mark, last reply by Oblivian on 2-Sep-2014 21:36 (35 replies)
Pages... 2 3


Judith Collins: I am resigning
Created by Presso, last reply by gzt on 2-Sep-2014 11:42 (109 replies)
Pages... 6 7 8


VideoEZY OnDemand
Created by Andib, last reply by sen8or on 3-Sep-2014 07:45 (64 replies)
Pages... 3 4 5


Cirque du Soleil Cellphone Hijack
Created by myopinion, last reply by PhantomNVD on 1-Sep-2014 18:01 (21 replies)
Pages... 2


Orcon Global Mode launched
Created by freitasm, last reply by shk292 on 1-Sep-2014 11:32 (132 replies)
Pages... 7 8 9


Lightbox press event release
Created by freitasm, last reply by IcI on 30-Aug-2014 17:54 (562 replies)
Pages... 36 37 38



Geekzone Live »
Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.

Are you subscribed to our RSS feed? You can download the latest headlines and summaries from our stories directly to your computer or smartphone by using a feed reader.

Alternatively, you can receive a daily email with Geekzone updates.